OraFAQ Forum: SQL & PL/SQL » Displaying DESC_TAB parms

Home » SQL & PL/SQL » SQL & PL/SQL » Displaying DESC_TAB parms (Oralce 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Displaying DESC_TAB parms [message #656351]

Mon, 03 October 2016 14:40

Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas

Member

Testing some dynamic SQL code.
I have this line in a procedure:
DBMS_SQL.DESCRIBE_COLUMNS(C_, COL_COUNT_, DESC_TAB_);

I have a 2nd display line where I want to simply see the value of "C_", "COL_COUNT_" & "DESC_TAB":
(Line#2) DBMS_OUTPUT.PUT_LINE('C_ : '||TO_CHAR(C_)||
' COL_COUNT: '||TO_CHAR(COL_COUNT_)||
' DESC_TAB_ : '||TO_CHAR(DESC_TAB_));

This "Line#2" throws an ORA-06550 error along with a "PLS-00306" error.
Is there a way to display the 3 parms from the DBMS_SQL.DESCRIBE_COLUMNS statement?

Here is the complete script:

DECLARE
TYPE REF_CURSOR IS REF CURSOR;
RC_ REF_CURSOR;
C_ NUMBER;
I_ NUMBER;
COL_COUNT_ NUMBER;
DESC_TAB_ DBMS_SQL.DESC_TAB;
BEGIN
OPEN RC_ FOR 'SELECT * FROM dba_tables';
C_ := DBMS_SQL.to_cursor_number(RC_);
DBMS_SQL.DESCRIBE_COLUMNS(C_, COL_COUNT_, DESC_TAB_);
DBMS_OUTPUT.PUT_LINE('C_ : '||TO_CHAR(C_)||
' COL_COUNT: '||TO_CHAR(COL_COUNT_)||
' DESC_TAB_ : '||TO_CHAR(DESC_TAB_));

DBMS_OUTPUT.PUT_LINE(' ~BEGIN LOOP~');
FOR I_ IN 1..COL_COUNT_ LOOP
DBMS_OUTPUT.PUT_LINE('Col-Counter: '||I_||' Column-Name: ~' ||DESC_TAB_(I_).COL_NAME||' ~ ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ~END LOOP~');
DBMS_SQL.CLOSE_CURSOR(C_);
END;

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656353 is a reply to message #656351]

Mon, 03 October 2016 14:47

Bill B
Messages: 1971
Registered: December 2004

Senior Member

the desc_tab is a collection, you have to do the following (example code from the net)

Code: [Select all] [Show/ hide]

DECLARE
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   cols DBMS_SQL.DESC_T;
   ncols PLS_INTEGER;
BEGIN
   DBMS_SQL.PARSE
      (cur, 'SELECT hiredate, sal FROM emp', DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN (cur, 1, SYSDATE);
   DBMS_SQL.DEFINE_COLUMN (cur, 2, 1);
   DBMS_SQL.DESCRIBE_COLUMNS (cur, ncols, cols);
   FOR colind IN 1 .. ncols
   LOOP
      DBMS_OUTPUT.PUT_LINE (cols.col_name);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656354 is a reply to message #656351]

Mon, 03 October 2016 15:12

Bill B
Messages: 1971
Registered: December 2004

Senior Member

YOUR VARIABLE DESC_TAB_ is a collection and needs to be queried by its position in the collection.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656361 is a reply to message #656351]

Mon, 03 October 2016 23:33

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Code: [Select all] [Show/ hide]

SCOTT@orcl_12.1.0.2.0> DECLARE
  2    TYPE REF_CURSOR IS REF CURSOR;
  3    RC_ REF_CURSOR;
  4    C_ NUMBER;
  5    I_ NUMBER;
  6    COL_COUNT_ NUMBER;
  7    DESC_TAB_ DBMS_SQL.DESC_TAB;
  8  BEGIN
  9    OPEN RC_ FOR 'SELECT * FROM dba_tables';
 10    C_ := DBMS_SQL.to_cursor_number(RC_);
 11    DBMS_SQL.DESCRIBE_COLUMNS(C_, COL_COUNT_, DESC_TAB_);
 12  
 13    DBMS_OUTPUT.PUT_LINE('C_ : '||TO_CHAR(C_)||' COL_COUNT: '||TO_CHAR(COL_COUNT_));
 14  
 15    DBMS_OUTPUT.PUT_LINE(' ~BEGIN LOOP~');
 16    FOR I_ IN 1..COL_COUNT_ LOOP
 17  	 DBMS_OUTPUT.PUT_LINE('Col-Counter: '||I_||' Column-Name: ~' ||DESC_TAB_(I_).COL_NAME||' ~ ');
 18    END LOOP;
 19    DBMS_OUTPUT.PUT_LINE(' ~END LOOP~');
 20    DBMS_SQL.CLOSE_CURSOR(C_);
 21  END;
 22  /
C_ : 1899196745 COL_COUNT: 65
~BEGIN LOOP~
Col-Counter: 1 Column-Name: ~OWNER ~
Col-Counter: 2 Column-Name: ~TABLE_NAME ~
Col-Counter: 3 Column-Name: ~TABLESPACE_NAME ~
Col-Counter: 4 Column-Name: ~CLUSTER_NAME ~
Col-Counter: 5 Column-Name: ~IOT_NAME ~
Col-Counter: 6 Column-Name: ~STATUS ~
Col-Counter: 7 Column-Name: ~PCT_FREE ~
Col-Counter: 8 Column-Name: ~PCT_USED ~
Col-Counter: 9 Column-Name: ~INI_TRANS ~
Col-Counter: 10 Column-Name: ~MAX_TRANS ~
Col-Counter: 11 Column-Name: ~INITIAL_EXTENT ~
Col-Counter: 12 Column-Name: ~NEXT_EXTENT ~
Col-Counter: 13 Column-Name: ~MIN_EXTENTS ~
Col-Counter: 14 Column-Name: ~MAX_EXTENTS ~
Col-Counter: 15 Column-Name: ~PCT_INCREASE ~
Col-Counter: 16 Column-Name: ~FREELISTS ~
Col-Counter: 17 Column-Name: ~FREELIST_GROUPS ~
Col-Counter: 18 Column-Name: ~LOGGING ~
Col-Counter: 19 Column-Name: ~BACKED_UP ~
Col-Counter: 20 Column-Name: ~NUM_ROWS ~
Col-Counter: 21 Column-Name: ~BLOCKS ~
Col-Counter: 22 Column-Name: ~EMPTY_BLOCKS ~
Col-Counter: 23 Column-Name: ~AVG_SPACE ~
Col-Counter: 24 Column-Name: ~CHAIN_CNT ~
Col-Counter: 25 Column-Name: ~AVG_ROW_LEN ~
Col-Counter: 26 Column-Name: ~AVG_SPACE_FREELIST_BLOCKS ~
Col-Counter: 27 Column-Name: ~NUM_FREELIST_BLOCKS ~
Col-Counter: 28 Column-Name: ~DEGREE ~
Col-Counter: 29 Column-Name: ~INSTANCES ~
Col-Counter: 30 Column-Name: ~CACHE ~
Col-Counter: 31 Column-Name: ~TABLE_LOCK ~
Col-Counter: 32 Column-Name: ~SAMPLE_SIZE ~
Col-Counter: 33 Column-Name: ~LAST_ANALYZED ~
Col-Counter: 34 Column-Name: ~PARTITIONED ~
Col-Counter: 35 Column-Name: ~IOT_TYPE ~
Col-Counter: 36 Column-Name: ~TEMPORARY ~
Col-Counter: 37 Column-Name: ~SECONDARY ~
Col-Counter: 38 Column-Name: ~NESTED ~
Col-Counter: 39 Column-Name: ~BUFFER_POOL ~
Col-Counter: 40 Column-Name: ~FLASH_CACHE ~
Col-Counter: 41 Column-Name: ~CELL_FLASH_CACHE ~
Col-Counter: 42 Column-Name: ~ROW_MOVEMENT ~
Col-Counter: 43 Column-Name: ~GLOBAL_STATS ~
Col-Counter: 44 Column-Name: ~USER_STATS ~
Col-Counter: 45 Column-Name: ~DURATION ~
Col-Counter: 46 Column-Name: ~SKIP_CORRUPT ~
Col-Counter: 47 Column-Name: ~MONITORING ~
Col-Counter: 48 Column-Name: ~CLUSTER_OWNER ~
Col-Counter: 49 Column-Name: ~DEPENDENCIES ~
Col-Counter: 50 Column-Name: ~COMPRESSION ~
Col-Counter: 51 Column-Name: ~COMPRESS_FOR ~
Col-Counter: 52 Column-Name: ~DROPPED ~
Col-Counter: 53 Column-Name: ~READ_ONLY ~
Col-Counter: 54 Column-Name: ~SEGMENT_CREATED ~
Col-Counter: 55 Column-Name: ~RESULT_CACHE ~
Col-Counter: 56 Column-Name: ~CLUSTERING ~
Col-Counter: 57 Column-Name: ~ACTIVITY_TRACKING ~
Col-Counter: 58 Column-Name: ~DML_TIMESTAMP ~
Col-Counter: 59 Column-Name: ~HAS_IDENTITY ~
Col-Counter: 60 Column-Name: ~CONTAINER_DATA ~
Col-Counter: 61 Column-Name: ~INMEMORY ~
Col-Counter: 62 Column-Name: ~INMEMORY_PRIORITY ~
Col-Counter: 63 Column-Name: ~INMEMORY_DISTRIBUTE ~
Col-Counter: 64 Column-Name: ~INMEMORY_COMPRESSION ~
Col-Counter: 65 Column-Name: ~INMEMORY_DUPLICATE ~
~END LOOP~

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656362 is a reply to message #656351]

Tue, 04 October 2016 00:12

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

@Irish88,

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656373 is a reply to message #656362]

Tue, 04 October 2016 08:58

Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas

Member

@Michel, Thank you for the Code tag link. I will use that going forward. Smile

Smile

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656376 is a reply to message #656353]

Tue, 04 October 2016 11:17

Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas

Member

BillB:

In your example, I have created a sample employee table just like what you have.
My objective is to run your code with the DBMS_OUTPUT so I can understand it better.
While I am familiar with some aspects of PL/SQL, I apologize in advance for my elementary questions as other facets of PL/SQL are very new to me; DBMS_SQL being one of them. (...coming from 15 years of VBA development...fwiw)

Anyway, this third line errors out because DESC_T is not declared:
>> cols DBMS_SQL.DESC_T;

I thought "DESC_T" was referencing DBMS_SQL.DESC_TAB somehow, but it doesn't appear like it does.
(Getting a little confused on the declarations)

Suggestions?
Thanks.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656377 is a reply to message #656376]

Tue, 04 October 2016 11:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

The statement in the declare has to define the collection. You would have

cols DBMS_SQL.DESC_TAB;

That being said, Barbara Boehmer did a much nicer write up on the topic and you might want to look at it. See her comment from Tue, 04 October 2016 00:33.

[Updated on: Tue, 04 October 2016 11:31]

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656378 is a reply to message #656377]

Tue, 04 October 2016 13:18

Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas

Member

Code: [Select all] [Show/ hide]

DECLARE
   CUR PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   COLS DBMS_SQL.DESC_TAB;
   NCOLS PLS_INTEGER;
BEGIN
   DBMS_SQL.PARSE
      (CUR, 'SELECT hiredate, salary FROM RLNEMPLOYEES', DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN (CUR, 1, SYSDATE);
   DBMS_SQL.DEFINE_COLUMN (CUR, 2, 1);
   DBMS_SQL.DESCRIBE_COLUMNS (CUR, NCOLS, COLS);
   FOR COLIND IN 1 .. NCOLS
   LOOP
      DBMS_OUTPUT.PUT_LINE (COLS.COL_NAME);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR (CUR);
END;
/

Per this line that errors out:
DBMS_OUTPUT.PUT_LINE (COLS.COL_NAME);
COL_NAME is not declared.
What am I missing here? I thought "COL_NAME" is contained within the 3rd line: "COLS DBMS_SQL.DESC_TAB;"

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656379 is a reply to message #656378]

Tue, 04 October 2016 13:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1027592

What datatype does DBMS_OUTPUT.PUT_LINE require/accept?
What datatype is COLS?

why are you messing with DBMS_SQL, when nothing is dynamic?

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656380 is a reply to message #656378]

Tue, 04 October 2016 13:32

Bill B
Messages: 1971
Registered: December 2004

Senior Member

This is the layout of the DESC_TAB. aLSO CHANGE IT TO

DBMS_OUTPUT.PUT_LINE (COLS(COLIND).COL_NAME);

type desc_rec is record (
col_type binary_integer := 0,
col_max_len binary_integer := 0,
col_name varchar2(32) := '',
col_name_len binary_integer := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len binary_integer := 0,
col_precision binary_integer := 0,
col_scale binary_integer := 0,
col_charsetid binary_integer := 0,
col_charsetform binary_integer := 0,
col_null_ok boolean := TRUE);

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656381 is a reply to message #656380]

Tue, 04 October 2016 16:00

Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas

Member

I was able to see this value in the output:
DBMS_OUTPUT.PUT_LINE('col_max_len: ~'||DESC_TAB_(I_).COL_MAX_LEN);
Worked fine.

I was trying to display the value of 'col_null_ok' but keep getting an ORA-06550 error:

DBMS_OUTPUT.PUT_LINE('col_null_ok: ~'||DESC_TAB_(I_).CAST(COL_NULL_OK AS VARCHAR2(5));

I set the Varchar2 to 5 positions for 'false'.
All of the examples that I saw online demoed the CAST in a SELECT statement only; I'm wanting to use it the DBMS_OUTPUT.PUT_LINE to simply display the output.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656382 is a reply to message #656381]

Tue, 04 October 2016 17:20

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Code: [Select all] [Show/ hide]

SCOTT@orcl_12.1.0.2.0> DECLARE
  2    TYPE REF_CURSOR IS REF CURSOR;
  3    RC_		  REF_CURSOR;
  4    C_		  NUMBER;
  5    I_		  NUMBER;
  6    COL_COUNT_	  NUMBER;
  7    DESC_TAB_ DBMS_SQL.DESC_TAB;
  8  BEGIN
  9    OPEN RC_ FOR 'SELECT * FROM dba_tables';
 10    C_ := DBMS_SQL.to_cursor_number(RC_);
 11    DBMS_SQL.DESCRIBE_COLUMNS(C_, COL_COUNT_, DESC_TAB_);
 12    --
 13    FOR I_ IN 1..COL_COUNT_ LOOP
 14  	 DBMS_OUTPUT.PUT_LINE('col_null_ok: ~' ||
 15  	   CASE DESC_TAB_(I_).COL_NULL_OK
 16  		WHEN TRUE THEN 'TRUE'
 17  		WHEN FALSE THEN 'FALSE'
 18  	   END);
 19    END LOOP;
 20    --
 21    DBMS_SQL.CLOSE_CURSOR(C_);
 22  END;
 23  /
col_null_ok: ~FALSE
col_null_ok: ~FALSE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE
col_null_ok: ~TRUE

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656428 is a reply to message #656382]

Wed, 05 October 2016 11:25

Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas

Member

Barbara,
Thank you for sharing your code here. Grateful and appreciative for your assistance. It was very helpful.
Razz

Razz

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656433 is a reply to message #656382]

Wed, 05 October 2016 12:53

Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas

Member

Sorry Barbara, I should have picked the other smiley for my last post.... Smile

Smile

In my continued quest, I'll check out more sites for more tutorials/examples for intermediate to advanced PL/SQL sites.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656453 is a reply to message #656433]

Thu, 06 October 2016 03:56

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Well, that's a nice idea to have DESCRIBE for SELECT statements as we have DESCRIBE for tables and views.
And that's a good exercise (for me) to investigate in the dictionary.

Code: [Select all] [Show/ hide]

SQL> @descsql 'select * from emp'

select * from emp

   1 EMPNO    NUMBER(4,0)                    NOT NULL
   2 ENAME    VARCHAR2(10)                   NOT NULL
   3 JOB      VARCHAR2(9)
   4 MGR      NUMBER(4,0)
   5 HIREDATE DATE
   6 SAL      NUMBER(7,2)
   7 COMM     NUMBER(7,2)
   8 DEPTNO   NUMBER(2,0)

SQL> @descsqlcr

Function created.

No errors.

Type dropped.


Type created.


Type created.


Function created.

No errors.

SQL> select * from table(descsql1(
  2  'select systimestamp "Time", e.empno "Empno", e.ename "Name", e.sal "Salary",
  3          e.hiredate "Hiredate", d.dname "Department", d.loc "Location"
  4  from emp e, dept d
  5  where d.deptno = e.deptno'))
  6  /
COLUMN_VALUE
-------------------------------------------------------------------------------------
   1 Time       TIMESTAMP(6) WITH TIME ZONE
   2 Empno      NUMBER(4,0)                    NOT NULL
   3 Name       VARCHAR2(10)                   NOT NULL
   4 Salary     NUMBER(7,2)
   5 Hiredate   DATE
   6 Department VARCHAR2(14)                   NOT NULL
   7 Location   VARCHAR2(13)

7 rows selected.

SQL> col name format a20
SQL> select * from table(descsql2(
  2  'select systimestamp "Time", e.empno "Empno", e.ename "Name", e.sal "Salary",
  3          e.hiredate "Hiredate", d.dname "Department", d.loc "Location"
  4  from emp e, dept d
  5  where d.deptno = e.deptno'))
  6  /
       NUM NAME                 TYPE                           NN
---------- -------------------- ------------------------------ --------
         1 Time                 TIMESTAMP(6) WITH TIME ZONE
         2 Empno                NUMBER(4,0)                    NOT NULL
         3 Name                 VARCHAR2(10)                   NOT NULL
         4 Salary               NUMBER(7,2)
         5 Hiredate             DATE
         6 Department           VARCHAR2(14)                   NOT NULL
         7 Location             VARCHAR2(13)

7 rows selected.

The first "pipelined" function returns the result in line of one column, the second one in four columns (which requires the creation of 2 types); pick up the one you prefer.

You will find the scripts in attachment.

Attachment: DescSQL.sql
(Size: 5.50KB, Downloaded 1275 times)
Attachment: DescSQLCr.sql
(Size: 10.47KB, Downloaded 1460 times)

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656465 is a reply to message #656453]

Thu, 06 October 2016 12:59

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Very nice! Thank you!

What is @@envint that you have not provided at the end of DescSQL.sql supposed to do?
Should we remove that or are we missing something?

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656466 is a reply to message #656465]

Thu, 06 October 2016 13:04

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

"envint.sql" is just a script containing all my default SQL*Plus "set" commands so when I leave a script I'm in a deterministic SQL*Plus environment. It is useless here, you can remove it from the script.

Report message to a moderator

Re: Displaying DESC_TAB parms [message #656468 is a reply to message #656466]

Thu, 06 October 2016 13:15

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Thanks for the explanation. In any script where I change such settings, I usually use:

store set saved_settings replace

at the beginning and

start saved_settings

at the end, but I like the idea of storing it once, then just running it. I also have some things in login.sql that I might add.

Report message to a moderator

Previous Topic:	Count of Data in Each Subpartition
Next Topic:	Declations unknown

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 08 05:05:39 CDT 2025