Function Digit To Word

Following the function for convert from digit to Word :

Create or Replace Function Terbilang(Digit varchar2) Return Varchar2 is
Tampung99 varchar2(100);

# (Author : Ida Bagus Enderajana)
kel_1    varchar2(30);
kel_2    varchar2(30);
kel_3    varchar2(30);
kel_4    varchar2(30);
kel_5    varchar2(30);
hasil   varchar2(3000);
cekdigit number;
sisabagi number;
Begin
tampung99 :=”;
kel_1:=”;
kel_2:=”;
kel_3:=”;
kel_4:=”;
kel_5:=”;
cekdigit :=length(digit);
sisabagi :=mod(cekdigit,3);

if sisabagi=0 then
tampung99:=digit;
elsif sisabagi=1 then
tampung99:=’00′||digit;
elsif sisabagi=2 then
tampung99:=’0′||digit;
end if;

if length(tampung99)=3 then
kel_1:=substr(tampung99,1,3);
hasil:=basecurr_triple_number_words(kel_1);

elsif length(tampung99)=6 then
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
if kel_1=’001′ and kel_2=’000′ then
hasil:=’seribu’;
elsif kel_1=(‘001′) and kel_2 <>(‘000′) then
hasil:=’seribu ‘||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>(‘001′) and kel_2 <>(‘000′) then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ ribu ‘||basecurr_triple_number_words(to_number(kel_2));
elsif kel_1 <>’001′ and kel_2=’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ ribu ‘;
end if;

elsif length(tampung99)=9 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
if kel_1 <>’000′ then
begin
if kel_2 <>’000′ and kel_2 <>’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_2)||’ ribu ‘||basecurr_triple_number_words(kel_3);
elsif kel_2 =’000′ and kel_3=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_3);
elsif kel_2=’000′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_3);
elsif kel_2=’001′ and kel_3=’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ juta ‘||’ seribu’;
elsif kel_2=’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(to_number(kel_1))||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(to_number(kel_3));
elsif kel_2 =’001′ and kel_3 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_3);
elsif kel_2 <>’000′ and kel_2 <>’001′ and kel_3 <>’000′ or kel_3=’000′   then
hasil:=basecurr_triple_number_words(kel_1)||’ juta ‘||basecurr_triple_number_words(kel_2)||’ ribu ‘||basecurr_triple_number_words(kel_3);
end if;
end;
end if;
end;

elsif length(tampung99)=12 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
if kel_1 <>’000′ and kel_2 <>’000′ then
begin
if kel_3 <>’000′ and kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 =’000′ and kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 =’000′ and kel_4=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_4);
elsif kel_3=’001′ and kel_4=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||’ seribu ‘;
elsif kel_3=’001′ and kel_4 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 <>’000′ and kel_3 =’001′ and kel_4 <>’000′ or kel_4=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_2)||’ juta ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
end if;
end;
elsif kel_1 <>’000′ and kel_2=’000′ then
begin
if kel_3 =’000′ and kel_4=’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_4);
elsif kel_3 <>’001′ or kel_3 <>’000′ and kel_4 =’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ ribu ‘||basecurr_triple_number_words(kel_4);
end if;
end;
end if;
end;
elsif length(tampung99)=15 then
begin
kel_1:=substr(tampung99,1,3);
kel_2:=substr(tampung99,4,3);
kel_3:=substr(tampung99,7,3);
kel_4:=substr(tampung99,10,3);
kel_5:=substr(tampung99,13,3);
if kel_1 <>’000′ and kel_2 <>’000′ and kel_3 <>’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2 <>’000′ and kel_3 =’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_2)||’ milyar ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2 =’000′ and kel_3 <>’000′ then
begin
if kel_4 <>’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 =’000′ and kel_5=’000′  then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_5);
elsif kel_4=’001′ and kel_5=’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘;
elsif kel_4=’001′ and kel_5 <>’000′ then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||’ seribu ‘||basecurr_triple_number_words(kel_5);
elsif kel_4 <>’000′ and kel_4 =’001′ and kel_5 <>’000′ or kel_5=’000′   then
hasil :=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_3)||’ juta ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
elsif kel_1 <>’000′ and kel_2=’000′ and kel_3=’000′ then
begin
if kel_3 =’000′ and kel_4=’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_5);
elsif kel_3 <>’001′ or kel_3 <>’000′ and kel_4 =’000′ or kel_4 <>’000′ then
hasil:=basecurr_triple_number_words(kel_1)||’ trilyun ‘||basecurr_triple_number_words(kel_4)||’ ribu ‘||basecurr_triple_number_words(kel_5);
end if;
end;
end if;
end;
end if;
return(hasil);
end;
/

Function Decrypt and Encrypt

This function is useful for encrypt  the secret data,an example thif function can be used to encrypt the “sensitive” filed in the table :

CREATE OR REPLACE function f_decrypt( p_str in raw ) return
varchar2
as
begin
return utl_raw.cast_to_varchar2(
dbms_obfuscation_toolkit.DESdecrypt
( input => p_str,
key   => utl_raw.cast_to_raw(‘MagicKey’) ) );
end;
/

CREATE OR REPLACE function F_Encrypt( p_str in varchar2 )
return raw
as
l_data  varchar2(255);
begin
l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
return dbms_obfuscation_toolkit.DESEncrypt
( input => utl_raw.cast_to_raw(l_data),
key => utl_raw.cast_to_raw(‘MagicKey’) );
end;
/

Calculate Time

Calculate time scripts is used to get differrent value between two datetime ,here the folowing scripts :

create or replace procedure ConversiTime(VInput in Number,VOutput out Varchar2) as
Months    Number;
Days    Number;
Hours   Number;
Minutes Number;
Seconds  Number;
MSeconds Number;
Diff    Number;
Diff0   Number;
Diff1   Number;
Diff2   Number;
Diff3   Number;
Begin
Diff :=VInput;
Days:=FLOOR(Diff);
Diff0:=Mod(Diff,1);
—dbms_output.put_line ( ‘Days – ‘||Days||’  Mod is ‘||Diff0);
Hours:=FLOOR(Diff0*24);
Diff1:=Mod(Diff0*24,1);
—dbms_output.put_line ( ‘Hours – ‘||Hours||’  Mod 1 is ‘||Diff1);
Minutes:=FLOOR(Diff1*60);
Diff2:=MOD(Diff1*60,1);
Seconds:=Floor(Diff2*60);
Diff3 :=Mod(Diff2 * 1000,1);
MSeconds:=Floor(Diff3 * 1000);
VOutput := To_Char(Days) ||’ Days ‘|| To_Char(Hours)||’ Hours ‘||To_Char(Minutes)||’ Minutes ‘||To_Char(Seconds)||’ Seconds ‘ ||To_Char(MSeconds)||’ MilliSeconds’;
end;

Get an oracle error info

Here folowing script to get oracle error info :

———————————————————-

CREATE OR REPLACE PACKAGE oracle_error_info
IS
FUNCTION is_app_error (code_in IN INTEGER)
RETURN BOOLEAN;

FUNCTION is_valid_oracle_error (
code_in            IN   INTEGER
, app_errors_ok_in   IN   BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN   BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN;

PROCEDURE validate_oracle_error (
code_in            IN       INTEGER
, message_out        OUT      VARCHAR2
, is_valid_out       OUT      BOOLEAN
, app_errors_ok_in   IN       BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN       BOOLEAN DEFAULT TRUE
);
END oracle_error_info;
/

CREATE OR REPLACE PACKAGE BODY oracle_error_info
IS
FUNCTION is_app_error (code_in IN INTEGER)
RETURN BOOLEAN
IS
BEGIN
RETURN code_in BETWEEN -20999 AND -20000;
END is_app_error;

PROCEDURE validate_oracle_error (
code_in            IN       INTEGER
, message_out        OUT      VARCHAR2
, is_valid_out       OUT      BOOLEAN
, app_errors_ok_in   IN       BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN       BOOLEAN DEFAULT TRUE
)
IS
l_message   VARCHAR2 (32767);

PROCEDURE set_failure
IS
BEGIN
message_out := NULL;
is_valid_out := FALSE;
END set_failure;
BEGIN
l_message := SQLERRM (code_in);

– If SQLERRM does not find an entry, it return a string like one of these:
– If the number is negative…
– ORA-NNNNN: Message NNNN not found;  product=RDBMS; facility=ORA
– If the number is positive…
–  -13000: non-ORACLE exception
– If the positive number is too big, we get numeric overflow.
IF is_app_error (code_in) AND NOT app_errors_ok_in
THEN
set_failure;
ELSIF code_in = 1 AND NOT user_error_ok_in
THEN
set_failure;
ELSIF    l_message LIKE ‘ORA-_____: Message%not found;%’
OR l_message LIKE ‘%: non-ORACLE exception%’
THEN
set_failure;
ELSE
message_out := l_message;
is_valid_out := TRUE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
–numeric overflow
IF SQLCODE = -1426
THEN
set_failure;
ELSE
RAISE;
END IF;
END validate_oracle_error;

FUNCTION is_valid_oracle_error (
code_in            IN   INTEGER
, app_errors_ok_in   IN   BOOLEAN DEFAULT TRUE
, user_error_ok_in   IN   BOOLEAN DEFAULT TRUE
)
RETURN BOOLEAN
IS
l_message   VARCHAR2 (32767);
retval      BOOLEAN;
BEGIN
validate_oracle_error (code_in
, l_message
, retval
, app_errors_ok_in
, user_error_ok_in
);
RETURN retval;
END is_valid_oracle_error;
END oracle_error_info;
/

Package Debuging

Debugging BP – Debugging Using Pre-Processor Directives

In Oracle 10g Release 2, Oracle introduced a new feature called conditional compilation. Using conditional compilation makes debugging statements a breeze. For instance, here is an example of the debugging statements placed in the source code:

CREATE OR REPLACE PACKAGE mypkg
IS
   PROCEDURE wrapper;

   gv_debug   CONSTANT BOOLEAN := FALSE;
END;
/

CREATE OR REPLACE PACKAGE BODY mypkg
AS
   v_empid   NUMBER (3) := 100;

   PROCEDURE myproc
   IS
      v_empid   NUMBER (3);
   BEGIN
      v_empid := 10;

      IF (gv_debug)
      THEN
         DBMS_OUTPUT.put_line ('Inside myproc(). v_empid = ' || v_empid);
      END IF;
   END;

   PROCEDURE wrapper
   IS
   BEGIN
      myproc;

      IF (gv_debug)
      THEN
         DBMS_OUTPUT.put_line ('Outside myproc(). v_empid = ' || v_empid);
      END IF;
   END;
END;

When the code is run in production, the debugging statements do not get executed because the condition “if (gv_debug)” evaluates to false. When needed, you can turn on the debugging by changing the value of gv_debug to TRUE. This works, but the variable is always evaluated at the runtime, affecting performance. A better approach would be to use conditional compilation, as shown below:

CREATE OR REPLACE PACKAGE mypkg
IS
   PROCEDURE wrapper;
END;
/

CREATE OR REPLACE PACKAGE BODY mypkg
AS
   g_empid   NUMBER (3) := 100;

   PROCEDURE myproc
   IS
      l_empid   NUMBER (3);
   BEGIN
      l_empid := 10;
      $if $$debug $then
      DBMS_OUTPUT.put_line ('Inside myproc(). l_empid = ' || l_empid);
      $end
   END;

   PROCEDURE wrapper
   IS
   BEGIN
      myproc;
      $if $$debug $then
      DBMS_OUTPUT.put_line ('Outside myproc(). g_empid = ' || g_empid);
      $end
   END;
END;

After the package is created, you can alter the debug level by simply issuing:

alter package mypkg compile plsql_ccflags = 'debug:true'

This turns on the debugging statements. So, why it is better that the variables approach? In the variable approach, here is a portion of the code where the condition is evaluated:

  BEGIN
      v_empid := 10;

      IF (gv_debug)
      THEN
         DBMS_OUTPUT.put_line ('Inside myproc(). v_empid = ' || v_empid);
      END IF;
   END;

In the post-processing example, if the value of the variable debug is false, the condition “$if $$debug” evaluates to false and hence the actual code that gets executed in the database is:

  BEGIN
      v_empid := 10;

   END;

Package Best Practice

Package Best Practice – Hide Package DataBy placing data inside the package body, it is protected from direct access by any programs outside of the package.

Recommendation:

Never put your variables and other data structures in the package specification.

• Always put them in the body.
• Then build programs to change values in the data structures and retrieve the current values.
• Make these “get and set” programs available in the package specification.

The benefits include:

• Tighter control over data structures.
• Flexibility to change implementation of data structure.

Hiding Package Data Examples

Package specification with an “exposed” variable:

PACKAGE P_and_L
IS
   last_stmt_dt DATE;
END P_and_L;

Package body which hides the variable and also asserts a rule about last statement date:

PACKAGE BODY P_and_L
IS
   last_stmt_dt DATE;               i NOW IN BODY

   FUNCTION last_date RETURN DATE IS
   BEGIN
      RETURN last_stmt_dt;
   END;

   PROCEDURE set_last_date (date_in IN DATE)
   IS
   BEGIN
      last_stmt_dt := 	          Business Rule!
         LEAST (date_in, SYSDATE);
   END;
END P_and_L;

This following version fully exposes the variable and allows for violation of the business rule: The last statement sets the date into the future.

IF P_and_L.last_stmt_dt <
   ADD_MONTHS (SYSDATE, -3)
THEN
   P_and_L.last_stmt_dt := SYSDATE + 12;
END IF;

This next version of same code hides the variable completely and protects against violations of the rules.

IF P_and_L.last_date <
   ADD_MONTHS (SYSDATE, -3)
THEN
   P_and_L.set_last_date (SYSDATE + 12);
END IF;

It’s recommended that you set standards for elements and their names in your “get and set” programs. For example, if the data structure is “maximum length,” then create the following elements:

• A public procedure named “set_max_length”
• A public function named “max_length”
• A private variable named “v_max_length”.

This way, you can use the most natural names (max_length) for the public elements. If the variable has a fixed number of distinct values, such as a Boolean, then you might take a different approach and create multiple procedures and functions to “set and get.”

PACKAGE PLVtrc
IS
   PROCEDURE turn_on;
   PROCEDURE turn_off;

   FUNCTION tracing RETURN BOOLEAN;
   FUNCTION not_tracing RETURN BOOLEAN;
END PLVtrc;

Reff Cursor

CREATE OR REPLACE PACKAGE types AS
   TYPE cursortyp is REF CURSOR;   — use weak form
END;
/

– Create test package to demonstrate passing result sets…
CREATE OR REPLACE PACKAGE test_ref_cursor AS
   PROCEDURE main;
   FUNCTION  get_cursor_ref(typ NUMBER) RETURN types.cursortyp;
   PROCEDURE process_cursor(cur types.cursortyp);
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS

  — Main program entry point
  PROCEDURE main IS
  BEGIN
    process_cursor( get_cursor_ref(1) );
    process_cursor( get_cursor_ref(2) );
  END;

  — Get and return a CURSOR REF/ Result Set
  FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS
    cur  types.cursortyp;
  BEGIN
    if typ = 1 THEN
      OPEN cur FOR SELECT * FROM emp  WHERE ROWNUM < 5;
    ELSE
      OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5;
    END IF;
    RETURN cur;
  END;

  — Process rows for an EMP or DEPT cursor
  PROCEDURE process_cursor(cur types.cursortyp) IS
    empRec  emp%ROWTYPE;
    deptRec dept%ROWTYPE;
  BEGIN
    LOOP
      FETCH cur INTO empRec;    — Maybe it was an EMP cursor, try to fetch…
      EXIT WHEN cur%NOTFOUND;
      dbms_output.put_line(‘EMP ROW: ‘||empRec.ename);
    END LOOP;
  EXCEPTION
    WHEN ROWTYPE_MISMATCH THEN  — OK, so it was’t EMP, let’s try DEPT.
       LOOP
         FETCH cur INTO deptRec;
         EXIT WHEN cur%NOTFOUND;
         dbms_output.put_line(‘DEPT ROW: ‘||deptRec.dname);
       END LOOP;
  END;

END;
/
show errors
EXEC test_ref_cursor.main;

DBMS Example

create or replace PACKAGE Table_Purge
AS
PROCEDURE Purge
  ( tab_owner IN varchar2,
    tab_name  IN varchar2,
    col_date  IN varchar2,
    date_fm   IN date,
    date_to   IN date       default sysdate,
    roll_back IN varchar2   default NULL
  );
END ;

create or replace PACKAGE body Table_Purge
AS
  sql_curs       integer;
  sql_text       varchar2 (2000);
  –
  PROCEDURE purge       
  (
    tab_owner IN varchar2,
    tab_name  IN varchar2,
    col_date  IN varchar2,
    date_fm   IN date,
    date_to   IN date       default sysdate,
    roll_back IN varchar2   default NULL
  )
  IS
    rows_deleted integer := 0;
  BEGIN
    if roll_back is not NULL then
       dbms_transaction.use_rollback_segment ( roll_back );
    end if;
    –
    sql_text := ‘delete from ‘ || tab_owner || ‘.’ ||
                tab_name || ‘ where ‘ || col_date ||
                ‘ between :fm_date and :to_date’;
    –
    sql_curs := dbms_sql.open_cursor;
    dbms_sql.parse (sql_curs, sql_text, dbms_sql.v7);
    dbms_sql.bind_variable (sql_curs, ‘fm_date’, date_fm);
    dbms_sql.bind_variable (sql_curs, ‘to_date’, date_to);
    rows_deleted := dbms_sql.execute (sql_curs) + rows_deleted;
    dbms_sql.close_cursor (sql_curs);
    –
    commit;
  EXCEPTION
    WHEN others then
        if dbms_sql.is_open (sql_curs) then
           dbms_sql.close_cursor ( sql_curs );
        end if;
        dbms_output.put_line (sql_text);
        raise;
  END purge;
END;
sqlplus>> execute Table_Purge.purge (’scott’,
                                     ‘GL_transactions’,
                                     ‘tran_date’,
                                     to_date(‘01-jan-90′),
                                     to_date(‘31-dec-92′));

          execute Table_Purge.purge (’scott’,
                                     ‘GL_jrnl_lines’,
                                     ‘jrnl_date’,
                                     to_date(‘01-jan-90′),
                                     to_date(‘31-dec-92′));

Bulk Collections

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
   FOR i IN Id.FIRST..Id.LAST LOOP
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i);
   END LOOP;
END;
/

The same with Bulk Bind (no more looping)

DECLARE
   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i);
END;
/

 Bulk Collects (BULK COLLECT INTO)

In the example above (Bulk Binds), the list with empno’s was statically built. With Bulk Collect you can dynamically build the entire list using BULK COLLECT INTO.

DECLARE
   TYPE Numlist IS TABLE OF emp.empno%TYPE;
   Id Numlist;
BEGIN
   SELECT empno BULK COLLECT INTO Id
     FROM emp
    WHERE sal < 2000;
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i);
END;
/

 Bulk Collects with RETURNING INTO

You can even use Bulk Collects with DML-Commands to return a value to the calling procedure using RETURNING without an additional fetch.

DECLARE
   TYPE Numlist IS TABLE OF emp.empno%TYPE;
   TYPE Bonlist IS TABLE OF emp.sal%TYPE;
   Id Numlist;
   Bl Bonlist;
BEGIN
   SELECT empno BULK COLLECT INTO Id
     FROM emp
    WHERE deptno = 10;
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i)
   RETURNING Sal BULK COLLECT INTO Bl;
END;
/

Create Schema

CREATE OR REPLACE PROCEDURE CREATE_SCHEMA (Tablespace_Name varchar2,size_Tablespace varchar2,namefile varchar2,
                                          user_name varchar2,passwords varchar2,
       temp_tablespace varchar2,default_tablespace varchar2) as
 stmt_createTablespace varchar2(4000); 
 stmt_user varchar2(4000);
 stmt_grantCreateSession varchar2(99);
 stmt_grantConnect  varchar2(99);
 stmt_grantDBA varchar2(99);
Begin
stmt_createTablespace := ‘CREATE TABLESPACE ‘|| Tablespace_Name ||’ LOGGING ‘||
    ‘DATAFILE ‘||””||namefile||””|| ‘ SIZE  ‘ ||size_Tablespace||
    ‘ EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT  AUTO ‘;
stmt_user := ‘CREATE USER ‘||user_name||
          ‘ IDENTIFIED BY ‘ || passwords || ‘ TEMPORARY TABLESPACE ‘||temp_tablespace||
          ‘ DEFAULT TABLESPACE ‘||default_tablespace||’ quota unlimited on  ‘
          ||default_tablespace || ‘ quota unlimited on ‘ ||temp_tablespace ;
stmt_grantCreateSession := ‘GRANT CREATE SESSION TO ‘||user_name;
stmt_grantConnect   := ‘GRANT CONNECT TO ‘||user_name ;
stmt_grantDBA     := ‘GRANT DBA TO  ‘ || user_name ;
execute immediate
    stmt_createTablespace;
execute immediate
    stmt_user;
execute immediate
    stmt_grantCreateSession;
execute immediate
    stmt_grantConnect;
execute immediate
    stmt_grantDBA;
End;
/
Cara Ubtuk execute Program

BEGIN
  CREATE_SCHEMA(TABLESPACE_NAME=>’NAMA’,
                size_Tablespace =>’12M’,
                namefile =>’D:\NAMA.ORA’,
    USER_NAME=>’COBA’,
    PASSWORDS=>’cobajuga’,
    temp_tablespace =>’TEMP’,
    DEFAULT_TABLESPACE =>’NAMA’
    );
END;