Originally posted by Tom Urbanowicz
To clarify, if only 3 columns in a record are populated (in a table w/
100 columns), I don't want a query to retrieve 97 NULLs with 97 column
headings. Instead, I need SQL (or PL/SQL) that:
A) Identifies which columns in the table are populated
for a specific record.
B) Creates a SELECT statement using only those populated
columns in the list of columns.
I would be able to identify the columns in SQL*Plus, because I would
have the headings on. For example, the generated SQL and the result
set would be:
set heading on
SQL> select COL1, COL8, COL93
2 from MYTEST
3 where COL1='35';
COL1 COL8 COL93
---------- ---------- ---------
35 44 21-JUL-03
Thanks for your insights.
aruneeshsalhotr wrote in message news:news:... It certainly is possibly what you are trying to do.
But how would you know what columns the result is refering
to. I mean if the query retruns col1, col2 and col93, how would u
know what columns are we talking about.
Though I dont understand the purpose of this query, but I could
give it a shot.
You want to do this for a SINGLE record, not a set of records?
If so then you can use DBMS_SQL to query the record and return each
column in turn. If it is NULL, skip it, otherwise output the column
name and value.
This procedure (based loosely on Tom Kyte's print_table procedure)
will do that:
create or replace procedure no_nulls( p_sql in varchar2 ) is
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(4000);
v_header2 varchar2(4000);
v_record varchar2(4000);
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 -- Varchar2
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 || RPAD( v_desctab(i).col_name, v_length ) || ' ';
v_header2 := v_header2 || RPAD( '-', v_length, '-' ) || ' ';
v_record := v_record || RPAD( v_value, v_length ) || ' ';
END IF;
end loop;
dbms_output.put_line( v_header1 );
dbms_output.put_line( v_header2 );
dbms_output.put_line( v_record );
end loop;
end;
/
For example:
SQL> exec no_nulls('select * from emp where ename=''KING''')
EMPNO ENAME JOB HIREDATE SAL DEPT
------ ---------- --------- ----------- --------- ----
7839 KING PRESIDENT 17-NOV-1981 5000 10
PL/SQL procedure successfully completed.
The above code only handles VARCHAR2, DATE and NUMBER.
--
Posted via
http://dbforums.com