Home » SQL & PL/SQL » SQL & PL/SQL » How to fetch desc_tab to sys_refcursor??? (Oracle 11g)
 How to fetch desc_tab to sys_refcursor??? [message #527882] |
Thu, 20 October 2011 11:28  |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
Hello to every one:
I have this procedure that use the dbms_sql.describe_columns to work with the columns of a sys_refcursor. Every example i have seen so far uses the dbms_output.put_line to print the result and everything works perfect, but how do i fetch that result into a sys_refcursor???
Here is the code example:
procedure describe_columns(cur_1 in out sys_refcursor, cur_2 out sys_refcursor) is
curid number;
colcnt number;
desctab dbms_sql.desc_tab;
begin
curid := dbms_sql.to_cursor_number(cur_1);
dbms_sql.describe_columns(curid, colcnt, desctab);
cur_1 := dbms_sql.to_refcursor(curid);
--HERE IS HOW I PRINT THE RESULT COLUMNS PROPERTIES
for a in 1..desctab.count loop
dbms_output.put_line(to_char(desctab(a).col_name));
end loop;
--HOW TO FETCH desctab dbms_sql.desc_tab INTO parameter cur_2...???
end;
Thanks...
|
|
|
|
|
|
| Re: How to fetch desc_tab to sys_refcursor??? [message #527889 is a reply to message #527883] |
Thu, 20 October 2011 11:59  |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
As i wrote in the original post:
1. Every example i have seen so far uses the dbms_output.put_line to print the result and everything works perfect...
Including the forum...
2. I just want to return another sys_refcursor instead of printing because i will use that cursor in my web server application...
Back to the example:
--HERE IS HOW I PRINT THE RESULT COLUMNS PROPERTIES
for a in 1..desctab.count loop
dbms_output.put_line(to_char(desctab(a).col_name));
end loop;
--HOW TO FETCH desctab dbms_sql.desc_tab INTO parameter cur_2...???
I mean how to fetch the desctab variable (dbms_sql.desc_tab type) to cur_2 out parameter (sys_refcur type)....
Thanks for the response...
|
|
|
|
| Re: How to fetch desc_tab to sys_refcursor??? [message #527915 is a reply to message #527889] |
Thu, 20 October 2011 15:36 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE desc_rec_obj AS OBJECT (
2 col_type INTEGER,
3 col_max_len INTEGER,
4 col_name VARCHAR2(32),
5 col_name_len INTEGER,
6 col_schema_name VARCHAR2(32),
7 col_schema_name_len INTEGER,
8 col_precision INTEGER,
9 col_scale INTEGER,
10 col_charsetid INTEGER,
11 col_charsetform INTEGER,
12 col_null_ok VARCHAR2(5));
13 /
Type created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE desc_tab_obj AS TABLE OF desc_rec_obj;
2 /
Type created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace procedure describe_columns
2 (cur_1 in out sys_refcursor,
3 cur_2 out sys_refcursor)
4 is
5 curid number;
6 colcnt number;
7 desctab dbms_sql.desc_tab;
8 v_desctab desc_tab_obj := desc_tab_obj();
9 begin
10 curid := dbms_sql.to_cursor_number (cur_1);
11 dbms_sql.describe_columns (curid, colcnt, desctab);
12 cur_1 := dbms_sql.to_refcursor(curid);
13 for a in 1..desctab.count loop
14 v_desctab.extend;
15 v_desctab(v_desctab.last) :=
16 desc_rec_obj
17 (desctab(a).col_type,
18 desctab(a).col_max_len,
19 desctab(a).col_name,
20 desctab(a).col_name_len,
21 desctab(a).col_schema_name,
22 desctab(a).col_schema_name_len,
23 desctab(a).col_precision,
24 desctab(a).col_scale,
25 desctab(a).col_charsetid,
26 desctab(a).col_charsetform,
27 null);
28 case when desctab(a).col_null_ok is null
29 then v_desctab(v_desctab.last).col_null_ok := null;
30 when desctab(a).col_null_ok
31 then v_desctab(v_desctab.last).col_null_ok := 'TRUE';
32 when not desctab(a).col_null_ok
33 then v_desctab(v_desctab.last).col_null_ok := 'FALSE';
34 end case;
35 end loop;
36 open cur_2 for select t.* from table (v_desctab) t;
37 end;
38 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> variable g_cur_2 refcursor
SCOTT@orcl_11gR2> declare
2 v_cur_1 sys_refcursor;
3 v_cur_2 sys_refcursor;
4 begin
5 open v_cur_1 for select * from dept;
6 describe_columns (v_cur_1, v_cur_2);
7 :g_cur_2 := v_cur_2;
8 end;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> print g_cur_2
COL_TYPE COL_MAX_LEN COL_NAME COL_NAME_LEN
---------- ----------- -------------------------------- ------------
COL_SCHEMA_NAME COL_SCHEMA_NAME_LEN COL_PRECISION COL_SCALE
-------------------------------- ------------------- ------------- ----------
COL_CHARSETID COL_CHARSETFORM COL_N
------------- --------------- -----
2 22 DEPTNO 6
0 2 0
0 0 TRUE
1 14 DNAME 5
0 0 0
873 1 TRUE
1 13 LOC 3
0 0 0
873 1 TRUE
3 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
 Re: How to fetch desc_tab to sys_refcursor??? [message #528102 is a reply to message #527915] |
Fri, 21 October 2011 11:36 |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
Is there any right way to know the data type name from de dbms_sql.desc_tab col_type property?
In the all_source view you can check that number but you have to parse the text column and for the number and data type name. I have been searching and find nothing like a view or something.
Thanks.
[Updated on: Fri, 21 October 2011 11:37] Report message to a moderator |
|
|
|
|
|
| Re: How to fetch desc_tab to sys_refcursor??? [message #528110 is a reply to message #528103] |
Fri, 21 October 2011 13:18 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I am not aware of any data dictionary view that relates those numeric data types to their descriptions. As far as I know, those numeric types are unique to the dbms_sql package. As you said, you can parse it out of:
select text from all_source where name = 'DBMS_SQL' order by line;
Comically, I believe there is a typo in that list within their code. I am pretty sure that binary_bouble should be binary_double. I just typed the values into a decode statement in the modified demonstration below.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE desc_rec_obj AS OBJECT (
2 col_type VARCHAR2(30),
3 col_max_len INTEGER,
4 col_name VARCHAR2(32),
5 col_name_len INTEGER,
6 col_schema_name VARCHAR2(32),
7 col_schema_name_len INTEGER,
8 col_precision INTEGER,
9 col_scale INTEGER,
10 col_charsetid INTEGER,
11 col_charsetform INTEGER,
12 col_null_ok VARCHAR2(5));
13 /
Type created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE desc_tab_obj AS TABLE OF desc_rec_obj;
2 /
Type created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace procedure describe_columns
2 (cur_1 in out sys_refcursor,
3 cur_2 out sys_refcursor)
4 is
5 curid number;
6 colcnt number;
7 desctab dbms_sql.desc_tab;
8 v_desctab desc_tab_obj := desc_tab_obj();
9 begin
10 curid := dbms_sql.to_cursor_number (cur_1);
11 dbms_sql.describe_columns (curid, colcnt, desctab);
12 cur_1 := dbms_sql.to_refcursor(curid);
13 for a in 1..desctab.count loop
14 v_desctab.extend;
15 v_desctab(v_desctab.last) :=
16 desc_rec_obj
17 (null,
18 desctab(a).col_max_len,
19 desctab(a).col_name,
20 desctab(a).col_name_len,
21 desctab(a).col_schema_name,
22 desctab(a).col_schema_name_len,
23 desctab(a).col_precision,
24 desctab(a).col_scale,
25 desctab(a).col_charsetid,
26 desctab(a).col_charsetform,
27 null);
28 select decode
29 (desctab(a).col_type,
30 1, 'varchar2',
31 2, 'number',
32 8, 'long',
33 11, 'rowid',
34 12, 'date',
35 23, 'raw',
36 24, 'long_raw',
37 96, 'char',
38 100, 'binary_float',
39 101, 'binary_double',
40 106, 'mlslabel',
41 109, 'user_defined',
42 111, 'ref',
43 112, 'clob',
44 113, 'blob',
45 114, 'bfile',
46 180, 'timestamp',
47 181, 'timestaemp_with_tz',
48 182, 'interval_year_to_mont',
49 183, 'interval_day_second',
50 208, 'urowid',
51 231, 'timestamp_with_local_tz')
52 into v_desctab(v_desctab.last).col_type
53 from dual;
54 case when desctab(a).col_null_ok is null
55 then v_desctab(v_desctab.last).col_null_ok := null;
56 when desctab(a).col_null_ok
57 then v_desctab(v_desctab.last).col_null_ok := 'TRUE';
58 when not desctab(a).col_null_ok
59 then v_desctab(v_desctab.last).col_null_ok := 'FALSE';
60 end case;
61 end loop;
62 open cur_2 for select t.* from table (v_desctab) t;
63 end;
64 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> variable g_cur_2 refcursor
SCOTT@orcl_11gR2> declare
2 v_cur_1 sys_refcursor;
3 v_cur_2 sys_refcursor;
4 begin
5 open v_cur_1 for select * from emp;
6 describe_columns (v_cur_1, v_cur_2);
7 :g_cur_2 := v_cur_2;
8 end;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> print g_cur_2
COL_TYPE COL_MAX_LEN COL_NAME
------------------------------ ----------- --------------------------------
COL_NAME_LEN COL_SCHEMA_NAME COL_SCHEMA_NAME_LEN COL_PRECISION
------------ -------------------------------- ------------------- -------------
COL_SCALE COL_CHARSETID COL_CHARSETFORM COL_N
---------- ------------- --------------- -----
number 22 EMPNO
5 0 4
0 0 0 FALSE
varchar2 10 ENAME
5 0 0
0 873 1 TRUE
varchar2 9 JOB
3 0 0
0 873 1 TRUE
number 22 MGR
3 0 4
0 0 0 TRUE
date 7 HIREDATE
8 0 0
0 0 0 TRUE
number 22 SAL
3 0 7
2 0 0 TRUE
number 22 COMM
4 0 7
2 0 0 TRUE
number 22 DEPTNO
6 0 2
0 0 0 TRUE
8 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: How to fetch desc_tab to sys_refcursor??? [message #528113 is a reply to message #528110] |
Fri, 21 October 2011 13:44 |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
Once again your help has been very useful, I where thinking something like that but I rather ask about a possible better solution.
Using describe_columns3 there is a property named col_type_name that show the name of a defined user type.
Thanks...
|
|
|
|
| Re: How to fetch desc_tab to sys_refcursor??? [message #528121 is a reply to message #528113] |
Fri, 21 October 2011 14:49 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
amazing wrote on Fri, 21 October 2011 11:44
Using describe_columns3 there is a property named col_type_name that show the name of a defined user type.
Unfortunately, that's all it shows. Values for the rest of the col_type_name are null. It would be nice if Oracle would populate that column with the rest of the type descriptions. The following demonstration uses describe_columns3 and desc_tab3 and includes the names of user-defined types.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE desc_rec_obj AS OBJECT (
2 col_type NUMBER,
3 col_max_len INTEGER,
4 col_name VARCHAR2(32),
5 col_name_len INTEGER,
6 col_schema_name VARCHAR2(32),
7 col_schema_name_len INTEGER,
8 col_precision INTEGER,
9 col_scale INTEGER,
10 col_charsetid INTEGER,
11 col_charsetform INTEGER,
12 col_null_ok VARCHAR2(5),
13 col_type_name VARCHAR2(30),
14 col_type_name_len NUMBER);
15 /
Type created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE desc_tab_obj AS TABLE OF desc_rec_obj;
2 /
Type created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace procedure describe_columns
2 (cur_1 in out sys_refcursor,
3 cur_2 out sys_refcursor)
4 is
5 curid number;
6 colcnt number;
7 desctab dbms_sql.desc_tab3;
8 v_desctab desc_tab_obj := desc_tab_obj();
9 begin
10 curid := dbms_sql.to_cursor_number (cur_1);
11 dbms_sql.describe_columns3 (curid, colcnt, desctab);
12 for a in 1..desctab.count loop
13 v_desctab.extend;
14 v_desctab(v_desctab.last) :=
15 desc_rec_obj
16 (desctab(a).col_type,
17 desctab(a).col_max_len,
18 desctab(a).col_name,
19 desctab(a).col_name_len,
20 desctab(a).col_schema_name,
21 desctab(a).col_schema_name_len,
22 desctab(a).col_precision,
23 desctab(a).col_scale,
24 desctab(a).col_charsetid,
25 desctab(a).col_charsetform,
26 null,
27 desctab(a).col_type_name,
28 desctab(a).col_type_name_len);
29 select decode
30 (desctab(a).col_type,
31 1, 'varchar2',
32 2, 'number',
33 8, 'long',
34 11, 'rowid',
35 12, 'date',
36 23, 'raw',
37 24, 'long_raw',
38 96, 'char',
39 100, 'binary_float',
40 101, 'binary_double',
41 106, 'mlslabel',
42 109, 'user_defined type: ' || desctab(a).col_type_name,
43 111, 'ref',
44 112, 'clob',
45 113, 'blob',
46 114, 'bfile',
47 180, 'timestamp',
48 181, 'timestaemp_with_tz',
49 182, 'interval_year_to_mont',
50 183, 'interval_day_second',
51 208, 'urowid',
52 231, 'timestamp_with_local_tz')
53 into v_desctab(v_desctab.last).col_type_name
54 from dual;
55 case when desctab(a).col_null_ok is null
56 then v_desctab(v_desctab.last).col_null_ok := null;
57 when desctab(a).col_null_ok
58 then v_desctab(v_desctab.last).col_null_ok := 'TRUE';
59 when not desctab(a).col_null_ok
60 then v_desctab(v_desctab.last).col_null_ok := 'FALSE';
61 end case;
62 end loop;
63 open cur_2 for
64 select t.col_name,
65 t.col_type,
66 t.col_type_name
67 from table (v_desctab) t;
68 end;
69 /
Procedure created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> create or replace type test_typ as object
2 (test_col number);
3 /
Type created.
SCOTT@orcl_11gR2> create table test_tab
2 (col1 number,
3 col2 varchar2(15),
4 col3 date,
5 col4 test_typ)
6 /
Table created.
SCOTT@orcl_11gR2> variable g_cur_2 refcursor
SCOTT@orcl_11gR2> declare
2 v_cur_1 sys_refcursor;
3 v_cur_2 sys_refcursor;
4 begin
5 open v_cur_1 for select * from test_tab;
6 describe_columns (v_cur_1, v_cur_2);
7 :g_cur_2 := v_cur_2;
8 end;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> print g_cur_2
COL_NAME COL_TYPE COL_TYPE_NAME
-------------------------------- ---------- ------------------------------
COL1 2 number
COL2 1 varchar2
COL3 12 date
COL4 109 user_defined type: TEST_TYP
4 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
Goto Forum:
Current Time: Fri Jan 23 04:16:56 CST 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
