Tag Archives: Oracle

2a. Oracle 11g PL/SQL Tutorial

Posted on by
Reply

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

9. Main development, test and production menu (& 3 sites) – PHP 7 RC5, Oracle 11g on Windows 10 all 64 bit

Posted on by
Reply

HOME – Download Win10 64 bit AMP config and three sites php scripts.

14.8.2016 I changed a lot, see my article 10. This article 9 can be used only for explanations but everything is in code in 1_3sites_mnu_crud_ver1.rar.

22.11.2015 code refactored – i uploaded sites code version 3, (SINGLETON UTILS-HELPERS CLASS, config_site.php…).

Scripts config_site.php, utls.php, utls_glbsetpg.php and lsweb.php explain most important changes. I hope this is final code skeleton, ver4 will be bug fixes and cosmetic updates. (In ver3 I tested only most important lsweb.php but testing others is good exercise – and it shows how difficult this job is).

Statements (functions) flow article (not yet uploaded) should link together all I wanted to say.

Every (vitual) host – I have them 3: dev, test, production – should have in its web site doc root script which tells web server where are resources which are outside doc tree (global fns, settings, css, imgs)

(Problem with resources outside web doc tree is that we need file_get_contents() fn which is disabled by some inet providers. But if you want own SOAP server eg for direct b2b copying einvoice xml – then every your user should have own web site (bad news for some not needed service brokers – posrednici).)

Most important are:
1. “path science” – unexplained enough in learning materials I met (for SPA script very different than for URL called script).
2. Statements (functions) flow (to understand SW & for debugging) – I shall soon write article 10 or 11 (?) about this (SOAP examples for copying xml across inet will also be soon).

I can not understand SPA & frameworks without above two explained. To me seems that framework authors intentionally do not explain important things about their scripts coding (there is to much commercial interests in free SW) – so we have 100 frameworks instead 5-6.

I am shure that Utils class should be, settings should be in global_config.php both outside web doc tree. Local settings in doc tree should owerwrite global.

Eg my : J:\awww\apl\dev1\config_site.php contais:

 < ?php
 // development site (virtual host on home PC) :
 // J:\awww\apl\dev1\config_site.php
//$_SERVER['DOCUMENT_ROOT'] = J:\awww\apl\dev1\
 // yii2 advanced site (virtual host on home PC) :
 // J:\awww\apl\dev1\zfw\yii205\frontend\web\config_site.php
 // J:\awww\apl\dev1\zfw\yii205\aplmy\backend\web\config_site.php
 // realpath
 use utlmoj\utlmoj as utl;
 $ds = DIRECTORY_SEPARATOR;
 /**
 * CONVENTION: sitedocroot/../zinc = eg J:/awww/apl/zinc
 * site does not know where (outside site doc tree) are global resources
 * (util scripts, css, img for all sites), so we must assign
 * 1. GLOBAL RESOURCES (INCLUDES) FOLDER :
 */
 $gloresdir = realpath($_SERVER['DOCUMENT_ROOT'].'/../zinc'); // 1.
 require_once($gloresdir.'/utls.php'); // 2. util (helper) scripts
 $utl=utl::uget(); // 3. get or create helper fns object (singleton)
 require_once($gloresdir.'/utls_glbsetpg.php'); // 4. global page properties
 /**
 * IN APPL (IN SPA) IF IT NEEDS C R U D :
 * 2. g l o b a l C R U D :
 * $dbi = 'sqlite'; $dsn = 'cars_makes_names_savings.sqlite';
 * require_once($gloresdir.$ds.'db_conn.php');
 * db_conn.php does:
 * 1. require_once($gloresdir.'/klase/dbi.php');
 * 2. require_once($gloresdir.'/tbl/zodiac_mdl.php);
 * CONVENTION for M D L of concrete tbl :
 * require_once($gloresdir.'/tbl/'
 * .str_replace('.php','_mdl.php', basename($curpgpath)));
 * 3. template crud script :
 * require_once($gloresdir.$ds.'crud.php');
 */
 // ******************************************
 //exit();
 ?>

I am not shure if Utils class should have static or non static or both methods & properties (because you need many years PHP programming experience to be shure). Properties of this two working ways are not clear to me and unexplained enough in my learning sources.

Pitty that somebody – PHP expert – which I am not – does not explain this somewhere.

2.Sept.2015 – site_ver2.rar – improved code for all scripts, eg for lsweb.php and added some dir icons and better presented/explained site dir structure (see awww_DIR_NOT_VISIBLE_TO_ME.txt).

This article is enough for (advanced) beginning PHP programming Oracle and SQLite CRUD. 
Articles 1 to 8 are supplementary info.

This article unites my posts 3. Zwamp menu and 5. CRUD simple table (example 1) with refactored code for 5. CRUD and my (I hope) final site directories structure.

Most important examples in this article (others are in site_verx.rar):

Example Šifrarnik – is not finished, but shows much. Model is table (id, few_columns)

Example web server directories listing – dir items listing can be extended with row filters, sorts, downloads… but so as it is is very useful for web development.
Model (input data) is DirectoryIterator().
I find lsweb.php very useful for web development:
J:\awww\apl\dev1\zinc\utl\ls.php          http://dev1:8083/zinc/utl/ls.php  (or with ?dir=J:\awww\apl\dev1)
J:\awww\apl\dev1\zinc\utl\lsweb.php

Code for article 3. Zwamp menu is also contained in  site_verx.rar – it is to complicated for real life sites (except if you like something like)  but is excellent for learning PHP.

 

Oracle example 1

Model (input data) is simple table (šifrarnik) (id, few_columns), but more than 50% programming techniques can be learned on this and next example.


J:\awww\apl\dev1\zinstalac\ddl\DDL_selfjoin.sql:
-- winkey+X -> Comm.prompt admin
C:
cd C:\oraclexe\app\oracle\product\11.2.0\server\bin
sqlplus hr/hr
sho user
CREATE TABLE ZODIAC (
  ID          NUMBER(10) NOT NULL,
  SIGN        VARCHAR2(11),
  SYMBOL      VARCHAR2(13),
  PLANET      VARCHAR2(7),
  ELEMENT     VARCHAR2(5),
  START_MONTH INTEGER,
  START_DAY   INTEGER,
  END_MONTH   INTEGER,
  END_DAY     INTEGER,
  PRIMARY KEY(ID)
);

CREATE SEQUENCE ZODIAC_SEQ;
CREATE or replace TRIGGER ZODIAC_PREINS_TRIG 
BEFORE INSERT ON ZODIAC 
FOR EACH ROW
BEGIN
  -- PRIOR TO 11G :
  SELECT ZODIAC_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  -- 11G :
  --:NEW.MSG_ID := ZODIAC_SEQ.NEXTVAL;
END;
/
sho err

DML :
INSERT INTO zodiac VALUES (1,'Aries','Ram','Mars','fire',3,21,4,19);
INSERT INTO zodiac VALUES (2,'Taurus','Bull','Venus','earth',4,20,5,20);
INSERT INTO zodiac VALUES (3,'Gemini','Twins','Mercury','air',5,21,6,21);
INSERT INTO zodiac VALUES (4,'Cancer','Crab','Moon','water',6,22,7,22);
INSERT INTO zodiac VALUES (5,'Leo','Lion','Sun','fire',7,23,8,22);
INSERT INTO zodiac VALUES (6,'Virgo','Virgin','Mercury','earth',8,23,9,22);
INSERT INTO zodiac VALUES (7,'Libra','Scales','Venus','air',9,23,10,23);
INSERT INTO zodiac VALUES (8,'Scorpio','Scorpion','Mars','water',10,24,11,21);
INSERT INTO zodiac VALUES (9,'Sagittarius','Archer','Jupiter','fire',11,22,12,21);
INSERT INTO zodiac VALUES (10,'Capricorn','Goat','Saturn','earth',12,22,1,19);
INSERT INTO zodiac VALUES (11,'Aquarius','Water Carrier','Uranus','air',1,20,2,18);
INSERT INTO zodiac VALUES (12,'Pisces','Fishes','Neptune','water',2,19,3,20);

Scripts directory structure (SPA, MVC domain style)

SPA means scripts are included, only exceptionally URL called. So scripts see all $ variables as own.

In SPA is not posible using relative page adresses because path relative to included script is not relative to includer (SPA) script  – adresses syntax explained here must be used !! To learn dirs science is one of most difficult PHP beginning parts, and allmost NOT EVEN MENTIONED in PHP learning materials.

MVC domain style means Scripts directory structure is “one form (application) one dir (& its subdeirs if needed)”. MVC is basically old structured programming:

  1. initialize (bootstrap, config),
  2. manage (ctr),
  3. input (model),
  4. output (view).

It is interesting that no one MVC promotor mentions this simple truth. Names are most important, but working means not (only) inventing new names but understand+explain.

DDLs are in J:\awww\apl\dev1\zinstalac\ddl\,  eg above  DDL_selfjoin.sql contain also selfjoin DDL & for SQLite.

Global config scripts (.php, .css, .js…most important of all types !) are in J:\awww\apl\dev1\zinc\,  eg  utls.php.

Below zinc are dirs: utl (helpers), slike (img), js…

Three sites (Apache virtual hosts on home PC):

Development URL is http://dev1:8083/ is Windows dir J:\awww\apl\dev1.

Production URL is http://pro1:8083/  is Windows dir J:\awww\apl\pro1.

Simmilar are both test site adresses.

 

After huderts Oracle Forms 6i created from scratch (from existing form ctrl+c,v) in more then 15 years it seems to me, whatever they say :), for PHP & Oracle should be :

CONVENTIONS FOR BETTER SCRIPTS VISIBILITY

  1. No camelcase becouse name higher_lower is (to me) better visible then camelcase higherLower
  2. Scripts sufixes :
    1. controller scripts have no sufix
    2. other scripts :
      1. model scripts: _mdl, _val (validation scripts)
      2. view scripts: _frm, _tbl, _rep
  3. ctr / bootstrap scripts are NOT named index.php, but are named simmilar to DB table name which they (CRUD) manage.Beside script visibility, this enables us to have more/all small tables crud (forms) scripts in one dir.To many dirs is not practical and old principle “one form (application) one dir (& its subdeirs if needed)” is newest fashion (2015 year).
  4. GLOBAL configuration scripts are in $CNFGD=J:\awww\apl\dev1\zinc, eg $CNFGD.$DS.utls.phputls.php (with help of config not static class & namespace) defines PHP $  (adress) variables for all included scripts (SPA !!) (no constants except pi and simmilar). Conf. vars must be defined as $ vars in utls.php and not as concatenation of dirs in all index.php scripts, becouse :
    IF DIR TREE CHANGES -> WE CHANGE ONLY ONE PLACE: utls.php
  5. Some strange names are better for search or for name conflicts (eg zinc instead of includes, chcons instead configclass, this example zodiac…)

 

Example 2: Web server directories listing programs

Model (input data) is DirectoryIterator() dir items listing.

I find them very useful for web development:
J:\awww\apl\dev1\zinc\utl\ls.php          http://dev1:8083/zinc/utl/ls.php  (or with ?dir=J:\awww\apl\dev1)
J:\awww\apl\dev1\zinc\utl\lsweb.php

 Conclusion

I did not see advantages in my testings compared with programming techniques I explained in this article (and in other before):

  1. AngularJS 1.4.3 CRUD with Oracle 11g
  2. Yii 2.0.6 with PHP 7 beta 3
  3. FatFree PHP fw

But frameworks above:

  1. no Oracle DBI example for normal people given
  2. It seems to me they are unfinished – always in development (uncompatible new versions), always new fws appear.
  3. slower ( min ~ 150 kB code with uncertain future included),
  4. more complicated – another (more) programming languages
  5. They do not even mention reporting (php reports from Eustáquio Rangel or similar) or tab key -> enter key which I made with few lines Javascript code (see site_ver1.rar J:\awww\apl\dev1\zinc\key_pressed.js).
  6. I doubt if they are more productive – learn time for programming techniques I explained in this article is ~ as for them  (lot of time 🙂 )
  7. to many incompatibilities uncertain future (yii2, ng 2),
  8. …

