2002 Tips
2001 Tips
2000 Tips
1999 Tips
1998 Tips
1997 Tips

January's Tip of the Month

Simulating LOCK TIMEOUT with INSERT/UPDATE/DELETE Statements
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr).

The SELECT statement has a FOR UPDATE clause, which allows you to lock the selected rows so that other users cannot lock or update the rows until you end your transaction. The FOR UPDATE clause has the optional keyword NOWAIT. NOWAIT returns control to you if the SELECT statement attempts to lock a row that is locked by another user. If you omit this clause, Oracle waits until the row is available and then returns the results of the SELECT statement.

INSERT/UPDATE/DELETE statements do not have (and probably will never have) a NOWAIT keyword or any sort of LOCK TIMEOUT keywords.

This is not a big problem with UPDATE/DELETE statements, because you can call SELECT ... FOR UPDATE NOWAIT before you call UPDATE/DELETE:

-- First database session
BEGIN
   UPDATE dept
      SET dname = dname
    WHERE deptno = 10;
END;
/
-- Second database session
DECLARE
   l_rowid ROWID;
BEGIN
   SELECT ROWID INTO l_rowid
     FROM dept
    WHERE deptno = 10
      FOR UPDATE NOWAIT;

   UPDATE dept
      SET dname = dname
    WHERE ROWID = l_rowid;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE = -54 THEN 
        -- ORA-00054: resource busy and acquire with NOWAIT specified
         DBMS_OUTPUT.PUT_LINE ('DEPT is locked - you can''t update!');
      ELSE
         RAISE;
      END IF;
END;
/

Calling SELECT ... FOR UPDATE NOWAIT before INSERT, however, has no effect because SELECT in the second database session can't see the non-committed row:

-- First database session:
BEGIN
   INSERT INTO dept
      (deptno, dname)
      VALUES
      (99, 'DEPT 99');
END;
/
-- Second database session:
DECLARE
   l_dummy NUMBER;
BEGIN
   SELECT 1 INTO l_dummy
     FROM dept
    WHERE deptno = 99
      FOR UPDATE NOWAIT;

   DBMS_OUTPUT.PUT_LINE ('DEPT exists!');
   ROLLBACK;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     INSERT INTO dept
        (deptno, dname)
        VALUES
        (99, 'DEPT 99');
   WHEN OTHERS THEN
      IF SQLCODE = -54 THEN
         DBMS_OUTPUT.PUT_LINE ('DEPT is locked - you can''t insert!');
      ELSE
         RAISE;
      END IF;
END;
/
(SELECT found nothing and INSERT waits)

We don't have a distributed transaction, but we'll execute a quasi-remote statement using a local database link:

CREATE DATABASE LINK local_db_link
   CONNECT TO scott IDENTIFIED BY tiger using 'local_alias' 
     -- alias for your local database
/

-- First database session:
BEGIN
   INSERT INTO dept
      (deptno, dname)
      VALUES
      (99, 'DEPT 99');
END;
/
-- Second database session:
BEGIN
   INSERT INTO dept@local_db_link 
     -- QUASI-REMOTE STATEMENT!!!
      (deptno, dname)
      VALUES
      (99, 'DEPT 99');
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
     DBMS_OUTPUT.PUT_LINE ('DEPT exists!');
   WHEN OTHERS THEN
      IF SQLCODE = -2049 THEN 
        -- ORA-02049: timeout: distributed transaction waiting for lock
         DBMS_OUTPUT.PUT_LINE ('DEPT is locked - you can''t insert!');
      ELSE
         RAISE;
      END IF;
END;
/
DEPT is locked - you can't insert!
--(message when first session COMMITs after time defined 
--in parameter DISTRIBUTED_LOCK_TIMEOUT)

February's Tip of the Month

Sorting a Collection
Compliments of Dan Clamage, Pipeline SYSOP, (DClamage@MSA.com).

The topic of sorting a collection came up in our shop recently. So for grins I thought I'd use the TABLE(CAST...))) technique to sort the nested table and store it back into itself.

DECLARE
  arr_var_seq obj_var_seq;
BEGIN
  SELECT var_seq
  BULK COLLECT INTO arr_var_seq
  FROM fsb_variables
  WHERE var_active_flag = 'A'
    AND var_actual_type = 'L'
  ORDER BY DBMS_RANDOM.random  -- so var seq's out of order
  ;
  DBMS_OUTPUT.put_line('Random Order:');
  DBMS_OUTPUT.put_line('loaded #seqs=' || TO_CHAR(arr_var_seq.COUNT));
  FOR i IN arr_var_seq.FIRST .. arr_var_seq.LAST LOOP
    DBMS_OUTPUT.put_line('var_seq=' || TO_CHAR(arr_var_seq(i)));
  END LOOP;
  -- now sort them
  SELECT column_value var_seq
  BULK COLLECT INTO arr_var_seq
  FROM TABLE(CAST(arr_var_seq AS obj_var_seq)) t
  ORDER BY column_value  -- should give var seq order
  ;
  DBMS_OUTPUT.put_line('Sorted Order:');
  DBMS_OUTPUT.put_line('sorted #seqs=' || TO_CHAR(arr_var_seq.COUNT));
  FOR i IN arr_var_seq.FIRST .. arr_var_seq.LAST LOOP
    DBMS_OUTPUT.put_line('var_seq=' || TO_CHAR(arr_var_seq(i)));
  END LOOP;
END;

DECLARE
 *
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 24
Random Order:
loaded #seqs=7
var_seq=824
var_seq=826
var_seq=827
var_seq=828
var_seq=823
var_seq=825
var_seq=829
Sorted Order:
sorted #seqs=0

DECLARE
  arr_random_var_seq obj_var_seq;
  arr_sorted_var_seq obj_var_seq;
BEGIN
  SELECT var_seq
  BULK COLLECT INTO arr_random_var_seq
  FROM fsb_variables
  WHERE var_active_flag = 'A'
    AND var_actual_type = 'L'
  ORDER BY DBMS_RANDOM.random  -- so var seq's out of order
  ;
  DBMS_OUTPUT.put_line('Random Order:');
  DBMS_OUTPUT.put_line('loaded #seqs=' ||
TO_CHAR(arr_random_var_seq.COUNT));
  FOR i IN arr_random_var_seq.FIRST .. arr_random_var_seq.LAST LOOP
    DBMS_OUTPUT.put_line('var_seq=' || TO_CHAR(arr_random_var_seq(i)));
  END LOOP;
  -- now sort them
  SELECT column_value var_seq
  BULK COLLECT INTO arr_sorted_var_seq
  FROM TABLE(CAST(arr_random_var_seq AS obj_var_seq)) t
  ORDER BY column_value  -- should give var seq order
  ;
  DBMS_OUTPUT.put_line('Sorted Order:');
  DBMS_OUTPUT.put_line('sorted #seqs=' ||
TO_CHAR(arr_sorted_var_seq.COUNT));
  FOR i IN arr_sorted_var_seq.FIRST .. arr_sorted_var_seq.LAST LOOP
    DBMS_OUTPUT.put_line('var_seq=' || TO_CHAR(arr_sorted_var_seq(i)));
  END LOOP;
