link 01

http://mahmoudoracle.blogspot.com/2012/09/oracle-forms-print-report-directly.html  --Reports Run
http://www.cnblogs.com/quanweiru/category/709059.html 

Oracle SQL Magic I Love You So Much Honey..... Happy Coding..

SELECT INITCAP (CHR(73)||' '||CHR(76)||CHR(79)||CHR(86)||CHR(69)||' '||CHR(89)||CHR(79)||CHR(85)||' '||CHR(83)||CHR(79)||' '||CHR(77)
||CHR(85)||CHR(67)||CHR(72)||' '||CHR(72)||CHR(79)||chr(78)||CHR(69)||chr(89)||'.'||'.'||'.'||'.'||'.'||' '||CHR(72)||CHR(65) ||CHR(80)||CHR
(80)||CHR(89)||' '||CHR(67)||CHR(79)||CHR(68)||CHR(73)||CHR(78)||CHR(71))||'.'||'.' "Message From Programmer's GF" FROM DUAL



Message From Programmer's GF
--------------------------------------------
I Love You So Much Honey..... Happy Coding..

TWO TYPE WINDOWS

Modal Window:

A modal window is a restricted window that the user must respond to before moving the input focus to another window. Modal windows:

• Must be dismissed before control can be returned to a modeless window
• Become active as soon as they display
• Require a means of exit or dismissal

Modeless Window:

A modeless window is an unrestricted window that the user can exit freely. Modeless windows:

• Can display many at once
• Are not necessarily active when displayed
• Are the default window type

TWO TYPE WINDOWS 

A dialog window can be moved outside the application window
while a document window cannot be moved

How to Center Oracle Forms Windows 10g

Message for check join date

begin
if :join_dt <=sysdate then
    message('Check Join Date!!');
else
    message('Join date must be less then or equal Current date!!');
    message('Join date must be less then or equal Current date!!');
    raise form_trigger_failure;
   
end if;
end;

Test 02

select sysdate from dual;

select add_months(last_day(sysdate),-1)+1 firt_day ,last_day(sysdate) lastday from dual;

select NEXT_DAY(trunc(SYSDATE), ‘&Day’) from dual;

select to_char(SYSDATE,’dd-mm-yy hh:mi:ss’) from dual;

select (last_day(add_months(trunc(SYSDATE),-to_char(trunc(sysdate),’MM’)))+1) first_day ,
last_day(add_months(trunc(SYSDATE),-to_char(trunc(sysdate),’MM’)))+1 last_day
from dual;

select trunc(sysdate) from dual;
select trunc(sysdate,’MON’) First_day_of_month from dual;
select trunc(sysdate,’YEAR’) First_day_of_year from dual;
SELECT TRUNC (SYSDATE , ‘YEAR’) FROM DUAL;

SELECT ADD_MONTHS(TRUNC (SYSDATE , ‘YEAR’),12)-1 FROM DUAL;

select trunc(sysdate,’MON’) from dual;

SELECT ADD_MONTHS (TRUNC (SYSDATE,’YEAR’), -12) FROM DUAL;

SELECT add_months(TRUNC(sysdate,’YYYY’),0) “First Day”,
add_months(TRUNC(sysdate,’YYYY’)-1,12) as Lasst_Day
FROM dual;

employee id already exits

DECLARE
    v varchar2(6);
BEGIN
    SELECT EMP_ID INTO V FROM EMP_MAST WHERE EMP_ID=:emp_mast.EMP_ID;
    IF V >0 THEN
        MESSAGE('Employee ID Already Exist !!');
        MESSAGE('Employee ID Already Exist !!');   
    else
        next_field;
    end if;
    exception when others then raise form_trigger_failure;
       
end;

Oracle 6i Report Run

---6i

Declare
  pl_id ParamList;
BEGIN
  pl_id := Get_Parameter_List('tmpdata');
  IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List( pl_id );
  END IF;
  pl_id := Create_Parameter_List('tmpdata');

 -- Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');
   Run_Product(REPORTS, :global.path||'reports\ledger_cover_letter.rep', SYNCHRONOUS, RUNTIME,
           FILESYSTEM, pl_id, NULL);
END;

------------------------

declare  pl_id ParamList;
BEGIN
  pl_id := Get_Parameter_List('tmpdata');
  IF NOT Id_Null(pl_id) THEN
    Destroy_Parameter_List( pl_id );
  END IF;
   pl_id := Create_Parameter_List('tmpdata');
    Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'no');
    Add_Parameter(pl_id, 'P_SERIALNO', TEXT_PARAMETER, :get_paas_mast.SERIALNO);
  --  Run_Product(REPORTS, :GLOBAL.path||'INVENTORY\Reports\get_pass_rep.rep', ASYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);
 -- Run_Product(REPORTS, 'D:\Star_ceramics\Get_pass\get_pass_rep.rep', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);
  Run_Product(REPORTS, '\\10.10.101.11\Star_Ceramics\Star_ceramics\menu\Get_pass\reports\get_pass_rep.rep', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);

END;

toolkit both encrypt decrypt

create or replace PACKAGE toolkit AS
  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW;
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2;
END toolkit;

/

create or replace PACKAGE BODY toolkit AS
  g_key     RAW(32767)  := UTL_RAW.cast_to_raw('12345678');
  g_pad_chr VARCHAR2(1) := '~';
  PROCEDURE padstring (p_text  IN OUT  VARCHAR2);
  -- --------------------------------------------------
  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW IS
  -- --------------------------------------------------
    l_text       VARCHAR2(32767) := p_text;
    l_encrypted  RAW(32767);
  BEGIN
    padstring(l_text);
    DBMS_OBFUSCATION_TOOLKIT.desencrypt(input          => UTL_RAW.cast_to_raw(l_text),
                                        key            => g_key,
                                        encrypted_data => l_encrypted);
    RETURN l_encrypted;
  END;
  -- --------------------------------------------------
  -- --------------------------------------------------
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2 IS
  -- --------------------------------------------------
    l_decrypted  VARCHAR2(32767);
  BEGIN
    DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key   => g_key,
                                        decrypted_data => l_decrypted);

    RETURN RTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);
  END;
  -- --------------------------------------------------
  -- --------------------------------------------------
  PROCEDURE padstring (p_text  IN OUT  VARCHAR2) IS
  -- --------------------------------------------------
    l_units  NUMBER;
  BEGIN
    IF LENGTH(p_text) MOD 8 > 0 THEN
      l_units := TRUNC(LENGTH(p_text)/8) + 1;
      p_text  := RPAD(p_text, l_units * 8, g_pad_chr);
    END IF;
  END;
  -- --------------------------------------------------
END toolkit;

Stock balance

Stock balance 

select itemcode,itemname, sum(bal_qty) qty
from (
select itemcode,itemname, qty bal_qty  from br_receipt_detail
union all
select itemcode,itemname, -qty bal_qty  from br_issue_detail
)
group by itemcode,itemname

 Stock balance

select itemcode,itemname,sum(qty)
    from (
     select req_detail.itemcode,item.itemname,req_detail.qty from store.req_detail,store.item
    where item.itemcode=req_detail.itemcode
    and req_detail.reqno=500000137
    union all
    select itemcode,itemname,-qty from br_receipt_detail, br_receipt_master
    where br_receipt_master.recpt_no=br_receipt_detail.recpt_no
    and prs_no='500000137')
    group by itemcode,itemname

WHEN-NEW-BLOCK-INSTANCE detail block

---WHEN-NEW-BLOCK-INSTANCE  detail block

declare
    cursor c1 is
    select req_detail.itemcode,item.itemname,req_detail.qty from store.req_detail,store.item
    where item.itemcode=req_detail.itemcode
    and req_detail.reqno=:br_receipt_master.prs_no;
    a number;
begin
    a:=1;
    for cr in c1 loop
        :br_receipt_detail.sl_no :=a;
        :br_receipt_detail.itemcode :=cr.itemcode;
            :br_receipt_detail.itemname:=cr.itemname;
                :br_receipt_detail.qty:=cr.qty;
        a:=a+1;
    next_record;
    end loop;
    first_record;
                    end;

 ---WHEN-NEW-record-INSTANCE  detail block

:sl_no := :system.trigger_record; 

slno in oracle  forms
1
2
3
4
5

flashback table in Oracle

create table ttt (id varchar2(10),nm varchar2(50));

drop table TTT;

flashback table e to before drop;

Set Column

SQL> column ename   heading "Employee"
SQL> column sal     heading "Salary"
SQL> column raise   heading "Raise"

column department_name format a30 heading "Department Names"

