desccols.pkg

OReilly Oracle PL SQL Programming第4版源代码

CREATE OR REPLACE PACKAGE desccols
IS
   varchar2_type CONSTANT PLS_INTEGER := 1;
   number_type CONSTANT PLS_INTEGER := 2;
   date_type CONSTANT PLS_INTEGER := 12;
   char_type CONSTANT PLS_INTEGER := 96;
   long_type CONSTANT PLS_INTEGER := 8;
   rowid_type CONSTANT PLS_INTEGER := 11;
   raw_type CONSTANT PLS_INTEGER := 23;
   mlslabel_type CONSTANT PLS_INTEGER := 106;
   clob_type CONSTANT PLS_INTEGER := 112;
   blob_type CONSTANT PLS_INTEGER := 113;
   bfile_type CONSTANT PLS_INTEGER := 114;
   
   PROCEDURE forcur (cur IN INTEGER);

   PROCEDURE show (fst IN INTEGER := 1, lst IN INTEGER := NULL);
   
   FUNCTION numcols RETURN INTEGER;

   FUNCTION nthcol (num IN INTEGER) RETURN DBMS_SQL.DESC_REC;
END desccols;
/
CREATE OR REPLACE PACKAGE BODY desccols
IS
   /* Here is the PL/SQL table holding the column information. */
   desctab DBMS_SQL.DESC_TAB;
   desccnt PLS_INTEGER;
   firstrow PLS_INTEGER;
   lastrow PLS_INTEGER;

   PROCEDURE forcur (cur IN INTEGER)
   IS
   BEGIN
      /* Clear out the PL/SQL table */
      desctab.DELETE;
       
      /* Fill up the PL/SQL table */
      DBMS_SQL.DESCRIBE_COLUMNS (cur, desccnt, desctab);

      /* Get the first and last row numbers to avoid future lookups */
      firstrow := desctab.FIRST;
      lastrow := desctab.LAST;
   END;

   PROCEDURE show (fst IN INTEGER := 1, lst IN INTEGER := NULL)
   IS
   BEGIN
      IF desccnt > 0
      THEN
         /* Show the specified rows. */
         FOR colind IN 
            GREATEST (fst, firstrow) .. 
            LEAST (NVL (lst, lastrow), lastrow)
         LOOP
            /* Add additional lines of output as you desire */
            DBMS_OUTPUT.PUT_LINE ('Column ' || TO_CHAR (colind));
            DBMS_OUTPUT.PUT_LINE (desctab(colind).col_name);
            DBMS_OUTPUT.PUT_LINE (desctab(colind).col_type);
         END LOOP;
      END IF;
   END;
   
   FUNCTION numcols RETURN INTEGER
   IS
   BEGIN
      RETURN desccnt;
   END;

   FUNCTION nthcol (num IN INTEGER) RETURN DBMS_SQL.DESC_REC
   IS
      retval DBMS_SQL.DESC_REC;
   BEGIN
      /* If a valid row number, retrieve that entire record. */
      IF num BETWEEN firstrow AND lastrow
      THEN
         retval := desctab(num);
      END IF;
      RETURN retval;
   END;
END;
/


/*======================================================================
| Supplement to the third edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2002 O'Reilly &
| Associates, Inc. To submit corrections or find more code samples visit
| http://www.oreilly.com/catalog/oraclep3/
*/