Home » SQL & PL/SQL » SQL & PL/SQL » Displaying DESC_TAB parms (Oralce 11g)
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;
|
|
|
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)
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;
/
|
|
|
|
|
|
|
|
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 |
|
|
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;"
|
|
|
|
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);
|
|
|
|
|
|
|
Re: Displaying DESC_TAB parms [message #656453 is a reply to message #656433] |
Thu, 06 October 2016 03:56   |
 |
Michel Cadot
Messages: 68757 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.
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 1273 times)
-
Attachment: DescSQLCr.sql
(Size: 10.47KB, Downloaded 1459 times)
|
|
|
|
|
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.
|
|
|
Goto Forum:
Current Time: Thu May 22 05:32:03 CDT 2025
|