In most of the development houses , the database API ( aka package ) are developed by the database development team and then the API's signature handed over the application / web development team.
For the most part , it is easier to print the ref cursor contents ; but to get the column name and its datatypes , it is not that obvious in sqlplus ( Some of the tools has features ) . In Oracle 11g , we can use dbms_sql package to display the column type , size , precision and so on.
Let us see an example .
First , let us create a package and package body . This API will display 10 zipcodes for a given state code.
SQL> desc zips
Name Null? Type
----------------------------------------- -------- ----------------------------
ZIPCODE VARCHAR2(5)
PO_NAME VARCHAR2(255)
COUNTY VARCHAR2(255)
STATE VARCHAR2(2)
SQL> create or replace PACKAGE listzipcode_pkg
2 AS
3 PROCEDURE list_by_statecode_p(
4 statecode_p VARCHAR2 ,
5 zipcode_list_rc OUT sys_refcursor) ;
6 END listzipcode_pkg;
7 /
Package created.
SQL> CREATE OR REPLACE PACKAGE body listzipcode_pkg
2 AS
3 PROCEDURE list_by_statecode_p(
4 statecode_p VARCHAR2 ,
5 zipcode_list_rc OUT sys_refcursor)
6 AS
7 BEGIN
8 OPEN zipcode_list_rc FOR SELECT * FROM
9 (
10 SELECT
11 state ,
12 zipcode ,
13 to_number(zipcode) AS ZipCode_Number
14 FROM
15 zips
16 WHERE
17 STATE = statecode_p
18 ORDER BY
19 zipcode
20 )
21 WHERE rownum < 11 ;
22 END list_by_statecode_p;
23 END listzipcode_pkg;
24 /
Package body created.
We can use the ref cursor to display the contents as shown below.
SQL> variable x refcursor;
SQL> exec listzipcode_pkg.list_by_statecode_p( 'NJ' , :x) ;
PL/SQL procedure successfully completed.
SQL> print :x ;
ST ZIPCO ZIPCODE_NUMBER
-- ----- --------------
NJ 07001 7001
NJ 07002 7002
NJ 07003 7003
NJ 07004 7004
NJ 07005 7005
NJ 07006 7006
NJ 07007 7007
NJ 07008 7008
NJ 07009 7009
NJ 07010 7010
10 rows selected.
To describe ( aka to list the column names , data types ) , let us create an anonymous PL/SQL block .
Here we go.
SQL> SET serveroutput ON
SQL> DECLARE
2 l_rcursor sys_refcursor;
3 l_statecode VARCHAR2(2);
4 l_col_type VARCHAR2(100) ;
5 l_cursor_name VARCHAR2(100) ;
6 l_colCnt NUMBER;
7 l_descTbl dbms_sql.desc_tab;
8 BEGIN
9 l_statecode := 'NJ';
10 listzipcode_pkg.list_by_statecode_p( statecode_p => l_statecode ,
11 zipcode_list_rc => l_rcursor) ;
12 l_cursor_name := 'list_by_statecode_p';
13 dbms_sql.describe_columns ( c => dbms_sql.to_cursor_number(l_rcursor) ,
14 col_cnt => l_colCnt, desc_t => l_descTbl );
15 dbms_output.put_line( RPAD( 'Name' , 25 , ' ' ) || 'Type' );
16 dbms_output.put_line( RPAD( '----' , 25 , ' ' ) || '----' );
17 FOR i IN 1 .. l_colCnt
18 LOOP
19 IF l_descTbl(i).col_type = 2 THEN
20 l_col_type := 'NUMBER';
21 ELSIF l_descTbl(i).col_type = 1 THEN
22 l_col_type := 'VARCHAR2';
23 ELSE
24 l_col_type := l_descTbl(i).col_type ;
25 END IF;
26 dbms_output.put_line( RPAD( l_descTbl(i).col_name, 25 , ' ' ) || l_col_type
27 );
28 END LOOP;
29 END ;
30
31 /
Name Type
---- ----
STATE VARCHAR2
ZIPCODE VARCHAR2
ZIPCODE_NUMBER NUMBER
PL/SQL procedure successfully completed.
In the PL/SQL block , we use dbms_sql to get the metadata contents of the ref cursor . The above shows only the column name , column's datatype . We can extend the above block to show the maximum length , precision , scale and so forth.
Another thing to note is that the return value of "col_type" is an integer . You need to look it up against the associated name . This information can be found in ALL_SOURCE view.
SQL> select text from all_source
2 where owner = 'SYS' and name = 'DBMS_TYPES' and type = 'PACKAGE';
TEXT
--------------------------------------------------------------------------------
PACKAGE dbms_types AS
TYPECODE_DATE PLS_INTEGER := 12;
TYPECODE_NUMBER PLS_INTEGER := 2;
TYPECODE_RAW PLS_INTEGER := 95;
TYPECODE_CHAR PLS_INTEGER := 96;
TYPECODE_VARCHAR2 PLS_INTEGER := 9;
TYPECODE_VARCHAR PLS_INTEGER := 1;
TYPECODE_MLSLABEL PLS_INTEGER := 105;
TYPECODE_BLOB PLS_INTEGER := 113;
TYPECODE_BFILE PLS_INTEGER := 114;
TYPECODE_CLOB PLS_INTEGER := 112;
TYPECODE_CFILE PLS_INTEGER := 115;
TYPECODE_TIMESTAMP PLS_INTEGER := 187;
TYPECODE_TIMESTAMP_TZ PLS_INTEGER := 188;
TYPECODE_TIMESTAMP_LTZ PLS_INTEGER := 232;
TYPECODE_INTERVAL_YM PLS_INTEGER := 189;
TYPECODE_INTERVAL_DS PLS_INTEGER := 190;
TYPECODE_REF PLS_INTEGER := 110;
TYPECODE_OBJECT PLS_INTEGER := 108;
TYPECODE_VARRAY PLS_INTEGER := 247; /* COLLECTION TYPE */
TYPECODE_TABLE PLS_INTEGER := 248; /* COLLECTION TYPE */
TYPECODE_NAMEDCOLLECTION PLS_INTEGER := 122;
TYPECODE_OPAQUE PLS_INTEGER := 58; /* OPAQUE TYPE */
/* NOTE: These typecodes are for use in AnyData api only and are short forms
for the corresponding char typecodes with a charset form of SQLCS_NCHAR.
*/
TYPECODE_NCHAR PLS_INTEGER := 286;
TYPECODE_NVARCHAR2 PLS_INTEGER := 287;
TYPECODE_NCLOB PLS_INTEGER := 288;
/* Typecodes for Binary Float, Binary Double and Urowid. */
TYPECODE_BFLOAT PLS_INTEGER := 100;
TYPECODE_BDOUBLE PLS_INTEGER := 101;
TYPECODE_UROWID PLS_INTEGER := 104;
SUCCESS PLS_INTEGER := 0;
NO_DATA PLS_INTEGER := 100;
/* Exceptions */
invalid_parameters EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_parameters, -22369);
incorrect_usage EXCEPTION;
PRAGMA EXCEPTION_INIT(incorrect_usage, -22370);
type_mismatch EXCEPTION;
PRAGMA EXCEPTION_INIT(type_mismatch, -22626);
END dbms_types;
51 rows selected.
Happy describing .
Comments Welcome !
