Skip to Main Content
  • Questions
  • How to get all records for a dynamic query using DBMS_SQL package

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 09, 2014 - 6:22 pm UTC

Last updated: December 10, 2014 - 11:22 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Newbie here. The following code works fine for a dynamic query but it just fetches only 1 record. I tried to use DEFINE_ARRAY function but not successful. Just need a hint for the following code on how to approach to get all the records. The procedure gives me only "populated" columns (ignoring empty columns) for a record.

create or replace procedure no_nulls( p_sql in varchar2, str out varchar2, str1 out varchar2 ) as
v_sql varchar2(32767) := p_sql;
v_cursor integer := dbms_sql.open_cursor;
v_value varchar2(4000);
v_status integer;
v_desctab dbms_sql.desc_tab;
v_numcols integer;
v_header1 varchar2(32000);
v_header2 varchar2(32000);
v_record varchar2(32000);
v_length integer;
begin
dbms_sql.parse( v_cursor, v_sql, dbms_sql.native );
dbms_sql.describe_columns( v_cursor, v_numcols, v_desctab );

for i in 1 .. v_numcols loop
dbms_sql.define_column(v_cursor, i, v_value, 4000);
end loop;

v_status := dbms_sql.execute(v_cursor);

while ( dbms_sql.fetch_rows(v_cursor) > 0 ) loop
v_header1 := '';
v_header2 := '';
v_record := '';
for i in 1 .. v_numcols loop
dbms_sql.column_value( v_cursor, i, v_value );
IF v_value IS NOT NULL THEN
IF v_desctab(i).col_type = 1 THEN -- Varchar
v_length := v_desctab(i).col_max_len;
ELSIF v_desctab(i).col_type = 9 THEN --Varchar2
v_length := v_desctab(i).col_max_len;
ELSIF v_desctab(i).col_type = 96 THEN --Char
v_length := v_desctab(i).col_max_len;
ELSIF v_desctab(i).col_type = 12 THEN -- Date
v_length := 11;
ELSE -- Assumes number!
v_length := v_desctab(i).col_precision+2;
END IF;

v_header1 := v_header1 ||'''''''||'||v_desctab(i).col_name|| '||'''''',';
v_header2 := v_header2 || v_desctab(i).col_name || ',';
--v_header2 := v_header2 || RPAD( '-', v_length, '-' ) || ' ';
--v_record := v_record || v_value || ' ';
v_record := v_record ||''''''|| v_value || ''''',';
END IF;
end loop;
v_header1 := RTRIM(v_header1,',');
v_header2 := RTRIM(v_header2,',');
v_record := RTRIM(v_record,',');
str1 := v_record;
str := v_header2;
--dbms_output.put_line( v_header1 );
--dbms_output.put_line( v_header2 );
--dbms_output.put_line( v_record );
end loop;
end;
/

and Tom said...

I think you are looking for something to create CSV type output. If so:

see http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html

for a link to a plsql unit that does just this - it demonstrates how to use dbms_sql in any case. It uses UTL_FILE, you can easily change that to dbms_output if you like.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 11, 2014 - 3:54 pm UTC

That link was very helpful but I was able to find the following from previous posts which is what exactly I wanted in my case. Thanks a ton for the quick response. From now on, I would not be going to google anything related to oracle and I would rather come straight to this portal as it has amazing items that a newbie like me can learn a lot just readying through it.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4458446091574

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library