RowIdToRowIdNum Oracle Function

November 8, 2011 By Leave a Comment

I’m not exactly sure if there’s an existing function in Oracle to convert ROW_ID into a Row_ID Number format. I already know the formula so I created a function. I’ve been using it for a project numerous times so I needed to create this reusable function.


CREATE OR REPLACE FUNCTION SH_RowIdToRowIdNum(v_rowid VARCHAR2) RETURN VARCHAR2
IS
v_pre VARCHAR2(30):='';
v_series VARCHAR2(30):='';
iCtr INTEGER:=0;
v_char VARCHAR2(1):='';
i_val INTEGER:=0;
i_Sum INTEGER:=0;
v_RowNum VARCHAR2(30):='';
BEGIN

v_series := SUBSTR(v_rowid,instr(v_rowid,'-')+1);
FOR iCtr IN 1..LENGTH(v_series)
LOOP
v_char := SUBSTR(v_series,iCtr*-1,1);

IF INSTR('0123456789',v_char) > 0 THEN
i_val := v_char;
ELSE
i_val := ASCII(v_char)-55;
END IF;
i_Sum := i_Sum + (i_val * power(36, iCtr-1));

END LOOP;
v_RowNum := SUBSTR(v_rowid,1,INSTR(v_rowid,'-')) || i_Sum;
RETURN v_RowNum;
END;

sample usage

SELECT SH_RowIdToRowIdNum('1-5GYG0'), SH_RowIdToRowIdNum('1-IB79I')
FROM dual

output

1-9189216 1-30755718

Reference:

http://onlysiebel.blogspot.com/2009/10/what-is-row-id.html

Incoming search terms:

  • equivalent database query for rowidtorowidnum
  • RowIdToRowIdNum
  • RowidtoRowidnum function in siebel
  • rowidtorowidnum length
  • siebel rowidtorowidnum sql
  • sql query for rowidtorowidnum
Filed Under: Blog, Siebel Tagged With: , , , , ,

Oracle Power Function

November 7, 2011 By Leave a Comment

There are some pl/sql functions that you rarely use and we always forget the syntax.And here’s another example — the power function. You know, that’s the n to the power of x (n^x). Again, I feel a bit ashamed for forgetting this one. Here’s the syntax.
[Read more...]

Incoming search terms:

  • oracle power
  • oracle power function
  • oracle power function negative
  • oracle power functions
  • oracle sql display power
  • power function in sql oracle
  • power function pl/sql integers
  • power oracle binary_double ask tom
  • powerc oracle
Filed Under: Blog, Oracle, PL/SQL Tagged With: , ,

Samsung Galaxy S II Office365 ActiveSynch Problem [SOLVED]

September 24, 2011 By Leave a Comment

I just extended my mobile contract with StarHub and since I was given a $100 voucher, I chose Samsung Galaxy S II as my new phone. I didn’t have any plans of getting a new phone but with my HTC Desire acting up lately and a voucher from StarHub, I was somehow ‘forced’ to extend my plan.

Samsung Galaxy S II is one fine smartphone. There are little problems here and there which was brought about by unfamiliarity with a new Android skin but overall, I’m very impressed with this phone. However, there’s one problem that I can’t solve which is setting up the ActiveSync to my Office365 email. Usually, the setting is as simple as below

Email Address : myemail.addr@mycompany.com
Password : MyPassWord%*&
Domain User : myloginid@mycompany.com
Server : m.outlook.com
Use SSL : Checked

I tried to use this in my brand new phone but it can’t connect to the server. I double-checked the settings and made sure that it was the same with that of my HTC Desire but the error is still the same. So after googling, I realized that it’s a common problem. As it turns out, the server name m.outlook.com no longer works with Samsung Galaxy S II. you have to change it to what exactly your Office365 is residing. But how? Here you go.

1. Login to your Office365 email via OWA. In case you don’t know, the URL is http://outlook.com/owa/mycompany.com

