My Experiments with Oracle & Others- By $riram $anka

All About Issues, Errors,News & Latest Findings etc

  • Moderator at

  • Member at

  • Categories

  • Archives

  • December 2019
    M T W T F S S
    « Jun    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Blogs I Follow

Archive for the ‘Functions In oracle’ Category

Spell number In Indian Currency System Using Function (Number into words)

Posted by Sriram Sanka on May 20, 2011


As we all know We can convert number into words Using ‘Jsp’ format string in to_char function.

ind> select to_char(to_date(‘250′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘
—————–
TWO HUNDRED FIFTY

1 row selected.

ind> select to_char(to_date(‘259′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘259’,
———————-
TWO HUNDRED FIFTY-NINE

1 row selected.

ind> select to_char(to_date(‘9999′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘9999′,’J’),’JSP’)
————————————–
NINE THOUSAND NINE HUNDRED NINETY-NINE

1 row selected.

But It has some limitations….See
ind> select to_char(to_date(‘250.25′,’J’),’JSP’) from dual;
select to_char(to_date(‘250.25′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

ind> select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual;
select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484

We can avoid this By creating one Function .
This is similar to Ask Tom Spell number

ind> select spell_number(‘9999999999.99’) from dual;

SPELL_NUMBER(‘9999999999.99’)
——————————————————————————–
nine billion nine hundred ninety-nine million nine hundred ninety-nine thousand
nine hundred ninety-nine

1 row selected.

ind> select into_rupees(‘9999999999.99’) from dual;

INTO_RUPEES(‘9999999999.99’)
——————————————————————————–
Nine Arab Ninety-Nine Crore Ninety-Nine Lakh Ninety-Nine Thousand Nine Hundred N
inety-Nine and Paise Ninety-Nine.

You can find this into_rupees script at Indian Rupees

Thank you 🙂

Posted in Functions In oracle | 7 Comments »

Change In “Wrap” methods Of oracle Versions

Posted by Sriram Sanka on May 4, 2011


As we all know ,

We can wrap the source code using wrapper utility.In the same way Oracle has wrapped some objects in Oracle Database By default.

But  there is some change in its wrapping methods.

For Example..
In Oracle 10g

CREATE OR REPLACE FUNCTION “SYSMAN”.”DECRYPT” wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9000000
1
4
0
15
2 :e:
1FUNCTION:
1DECRYPT:
1CIPHER_TEXT:
1VARCHAR2:
1RETURN:
1RAW_TEXT:
1RAW:
132767:
1DBMS_CRYPTO:
1SRC:
1HEXTORAW:
1TYP:
1ENCRYPT_3DES:
1+:
1CHAIN_CBC:
1PAD_PKCS5:
1KEY:
1GETEMKEY:
1UTL_I18N:
1RAW_TO_CHAR:
1AL32UTF8:
0

0
0
48
2
0 a0 8d 8f a0 b0 3d b4
:2 a0 2c 6a a3 a0 51 a5 1c
81 b0 :3 a0 6b :3 a0 a5 b e
:3 a0 6b 7e :2 a0 6b b4 2e 7e
:2 a0 6b b4 2e e :2 a0 b4 2e
e a5 b d :3 a0 6b a0 6e
a5 b 65 b7 a4 b1 11 68
4f 1d 17 b5
48
2
0 3 7 23 1f 1e 2b 1b
30 34 38 3c 59 44 48 4b
4c 54 43 60 64 68 40 6c
70 74 78 79 7b 7d 81 85
89 8c 8f 93 97 9a 9b a0
a3 a7 ab ae af b4 b6 ba
be bf c4 c6 c7 c9 cd d1
d5 d9 dc e0 e5 e6 e8 ec
ee f2 f4 100 104 106 107 110
48
2
0 1 a 12 21 :2 12 11 b
12 :2 1 7 10 14 13 :2 10 7
5 11 :2 1d 16 1b 24 :2 1b :2 16
1b :2 27 33 34 :2 40 :2 1b 49 4a
:2 56 :2 1b :2 16 :3 1b 16 :2 11 :2 5 c
:2 15 21 2b :2 c 5 :9 1
48
4
0 :7 1 :2 2 :2 1
:7 3 :4 5 :6 6 :11 7
:5 8 :3 5 :9 a :2 4
:7 1
112
4
:3 0 1 :3 0 2
:a 0 43 1 :7 0
5 :2 0 3 4
:3 0 3 :7 0 5
4 :3 0 5 :3 0
4 :3 0 7 9
0 43 2 a
:2 0 14 15 0
9 7 :3 0 8
:2 0 7 d f
:6 0 12 10 0
41 0 6 :6 0
6 :3 0 9 :3 0
2 :3 0 a :3 0
b :3 0 3 :3 0
b 18 1a 17
1b c :3 0 9
:3 0 d :3 0 1e
1f 0 e :2 0
9 :3 0 f :3 0
22 23 0 d
21 25 :3 0 e
:2 0 9 :3 0 10
:3 0 28 29 0
10 27 2b :3 0
1d 2c 11 :3 0
12 :4 0 2f 30
:3 0 2e 31 13
16 33 13 34
0 3f 5 :3 0
13 :3 0 14 :3 0
37 38 0 6
:3 0 15 :4 0 17
39 3c 3d :2 0
3f 1a 42 :3 0
42 1d 42 41
3f 40 :6 0 43
:2 0 2 a 42
46 :3 0 45 43
47 :8 0
1f
4
:3 0 1 3 1
6 1 e 1
c 1 19 2
20 24 2 26
2a 3 1c 2d
32 2 3a 3b
2 35 3e 1
11
1
4
0
46
0
1
14
1
3
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0
3 1 0
c 1 0
2 0 1
0

where as in Oracle 11.2.0 the same function wrapped as

CREATE OR REPLACE FUNCTION “SYSMAN”.”DECRYPT” wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
1d9 16d
b7I22qBmrA5hngZ8PfHmiTb8O+UwgzJpNdxqfC9dbuqKO2FNZvfno/Ns8QsgqwomCzHLyLco
3aSo0eQcxmPI6Euw98YDgtrXDlO37aycscH+csQoFWNIszAHrssarFvJMNlZYLbOiNZIfJ2O
fPguqzMwjjlnm37HxGvpCYbc5c4137hhVayrRctxbcWTikHjZiJR0zVUFPpYrxC9n6AmTSg8
x4InsbDh78p8DHv99jCTDTUFru7JrX2SIDf9MpNiLmnpj8c3MyOhQN8amzSLH1/JFR4AxMS1
MM1SVgqke3o2ohYAkBJWC4PfU//ZEzvyC0TtT+xJsHs8S+BMQbLiMeKs38MrcRD7gl5SzA==

seems its more difficult than 10g.
for Examples see these lines from 10g code.

1FUNCTION:
1DECRYPT:
1CIPHER_TEXT:
1VARCHAR2:
1RETURN:
1RAW_TEXT:
1RAW:
132767:
1DBMS_CRYPTO:
1SRC:
1HEXTORAW:
1TYP:
1ENCRYPT_3DES:
1+:
1CHAIN_CBC:
1PAD_PKCS5:
1KEY:
1GETEMKEY:
1UTL_I18N:
1RAW_TO_CHAR:
1AL32UTF8:

which can give us some clues to re write it as below

create or replace FUNCTION DECRYPT(CIPHER_TEXT IN VARCHAR2)
RETURN VARCHAR2 AS
RAW_TEXT RAW(32767);
BEGIN
IF CIPHER_TEXT IS NULL THEN
RETURN NULL;
END IF;

RAW_TEXT := SYS.DBMS_CRYPTO.DECRYPT(

SRC=>HEXTORAW(CIPHER_TEXT),
TYP=>SYS.DBMS_CRYPTO.ENCRYPT_3DES+SYS.DBMS_CRYPTO.CHAIN_CBC+SYS.DBMS_CRYPTO.PAD_PKCS5,
KEY=>GETEMKEY());

RETURN SYS.UTL_I18N.RAW_TO_CHAR(RAW_TEXT, ‘AL32UTF8‘);
END;

Similarly some other methods.

Hope you Enjoyed it. 😉

Posted in Functions In oracle | 3 Comments »

Reverse function in oracle

Posted by Sriram Sanka on December 9, 2009


SQL> select reverse('sriram oracle DBA') from dual;

REVERSE('SRIRAMOR
-----------------
ABD elcaro marirs

As this reverse funcction is undocumented we can achive the same using a procedure........

SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /

Procedure created.

SQL> exec rev('sriram oracle DBA');
ABD elcaro marirs

PL/SQL procedure successfully completed.

SQL>

sriram.

Source : http://www.oracle.com/code tips

Posted in Functions In oracle | Leave a Comment »

 
Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBAKevlar

Tips, tricks, (and maybe a few rants) so more DBAs become bulletproof!

%d bloggers like this: