Revised December 2021, published May 2017

Main menu – download 27 May 2017 I uploaded  1_pl_sq.zip Oracle 11g PL/SQL Tutorial

Oracle 11g PL/SQL Tutorial about testing developing DB procedures

1. Three  loops  and three branches
2. Function, procedure, package, array object
3. Number, string, date, boolean
4. DDL
5. CRUD
6. Ref Cursor pointer to cursor (ee to record set object variable) – December 2021

1. Three  loops  and three branches

/*
start ...path...02_02Hello_var_loop_if_exception_date.sql

O U T P U T :

~~~~~~~~ Three  L O O P S ~~~~~~~~
--- loop1. LOOP,EXIT WHEN v_cntr>=2 (v_cntr=0,1)
Hello 28.05.2017
Hello 28.05.2017
---
--- loop2. FOR v_cntr IN 1..2 LOOP - IF v_cntr = 2 THEN 
        RAISE_APPLICATION_ERROR(-20001, v_errmsg);
- EXCEPTION - WHEN others THEN - IF sqlcode = -20001...
Hello
Hello , ***v_cntr=2 custom EXCEPTION !!!at!!! IF v_cntr=2 <-- !!
---
--- loop2a. FOR ii IN REVERSE 3..9 LOOP, mod(ii,3) != 0
ii=9
ii=6
ii=3
---
--- loop3. From WHILE v_cntr < 2 LOOP
Hello
Hello
---
---
~~~~~~~~ Three  B R A N C H E S ~~~~~~~~
--- branch1. IF - ELSIF - ELSE - ENDIF
x message
---
--- branch2. searched CASE - WHEN - ELSE - END CASE
aaa
---
--- branch3. simple "CASE x" - WHEN - ELSE - END CASE (as PHP switch - case - default)
I don't know what v_msg is
*/



set linesize 200
set serveroutput oFF
set serveroutput on size 1000000

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

DECLARE
  v_cntr NUMBER := 0;
  v_system_date CONSTANT DATE := sysdate;
  v_errmsg VARCHAR2(255);
  v_msg VARCHAR2(255) := 'Hello ';
  
  BEGIN
    msg('---');
    msg('~~~~~~~~ Three  L O O P S ~~~~~~~~'); 
    msg('--- loop1. LOOP,EXIT WHEN v_cntr>=2 (v_cntr=0,1)');
  LOOP
    EXIT WHEN v_cntr >= 2; -- 2 loops : v_cntr = 0, 1
    v_cntr := v_cntr + 1;
      
    msg(v_msg || to_char(v_system_date, 'DD.MM.YYYY'));
  END LOOP;


  BEGIN
    msg('---');
    msg('--- loop2. FOR v_cntr IN 1..2 LOOP - IF v_cntr = 2 
          THEN RAISE_APPLICATION_ERROR(-20001, v_errmsg);');
    msg('      - EXCEPTION - WHEN others THEN - IF sqlcode = -20001...');
    
    FOR v_cntr IN 1..2 LOOP
     IF v_cntr = 2 THEN
       v_errmsg:=v_msg || ', ***v_cntr=2 custom EXCEPTION !!!at!!! IF v_cntr=2 <----------- !!!!!!!!';
       RAISE_APPLICATION_ERROR(-20001, v_errmsg);
     END IF;
     msg(v_msg);
    END LOOP;
    
  EXCEPTION
  WHEN no_data_found THEN  msg(
    'no_data_found EXCEPTION --- 2 From FOR v_cntr IN 1..2 LOOP');
  WHEN others THEN
    IF sqlcode = -20001 THEN msg(v_errmsg);
    ELSE RAISE; -- go to end script err handler
    END IF;
  END;
                --O U T P U T S :
                --Hello
                --Hello , ***v_cntr=2 custom EXCEPTION !!!at!!! IF v_cntr=2 
  msg('---');
  msg('--- loop2a. FOR ii IN REVERSE 3..9 LOOP, 
                   mod(ii,3) != 0');
  DECLARE
    v_increment NUMBER := 3;
  BEGIN
    FOR ii IN REVERSE 3..9 LOOP
      IF mod(ii,v_increment) != 0 THEN CONTINUE; END IF;
      msg('ii=' || ii);
    END LOOP;
  END;

      
      
  msg('---');
  msg('--- loop3. From WHILE v_cntr < 2 LOOP');
  v_cntr := 0 ;
  WHILE v_cntr < 2 LOOP
   v_cntr := v_cntr + 1;
   msg(v_msg);
  END LOOP;


  
  
  msg('---'); msg('---');
  msg('~~~~~~~~ Three  B R A N C H E S ~~~~~~~~');
  msg('--- branch1. IF - ELSIF - ELSE - ENDIF');
  v_msg := 'x message';
  IF v_msg LIKE 'x%' THEN msg(v_msg);
  ELSIF v_msg = 'aaa' THEN msg(v_msg);
  ELSE msg('I don''t know what v_msg is');
  END IF;
  
  msg('---');
  msg('--- branch2. searched CASE - WHEN - ELSE - END CASE');
  v_msg := 'aaa';
  CASE 
  WHEN v_msg LIKE 'x%' THEN msg(v_msg);
  WHEN v_msg = 'aaa' THEN msg(v_msg);
  ELSE msg('I don''t know what v_msg is');
  END CASE;
  
  msg('---');
  msg('--- branch3. simple "CASE x" - WHEN - ELSE - END CASE 
                    (as PHP switch - case - default)');
  v_msg := 'x';
  CASE v_msg -- same as PHP switch - case - default
  WHEN 'aaa' THEN msg(v_msg);
  ELSE msg('I don''t know what v_msg is');
  END CASE;
  

END;
/
set serveroutput oFF

go top

2. Function, procedure, package, array object

/*
start ...path...\02_03fn_proc_pck_assoc_arr_object.sql

O U T P U T :

--- 1. fn return_1 SAYS: 1

DML A S S O C I A T I V E  A R R A Y
--- 2. anonymus pl/sql block SAYS: DML associative_array v_array(2)=Hello Again!
--------2.1 robust loop through array
Hello World!
Hello Again!
--------2.2 simple loop through array
Hello World!
Hello Again!

--------2.3 robust loop through array
Doe
King

--------2.4 robust loop through array with delete
Hello World! Date and time: Sunday    on 28 May, 2017 @ 08:12:34

--- 3.1 anonymus pl/sql block SAYS: DDL object_o_person v_person.lname=Doe2

--- 3.2 anonymus pl/sql block SAYS: DDL object_o_person v_person(1).lname=Doe3

--- 4. my_package.crerow_t;  and read it
This is a message

--- 5. print business days
Tuesday 4 of July, 2017
Monday 25 of December, 2017
*/

set linesize 200
set serveroutput OFF
set serveroutput on size 1000000
------------

-- c a l l  f u n c t i o n :
--BEGIN msg('---aaaaa'); END;
BEGIN msg('--- 1. fn return_1 SAYS: '||return_1); END;
/
-- c a l l  p r o c e d u r e :
--BEGIN insert_a_rec(...); END;


-- DML associative_array
DECLARE
  TYPE t_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  v_array t_array;
  v_index BINARY_INTEGER;    
BEGIN
  v_array(1) := 'Hello World!';
  v_array(2) := 'Hello Again!';
  msg('DML A S S O C I A T I V E  A R R A Y');
  msg('--- 2. anonymus pl/sql block SAYS: DML associative_array v_array(2)='||v_array(2));
  
  
  msg('--------2.1 robust loop through array');
  v_index := v_array.FIRST;
  LOOP
    EXIT WHEN v_index IS NULL;
    msg( v_array( v_index ) );
    v_index := v_array.NEXT(v_index);
  END LOOP;
  
  
  msg('--------2.2 simple loop through array');
  FOR ii IN v_array.FIRST..v_array.lAST 
  LOOP
    msg( v_array(ii) );
  END LOOP;
  
END;
/

DECLARE
  TYPE t_varchar2 IS TABLE OF VARCHAR2(100)
    INDEX BY BINARY_INTEGER;
    
  TYPE t_number IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
   
  v_first_name t_varchar2;
  v_last_name  t_varchar2;
  v_salary     t_number;
  
  v_index BINARY_INTEGER;    
BEGIN

  SELECT first_name, last_name, salary 
    BULK COLLECT INTO v_first_name, v_last_name, v_salary
    FROM employees where rownum < 3;
    
    
  msg('--------2.3 robust loop through array');
  v_index := v_last_name.FIRST;
  LOOP
    EXIT WHEN v_index IS NULL;
    msg( v_last_name( v_index ) );
    v_index := v_last_name.NEXT(v_index);
  END LOOP;
  
  
END;
/

DECLARE
  TYPE t_array IS TABLE OF VARCHAR2(30);
  v_array t_array;    
  v_index BINARY_INTEGER;
  v_date DATE := SYSDATE;
BEGIN
  msg('--------2.4 robust loop through array with delete');
  v_array := t_array();
  v_array.extend; v_array(1) := 'Hello World!';
  v_array.extend; v_array(2) := 'Hello Again!';
  
  v_array.DELETE(2);
  v_index := v_array.FIRST;
  LOOP
    EXIT WHEN v_index IS NULL;
    --msg( v_array( v_index ) );
    msg(
      v_array( v_index ) ||
      ' Date and time: ' ||
      to_char(v_date, 'Day') || ' on ' ||
      to_char(v_date, 'FMDD Month, YYYY') ||
      ' @ ' ||
      to_char(v_date, 'HH24:MI:SS')
    );
    v_index := v_array.NEXT(v_index);
  END LOOP;  
END;
/



--------
DECLARE
  v_person o_person;    
BEGIN 
  v_person := o_person('John', 'Doe', 21);
  v_person := o_person('John2', 'Doe2', 22);
  
  msg('--- 3.1 anonymus pl/sql block SAYS: DDL object_o_person v_person.lname='||v_person.lname);
END;
/


DECLARE
  v_person o_person_collec := o_person_collec();    -- table
BEGIN
  v_person.extend;
  --row is TYPE OBJECT :
  v_person(1) := o_person('John3', 'Doe3', 23); 
 
  msg('--- 3.2 anonymus pl/sql block SAYS: DDL object_o_person v_person(1).lname='||v_person(1).lname);
 
END;
/



BEGIN
  msg('--- 4. my_package.crerow_t;  and read it');
  --in sql+ : exec my_package.delrow_t('xx')
  my_package.crerow_t;
  for rx in (select * from t where country_id = 'xx') loop
    msg( rx.country_name );
  end loop;
END;
/


begin
  msg('--- 5. print business days ');
  print_business_days(
     to_date('31-DEC-2016', 'DD-MON-YYYY'),
     to_date('31-DEC-2017', 'DD-MON-YYYY')
  );
end;
/

set serveroutput oFF

go top

3. Number, string, date, boolean

-- ...path...03_01num_string_date_boolean.sql
-- 1. Valid numbers
DECLARE
  vNum NUMBER;
BEGIN
  vNum := 100;
  vNum := 98989898989898;
  vNum := 0.00000000000000001;
  vNum := 10928383.9028282772722626262;
END;
/

-- 2. CHAR versus VARCHAR2
DECLARE
  vc2_Name  VARCHAR2(10) := 'Lewis';
  char_Name CHAR(10)   := 'Lewis';
BEGIN
  IF vc2_Name = char_Name THEN
    dbms_output.put_line('VARCHAR2, CHAR Variables Match');
  ELSE dbms_output.put_line(
     'VARCHAR2, CHAR Variables Do Not Match');
  END IF;  
END;
/


-- 3. String to number conversion
DECLARE
  v_string_var VARCHAR2(10) := '15';
  v_number_var NUMBER;
BEGIN
   v_number_var := TO_NUMBER(v_string_var);
END;

-- 4. Date conversion
DECLARE
  v_string VARCHAR2(30) := '10/30/1998 12:34:03 PM';
  v_date DATE;
BEGIN
  v_date := to_date(v_string, 'MM/DD/YYYY HH:MI:SS AM');
  v_date := to_date(v_string, 'DD.MM.YYYY HH24:MI:SS');
END;
/

-- 5. Timestamp conversion
DECLARE
  v_string VARCHAR2(30) := '10/30/1998 12:34:03.987654 PM';
  v_date timestamp;
BEGIN
  v_date := to_timestamp(v_string, 'MM/DD/YYYY HH:MI:SS.FF AM');
END;


-- 6. Date to char conversion
DECLARE
  v_string VARCHAR2(30) := '10/30/1998 12:34:03 PM';
  v_date DATE;
BEGIN
  v_date := to_date(v_string, 'MM/DD/YYYY HH:MI:SS AM');
  dbms_output.put_line(  to_char(v_date, 'FMDD Month, YYYY') );
END;


-- 7.1 BOOLEAN good example of direct usage
DECLARE
  v_var1 NUMBER := 0;
  v_var2 NUMBER := 1;
BEGIN
  IF v_var1 = v_var2 THEN NULL; END IF;
END;

-- 7.2 BOOLEAN good example for variable reuse
DECLARE
  v_boolean BOOLEAN;
  v_var1 NUMBER := 0;
  v_var2 NUMBER := 1;
BEGIN
  v_boolean := v_var1 = v_var2;
  IF v_boolean THEN NULL; END IF;
END;





-- 7.3 BOOLEAN Poor Example
DECLARE
  v_boolean BOOLEAN;
  v_var1 NUMBER := 0;
  v_var2 NUMBER := 1;
BEGIN
  IF v_var1 = v_var2 THEN v_boolean := TRUE;
  ELSE v_boolean := FALSE;
  END IF;
  
  IF v_boolean THEN NULL; END IF;
END;
/

go top

4. DDL

/*
HR@ora7 27.05.2017 21:21:16> start ...path...\01_02DDL.sql
O U T P U T :
~~~~~ 1. c r e  LOG_ TABLE ~~~~~
~~~~~ 2. c r e  tbl t2 ~~~~~
~~~~~ 3. c r e  tbl emp_names_nds ~~~~~

~~~~~ 1. c r e  p r o c e d u r e  m s g ~~~~~
~~~~~ 2. c r e  p r o c e d u r e  l o g i t ~~~~~
~~~~~ 3. c r e  p r o c PRINT_ BUSINESS_ DAYS ~~~~~
~~~~~ 4. cre fn return_1 ~~~~~
~~~~~ 5. cre R O W TYPE o_ person AS OBJECT ~~~~~
~~~~~ 6. cre T B L TYPE o_ person_ collec AS TABLE OF ~~
~~~~~ 6.1 cre pck my_ package ~~~~~
~~~~~ 6.2 cre pck body my_ package ~~~~~
~~~~~ 7.1 cre pck t2_ dyn_ api ~~~~~
~~~~~ 7.2 cre pck body t2_ dyn_ api ~~~~~
*/
drop TABLE LOG_TABLE;
prompt ~~~~~ 1. c r e  LOG_ TABLE ~~~~~
CREATE TABLE LOG_TABLE
  (
    DATUM DATE,
    MESSAGE VARCHAR2(255 BYTE)
 ) ;
--insert into log_table (date_and_time, message) 
--    VALUES (sysdate, 'Hello World!');
prompt ~~~~~ 2. c r e  tbl t2 ~~~~~
drop TABLE t2 ;
BEGIN
  EXECUTE IMMEDIATE 
  'CREATE TABLE t2 (
       col1 VARCHAR2(25)
     , col2 VARCHAR2(25)
     , col3 VARCHAR2(25)
  )';
END;
/

prompt ~~~~~ 3. c r e  tbl emp_names_nds ~~~~~
drop TABLE emp_names_nds;
CREATE TABLE emp_names_nds (
  last_name VARCHAR2(25), first_name VARCHAR2(20) );
  




prompt ~~~~~ 1. c r e  p r o c e d u r e  m s g ~~~~~
CREATE OR REPLACE PROCEDURE msg(
   p_msg IN VARCHAR2 DEFAULT 'NO MESSAGE')
AS
BEGIN
   dbms_output.put_line(p_msg);
END;
/
--show error



prompt ~~~~~ 2. c r e  p r o c e d u r e  l o g i t ~~~~~
create or replace PROCEDURE logit(
  v_message IN VARCHAR2 DEFAULT 'Hello World!',
  v_output_target IN VARCHAR2 DEFAULT 'T')
AS
  -- v_output target may be T for table or
  --    D for dbms_output
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_date DATE := SYSDATE;

  PROCEDURE do_output(
    v_message IN VARCHAR2,
    v_date IN DATE )
  AS
  BEGIN

    DBMS_OUTPUT.put_line(
        v_message ||
        ' On date ' ||
        to_char(v_date, 'Day') || ' on ' ||
        to_char(v_date, 'FMDD Month, YYYY') ||
        ' @ ' ||
        to_char(v_date, 'HH24:MI:SS')
        ); 
  END;    

  PROCEDURE do_insert(
    v_message IN VARCHAR2,
    v_date IN DATE )
  AS
  BEGIN
    insert into log_table
      (datum, message)
      VALUES (v_date, v_message);
  END;
-------------
BEGIN
  CASE
  WHEN v_output_target = 'T'
  THEN
    do_insert(v_message, v_date);
  WHEN v_output_target = 'D'
  THEN
    do_output(v_message, v_date);
  WHEN v_output_target = 'TD'
    OR v_output_target = 'DT'
  THEN
    do_insert(v_message, v_date);
    do_output(v_message, v_date);
  ELSE

    logit('ERROR v_output_target: ' ||
          v_output_target ||
          ' not found.', 'T' );

  END CASE;

  COMMIT;

END logit;
/



prompt ~~~~~ 3. c r e  p r o c PRINT_ BUSINESS_ DAYS ~~~~~
create or replace PROCEDURE PRINT_BUSINESS_DAYS
  (
      P_START_DATE IN DATE 
    , P_END_DATE IN DATE 
  )
AS
  TYPE t_holidays IS TABLE OF VARCHAR2(100)
    INDEX BY VARCHAR2(30);
  v_dates t_holidays;
  v_loop_increment NUMBER := 0;
BEGIN
  v_dates('01-JAN') := 'Mew Years';
  v_dates('04-JUL') := 'Independence Day';
  v_dates('25-DEC') := 'Christmas Day';

  LOOP
    EXIT WHEN p_start_date + v_loop_increment > p_end_date;

    /*
    IF to_number(to_char(p_start_date 
          + v_loop_increment, 'd'))
      IN (2,3,4,5,6)
    THEN 
      dbms_output.put_line(to_char(p_start_date 
        + v_loop_increment, 'FMDay DD "of" Month, YYYY'));
    END IF;
    */    
    
    -- or :
    IF v_dates.EXISTS(
      to_char(p_start_date + v_loop_increment, 'DD-MON'))
    THEN
    
      IF to_number(to_char(p_start_date 
          + v_loop_increment, 'd'))
        IN (2,3,4,5,6)
      THEN 
        dbms_output.put_line(to_char(p_start_date 
          + v_loop_increment, 'FMDay DD "of" Month, YYYY')); 
      END IF;

    END IF;

    v_loop_increment := v_loop_increment + 1;

  END LOOP;

END PRINT_BUSINESS_DAYS;
/









prompt ~~~~~ 4. cre fn return_1 ~~~~~
CREATE OR REPLACE FUNCTION return_1
RETURN NUMBER
AS
BEGIN
  RETURN 1;
END;
/
--show error

prompt ~~~~~ 5. cre R O W TYPE o_ person AS OBJECT ~~~~~
drop TYPE o_person_collec ;
CREATE OR REPLACE TYPE o_person AS OBJECT (
    fname VARCHAR2(30),
    lname VARCHAR2(30),
    age NUMBER 
);
/
--show error


prompt ~~~~~ 6. cre T B L TYPE o_ person_ collec AS TABLE OF ~~
CREATE OR REPLACE TYPE o_person_collec --table 
  AS TABLE OF o_person; --row is TYPE OBJECT 
/
--show error



prompt ~~~~~ 6.1 cre pck my_ package ~~~~~
CREATE OR REPLACE PACKAGE my_package
AS
  FUNCTION return_1 RETURN NUMBER;
  PROCEDURE crerow_t;
  PROCEDURE delrow_t(p_id in varchar2);
END;
/
prompt ~~~~~ 6.2 cre pck body my_ package ~~~~~
CREATE OR REPLACE PACKAGE BODY my_package
AS
  FUNCTION return_1 RETURN NUMBER AS
  BEGIN
    RETURN 1;
  END;

  PROCEDURE crerow_t AS
  BEGIN
    INSERT INTO t (country_id, country_name)
      VALUES ('xx', 'This is a message');
  END;
  
  PROCEDURE delrow_t(p_id in varchar2) AS
  BEGIN
    delete t where country_id = p_id; --'xx'
  END;

END;  
/



prompt ~~~~~ 7.1 cre pck t2_ dyn_ api ~~~~~
create or replace PACKAGE t2_dyn_api IS
 PROCEDURE insert_row(
    p_col1 IN t2.col1%TYPE,
    p_col2 IN t2.col2%TYPE,
    p_col3 IN t2.col3%TYPE
  );
  
 PROCEDURE update_row(
    p_col1 IN t2.col1%TYPE,
    p_col2 IN t2.col2%TYPE,
    p_col3 IN t2.col3%TYPE
  );
  
 PROCEDURE delete_row(
    p_col1 IN t2.col1%TYPE,
    p_col2 IN t2.col2%TYPE,
    p_col3 IN t2.col3%TYPE
  );  
END;
/

prompt ~~~~~ 7.2 cre pck body t2_ dyn_ api ~~~~~
create or replace PACKAGE BODY t2_dyn_api IS
  PROCEDURE insert_row(
    p_col1 IN t2.col1%TYPE,
    p_col2 IN t2.col2%TYPE,
    p_col3 IN t2.col3%TYPE
  ) 
  AS 

    v_dml_string CLOB;
  
    -- DBMS_SQL variables
    v_cursor_id NUMBER;
    v_rows_fetched NUMBER;
  
  BEGIN

    v_cursor_id := DBMS_SQL.open_cursor;
    
    v_dml_string := 'INSERT INTO t2 (col1, col2, col3) ';
    v_dml_string := v_dml_string || 'VALUES (:col1
         , :col2, :col3) ';
  
    -- Display the string
    logit( v_dml_string, 'D');
 
    DBMS_SQL.PARSE(v_cursor_id, v_dml_string
       , DBMS_SQL.NATIVE);

    DBMS_SQL.bind_variable( v_cursor_id, 'col1', p_col1);
    DBMS_SQL.bind_variable( v_cursor_id, 'col2', p_col2);
    DBMS_SQL.bind_variable( v_cursor_id, 'col3', p_col3);
  
    v_rows_fetched := DBMS_SQL.EXECUTE(v_cursor_id);
    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
    
    COMMIT;

    logit( 'Rows Fetched: ' || to_char(v_rows_fetched), 'D');

  END;
  
   
  PROCEDURE update_row(
    p_col1 IN t2.col1%TYPE,
    p_col2 IN t2.col2%TYPE,
    p_col3 IN t2.col3%TYPE
  ) 
  AS 

    v_dml_string CLOB;
  
    -- DBMS_SQL variables
    v_cursor_id NUMBER;
    v_rows_fetched NUMBER;
  
  BEGIN

    v_cursor_id := DBMS_SQL.open_cursor;
    
    v_dml_string := 'UPDATE t2 ';
    v_dml_string := v_dml_string || 
               'SET col1 = :col1, 
                    col2 = :col2,
                    col3 = :col3 ';
  
    -- Display the string
    logit( v_dml_string, 'D');
 
    DBMS_SQL.PARSE(v_cursor_id, v_dml_string
       , DBMS_SQL.NATIVE);

    DBMS_SQL.bind_variable( v_cursor_id, 'col1', p_col1);
    DBMS_SQL.bind_variable( v_cursor_id, 'col2', p_col2);
    DBMS_SQL.bind_variable( v_cursor_id, 'col3', p_col3);
  
    v_rows_fetched := DBMS_SQL.EXECUTE(v_cursor_id);
    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
    
    COMMIT;

    logit( 'Rows Fetched: ' || to_char(v_rows_fetched), 'D');

  END;  
  PROCEDURE delete_row(
    p_col1 IN t2.col1%TYPE,
    p_col2 IN t2.col2%TYPE,
    p_col3 IN t2.col3%TYPE
  ) 
  AS 

    v_dml_string CLOB;
  
    -- DBMS_SQL variables
    v_cursor_id NUMBER;
    v_rows_fetched NUMBER;
  
  BEGIN

    v_cursor_id := DBMS_SQL.open_cursor;
    
    v_dml_string := 'DELETE FROM t2 ';
    v_dml_string := v_dml_string || 
               'WHERE col1 = :col1 AND 
                      col2 = :col2 AND
                      col3 = :col3 ';
  
    -- Display the string
    logit( v_dml_string, 'D');
 
    DBMS_SQL.PARSE(v_cursor_id, v_dml_string
       , DBMS_SQL.NATIVE);

    DBMS_SQL.bind_variable( v_cursor_id, 'col1', p_col1);
    DBMS_SQL.bind_variable( v_cursor_id, 'col2', p_col2);
    DBMS_SQL.bind_variable( v_cursor_id, 'col3', p_col3);
  
    v_rows_fetched := DBMS_SQL.EXECUTE(v_cursor_id);
    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
    
    COMMIT;

    logit( 'Rows Fetched: ' || to_char(v_rows_fetched), 'D');

  END; 

END t2_dyn_api;
/


/*
-- ORA-01031: insufficient privileges
CREATE OR REPLACE PROCEDURE CREATE_TABLE 
(
  P_TABLE_NAME IN VARCHAR2  
, P_COLUMNS IN DBMS_SQL.varchar2a  
) AS 
  v_ddl_string CLOB;
  v_index PLS_INTEGER;
  -- DBMS_SQL variables
  v_cursor_id NUMBER;
  v_rows_fetched NUMBER;
BEGIN
  IF p_table_name IS NULL OR p_columns.COUNT = 0
  THEN RETURN; END IF;
  
  v_ddl_string := 'CREATE TABLE ';
  v_ddl_string := v_ddl_string || p_table_name || '( '; 
  v_index := p_columns.FIRST;
  
  LOOP
    EXIT WHEN v_index IS NULL;
    IF v_index != p_columns.FIRST
    THEN
      v_ddl_string := v_ddl_string || ', ';
    END IF;
    
    v_ddl_string := v_ddl_string || p_columns(v_index);
    v_index := P_columns.NEXT(v_index);
  END LOOP;
  
  v_ddl_string := v_ddl_string || ')';
  -- Display the string
  logit( v_ddl_string, 'D');
  
  -- Create the table
  v_cursor_id := DBMS_SQL.open_cursor;
  DBMS_SQL.PARSE(v_cursor_id, v_ddl_string, DBMS_SQL.NATIVE);
  v_rows_fetched := DBMS_SQL.EXECUTE(v_cursor_id);
  DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

END CREATE_TABLE;
/

DECLARE
  v_table_name VARCHAR2(30) := 't3';
  v_columns DBMS_SQL.varchar2a;
BEGIN
  v_columns(1)  := 'COL1 VARCHAR2(10)';
  v_columns(5)  := 'COL2 VARCHAR2(2)';
  v_columns(10) := 'COL3 NUMBER';
  -- ORA-01031: insufficient privileges
  CREATE_TABLE(v_table_name, v_columns); 
END;
/
*/

go top

5. CRUD

/*
start ...path...\02_01CRUd_rowtype.sql

O U T P U T :
--- 1. CRUD R employee_id= 115
r_person.salary=3246,86
--- 2. CRUD U employee_id =115
r_person.salary*,95=3084,52
r_person.LAST_NAME=Khoo
--- 3. CRUD D (if exists) employee_id=999
--- 4. CRUD C employee_id=999
r_person.salary=3084,52
r_person.LAST_NAME=Doe
*/
set linesize 200
set serveroutput oFF
set serveroutput on size 1000000
------------
/*
-- rowtype cursor
DECLARE
  CURSOR c1 IS
    SELECT first_name, last_name, salary
    FROM employees;
  r_person c1%ROWTYPE;    
BEGIN
 r_person.first_name := 'John';
 r_person.last_name  := 'Doe';
 r_person.salary     := 2200.00; 
END;

-- rowtype record
DECLARE
  r_person employees%ROWTYPE;    
BEGIN
 r_person.first_name := 'John';
 r_person.last_name  := 'Doe';
 r_person.salary     := 2200.00; 
END;
*/

BEGIN
  EXECUTE IMMEDIATE 'BEGIN logit(:mybindvar, :displaymode);
    END;' USING 'This is my dynamic message.', 'D';
END;


delete t2;
BEGIN
  execute immediate 'insert into t2 (col1, col2) 
    values (:col1val, :col2val)' using 'A', 'B';
  logit('Created Rows: ' || SQL%ROWCOUNT, 'D' );
  
  execute immediate 'update t2 set col1 = :newcol1
    where col2 = :oldcol2' using 'C', 'B';
  logit('Updated Rows: ' || SQL%ROWCOUNT, 'D' );
  
  execute immediate 
  'delete from  t2 where col2 = :oldcol2' using 'B';  
  logit('Deleted Rows: ' || SQL%ROWCOUNT, 'D' );
END;
select * from emp_names_nds;

delete t2;
BEGIN
  t2_dyn_api.insert_row('A', 'B', 1);
  t2_dyn_api.insert_row('D', 'E', 2);
  t2_dyn_api.insert_row('G', 'H', 3);
END;

select * from t2;
BEGIN t2_dyn_api.update_row('X', 'B', 1); END;

select * from t2;
BEGIN t2_dyn_api.delete_row('X', 'B', 1); END;

select * from t2;




DECLARE v_output VARCHAR2(1);
BEGIN
  execute immediate 'insert into t2 (col1, col2) 
    values (:col1val, :col2val) RETURNING col1 INTO :colret'
  using 'A', 'B', OUT v_output;
  logit('v_output: ' || v_output, 'D' );   
END;


DECLARE
  v_output VARCHAR2(1);
BEGIN
  execute immediate 
      'insert into t2 (col1, col2) 
         values (:col1val, :col2val)
         RETURNING col1 INTO :colret'
    using 'A', 'B'
    RETURNING INTO v_output;
  
  logit('v_output: ' || v_output, 'D' );
    
END;


DECLARE
  v_dml_sel VARCHAR2(8000) := '
    SELECT last_name, first_name FROM employees
    WHERE rownum < :rowsproc';
  TYPE r_emp IS RECORD (
    last_name VARCHAR2(25),
    first_name VARCHAR2(20) );
  TYPE v_emp_tbltyp IS TABLE OF r_emp;
  v_emp_tbl v_emp_tbltyp;
BEGIN
  EXECUTE IMMEDIATE v_dml_sel BULK COLLECT INTO v_emp_tbl
  USING 10;

  FORALL ii IN v_emp_tbl.FIRST..v_emp_tbl.LAST
    EXECUTE IMMEDIATE 'INSERT INTO emp_names_nds (last_name, first_name) VALUES (:lastname, :firstname)'
    USING v_emp_tbl(ii).last_name, v_emp_tbl(ii).first_name;
END;

select * from emp_names_nds;
--delete emp_names_nds; -- 99 deleted



-- measure bind performance
declare
  v_char_null varchar2(10);
  v_num_null number;
  v_not_null number := 0;
  v_value number;
  v_loop_cnt PLS_INTEGER := 10000;
  v_start_time timestamp(9);
  v_end_time timestamp(9);
  
  v_rand number := abs(dbms_random.random);

  v_sql_stmt VARCHAR2(1000);  
begin

  v_loop_cnt := v_loop_cnt + v_rand;

  dbms_output.put_line('Random: ' || to_char(v_rand) );
  
  v_start_time := systimestamp;

  FOR i IN v_rand..v_loop_cnt
  LOOP
    v_sql_stmt := 'SELECT NVL(to_number(''''), to_number(''' || i || ''')) FROM DUAL';
    EXECUTE IMMEDIATE v_sql_stmt INTO v_value;
  END LOOP;  
              
  v_end_time := systimestamp;
              
  dbms_output.put_line( 'No Binds, Conversion         ' ||  
              to_char(v_end_time - v_start_time) );

  v_start_time := systimestamp;

  FOR i IN v_rand..v_loop_cnt
  LOOP
    v_sql_stmt := 'SELECT NVL(null, ' || i || ') FROM DUAL';
    EXECUTE IMMEDIATE v_sql_stmt  INTO v_value;
  END LOOP;  
              
  v_end_time := systimestamp;
              
  dbms_output.put_line( 'No Binds, No Conversion      ' ||  
              to_char(v_end_time - v_start_time) );

  v_start_time := systimestamp;

  v_sql_stmt := 'SELECT NVL(:v_num_null, :v_not_null) FROM DUAL' ;
      
  FOR i IN v_rand..v_loop_cnt
  LOOP
    v_not_null := i;
    EXECUTE IMMEDIATE v_sql_stmt  INTO v_value USING IN v_num_null, IN v_not_null;
  END LOOP;  
              
  v_end_time := systimestamp;
              
  dbms_output.put_line( 'Binds, No Conversion         ' ||  
              to_char(v_end_time - v_start_time) );

  v_start_time := systimestamp;

  FOR i IN v_rand..v_loop_cnt
  LOOP
    v_not_null := i;
    SELECT NVL(v_num_null, v_not_null) INTO v_value FROM DUAL;
  END LOOP;  
              
  v_end_time := systimestamp;
              
  dbms_output.put_line( 'Not Dynamic                  ' ||  
              to_char(v_end_time - v_start_time) );

end;






DECLARE
  cursor c_person(c_id in number) is
    SELECT * FROM employees WHERE employee_id = c_id;
  r_person employees%ROWTYPE;    
  v_koef NUMBER;    
  v_upd_employee_id NUMBER;    
  v_cre_employee_id NUMBER;    
BEGIN
  v_koef  := 0.95;
  v_upd_employee_id := 115;
  v_cre_employee_id := 999;
  -- 1. r e a d
  for rx in c_person(v_upd_employee_id) loop
    r_person := rx ;
  end loop ;
                dbms_output.put_line(
                 '--- 1. CRUD R employee_id= '
                 ||v_upd_employee_id||chr(10)
                 ||'r_person.salary='||r_person.salary);
  
  -- 2. u p d a t e
  r_person.salary := r_person.salary * v_koef ;
  UPDATE employees SET salary = r_person.salary 
  WHERE employee_id = r_person.employee_id;
              for rx in c_person(v_upd_employee_id) loop
                r_person := rx ;
              end loop ;
              dbms_output.put_line(
                '--- 2. CRUD U employee_id ='
              ||v_upd_employee_id||chr(10)
              ||'r_person.salary*'||v_koef||'='
              ||r_person.salary
              ||chr(10)
              ||'r_person.LAST_NAME='||r_person.LAST_NAME
              );

  -- 3. d e l e t e
  BEGIN DELETE employees WHERE employee_id = v_cre_employee_id;
  EXCEPTION WHEN others THEN null; END;
               dbms_output.put_line(
 '--- 3. CRUD D (if exists) employee_id='||v_cre_employee_id);

  -- 4. c r e a t e
  r_person.employee_id := v_cre_employee_id;
  r_person.last_name   := 'Doe';
  r_person.first_name  := 'John';
  r_person.email       := '[email protected]';
  INSERT INTO employees VALUES r_person;
              for rx in c_person(v_cre_employee_id) loop
                r_person := rx ;
              end loop ;
               dbms_output.put_line(
               '--- 4. CRUD C employee_id='||v_cre_employee_id
               ||chr(10)
              ||'r_person.salary='||r_person.salary||chr(10)
              ||'r_person.LAST_NAME='||r_person.LAST_NAME
               );

  BEGIN
    --logit;
    BEGIN
      logit('Inserted new employee_id='||v_cre_employee_id);
      DECLARE
        v_a_different_msg VARCHAR2(100);
      BEGIN
        rollback; -- Notice rollback
        v_a_different_msg := 'Inserted new employee_id='||v_cre_employee_id;
        logit( v_a_different_msg );
      END;
    END;
  END;

  --------------- 
END;
/   DECLARE
  v_dml_sel VARCHAR2(8000) := 
    'SELECT * FROM employees WHERE rownum < :rnum1';
  TYPE v_emp_tbltyp IS TABLE OF employees%ROWTYPE;
  v_emp_tbl v_emp_tbltyp;
BEGIN
  EXECUTE IMMEDIATE 
    v_dml_sel BULK COLLECT INTO v_emp_tbl USING 3; --rnum1
  FOR ii IN 1..v_emp_tbl.LAST
  LOOP msg('On Loop: '||ii||', '||v_emp_tbl(ii).last_name);
  END LOOP;     
END;


DECLARE
  v_dml_sel VARCHAR2(8000) := 
  'SELECT first_name, last_name, email, phone_number, hire_date
   FROM employees WHERE employee_id = :p_employee_id';
  r_emp employees%ROWTYPE;
BEGIN
  r_emp.employee_id := 101;
  execute immediate v_dml_sel 
     into r_emp.last_name,
          r_emp.first_name,
          r_emp.email,
          r_emp.phone_number,
          r_emp.hire_date
     using r_emp.employee_id;
    logit(r_emp.last_name||', email='||r_emp.email, 'D');
END;


DECLARE
  v_dml_sel VARCHAR2(8000) := 
  'SELECT first_name, last_name, email, phone_number, hire_date
   FROM employees WHERE employee_id = :p_employee_id';
  --r_emp_refcurs_id NUMBER;
  r_emp_refcurs sys_refcursor;
  r_emp employees%ROWTYPE;
  --v_rows_fetched NUMBER;
BEGIN
  --r_emp_refcurs_id := DBMS_SQL.open_cursor;
  OPEN r_emp_refcurs FOR v_dml_sel USING 101; 
  -- see (1)
  LOOP 
    FETCH r_emp_refcurs INTO 
         r_emp.first_name, 
         r_emp.last_name, 
         r_emp.email, 
         r_emp.phone_number, 
         r_emp.hire_date;
    EXIT WHEN r_emp_refcurs%NOTFOUND; 
    -- see (2)    
  END LOOP; 
    
    --DBMS_SQL.close_cursor(r_emp_refcurs_id);
  CLOSE r_emp_refcurs;
    
    logit(r_emp.last_name||', email='||r_emp.email, 'D');
    
END;

  -- see (1)
  /*
  DBMS_SQL.parse(r_emp_refcurs_id, v_dml_sel, DBMS_SQL.native );
  
  DBMS_SQL.define_column( r_emp_refcurs_id, 1, r_emp.first_name, 20);
  DBMS_SQL.define_column( r_emp_refcurs_id, 2, r_emp.last_name, 25);
  DBMS_SQL.define_column( r_emp_refcurs_id, 3, r_emp.email, 20);
  DBMS_SQL.define_column( r_emp_refcurs_id, 4, r_emp.phone_number, 25);
  DBMS_SQL.define_column( r_emp_refcurs_id, 5, r_emp.hire_date);
  
  DBMS_SQL.bind_variable( r_emp_refcurs_id, 'p_employee_id', 101);
  
  v_rows_fetched := DBMS_SQL.execute(r_emp_refcurs_id);
  */
  -- see (2)
  /*
  IF DBMS_SQL.FETCH_ROWS(r_emp_refcurs_id)> 0 THEN 
    DBMS_SQL.COLUMN_VALUE( r_emp_refcurs_id, 1, r_emp.first_name);
    DBMS_SQL.COLUMN_VALUE( r_emp_refcurs_id, 2, r_emp.last_name);
    DBMS_SQL.COLUMN_VALUE( r_emp_refcurs_id, 3, r_emp.email);
    DBMS_SQL.COLUMN_VALUE( r_emp_refcurs_id, 4, r_emp.phone_number);
    DBMS_SQL.COLUMN_VALUE( r_emp_refcurs_id, 5, r_emp.hire_date);
  ELSE 
    EXIT; 
  END IF;
  */

  
  
set serveroutput oFF

go top

6. Ref Cursor pointer to cursor (ee to record set object variable)

CREATE OR REPLACE PACKAGE PCK_EMPLOYEES IS
--Returned cursor variable of type SYS_REFCURSOR :
-- 1. can be opened for any query 2. it is like view with parameters id, rc...
PROCEDURE getrc_itms_by_mast_id(id IN NUMBER, rc IN OUT SYS_REFCURSOR) ;
--also works FUNCTION getrc_itms_by_mast_id (p_sifra IN NUMBER) RETURN SYS_REFCURSOR ;

PROCEDURE tbl_displ (p_rows IN SYS_REFCURSOR);

END PCK_EMPLOYEES ;
/
sho err

CREATE OR REPLACE PACKAGE BODY PCK_EMPLOYEES IS
PROCEDURE getrc_itms_by_mast_id (id IN NUMBER, rc IN OUT SYS_REFCURSOR) IS
BEGIN
OPEN rc FOR
select * from EMPLOYEES emp where emp.DEPARTMENT_ID = id
order by emp.DEPARTMENT_ID, emp.EMPLOYEE_ID
;
END getrc_itms_by_mast_id;


PROCEDURE tbl_displ(p_rows IN SYS_REFCURSOR) IS
v_row EMPLOYEES%ROWTYPE ;
BEGIN 
dbms_output.put_line('-- ********************** O U T P U T :' );
-- process each row
LOOP
FETCH p_rows INTO v_row ;
EXIT WHEN p_rows%notfound;
dbms_output.put_line('id_dep=' || v_row.DEPARTMENT_ID ||', id_emp=' || v_row.EMPLOYEE_ID);
END LOOP;

CLOSE p_rows;


EXCEPTION 
WHEN OTHERS THEN 
IF p_rows%ISOPEN THEN CLOSE p_rows; END IF; 
RAISE;

END tbl_displ;

END PCK_EMPLOYEES;
/
sho err

--CALL CLS "PCK_EMPLOYEES", METHODS getrc_itms_by_mast_id, tbl_displ
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
DECLARE
itms_by_mast_id_rc SYS_REFCURSOR ;
BEGIN
PCK_EMPLOYEES.getrc_itms_by_mast_id(30, itms_by_mast_id_rc); --1281, 1282
--itms_by_mast_id_rc := PCK_EMPLOYEES.getrc_itms_by_mast_id (1282); --1281, 1282
PCK_EMPLOYEES.tbl_displ(itms_by_mast_id_rc);
END;
/

-- ********************** O U T P U T :
id_dep=30, id_emp=114
id_dep=30, id_emp=115
id_dep=30, id_emp=116
id_dep=30, id_emp=117
id_dep=30, id_emp=118
id_dep=30, id_emp=119







--NATIVE DYNAMIC SQL – NDS : EXECUTE IMMEDIATE statement example:
CREATE FUNCTION F_row_cnt(tablica_p VARCHAR2) RETURN NUMBER IS 
  naredba VARCHAR2 (1000); 
  broj_redaka NUMBER; 
BEGIN 
  naredba := 'SELECT COUNT (*)' || '  FROM ' || tablica_p; 
  EXECUTE IMMEDIATE naredba INTO broj_redaka; 
  RETURN broj_redaka; 
END; 
/
sho err

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
exec dbms_output.put_line('-- ********************** O U T P U T :' );
exec dbms_output.put_line('row_cnt emps=' || F_row_cnt('employees'));
-- ********************** O U T P U T :
row_cnt emps=107







SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
-- create dynamic cursor varijable
DECLARE 
  rcdyn SYS_REFCURSOR; 
  stmt_str VARCHAR2(200); 

  LAST_NAME VARCHAR2(100); 
  SALARY NUMBER; 
BEGIN 
  stmt_str := 'SELECT LAST_NAME, SALARY' || ' FROM employees' || ' WHERE JOB_ID = :1' ;
  -- create dynamic cursor varijable :
  dbms_output.put_line('-- ********************** O U T P U T :' );
  OPEN rcdyn FOR stmt_str USING 'SA_MAN';  
  LOOP 
    FETCH rcdyn INTO LAST_NAME, SALARY;
    EXIT WHEN rcdyn%NOTFOUND;  


    --procesiranje podataka
    dbms_output.put_line('LAST_NAME=' || LAST_NAME ||', SALARY=' || SALARY);
  END LOOP;  
  CLOSE rcdyn; 
END; 
/ 

-- ********************** O U T P U T :
LAST_NAME=Russell, SALARY=14000
LAST_NAME=Partners, SALARY=13500
LAST_NAME=Errazuriz, SALARY=12000
LAST_NAME=Cambrault, SALARY=11000
LAST_NAME=Zlotkey, SALARY=10500