END;

Random Order:
loaded #seqs=7
var_seq=823
var_seq=824
var_seq=827
var_seq=829
var_seq=828
var_seq=826
var_seq=825
Sorted Order:
sorted #seqs=7
var_seq=823
var_seq=824
var_seq=825
var_seq=826
var_seq=827
var_seq=828
var_seq=829

DECLARE
  arr_random_var_seq obj_var_seq;
  arr_sorted_var_seq obj_var_seq;
BEGIN
  SELECT var_seq
  BULK COLLECT INTO arr_random_var_seq
  FROM fsb_variables
  WHERE var_active_flag = 'A'
    AND var_actual_type = 'L'
  ORDER BY DBMS_RANDOM.random  -- so var seq's out of order>
  ;
  DBMS_OUTPUT.put_line('Random Order:');
  DBMS_OUTPUT.put_line('loaded #seqs=' ||
TO_CHAR(arr_random_var_seq.COUNT));
  FOR i IN arr_random_var_seq.FIRST .. arr_random_var_seq.LAST LOOP
    DBMS_OUTPUT.put_line('var_seq=' || TO_CHAR(arr_random_var_seq(i)));
  END LOOP;
  -- now sort them
  SELECT column_value var_seq
  BULK COLLECT INTO arr_sorted_var_seq
  FROM TABLE(CAST(arr_random_var_seq AS obj_var_seq)) t
  ORDER BY column_value  -- should give var seq order
  ;
  DBMS_OUTPUT.put_line('Sorted Order:');
  DBMS_OUTPUT.put_line('sorted #seqs=' ||
TO_CHAR(arr_sorted_var_seq.COUNT));
  FOR i IN arr_sorted_var_seq.FIRST .. arr_sorted_var_seq.LAST LOOP
    DBMS_OUTPUT.put_line('var_seq=' || TO_CHAR(arr_sorted_var_seq(i)));
  END LOOP;
  -- copy the sorted list back over the random list
  arr_random_var_seq := arr_sorted_var_seq;
  DBMS_OUTPUT.put_line('Original List:');
  DBMS_OUTPUT.put_line('loaded #seqs=' ||
TO_CHAR(arr_random_var_seq.COUNT));
  FOR i IN arr_random_var_seq.FIRST .. arr_random_var_seq.LAST LOOP
    DBMS_OUTPUT.put_line('var_seq=' || TO_CHAR(arr_random_var_seq(i)));
  END LOOP;
END;

Random Order:
loaded #seqs=7
var_seq=827
var_seq=825
var_seq=823
var_seq=829
var_seq=824
var_seq=826
var_seq=828
Sorted Order:
sorted #seqs=7
var_seq=823
var_seq=824
var_seq=825
var_seq=826
var_seq=827
var_seq=828
var_seq=829
Original List:
loaded #seqs=7
var_seq=823
var_seq=824
var_seq=825
var_seq=826
var_seq=827
var_seq=828
var_seq=829

March's Tip of the Month

Script to Generate a CSV File from any Oracle Table
Compliments of Nilesh Chindarkar, (Nilesh.Chindarker@lvl.co.uk).

The PL/SQL package below will transform any Oracle table data into a CSV format file. It will also accept any user defined delimiter or separation characters. The package requires access to the Oracle supplied UTL_FILE package.

1. Compile the package below in your schema.
    
2. Call the package using either the following PL/SQL block or SQL+ command:

   BEGIN 
     pkg_csv.prc_table_to_csv (p_table_name    => 'EMP',
                               p_sep_char      => '|', -- Default is "," (comma)
                               p_filename      => 'Output.txt', -- File Name 
                               p_file_location => 'c:\temp' -- Path of the file, 
                               --please confirm your UTL_FILE_DIR path with DBA.
                               );
   END;
   /

   OR

   sql> execute pkg_csv.prc_table_to_csv('EMP',p_file_location => 'c:\temp');

The Package:

CREATE OR REPLACE PACKAGE Pkg_Csv IS

PROCEDURE prc_table_to_csv (p_table_name VARCHAR2,
                            p_sep_char VARCHAR2   DEFAULT '","',
			    p_filename VARCHAR2   DEFAULT 'Output.txt',
			    p_file_location VARCHAR2
			    );
						   
PROCEDURE Prc_Dsql(p_sql VARCHAR2);

END Pkg_Csv;
/

CREATE OR REPLACE PACKAGE BODY Pkg_Csv IS

--
PROCEDURE Prc_Dsql(p_sql   IN VARCHAR2)
--
IS
BEGIN
   dbms_output.put_line(p_sql);
   EXECUTE IMMEDIATE p_sql;
--
END Prc_Dsql;
--

--
PROCEDURE prc_table_to_csv (p_table_name VARCHAR2,
                            p_sep_char VARCHAR2   DEFAULT '","',
			    p_filename VARCHAR2 DEFAULT 'Output.txt',
			    p_file_location VARCHAR2
			    )
--
IS

CURSOR cur_table_data 
IS
SELECT column_name
FROM   user_tab_columns
WHERE  table_name = p_table_name;

v_line                         VARCHAR2(20000);
v_sep                          VARCHAR2(30);

file_handle                    UTL_FILE.FILE_TYPE;


TYPE TabCurTyp IS              REF CURSOR;
v_TabCurTyp                    TabCurTyp;

