/* Formatted by PL/Formatter v2.0.8.0 on 1999/12/17 15:26  (03:26 PM) */
PROCEDURE punloadrecord (
   ppowner IN VARCHAR2,
   pptablename IN VARCHAR2,
   ppwhereclause IN VARCHAR2)
IS
   /****************************************************************************
   Author:  Sean Cassidy (scassidy@ozemail.com.au)   December 1999
   Purpose: Unload a record to be a SQL INSERT Statement.
            Primarily for one record but will handle if many.

   Parameters
   Name             Details
   ---------------- ------------------------------------------------------------
   ppOwner          The Owner of the table.
   ppTableName      The Name of the Table from which the record is to be
                    Unloaded from
   ppWhereClause    A CLAUSE to identify the record. May select multiple records

   NB: This Procedure is about as robust as those little sachets
       of International Roast Coffee that you get in cheap hotel rooms
       It will fall over for any number of reasons and does not handle LONG
   ****************************************************************************/
   CURSOR ctablecolumns (cpowner VARCHAR2, cptablename VARCHAR2)
   IS
      SELECT atc.column_name, atc.data_type
        FROM all_tab_columns atc, all_tables ats
       WHERE
                 --== Join to All_Tables so we can't get views (Inserts would fail) ==--

                 atc.owner = UPPER (cpowner)
             AND atc.owner = ats.owner
             AND atc.table_name = ats.table_name
             AND ats.table_name = UPPER (cptablename)
             AND atc.data_type IN ('DATE', 'NUMBER', 'VARCHAR2');
   rtablecolumns ctablecolumns%ROWTYPE;
   --
   TYPE ttctable IS TABLE OF ctablecolumns%ROWTYPE
      INDEX BY BINARY_INTEGER;
   TYPE tcursortable IS TABLE OF INTEGER
      INDEX BY BINARY_INTEGER;
   TYPE tvaltable IS TABLE OF VARCHAR2(2000)
      INDEX BY BINARY_INTEGER;
   lvcursortable tcursortable;
   lvtctable ttctable;
   lvvaluetable tvaltable;
   lvselectioncursor INTEGER;
   lvselectstatement VARCHAR2(2000);
   lvcolumnlist VARCHAR2(2000);
   i BINARY_INTEGER := 0;
   lvnumcolumns NUMBER := 0;
   lvcurrentrow ROWID;
   lvignore NUMBER;
   lvcurrentvalue VARCHAR2(2000);
BEGIN   -- pUnloadRecord;
   --== Fetch our columns into a pl/sql table ==--
   FOR rtablecolumns IN ctablecolumns (ppowner, pptablename)
   LOOP
      i := i + 1;
      lvtctable (i) := rtablecolumns;
   END LOOP;
   lvnumcolumns := i;
   <<columnlist_loop>>
   FOR i IN 1 .. lvnumcolumns
   LOOP
      -- Add each column name to a list
      IF lvcolumnlist IS NULL
      THEN
         lvcolumnlist := lvtctable (i).column_name;
      ELSE
         lvcolumnlist := lvcolumnlist || ','|| lvtctable (i).column_name;
      END IF;   -- ColumnList is NULL
      IF lvtctable (i).data_type = 'DATE'
      THEN
         --== All the quotes are so our strings have enough in the end ==--
         lvselectstatement :=
            'SELECT ''to_date(''''''||to_char('||
            lvtctable (i).column_name ||
            ', ''yyyymmddhh24miss'')|| 
                           '''''', ''''yyyymmddhh24miss'''')'' FROM '||
            pptablename ||
            ' WHERE rowid = :x';
      ELSIF lvtctable (i).data_type = 'NUMBER'
      THEN
         lvselectstatement :=
            'SELECT to_char('||
            lvtctable (i).column_name ||
            ')'||
            ' FROM '||
            pptablename ||
            ' WHERE rowid = :x';
      ELSIF lvtctable (i).data_type = 'VARCHAR2'
      THEN
         lvselectstatement :=
            'SELECT ''''''''||'||
            lvtctable (i).column_name ||
            '||'''''''' FROM '||
            pptablename ||
            ' WHERE rowid = :x';
      END IF;   -- Data type
      lvcursortable (i) := DBMS_SQL.open_cursor;
      DBMS_SQL.parse (lvcursortable (i), lvselectstatement, DBMS_SQL.native);
   END LOOP column_list_loop;
   --===--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--
   --== Because DBMS_SQL requires us to call define_column for each   ==--
   --== column, we cannot have dynamic number of columns fetched      ==--
   --== Instead, we need to fetch each column seperately... Bugger!   ==--
   --===--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--
   lvselectstatement :=
      'SELECT rowid'|| ' FROM '|| pptablename || ' WHERE '|| ppwhereclause;
   lvselectioncursor := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (lvselectioncursor, lvselectstatement, DBMS_SQL.native);
   DBMS_SQL.define_column_rowid (lvselectioncursor, 1, lvcurrentrow);
   lvignore := DBMS_SQL.execute (lvselectioncursor);
   <<fetch_rows_loop>>
   LOOP
      IF DBMS_SQL.fetch_rows (lvselectioncursor) > 0
      THEN
         DBMS_SQL.column_value (lvselectioncursor, 1, lvcurrentrow);
         <<column_loop>>
         FOR i IN 1 .. lvnumcolumns
         LOOP
            DBMS_SQL.define_column (lvcursortable (i), 1, lvcurrentvalue, 2000);
            DBMS_SQL.bind_variable (lvcursortable (i), 'x', lvcurrentrow);
            lvignore := DBMS_SQL.execute (lvcursortable (i));
            IF DBMS_SQL.fetch_rows (lvcursortable (i)) > 0
            THEN
               DBMS_SQL.column_value (lvcursortable (i), 1, lvcurrentvalue);
            ELSE
               lvcurrentvalue := NULL;
            END IF;
            IF lvcurrentvalue IS NULL
            THEN
               lvcurrentvalue := 'NULL';
            END IF;
            lvvaluetable (i) := lvcurrentvalue;
         END LOOP column_loop;
         --== Output this row ==--
         DBMS_OUTPUT.put_line (
            'INSERT INTO '|| ppowner || '.'|| pptablename || '('
         );
         pprintcolumnlist (lvcolumnlist);
         DBMS_OUTPUT.put_line (') VALUES (');
         <<output_values>>
         FOR i IN 1 .. lvnumcolumns
         LOOP
            IF i > 1
            THEN
               DBMS_OUTPUT.put (',');
            END IF;
            pprintstring (lvvaluetable (i));
         END LOOP output_values;
         DBMS_OUTPUT.put_line (')');
         DBMS_OUTPUT.put_line ('/');
      ELSE
         EXIT fetch_rows_loop;
      END IF;   -- fetch > 0
   END LOOP fetch_rows_loop;
   --== Close all the cursors ==--
   <<column_cursor_loop>>
   FOR i IN 1 .. lvnumcolumns
   LOOP
      DBMS_SQL.close_cursor (lvcursortable (i));
   END LOOP column_cursor_loop;
   DBMS_SQL.close_cursor (lvselectioncursor);
END punloadrecord;

/