2. From the top-right pull-down menu Options, click on “See All Options”.

3. From the account, click on “Settings for POP, IMAP and SMTP address..”

And then you’ll get your server. So now, you’ll have the following settings
Email Address : myemail.addr@mycompany.com
Password : MyPassWord%*&
Domain User : myloginid@mycompany.com
Server : pod502212.outlook.com
Use SSL : Checked

As I have said, m.outlook.com is working on my HTC Desire which is still on Froyo. I guess something broke with the Gingerbread release or maybe, it’s just the Samsung Galaxy S II that has this problem.

Incoming search terms:

  • activesync samsung galaxy2 office365
  • 365 on samsung galaxy
  • exchange activesync server name galaxy
  • office 365 galaxy s ii
  • office365 activesync
  • office 365 android activesync
  • office 365 android galaxy s
  • samsung galaxy s office 365
  • Problem solving for samsung galaxy
  • pop imap htc
Filed Under: Blog, Gadgets

Rowcount on Oracle Dynamic SQLs

August 25, 2011 By Leave a Comment

A good friend asked me on how to capture the number of affected records on his delete statement. The answer would be as

simple as below:

DELETE FROM my_table
WHERE mycolumn = 'mysearch';

v_rec_count := SQL%ROWCOUNT;

On this case, SQL%ROWCOUNT will return the number of affected records in the immediate preceding statement. It is the

equivalent command for @@rowcount in Sybase/SQL Server.

However in my friend’s case, the DELETE statement is part of the dynamic SQL. Obviously, we cannot use the approach

above as it is. Here’s how to capture the affected records in dynamic SQLs.


vSQL := 'DELETE FROM ' || v_SchemaName || '.' || v_TableName || ' WHERE ' || v_Conditions || ‘;’;
EXECUTE IMMEDIATE ‘BEGIN ’ || vSQL || ‘ :z := sql%rowcount; END; ’ USING OUT nRecCount ;

Basically, we have to capture the rowcount from within the dynamic sql. That means you have to pass the variable via “OUT” parameter to flow the value out of the execute immediate. You can’t just fire nRecCount := sql%rowcount after the execute immediate because with that, the last command that ran is the execute immediate. It will return 1 instead of the actual value from the dynamic sql.

Source :

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:497908700346519628

Incoming search terms:

  • oracle rowcount
  • oracle sql rowcount execute immediate
  • %rowcount
  • rowcount in oracle
  • rowcount oracle
  • oracle execute immediate rowcount
  • return rowcount sybase sql
  • return type of rowcount in oracle
  • rowcount in oracle pl sql
  • rowcount oracle sql
Filed Under: Blog, Oracle, PL/SQL

How To Install Setacl in Linux

July 29, 2011 By Leave a Comment

First, there’s no setacl in Linux. Instead, it has setfacl. Take note of the f. Second, setfacl is part of the access control list(acl) package so we install the acl intead of setfacl. In that way, we also install getfacl.
[Read more...]

Incoming search terms:

  • setacl centos
  • linux install setfacl
  • setfacl install
  • ubuntu setacl install
  • ubuntu install getfacl
  • ubuntu setfacl install
  • setacls centos
  • setacl in linux
  • which package setfacl
  • acl linux blog
Filed Under: Blog, Linux Tagged With: , , , ,

How to Upgrade MySQL 5.0 to 5.5 in CentOS

July 25, 2011 By 1 Comment

I upgraded the installed MySQL on my two VPSes about two months ago and I thought I already saved the steps somewhere or at least the saved the URL of the how to. I did not so when I tried to upgrade the Pagudpud Resort‘s (owned by the Jackoleros) database server, I don’t know where to start. The MySQL official website suggests(or at least, that’s how I understand it) that I recompile. I know I did not recompile before so after some fruitless googling, I finally found a working version. But first, thanks to remi for sharing the rpm.
[Read more...]