Until authors of fw sw convince us on CRUD examples I give in this article + master-detail example I can not believe their to simple / not clear CRUD examples.

~~~utls.php $test=1 ~~~end script J:\awww\apl\dev1\index.php   source   phpinfo~~~

 

8. Understand AngularJS (ng) ver. 1.4.3 & PHP server script Get Emp from Oracle DB 11g

Posted on by
Reply

HOME   – Download code

2.Sept.2015  I uploaded  ng_understand.rar – it contains now all CRUD code.

It seems to me that ng is very nice but brings no advantage – PHP is enough, and problem is to learn two programming techniques, to include ng (minimum 150 kB) …

We fill in $scope ng object with Oracle DB 11g data with help of
AJAX “client-server” PHP script get_emp_data.php (hr user).

Output page shows (looks better with .css included in zip):

ng & PHP server script Get Emp from oracle DB 11g

Search:

EmpId LastName HireDate
DML: select EMPLOYEE_ID, LAST_NAME, HIRE_DATE from (select EMPLOYEE_ID, LAST_NAME,to_char(HIRE_DATE,’RRRR.DD.MM’) HIRE_DATE from EMPLOYEES order by LAST_NAME) where ROWNUM < 11
167 Banda 2008.21.04
116 Baida 2005.24.12

This script get_emp.html 15.7.2015 says:

  1. Script get_emp.html is same as index.html – understand ng.
    Included is app.js code for quick testing.
    Added are few additional lines to fill in $scope object from Oracle DB 11g data with help of
    AJAX “client-server” called PHP script get_emp_data.php.
  2. Search field works after every character typed in.
  3. Click on eg HireDate column title works but not for both sorts asc/desc – I shall do this later.
  4. I shall add later to this post (to ng_understand.zip)
    all CRUD functions in programming techniques :
    ng – PHP server scripts called with AJAX (client-server web programming technique) – Oracle DB 11g.

    // get_emp.html contains app.js code for quick testing
// HH24:mi:ss
$http.get("get_emp_data.php?sqlStr="
  +"select EMPLOYEE_ID, LAST_NAME, HIRE_DATE from"
  +" (select EMPLOYEE_ID,  LAST_NAME"
  +   ",to_char(HIRE_DATE,'RRRR.DD.MM') HIRE_DATE
  + " from EMPLOYEES order by LAST_NAME)"
  +" where ROWNUM < 11"
)
.success(
    function(response) {
      $scope.myData  = response;
      $scope.reverse = true;
    }
)  
.error(
   function() {
            
        }
) ;
  5. AJAX “client-server” called PHP script get_emp_data.php has ~five important statements: 
    <?php
/*
http://dev1:8083/my_dev/test/...get_emp_data.php
http://dev1:8083/my_dev/test/...get_emp_data.php?sqlStr=select...
J:\awww\apl\dev1\my_dev\test\...get_emp_data.php
*/
$pdo=new PDO('oci:dbname=sspc/XE','hr','hr');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare($_GET['sqlStr']);
/*
$stmt = $pdo->prepare(
  'select EMPLOYEE_ID,  LAST_NAME'
  .',to_char(HIRE_DATE,\'DD.MM.RRRR\') HIRE_DATE' // HH24:mi:ss
  .' from EMPLOYEES where ROWNUM < 16'
);
*/

$stmt->execute();
$arr = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   $arr[] = $row;
}
//echo '<pre>'; print_r($arr); echo '</pre>';
//$arr = $_GET; // {"sqlStr":"select..."}

$arr[] = Array (
    'EMPLOYEE_ID' => "DML:"
   ,'LAST_NAME'   => $_GET['sqlStr']
   ,'HIRE_DATE'   => ''
);
//{"EMPLOYEE_ID":"114","LAST_NAME":"Raphaely","HIRE_DATE":"07.12.2002"},
//{"EMPLOYEE_ID":-1,"LAST_NAME":"select...","HIRE_DATE":""}]
$json_response = json_encode($arr);
echo $json_response;
?>