column ename format a13 heading "Employee Name"

 column department_name format a13
SQL>  column employees format a63 word_wrapped  

SQL> COLUMN product_price FORMAT $99.99  

SQL>> SET SQLPROMPT "QUIUM>>" 

SQL> --Set the prompt. Use predefined variables to access login
SQL> --user name and net service name
SQL> SET SQLPROMPT "&_user@&_connect_identifier(&database_name) >"

JAVA2S@XE(XE) >SET TERMOUT ON

JAVA2S@XE(XE) >SET FEEDBACK ON 

show parameters block 

A dropped table is preserved in the recycle bin until purged. 

 purge recyclebin; 

select object_name, original_name, type from recyclebin; 

--------------------------------------------------------

purge recyclebin
SQL> SQL> purge recyclebin; Recyclebin purged. SQL> drop table vacation; SQL> SQL> select object_name, original_name, droptime 2 from recyclebin; no rows selected SQL> SQL> flashback table vacation to before drop;


---------------------------------------------------------

 SQL>>conn sqlplus / as sysdba

SQL>> conn sqlplus sys/password as sysdba 

set pagesize 10;
SQL>
SQL> show feedback;

 set linesize 54

 SET PAGESIZE 0
SQL> SET FEEDBACK OFF
SQL> SELECT emp_id || ',"'
  2         || ename || '",' ||
  3         rate
  4  FROM emp;

 SQL> SET FEEDBACK ON

LTRIM 

- Ltrim: removes a set of characters from the left of a string.
SQL>
SQL>
SQL> -- LTRIM stands for "left trim."
SQL> -- The general format for this function is:
SQL>
SQL> -- LTRIM(string, characters_to_remove)
SQL>
SQL> SELECT LTRIM('...Mitho', '.') FROM dual;

LTRIM
-----
Mitho
 

LTRIM function removes leading characters
SQL> SQL> --LTRIM function removes leading characters SQL> SQL> SELECT LTRIM('ST' , 'STEVENS') AS TRIM FROM Dual; T -



select '"' || ltrim( ' Some String' ) || '"' "A String"

  
SQL>
SQL> select '"' || ltrim( '    Some String' ) || '"' "A String" from dual;
A String
-------------
"Some String"

1 row selected.

LTRIM('ABCBCA12345', 'ABC')
SQL> SQL> SELECT LTRIM('ABCBCA12345', 'ABC') 2 FROM DUAL; LTRIM ----- 12345

 select ename
  2  ,      ltrim(ename,'S') as ltrim_s
  3  ,      rtrim(ename,'S') as rtrim_s
  4  from   emp
  5  where  deptno = 20;

ENAME    LTRIM_S  RTRIM_S
-------- -------- --------
Tom      Tom      Tom
Jane     Jane     Jane
Smart    mart     Smart
Ana      Ana      Ana
Fake     Fake     Fake

 RTRIM