Incoming search terms:

  • upgrade mysql centos
  • mysql upgrade 5 0 to 5 5
  • update mysql centos
  • centos update mysql
  • centos upgrade mysql
  • mysql upgrade centos
  • mysql 5 0 to 5 5
  • upgrade mysql 5 0 to 5 5 centos
  • mysql 5 0 to 5 5 upgrade
  • yum update mysql 5 5
Filed Under: Blog, Linux, MySQL

LAMP Install on VPS : CentOS5+Apache+MySQL+suPHP

July 24, 2011 By Leave a Comment

Here’s the work-in-progress instructions for setting up a LAMP. It’s been sitting on my draft for ages and I have to publish it because a few of my friends need it.

**** NOTE : If you notice, it’s still a draft. It came from a number of sources but these are the steps I use. So I know it works. **********

[Read more...]

Incoming search terms:

  • centos 5 lamp install
  • setup proftpd vps centos
  • stop suphp
  • suphp configuration centos apache
  • suphp lamp
  • touch /var/lock/subsys/proftpd
  • vps centos how to instyall php version 5
  • yum -y mysql
  • yum install php3
  • yum upgrade apache centos
Filed Under: Apache, Blog, Linux, PHP

Apache Cannot Allocate Memory Problem

July 23, 2011 By Leave a Comment

For the past month, I have been encountering some problems with my WordPress installation. I keep getting the Error 500. I checked the log files and here’s what I found.
(12)Cannot allocate memory: couldn't create child process: /opt/suphp/sbin/suphp for
[Read more...]

Incoming search terms:

  • apache cannot allocate memory
  • Cannot allocate memory: couldn\t create child process: /opt/suphp/sbin/suphp
  • Cannot allocate memory: couldnt create child process: /opt/suphp/sbin/suphp
  • cannot allocate memory
  • cannot allocate memory apache
  • (12)Cannot allocate memory: couldn\t create child process: /opt/suphp/sbin/suphp
  • cannot allocate memory couldnt create child process opt suphp sbin suphp
  • cannot allocate memory couldn\t create child process wordpress
  • cannot allocate memory: couldnt create child process: /opt/suphp/sbin/suphp for apache
  • cannot allocate memory: couldn\t create child process: /opt/suphp/sbin/suphp for /home
Filed Under: Apache, Blog, Unix, WordPress

Missing Custom Fields on WordPress 3.2

July 9, 2011 By 10 Comments

I’m now using WordPress 3.2 in all my new installation. Of course, I already upgraded the rest of my websites. But I found something that’s really strange. The Custom Fields and a few other modules are missing the the post editor. I was thinking maybe it was removed in the latest version or that it may have been affected by an errant plugin. The problem is that I’m using TwentyEleven theme and no plugins and yet the Custom Fields module is missing. It turned out that there’s actually an option in the Screen Options menu to display/hide certain parts of the editor.
[Read more...]

Incoming search terms:

  • custom fields wordpress 3 2
  • wordpress 3 2 custom fields
  • custom fields in wordpress 3 2
  • wordpress 3 2 add custom field
  • always show custom fields wordpress3
  • where are custom fields in wordpress 3
  • where is custom fields wordpress 3 2
  • wordpress 3 2 add custom fields
  • wordpress 3 2 how to show custom fields
  • wordpress 3 2 missing option custom content
Filed Under: Blog, WordPress

Update User Password in MySQL

July 9, 2011 By Leave a Comment

Apparently, quite a number of people are looking into my humble website trying to find out how to change a password of an existing user. Doing this is easy in Oracle with a ALTER USER command. In MySQL, it is different in such a way that you need to update a table. Here’s the command.
[Read more...]

Incoming search terms:

  • mysql alter user
  • alter user change password mysql
  • oracle alter user set password
  • mysql alter user password
  • mysql alter user change password
  • mysql alter password
  • myphp user_password
  • how to set password for existing user in mysql
  • how to find password of an existing users in mysql
  • alter user password
Filed Under: Blog, MySQL
« Older Posts
Newer Posts »