BEGIN

   v_sep    := '||''' || p_sep_char|| '''||';
   dbms_output.put_line(v_sep);
   
   FOR  cur_table_data_rec IN cur_table_data
   LOOP
      v_line := v_line || cur_table_data_rec.column_name || v_sep;
   END LOOP;
   
   v_line := SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) );
   
   IF p_sep_char = '","'
   THEN
      v_line := ''''||'"'||'''||' || v_line || '||''' || '"' || '''';
	  dbms_output.put_line(v_line);   
   END IF;			    
   
   v_line := 'SELECT ' || v_line || ' FROM ' || p_table_name;
   
   /*
      v_line := 'SELECT '||
             SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) )||
             ' FROM ' || p_table_name ;
		*/	 


   dbms_output.put_line(v_line);

   OPEN  v_TabCurTyp FOR v_line;
   
   file_handle := UTL_FILE.FOPEN(p_file_location, p_filename, 'W');
   
   LOOP
   
      FETCH v_TabCurTyp INTO v_line;
      EXIT WHEN v_TabCurTyp%NOTFOUND;
	  
      UTL_FILE.PUT_LINE(file_handle,v_line);
      dbms_output.put_line(v_line);
	  
   END LOOP;
   
   CLOSE v_TabCurTyp;
   UTL_FILE.FCLOSE(file_handle);
   
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH
   THEN
      dbms_output.put_line('file location or name was invalid');
      RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);    
   WHEN UTL_FILE.INVALID_MODE
   THEN
      dbms_output.put_line('the open_mode string was invalid');
      RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);    
   WHEN UTL_FILE.INVALID_FILEHANDLE
   THEN
      dbms_output.put_line('not a valid file handle');
      RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);    
   WHEN UTL_FILE.INVALID_OPERATION
   THEN
      dbms_output.put_line('File could not be opened as requested');
      RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);    
   WHEN UTL_FILE.READ_ERROR
   THEN
      dbms_output.put_line('OS error occurred during read');
      RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);    
   WHEN UTL_FILE.WRITE_ERROR
   THEN
      dbms_output.put_line('OS error occured during write operation');
      RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);    
   WHEN UTL_FILE.INTERNAL_ERROR
   THEN
      dbms_output.put_line('Internal Error');
      RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);    
   WHEN OTHERS
   THEN
      UTL_FILE.FCLOSE(file_handle);
	  RAISE;
	     
END prc_table_to_csv;

END Pkg_Csv;
/

April's Tip of the Month

Deferred Database Constraints and Forms
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr).

Oracle (version 8.0 and later) has the ability to delay the checking of declarative constraints (NOT NULL, Primary Key, Unique Key, Foreign Key, Check Constraints) until the commit. Unfortunately, if we use Oracle Forms as a front-end application, the errors raised from deferred constraints are not handled very well.

A more serious problem arises if we use POST built-in in Forms.

For example, if we add a deferred primary key to the DEPT table:

ALTER TABLE dept
   ADD CONSTRAINT dept_pk PRIMARY KEY (deptno) INITIALLY DEFERRED
   USING INDEX
/

When we insert two records with the same value in column deptno and send a COMMIT statement in SQL+, we get the following errors:

ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.DEPT_PK) violated

OK, we can wait for the patch. But what do we do when we want to use POST statement in Forms? POST built-in writes data in the form to the database, but does not perform a database COMMIT. After POSTing rows, Forms "forgets" which rows are inserted/changed/deleted. The problem with deferred database constraints is that when something goes wrong within the transaction, the complete transaction is rolled back after the COMMIT statement.

The solution for both problems is simple - we can check deferred constraints before the COMMIT statement occurs:

SET CONSTRAINT dept_pk IMMEDIATE;

FORMS_DDL ('SET CONSTRAINT dept_pk IMMEDIATE');

FORMS_DDL ('SET CONSTRAINT dept_pk IMMEDIATE'); 
IF NOT FORM_SUCCESS THEN 
   MESSAGE ('COMMIT ERROR: ' || DBMS_ERROR_CODE || ' ' || DBMS_ERROR_TEXT); PAUSE; 
   FORMS_DDL ('SET CONSTRAINT dept_pk DEFERRED'); 
   RAISE FORM_TRIGGER_FAILURE; 
ELSE 
   FORMS_DDL ('SET CONSTRAINT dept_pk DEFERRED'); 
   COMMIT_FORM; 
END IF;

To resolve this, instead of using SET CONSTRAINT directly in Forms, we can write a database package like the following:

CREATE OR REPLACE PACKAGE deferred_constraints AS 
   PROCEDURE set_immediate (p_constraint VARCHAR2); 
   PROCEDURE set_deferred  (p_constraint VARCHAR2); 
END deferred_constraints; 
/ 
CREATE OR REPLACE PACKAGE BODY deferred_constraints AS 
   PROCEDURE set_immediate (p_constraint VARCHAR2) IS 
   BEGIN 
      EXECUTE IMMEDIATE 'SET CONSTRAINT ' || p_constraint || ' IMMEDIATE'; 
   END; 

   PROCEDURE set_deferred (p_constraint VARCHAR2) IS 
   BEGIN 
      EXECUTE IMMEDIATE 'SET CONSTRAINT ' || p_constraint || ' DEFERRED'; 
   END; 
END deferred_constraints; 
/ 

-- user like SYSTEM 
CREATE PUBLIC SYNONYM deferred_constraints FOR scott.deferred_constraints 
/ 
-- user SCOTT 
GRANT EXECUTE ON deferred_constraints TO your_user 
/

FORMS_DDL ('SET CONSTRAINT dept_pk IMMEDIATE');

FORMS_DDL ('BEGIN deferred_constraints.set_immediate (''dept_pk''); END;');

Even cleaner than that, however, is to write:

BEGIN
   deferred_constraints.set_immediate ('dept_pk');
   deferred_constraints.set_deferred  ('dept_pk');
   COMMIT_FORM;
EXCEPTION
   WHEN OTHERS THEN
      MESSAGE ('COMMIT ERROR: ' || DBMS_ERROR_CODE || ' ' || DBMS_ERROR_TEXT); PAUSE;
      deferred_constraints.set_deferred ('dept_pk');
      RAISE FORM_TRIGGER_FAILURE;
END;

May's Tip of the Month

Dynamic Queries in Pro*C without Method-4 or ANSI Dynamic SQL
Compliments of Dan Clamage, Pipeline SYSOP, (DClamage@MSA.com).

You can use dynamic ref cursors in PL/SQL to provide easy-to-maintain dynamic SQL in Pro*C. Method-4 and ANSI-Dynamic SQL require much more code. This technique is suitable when the SELECT clause is unchanging, but the rest of the query is completely dynamic. Here is a code snippet:

  // define bind variables
  EXEC SQL BEGIN DECLARE SECTION;
      sql_cursor ref_store;  /* ref cursor for processing list */
      int sic_derived_start_pos = 0;
      int sic_derived_len = 0;
      int sic_collapse_src_start_pos = 0;
      int sic_collapse_search_len = 0;
      int log_file_ID;
      char sic_derived_col_name[101];
      char sic_collapse_src_col_name[101];
      int rec_no;
      char sic_code[17];
      char collapse_type[101];
  EXEC SQL END DECLARE SECTION;
 
  // assign values to input bind variables...
 
  // set up a dynamic ref cursor to get all store rows
  // open the ref cursor
  EXEC SQL ALLOCATE :ref_store;
  EXEC SQL EXECUTE
  DECLARE
    v_stmt VARCHAR2(32767) :=
'SELECT s.record_number,SUBSTR(s.~siccol,:sicpos,:siclen) sic_code,
                        SUBSTR(s.~typcol,:typepos,:typelen) collapse_type 
                        FROM stage_store s WHERE s.log_file_oid=:log';
  BEGIN
    v_stmt := REPLACE(v_stmt, '~siccol', :sic_derived_col_name);
    v_stmt := REPLACE(v_stmt, '~typcol', :sic_collapse_src_col_name);
    OPEN :ref_store FOR v_stmt
    USING
      :sic_derived_start_pos
     ,:sic_derived_len
     ,:sic_collapse_src_start_pos
     ,:sic_collapse_search_len
     ,:log_file_ID;
  END;
  END-EXEC;
 
  EXEC SQL WHENEVER NOT FOUND DO break;
  if (sqlca.sqlcode == 0)
  {
    for (;;)
    {
      EXEC SQL FETCH :ref_store
      INTO :rec_no, :sic_code, :collapse_type;
      EXEC SQL WHENEVER NOT FOUND DO continue;
 
      // process each row ...
 
    }   /* End For */ 
 
    EXEC SQL CLOSE :ref_store;
    EXEC SQL FREE :ref_store;
  }  // opened stage_store ref cursor?
  else
  {
    printf("Failed to open stage_store ref_cursor: %d\n",
           sqlca.sqlcode);
  }

June's Tip of the Month

Calling the Parent Object's Version of an Overridden Method
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr).

Consider a child object type that overrides a method inherited from a parent object type. Sometimes, a method has to be overridden if only to add a few lines of code (at the beginning, at the end, or both).

In Java, the keyword "super" is used to invoke the corresponding method on the base class, in C++ the programmer can control which method is called by using the class name.

As of Oracle Server 9i Release 2 however, this feature is not available in PL/SQL. For example:

CREATE OR REPLACE TYPE parent_t AS OBJECT (
   NAME   VARCHAR2 (20),
   MEMBER PROCEDURE display
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY parent_t
IS
   MEMBER PROCEDURE display
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Parent procedure. Object name is: ' || NAME);
   END;
END;
/

CREATE OR REPLACE TYPE child_t
UNDER parent_t (
   OVERRIDING MEMBER PROCEDURE display
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY child_t
IS
   OVERRIDING MEMBER PROCEDURE display
   IS
   BEGIN
      super.display;               -- ERROR: SUPER is not available in PL/SQL
      DBMS_OUTPUT.put_line ('Child procedure. Object name is: ' || NAME);
   END;
END;
/

Warning: Type Body created with compilation errors.

SQL> show errors;
Errors for TYPE BODY CHILD_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/7      PLS-00201: identifier 'SUPER.DISPLAY' must be declared
4/7      PL/SQL: Statement ignored

1. add a new FINAL method (can't be changed in subtypes) in the parent object type, with code from the original parent's method
    
2. replace the code in the original parent's method with only one line - a call to the new FINAL method
    
3. call the new FINAL method from the child's method

CREATE OR REPLACE TYPE parent_t AS OBJECT (
   NAME   VARCHAR2 (20),
   MEMBER PROCEDURE display,
   FINAL MEMBER PROCEDURE display_parent
)
NOT FINAL
/

CREATE OR REPLACE TYPE BODY parent_t
IS
   MEMBER PROCEDURE display
   IS
   BEGIN
      display_parent;
   END;
   FINAL MEMBER PROCEDURE display_parent
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Parent procedure. Object name is: ' || NAME);
   END;
END;
/

CREATE OR REPLACE TYPE child_t
UNDER parent_t (
   OVERRIDING MEMBER PROCEDURE display
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY child_t
IS
   OVERRIDING MEMBER PROCEDURE display
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Child procedure - before parent''s procedure');
      SELF.display_parent;
      DBMS_OUTPUT.put_line ('Child procedure - after parent''s procedure');
   END;
END;
/

An anonymous block for testing:

DECLARE
   l_parent parent_t := parent_t ('Parent object');
   l_child  child_t  := child_t  ('Child object');
BEGIN
   l_parent.display;
   DBMS_OUTPUT.PUT_LINE ('*****'); 
   l_child.display;
END;
/
Parent procedure. Object name is: Parent object
*****
Child procedure - before parent's procedure
Parent procedure. Object name is: Child object
Child procedure - after parent's procedure

July's Tip of the Month

Dynamic Table in the Second Query with Oracle Reports
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr).

Sometimes we have a report with two queries in which each row returned in the first query defines (directly or indirectly) the table to be used in the FROM clause of the second query. How can we get the second query to use a different FROM clause based on which table is defined in the first query?

One simple solution is to use a dynamic ref cursor query:

1. Database tables:

   CREATE TABLE dept_all ( 
      deptno NUMBER (2), 
      dname  VARCHAR2 (20), 
      in_usa CHAR (1) DEFAULT 'Y') 
   / 
   INSERT INTO dept_all VALUES (10, 'DEPT 10', 'Y'); 
   INSERT INTO dept_all VALUES (20, 'DEPT 20', 'N'); 
   INSERT INTO dept_all VALUES (30, 'DEPT 30', 'Y'); 
   
   CREATE TABLE emp_usa ( 
      empno  NUMBER (4), 
      ename  VARCHAR2 (20), 
      deptno NUMBER (2)) 
   / 
   INSERT INTO emp_usa VALUES (1001, 'EMP 1001', 10); 
   INSERT INTO emp_usa VALUES (1002, 'EMP 1002', 10); 
   INSERT INTO emp_usa VALUES (3001, 'EMP 3001', 30); 
   INSERT INTO emp_usa VALUES (3002, 'EMP 3002', 30); 
   
   CREATE TABLE emp_non_usa ( 
      empno  NUMBER (4), 
      ename  VARCHAR2 (20), 
      deptno NUMBER (2)) 
   / 
   INSERT INTO emp_non_usa VALUES (2001, 'EMP 2001', 20); 
   INSERT INTO emp_non_usa VALUES (2002, 'EMP 2002', 20);

2. Database package:

   Note that Oracle Reports 3.0 / 6i needs 'static' ref cursor type for building the Report Layout. So, in the package specification we must have both ref cursor types - static for the Report Layout and dynamic for the ref cursor query.

   CREATE OR REPLACE PACKAGE example IS 
      TYPE dept_static_rc_t IS REF CURSOR RETURN dept_all%ROWTYPE;--for Rep Layout
      TYPE dept_rc_t IS REF CURSOR; 
      FUNCTION dept_query (p_where VARCHAR2) RETURN dept_rc_t; 
   
      TYPE emp_rec_t IS RECORD ( 
         empno emp_usa.empno%TYPE, 
         ename emp_usa.ename%TYPE); 
      TYPE emp_static_rc_t IS REF CURSOR RETURN emp_rec_t;--for Rep Layout
      TYPE emp_rc_t IS REF CURSOR; 
      FUNCTION emp_query ( 
         p_in_usa dept_all.in_usa%TYPE, 
         p_deptno dept_all.deptno%TYPE) 
         RETURN emp_rc_t; 
   END;
   / 
   CREATE OR REPLACE PACKAGE BODY example IS 
      FUNCTION dept_query (p_where VARCHAR2) RETURN dept_rc_t IS 
         l_dept_rc dept_rc_t; 
      BEGIN 
         OPEN l_dept_rc FOR 
            'SELECT *' ||
            '  FROM dept_all' ||
            ' WHERE ' || NVL (p_where, '1 = 1') ||
            ' ORDER BY deptno'; 
   
         RETURN l_dept_rc; 
      END; 
   
      FUNCTION emp_query ( 
         p_in_usa dept_all.in_usa%TYPE, 
         p_deptno dept_all.deptno%TYPE) 
      RETURN emp_rc_t
      IS 
         l_emp_rc emp_rc_t; 
         l_table  VARCHAR2 (30); 
      BEGIN 
         IF p_in_usa = 'Y' THEN 
            l_table := 'emp_usa'; 
         ELSE 
            l_table := 'emp_non_usa'; 
         END IF; 
   
         OPEN l_emp_rc FOR 
            'SELECT empno, ename ' ||
            '  FROM ' || l_table ||
            ' WHERE deptno = :p_deptno' ||
            ' ORDER BY empno'
            USING p_deptno; 
   
         RETURN l_emp_rc; 
      END; 
   END; 
   /

3. Report - query functions and data link:

   FUNCTION q_dept RETURN example.dept_static_rc_t IS 
   BEGIN 
      -- "p_where" is a User Parameter 
      RETURN example.dept_query (:p_where); 
   END; 
   
   FUNCTION q_emp RETURN example.emp_static_rc_t IS 
   BEGIN 
      -- "in_usa" and "deptno" are columns from Parent Group (G_DEPT) 
      RETURN example.emp_query (:in_usa, :deptno); 
   END;

August's Tip of the Month

Using the LONGNAME function to Convert Java Long Names
Compliments of Steven Feuerstein, Pipeline SYSOP.

The Oracle built-in package DBMS_JAVA gives you access to and the ability to modify various characteristics of the Aurora Java Virtual Machine.

The DBMS_JAVA package contains a large number of programs, many of which are intended for Oracle internal use only. Nevertheless, we can take advantage of a number of very useful programs; most can also be called within SQL statements. One of these is the LONGNAME function.

Java class and method names can easily exceed the maximum SQLidentifier length of 30 characters. In such cases, Oracle creates a unique "short name" for the Java code element and uses that name for SQL- and PL/SQL-related access.

Use the following function to obtain the full (long) name for a given short name:

FUNCTION DBMS_JAVA.LONGNAME (shortname VARCHAR2) RETURN VARCHAR2

/* File on web: longname.sql */
SELECT object_name shortname,
DBMS_JAVA.LONGNAME (object_name) longname
  FROM USER_OBJECTS
  WHERE object_type = 'JAVA CLASS'
    AND object_name != DBMS_JAVA.LONGNAME (object_name);

public class DropAnyObjectIdentifiedByTypeAndName {

SQL> exec myJava.showlongnames
Short Name | Long Name
----------------------------------------------------
Short: /247421b0_DropAnyObjectIdentif
Long:  DropAnyObjectIdentifiedByTypeAndName

September's Tip of the Month

Counting Delimiters in a String without PL/SQL
Compliments of Kevin Meade and Steve Cosner, Pipeline SYSOP.

Most Oracle developers, when looking to count the number of delimiters in a text string (a space for example), use a PL/SQL function to do the counting. However, some of us as a general rule, prefer to use pure SQL as much as possible. Call it a throw back for the ancients if you like. PL/SQL is great stuff, but it is slower than a SQL statement in most cases. With this in mind, here is a simple way to count the number of delimiters in a string using only Oracle SQL functions:

select length('xyz pdq abc')-nvl(length(replace('xyz pdq abc',' ')),0) 

delimiter_count from dual;

DELIMITER_COUNT
---------------
2

1 row selected.

Recognizing that there are just as many reasons for using PL/SQL only solutions as there are for using SQL only solutions, we note that this is also a valid PL/SQL snippet, so the same code can be used in either environment.

SQL> set serveroutput on

SQL> begin
2 dbms_output.put_line(length('xyz pdq abc')-length(replace('xyz pdqabc',' ')));
3 end;
4 /

2

PL/SQL procedure successfully completed.

If translate(nvl(xstring,'A'),'A0123456789','A') is null then...

October's Tip of the Month

Trimming the Run Time for a Large Data Load
Compliments of Dan Clamage, Pipeline SYSOP, (DClamage@MSA.com).

Recently I was tasked with loading a large volume of data from several source tables. I know that indexes can really slow a large insert down. So before I issued an Insert-Select, I disabled all the primary key and unique key constraints on the table, and marked the secondary indexes unusable. I do it in this order because when I disable the constraints, the corresponding indexes are automatically dropped. The indexes that remain are secondary indexes (both unique and non-unique).

I developed some packaged routines to make the job easier.

-- private global user-defined types
  -- use with any index that returns the name of a schema-level object
  TYPE typ_obj_name IS TABLE OF user_objects.object_name%TYPE;
-- private global cursor declarations
  -- returns set of indexes for given table
  -- make sure table name is UPPER CASE
  CURSOR cur_index_name(p_table_name user_indexes.table_name%TYPE)
  IS
    SELECT index_name
    FROM user_indexes
    WHERE table_name = p_table_name;
  -- returns set of primary or unique key constraints for a given table
  -- make sure table name is UPPER CASE
  CURSOR cur_cons_name(p_table_name user_indexes.table_name%TYPE)
  IS
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = p_table_name
      AND constraint_type IN ('P','U');

  -- disables primary or unique key constraints, if any
  -- for the given table.
  -- NOTE such indexes are dropped
  PROCEDURE disable_index_constraints(
    p_calling_context IN VARCHAR2
   ,p_tablename IN user_indexes.table_name%TYPE)
  IS
    -- local constants
    -- this routine name
    c_proc_name CONSTANT VARCHAR2(30) := 'disable_index_constraints';
    -- composite variables
    arr_cons_name typ_obj_name := typ_obj_name();
    -- scalar variables
    v_tablename user_indexes.table_name%TYPE := UPPER(p_tablename);
    v_stmt VARCHAR2(500);
  BEGIN
    fm_log_lib.put(  -- start
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'START: p_tablename=[' || p_tablename || ']'
    );
 
    -- get list of all constraint indexes for the given table
    OPEN cur_cons_name(v_tablename);
    FETCH cur_cons_name BULK COLLECT INTO arr_cons_name;
    CLOSE cur_cons_name;
    IF (arr_cons_name.COUNT > 0) THEN
      -- table has primary/unique key constraints
      FOR i IN arr_cons_name.FIRST .. arr_cons_name.LAST LOOP
        v_stmt := 'ALTER TABLE ' || v_tablename ||
                  ' DISABLE CONSTRAINT ' || arr_cons_name(i);
        fm_log_lib.put(  -- log stmt before attempt to execute
          c_proc_name || '->' || p_calling_context
         ,fm_log_lib.c_info
         ,'v_stmt=[' || v_stmt || ']'
        );
        EXECUTE IMMEDIATE v_stmt;
      END LOOP;
    END IF;  -- table have primary/unique key constraints?
 
    fm_log_lib.put(  -- finish
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'STOP'
    );
  END disable_index_constraints;

  -- disables all indexes for given table, then
  -- alters session to skip unusable indexes
  -- this is needed before loading a large table
  -- don't forget to rebuild them when you're finished loading!
  PROCEDURE disable_indexes(
    p_calling_context IN VARCHAR2
   ,p_tablename IN user_indexes.table_name%TYPE)
  IS
    -- local constants
    -- this routine name
    c_proc_name CONSTANT VARCHAR2(30) := 'disable_indexes';
    -- composite variables
    arr_index_name typ_obj_name := typ_obj_name();
    -- scalar variables
    v_tablename user_indexes.table_name%TYPE := UPPER(p_tablename);
    v_stmt VARCHAR2(500);
  BEGIN
    fm_log_lib.put(  -- start
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'START: p_tablename=[' || p_tablename || ']'
    );
 
    -- get list of all indexes for the given table
    OPEN cur_index_name(v_tablename);
    FETCH cur_index_name BULK COLLECT INTO arr_index_name;
    CLOSE cur_index_name;
    IF (arr_index_name.COUNT > 0) THEN  -- table has indexes
      FOR i IN arr_index_name.FIRST .. arr_index_name.LAST LOOP
        v_stmt := 'ALTER INDEX ' || arr_index_name(i) || ' unusable';
        fm_log_lib.put(  -- log stmt before attempt to execute
          c_proc_name || '->' || p_calling_context
         ,fm_log_lib.c_info
         ,'v_stmt=[' || v_stmt || ']'
        );
        EXECUTE IMMEDIATE v_stmt;
      END LOOP;
      EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unusable_indexes=true';
    END IF;  -- table have indexes?
 
    fm_log_lib.put(  -- finish
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'STOP'
    );
  END disable_indexes;

Now I can go ahead and truncate the table. I do this in a packaged routine mostly so I can add logging messages for tracking purposes.

  -- truncates given table
  -- NOTE this is an unrecoverable operation!
  PROCEDURE trunc_table(
    p_calling_context IN VARCHAR2
   ,p_tablename IN user_indexes.table_name%TYPE)
  IS
    -- local constants
    -- this routine name
    c_proc_name CONSTANT VARCHAR2(30) := 'trunc_table';
  BEGIN
    fm_log_lib.put(  -- start
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'START: p_tablename=[' || p_tablename || ']'
    );
 
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_tablename;
 
    fm_log_lib.put(  -- finish
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'STOP'
    );
  END trunc_table;

Once my data is loaded, I need to enable the constraints I turned off, rebuild all my indexes, and analyze my table and indexes. I have routines for those as well.

  -- enables primary or unique key constraints, if any
  -- for the given table.
  -- NOTE recreates the indexes named after the constraint
  PROCEDURE enable_index_constraints(
    p_calling_context IN VARCHAR2
   ,p_tablename IN user_indexes.table_name%TYPE)
  IS
    -- local constants
    -- this routine name
    c_proc_name CONSTANT VARCHAR2(30) := 'enable_index_constraints';
    -- composite variables
    arr_cons_name typ_obj_name := typ_obj_name();
    -- scalar variables
    v_tablename user_indexes.table_name%TYPE := UPPER(p_tablename);
    v_stmt VARCHAR2(500);
  BEGIN
    fm_log_lib.put(  -- start
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'START: p_tablename=[' || p_tablename || ']'
    );
 
    -- get list of all constraint indexes for the given table
    OPEN cur_cons_name(v_tablename);
    FETCH cur_cons_name BULK COLLECT INTO arr_cons_name;
    CLOSE cur_cons_name;
    IF (arr_cons_name.COUNT > 0) THEN
      -- table has primary/unique key constraints
      FOR i IN arr_cons_name.FIRST .. arr_cons_name.LAST LOOP
        v_stmt := 'ALTER TABLE ' || v_tablename ||
                  ' ENABLE CONSTRAINT ' || arr_cons_name(i) ||
                  ' USING INDEX';
        fm_log_lib.put(  -- log stmt before attempt to execute
          c_proc_name || '->' || p_calling_context
         ,fm_log_lib.c_info
         ,'v_stmt=[' || v_stmt || ']'
        );
        EXECUTE IMMEDIATE v_stmt;
      END LOOP;
    END IF;  -- table have primary/unique key constraints?
    fm_log_lib.put(  -- finish
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'STOP'
    );
  END enable_index_constraints;

  -- rebuilds all indexes for given table, then
  -- resets "skip unusable" to false
  -- this is needed after loading a large table
  -- whose indexes were previously disabled
  PROCEDURE rebuild_indexes(
    p_calling_context IN VARCHAR2
   ,p_tablename IN user_indexes.table_name%TYPE)
  IS
    -- local constants
    -- this routine name
    c_proc_name CONSTANT VARCHAR2(30) := 'rebuild_indexes';
    -- composite variables
    arr_index_name typ_obj_name := typ_obj_name();
    -- scalar variables
    v_tablename user_indexes.table_name%TYPE := UPPER(p_tablename);
    v_stmt VARCHAR2(500);
  BEGIN
    fm_log_lib.put(  -- start
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'START: p_tablename=[' || p_tablename || ']'
    );
 
    -- get list of all indexes for the given table
    OPEN cur_index_name(v_tablename);
    FETCH cur_index_name BULK COLLECT INTO arr_index_name;
    CLOSE cur_index_name;
    IF (arr_index_name.COUNT > 0) THEN  -- table has indexes
      FOR i IN arr_index_name.FIRST .. arr_index_name.LAST LOOP
        v_stmt := 'ALTER INDEX ' || arr_index_name(i) ||
                  ' rebuild parallel nologging';
        fm_log_lib.put(  -- log stmt before attempt to execute
          c_proc_name || '->' || p_calling_context
         ,fm_log_lib.c_info
         ,'v_stmt=[' || v_stmt || ']'
        );
        EXECUTE IMMEDIATE v_stmt;
      END LOOP;
      EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unusable_indexes=false';
    END IF;  -- table have indexes?
 
    fm_log_lib.put(  -- finish
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'STOP'
    );
  END rebuild_indexes;

  -- analyzes table using as much parallelism as possible,
  -- controllable to some degree by c_degree
  -- also analyzes the table's indexes (but not in parallel)
  -- statistics are estimated at c_est_pct
  -- (package constants are only modifiable at compile time)
  PROCEDURE analyze_table(
    p_calling_context IN VARCHAR2
   ,p_tablename IN user_indexes.table_name%TYPE)
  IS
    -- local constants
    -- this routine name
    c_proc_name CONSTANT VARCHAR2(30) := 'analyze_table';
    -- scalar variables
    v_tablename user_indexes.table_name%TYPE := UPPER(p_tablename);
  BEGIN
    fm_log_lib.put(  -- start
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'START: p_tablename=[' || p_tablename || ']'
    );
 
    DBMS_STATS.GATHER_TABLE_STATS (
       ownname => v_username         -- schema owner
      ,tabname => v_tablename        -- table to analyze
      ,estimate_percent => c_est_pct -- sample size
      ,degree => c_degree            -- parallelism
      ,cascade => TRUE               -- analyzes indexes as well
    );
 
    fm_log_lib.put(  -- finish
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'STOP'
    );
  END analyze_table;

I analyze my tables and indexes using package dbms_stats because Oracle recommends it, and on SMP systems, allows the process to be parallelized (unfortunately for this particular project, the machine had but a single CPU).

These routines are intended to be used in a particular order, for example:

    -- local constants
    -- this routine name
    c_proc_name CONSTANT VARCHAR2(30) := 'populate_wips';
    -- table name
    c_table_name CONSTANT user_tables.table_name%TYPE := 'wips_baseline';
  BEGIN
    fm_utl_lib.disable_index_constraints(
      c_proc_name || '->' || p_calling_context
     ,c_table_name);  -- 1
    fm_utl_lib.disable_indexes(
      c_proc_name || '->' || p_calling_context
     ,c_table_name);  -- 2
 
    fm_utl_lib.trunc_table(
      c_proc_name || '->' || p_calling_context
     ,c_table_name);  -- wipe old data
 
    COMMIT;  -- start a new transaction
 
-- 3: insert goes here
    INSERT INTO WIPS_BASELINE
    (
      NO_PART_PREFIX,
      NO_PART_BASE,
      NO_PART_SUFFIX,
      PART_NUMBER,
      CD_NAT_CMPNY_CODE,
      CD_SUPPLR_SHIP_PT,
      CD_SUPPLR_MANUF,
      CD_PLANT_CODE,
      CS_PIECE_PRICE,
      CD_PIECE_CURR,
      DT_PRICE_EFF_DATE
    )
    SELECT
        T076.NO_PART_PREFIX,
        T076.NO_PART_BASE,
        T076.NO_PART_SUFFIX,
        T076.PART_NUMBER,
        T076.CD_NAT_CMPNY_CODE,
        T076.CD_SUPPLR_SHIP_PT,
        T076.CD_SUPPLR_MANUF,
        SUBSTR(T076.CD_PLANT_CODE, 3, 2) CD_PLANT_CODE,
        T076.CS_PIECE_PRICE,
        T076.CD_PIECE_CURR,
        T076.DT_PRICE_EFF_DATE
    FROM
          TAHP076_SOURCING_VISTEON_V T076,
          TAHP108_NDPCTOVR_VISTEON_V T108
    WHERE T076.CD_PIECE_CURR = T108.CD_SUPPLR_CURR(+)
    ;
 
    fm_log_lib.put(  -- log #rows generated
      c_proc_name || '->' || p_calling_context
     ,fm_log_lib.c_info
     ,'generated #rows: ' || TO_CHAR(SQL%ROWCOUNT));
 
    COMMIT;  -- save work
 
    fm_utl_lib.rebuild_indexes(
      c_proc_name || '->' || p_calling_context
     ,c_table_name);  -- 4
    fm_utl_lib.enable_index_constraints(
      c_proc_name || '->' || p_calling_context
     ,c_table_name);  -- 5
    fm_utl_lib.analyze_table(
      c_proc_name || '->' || p_calling_context
     ,c_table_name);  -- 6

November's Tip of the Month

Using a Collection Instead of a Temporary Table in Complex Reports
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr.

Sometimes there is a query in our reports that is so complex that we have to use a temporary table (in an 8i database the most effective type would be a GLOBAL TEMPORARY TABLE). Without such a table, we couldn't do what we intended to, or we could do it but the solution would be overly complicated to design and difficult to maintain. Unfortunately, loading the temporary table (and processing it) may be more time consuming.

Sometimes we can avoid the use of a temporary table by using a REF CURSOR, especially if we use a dynamic REF CURSOR. However, REF CURSORs can't always replace a temporary table. Wouldn't it be nice if we could use a collection instead of temporary tables in our reports?

The reports are not set up to work directly with collections, but we can use collections indirectly in two ways:

• Our report can be based on a VIEW that uses the collection.
• Our report can be based on a REF CURSOR that uses the collection.

The method consists of three steps:

1. Load a collection with data from one or more tables
    
2. Execute complex processing on the collection
    
3. Open a REF CURSOR variable which uses a collection as follows:

   OPEN ref_cursor_variable FOR
      SELECT ... FROM TABLE (CAST (collection_variable AS collection_type)) ...

   or even simpler (this works only with 9i, and not with 8i):

   OPEN ref_cursor_variable FOR
      SELECT ... FROM TABLE (collection_variable) ...

   and send the REF CURSOR to the Reports

CREATE OR REPLACE TYPE dept_obj_t AS OBJECT (
   deptno NUMBER (2),
   dname  VARCHAR2 (14)
)
/

CREATE OR REPLACE TYPE dept_tab_t AS TABLE OF dept_obj_t
/

CREATE OR REPLACE PACKAGE collection_reports_rc IS
   TYPE dept_rec_t IS RECORD (
      deptno dept.deptno%TYPE,
      dname  dept.dname%TYPE);
   TYPE dept_rc_t IS REF CURSOR RETURN dept_rec_t;
   FUNCTION dept_query RETURN dept_rc_t;
END;
/

CREATE OR REPLACE PACKAGE BODY collection_reports_rc IS
   FUNCTION dept_query RETURN dept_rc_t IS
      l_dept_tab dept_tab_t;
      l_dept_rc  dept_rc_t;
      l_counter  NUMBER := 1;
   BEGIN
      -- ***** step A *****

      -- 1.version: CAST (MULTISET ...
      SELECT CAST (MULTISET (SELECT deptno, dname
                               FROM dept)
                   AS dept_tab_t)
        INTO l_dept_tab
        FROM DUAL;

      -- 2.version: BULK COLLECT
      /*
      SELECT dept_obj_t (deptno, dname)
        BULK COLLECT INTO l_dept_tab
        FROM dept;
      */

      -- 3.version: FOR LOOP
      /*
      l_dept_tab := dept_tab_t ();
      FOR dept IN (
         SELECT deptno, dname
           FROM dept)
      LOOP
         l_dept_tab.EXTEND;
         l_dept_tab (l_counter) := dept_obj_t (dept.deptno, dept.dname);
         l_counter := l_counter + 1;
      END LOOP;
      */

      -- ***** step B (SOME PROCESSING) *****

      -- ***** step C *****

      OPEN l_dept_rc FOR
         SELECT deptno, dname
           FROM TABLE (CAST (l_dept_tab AS dept_tab_t)) -- in 9i works without CAST
          ORDER BY deptno;

      RETURN l_dept_rc;
   END;
END;
/

CREATE OR REPLACE PACKAGE collection_reports2_rc IS
   TYPE dept_rec_t IS RECORD (
      deptno dept.deptno%TYPE,
      dname  dept.dname%TYPE);
   TYPE dept_rc_t IS REF CURSOR RETURN dept_rec_t;
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_rc_t;
END;
/

CREATE OR REPLACE PACKAGE BODY collection_reports2_rc IS
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_rc_t IS
      l_dept_tab  dept_tab_t;
      l_dept_rc   dept_rc_t;
      l_statement VARCHAR2 (32000);
   BEGIN
      -- ***** step A *****

      -- 1.version: CAST (MULTISET ...
      l_statement := 
      ' SELECT CAST (MULTISET (SELECT deptno, dname
                                 FROM dept
                                WHERE ' || NVL (p_where, '1 = 1') || ')
                     AS dept_tab_t)
          FROM DUAL';

      EXECUTE IMMEDIATE l_statement INTO l_dept_tab;

      -- 2.version: BULK COLLECT (but this don't compile in Oracle 8i)
      /*
      l_statement := 
      ' SELECT dept_obj_t (deptno, dname)
          FROM dept
         WHERE ' || NVL (p_where, '1 = 1');

      EXECUTE IMMEDIATE l_statement BULK COLLECT INTO l_dept_tab;
      */

      -- ***** step B (SOME PROCESSING) *****

      -- ***** step C *****

      OPEN l_dept_rc FOR
         SELECT deptno, dname
           FROM TABLE (CAST (l_dept_tab AS dept_tab_t)) -- in 9i works without CAST
          ORDER BY deptno;

      RETURN l_dept_rc;
   END;
END;
/

CREATE OR REPLACE PACKAGE collection_reports_dyn_rc IS
   TYPE dept_rec_t IS RECORD (
      deptno dept.deptno%TYPE,
      dname  dept.dname%TYPE);
   TYPE dept_rc_t IS REF CURSOR RETURN dept_rec_t;
   TYPE dept_dyn_rc_t IS REF CURSOR;
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_dyn_rc_t;
END;
/

CREATE OR REPLACE PACKAGE BODY collection_reports_dyn_rc IS
   FUNCTION dept_query (p_where VARCHAR2) RETURN dept_dyn_rc_t IS
      l_dept_tab dept_tab_t;
      l_dept_rc  dept_dyn_rc_t;
   BEGIN
      -- ***** step A *****

      SELECT CAST (MULTISET (SELECT deptno, dname
                               FROM dept)
                   AS dept_tab_t)
        INTO l_dept_tab
        FROM DUAL;

      -- ***** step B (SOME PROCESSING) *****

      -- ***** step C *****

      OPEN l_dept_rc FOR
         'SELECT deptno, dname
            FROM TABLE (CAST (:l_dept_tab AS dept_tab_t)) -- in 9i works without CAST
           WHERE ' || NVL (p_where, '1 = 1') ||
         ' ORDER BY deptno'
         USING l_dept_tab;

      RETURN l_dept_rc;
   END;
END;
/

December's Tip of the Month

Creating Random Numbers in PL/SQL
Compliments of Jeff Hunter, ideveleopment.info.

The following tip presents two methods for creating random numbers using PL/SQL.

Method 1 is a PL/SQL implementation of the linear congruential method of generating random numbers. It is in the form of a PL/SQL package, so it should be easy add to existing applications.

Method 2 takes the seconds past midnight and converts it to a number between 0 to 32767. Although this method requires less coding, Method 1 has two advantages:

• Method 2 does not produce evenly distributed results. If you map 86400 possible numbers to 32768 possible final values, some values become more likely than others.
   
• Method 2 is not repeatable. However, given the same seed, Method 1 produces the same sequence of numbers.

Method 1 - Linear congruential random number generator

CREATE OR REPLACE PACKAGE random IS

  -- Returns random integer between [0, r-1]
  FUNCTION rndint(r IN NUMBER) RETURN NUMBER;

  -- Returns random real between [0, 1]
  FUNCTION rndflt RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY random IS

  m         CONSTANT NUMBER:=100000000;  /* initial conditions */
  m1        CONSTANT NUMBER:=10000;      /* (for best results) */
  b         CONSTANT NUMBER:=31415821;   /*      */
  a         NUMBER;                      /* seed */
  the_date  DATE;                        /*      */
  days      NUMBER;                      /* for generating initial seed */
  secs      NUMBER;                      /*      */

  -- ------------------------
  -- Private utility FUNCTION
  -- ------------------------
  FUNCTION mult(p IN NUMBER, q IN NUMBER) RETURN NUMBER IS
    p1     NUMBER; 
    p0     NUMBER; 
    q1     NUMBER; 
    q0     NUMBER; 
  BEGIN 
    p1:=TRUNC(p/m1); 
    p0:=MOD(p,m1); 
    q1:=TRUNC(q/m1); 
    q0:=MOD(q,m1); 
    RETURN(MOD((MOD(p0*q1+p1*q0,m1)*m1+p0*q0),m)); 
  END;

  -- ---------------------------------------
  -- Returns random integer between [0, r-1]
  -- ---------------------------------------
  FUNCTION rndint (r IN NUMBER) RETURN NUMBER IS 
  BEGIN 
    -- Generate a random NUMBER, and set it to be the new seed
    a:=MOD(mult(a,b)+1,m); 

    -- Convert it to integer between [0, r-1] and return it
    RETURN(TRUNC((TRUNC(a/m1)*r)/m1));
  END;
 
  -- ----------------------------------
  -- Returns random real between [0, 1]
  -- ----------------------------------
  FUNCTION rndflt RETURN NUMBER IS
    BEGIN
      -- Generate a random NUMBER, and set it to be the new seed
      a:=MOD(mult(a,b)+1,m);
      RETURN(a/m);
    END;

BEGIN
  -- Generate initial seed "a" based on system date
  the_date:=SYSDATE;
  days:=TO_NUMBER(TO_CHAR(the_date, 'J'));
  secs:=TO_NUMBER(TO_CHAR(the_date, 'SSSSS'));
  a:=days*24*3600+secs;
END;
/

SELECT
  TRUNC( 
    (TO_NUMBER(SUBSTR(TO_CHAR(TO_NUMBER(TO_CHAR
                 (SYSDATE,'sssss'))/86399),-7,7))
                  /10000000)*32767
  ) random 
FROM dual;

For questions or comments on this site: webmaster@quest-pipelines.com
All content Copyright © Quest Software, Inc. All rights reserved.