Rtrim: removes a set of characters from the right of a string
SQL> -- RTRIM stands for "right trim."SQL> -- The general format for this function is: SQL> SQL> -- RTRIM(string, characters_to_remove) SQL> SQL> SQL> SELECT RTRIM('Computers', 's') FROM dual; RTRIM('C -------- Computer

RTRIM function removes trailing characters

SQL> --RTRIM function removes trailing characters.
SQL>
SQL> SELECT RTRIM('S' , 'STEVENS') AS TRIM FROM Dual;

T
-

Use rtim to remove space
SQL> SQL> select '"' || rtrim( 'Some String ' ) || '"' "A String" 2 from dual 3 / A String ------------- "Some String" 1 row selected.

 Rtrim char type column data

SELECT 'Item  ' ||
  2         RTRIM(item_id) ||
  3         ' is described as a ' ||
  4         RTRIM(item_desc) ||
  5         '.'  "Item Description Sentence"
  6  FROM   old_item;

Item Description Sentence
-----------------------------------------------------------------------
Item  LA-101 is described as a Can, Small.
Item  LA-102 is described as a Can, Large.

 CHR

CHR() function returns a character corresponding to the number passed in as the argument
SQL> Syntax: CHR( [USING NCHAR_CS]) SQL> 
SQL>
 SELECT 2 CHR(65) "DB_CS", 3 CHR(65 USING NCHAR_CS) "N_CS" 4 FROM dual; D N - - A A

SQL> select chr(71)||chr(114)||chr(111)||chr(111)||chr(118)||chr(121) "Cool!"
  2    from dual;

Cool!
------
Groovy


CONCAT

Simple demo for CONCAT function: concatenate two strings together
SQL> SELECT CONCAT('Oracle','Server')


AS Concat FROM Dual; CONCAT ------------ OracleServer

CONCAT() concatenates two (or more) strings
SQL> Syntax: CONCAT(,) SQL> SQL> SELECT 2 CONCAT('Hello ', ' ,world') result from dual; RESULT ---------------------------------------------------------------- Hello ,world

 DUMP

Union all with dump reverse
SQL> SQL> SQL> select 90101, dump(reverse(90101),16) from dual 2 union all 3 select 90102, dump(reverse(90102),16) from dual 4 union all 5 select 90103, dump(reverse(90103),16) from dual 6 / 90101 DUMP(REVERSE(90101),1 ---------- --------------------- 90101 Typ=2 Len=4: 2,2,a,c3 90102 Typ=2 Len=4: 3,2,a,c3 90103 Typ=2 Len=4: 4,2,a,c3 3 rows selected.

Syntax: DUMP(expression [,RETURN_FORMAT [START_POSITION] [LENGTH]])
SQL> SQL> SELECT 2 DUMP('ABC',1010) type_info 3 FROM dual;



START_POSITION specifies which character in the supplied string to start with, and the optional length specifies how many characters to consider.

  
SQL>
SQL> SELECT
  2     DUMP('ABC',1010,1)  type_info
  3  FROM dual;

TYPE_INFO
------------------------------------------------
Typ=96 Len=3 CharacterSet=WE8MSWIN1252: 65,66,67

   
  
dump table column

select dump(emp_id,8) from emp_mast;

dump(90101,16)
SQL> SQL> SQL> select 90101, dump(90101,16) from dual 2 union all 3 select 90102, dump(90102,16) from dual 4 union all 5 select 90103, dump(90103,16) from dual 6 / 90101 DUMP(90101,16) ---------- --------------------- 90101 Typ=2 Len=4: c3,a,2,2 90102 Typ=2 Len=4: c3,a,2,3 90103 Typ=2 Len=4: c3,a,2,4




dump(reverse(90101),16)

   

SQL>
SQL>
SQL> select 90101, dump(reverse(90101),16) from dual
  2  union all
  3  select 90102, dump(reverse(90102),16) from dual
  4  union all
  5  select 90103, dump(reverse(90103),16) from dual
  6  /

     90101 DUMP(REVERSE(90101),1
---------- ---------------------
     90101 Typ=2 Len=4: 2,2,a,c3
     90102 Typ=2 Len=4: 3,2,a,c3

String||String: concatenates two strings

SQL> -- String||String
SQL>
SQL> -- This function concatenates two strings.
SQL>
SQL> -- String||String
SQL> SELECT 'This' || ' is '|| 'a' || ' concatenation' FROM dual;

'THIS'||'IS'||'A'||'CON
-----------------------
This is a concatenation

Converting DATE to another language

SQL>
SQL>
SQL> -- Converting DATE to another language.
SQL> SELECT TO_CHAR(SYSDATE,'MONTH DD YY','NLS_DATE_LANGUAGE=german') "German Date" from DUAL;

German Date
---------------
AUGUST    31 06

Converting German date to DATE format
SQL> SQL> -- Converting German date to DATE format. SQL> SELECT TO_DATE('februar-23','MONTH-DD','NLS_DATE_LANGUAGE=german') "Converted" from DUAL; Converted --------- 23-FEB-06

TRANSLATE

all dashes ("-") were dropped from the translated string, and the digit "9" was translated into an asterisk.

 
SQL>
SQL> SELECT
  2       TRANSLATE('4428-2174-5093-1501'
  3                ,'0123456789-'
  4                ,'0123456789') numbers_only
  5  FROM dual;

NUMBERS_ONLY
----------------
4428217450931501

 

SQL>  update nchar_samples
  2       set char_data = translate( nchar_data using char_cs )

all dashes ("-") were dropped from the translated string, and the digit "9" was translated into an asterisk.
SQL> SQL> SELECT 2 TRANSLATE('4428-2174-5093-1501' 3 ,'0123456789-' 4 ,'0123456789') numbers_only 5 FROM dual; NUMBERS_ONLY ---------------- 4428217450931501

TRANSLATE(): convert numbers
SQL> SQL> -- TRANSLATE(): convert numbers SQL> SQL> SELECT TRANSLATE(12345, 2 54321, 3 67890) FROM dual; TRANS ----- 09876



Translate: replaces a string character by character

SQL> -- Translate: replaces a string character by character.
SQL>
SQL> -- RANSLATE(string, characters_to_find, characters_to_replace_by)
SQL> SELECT TRANSLATE('Mississippi', 's','S') FROM dual;

TRANSLATE('
-----------
MiSSiSSippi


           
       

Using the TRANSLATE() Function: to encode and decode
SQL> SQL> --Using the TRANSLATE() Function SQL> SQL> --TRANSLATE(x, from_string, to_string) to convert the occurrences of characters in from_string found in x to corresponding characters in to_string. SQL> SQL> --TRANSLATE(): shift each character in the string SECRET MESSAGE: MEET ME IN THE PARK by four places to the right: A becomes E, B becomes F SQL>

SQL> SELECT TRANSLATE('SECRET MESSAGE: MEET ME IN THE PARK', 2 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 3 'EFGHIJKLMNOPQRSTUVWXYZABCD') FROM dual; TRANSLATE('SECRETMESSAGE:MEETMEINTH ----------------------------------- WIGVIX QIWWEKI: QIIX QI MR XLI TEVO SQL> SQL> --select translate('www.java2s.com','wjavscom','abced123') from dual; SQL>

Test 03

https://plus.google.com/u/0/communities/111151329130758791320  ---image see

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle - OraDb11g_home1\Application Development\


---more question---


http://www.orafaq.com/forum/?SQ=0&t=search&srch=call+report+form+6i&btn_submit=Search&field=all&forum_limiter=17&attach=0&search_logic=AND&sort_order=DESC&author=

duplicate row

select a.serialno,b.seq from issue_master a,issue_detail b where a.mdate = '12-SEP-18' and a.trcode||a.fyear||a.serialno=b.trcode||b.fyear||b.serialno group by a.serialno, b.seq having COUNT(seq)=2;  SERIALNO        SEQ---------- ----------     15134         26

DBA Scripts

select * from v$version;

select version from v$instance;

All Trigger in Oracle Forms

You should call this trigger in when-new-record-instance against the data block.

:ROW_NUM := :system.trigger_record;

where :ROW_NUM is the field name i.e (:blockname.field_name)

Deleting Duplicate Rows in Oracle

CREATE TABLE tbl_test(

  SER_NO NUMBER,
   FST_NM VARCHAR2(30),
   DEPTID NUMBER,
   CMNT   VARCHAR2(30));

INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy');
INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(3, 'ccccc', 2005, 'zzz');
INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy');

1. Using MIN(rowid) : The most common method of removing duplicate rows.

DELETE FROM tbl_test
      WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                              FROM tbl_test
                          GROUP BY ser_no, fst_nm, deptid, cmnt);

Comment: This will take hours & hours if the table is large (records in million).
2. Using MIN(rowid) & Join: More or less the same as first one

DELETE FROM tbl_test t
      WHERE t.ROWID NOT IN (SELECT MIN (b.ROWID)
                              FROM tbl_test b
                             WHERE b.ser_no = t.ser_no
                               AND b.fst_nm = t.fst_nm
                               AND b.deptid = t.deptid
                               AND b.cmnt   = t.cmnt);

3. Using Subquery: This is an interesting one

DELETE FROM tbl_test
      WHERE ser_no IN (SELECT ser_no FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND fst_nm IN (SELECT fst_nm FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND deptid IN (SELECT deptid FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND cmnt   IN (SELECT cmnt   FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND ROWID NOT IN (SELECT   MIN (ROWID) 
                              FROM tbl_test
                          GROUP BY ser_no, fst_nm, deptid, cmnt
                            HAVING COUNT (*) > 1)

Comment: A complicated way of performing the same task. Not efficient.
4. Using Nested Subqueries:

DELETE FROM tbl_test a
      WHERE (a.ser_no, a.fst_nm, a.deptid, a.cmnt) IN (SELECT b.ser_no, b.fst_nm, b.deptid, b.cmnt
                                                     FROM tbl_test b
                                                    WHERE a.ser_no = b.ser_no
                                                      AND a.fst_nm = b.fst_nm
                                                      AND a.deptid = b.deptid
                                                      AND a.cmnt   = b.cmnt
                                                      AND a.ROWID  > b.ROWID);

Comment: Will work but for large tables, this is not efficient.
5. Using Analytic Fucntions:

DELETE FROM tbl_test
      WHERE ROWID IN (
               SELECT rid
                 FROM (SELECT ROWID rid,
                              ROW_NUMBER () OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn
                         FROM tbl_test)
                WHERE rn <> 1);

Comments: This is by far one of the best solutions if the table is really really large. Using the invaluable power of Analytics.
6. CREATE-DROP-RENAME:  This one is a more appropriate solution in terms of resource usage in the sense that if we have a really large table, then with delete option we are generating a huge amount of UNDO information.(if we want to rollback for any reason). Even worst, the rollback segment may not be large enough to hold your UNDO information and give error. CTAS comes handy in this case.
Step 1.

CREATE  TABLE tbl_test1 NOLOGGING
   AS
   SELECT tbl_test .*
     FROM tbl_test tbl_test
    WHERE ROWID IN (SELECT rid 
                      FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn 
                              FROM tbl_test)
                     WHERE rn=1);

Step 2.

DROP TABLE tbl_test; --drop the original table with lots of duplicate

Step 3.

RENAME tbl_test1 TO tbl_test; -- your original table without duplicates.

Generating Random Data in Oracle

SELECT ROWNUM
FROM DUAL
CONNECT BY LEVEL < 10000;

/

SELECT     LEVEL                                                       empl_id,
           MOD (ROWNUM, 50000)                                         dept_id,
           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2)                 salary,
           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F')   gender,
           TO_DATE (   ROUND (DBMS_RANDOM.VALUE (1, 28))
                    || '-'
                    || ROUND (DBMS_RANDOM.VALUE (1, 12))
                    || '-'
                    || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
                    'DD-MM-YYYY'
                   )                                                   dob,
           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50))        address
      FROM DUAL
CONNECT BY LEVEL < 10000;
 
 

Number To Word in oracle

 How can you convert a number into words using Oracle Sql Query? What I mean by Number to Word is:

12 = Twelve
102 = One Hundred Two
1020 = One Thousand Twenty

Here’s a classy query which will convert number into words.Please see the query below:

select to_char(to_date(:number,'j'),'jsp') from dual;

If I pass 234 in number, then the output will : two hundred thirty-four

SELECT TO_CHAR (TO_DATE (234, 'j'), 'jsp') FROM DUAL;
//Output: two hundred thirty-four

SELECT TO_CHAR (TO_DATE (24834, 'j'), 'jsp') FROM DUAL;
//Output: twenty-four thousand eight hundred thirty-four

SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
//Output: two million four hundred forty-seven thousand eight hundred thirty-four

So how the query works? Well here’s why:
If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713 BC), basically this date is been used for astronomical studies.
So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.
If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.
Now to_char(to_date(:number,'j'),'jsp'), jsp = Now; take that date(to_date(:number,'j')) and spell the julian number it represents

Limitation & workaround

There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:

ORA-01854: julian date must be between 1 and 5373484

To cater the above problem ,create a function ,and with little trick with j->jsp ,you can fetch the desired result.

CREATE OR REPLACE FUNCTION spell_number (p_number IN NUMBER)
   RETURN VARCHAR2
AS
   TYPE myArray IS TABLE OF VARCHAR2 (255);

   l_str myArray
         := myArray ('',
                     ' thousand ',
                     ' million ',
                     ' billion ',
                     ' trillion ',
                     ' quadrillion ',
                     ' quintillion ',
                     ' sextillion ',
                     ' septillion ',
                     ' octillion ',
                     ' nonillion ',
                     ' decillion ',
                     ' undecillion ',
                     ' duodecillion ');

   l_num      VARCHAR2 (50) DEFAULT TRUNC (p_number);
   l_return   VARCHAR2 (4000);
BEGIN
   FOR i IN 1 .. l_str.COUNT
   LOOP
      EXIT WHEN l_num IS NULL;

      IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
      THEN
         l_return :=
            TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
                     'Jsp')
            || l_str (i)
            || l_return;
      END IF;

      l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);
   END LOOP;

   RETURN l_return;
END;
/


SELECT spell_number (53734555555585) FROM DUAL;