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...
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment