Introduction : This pl/sql block displayes in the following fashion

column1 - value1
column2 - value2
column3 - value3

Any number of tables can be there

Usage - Save the qurey as disprec.sql
        In the sqlprompr @disprec
        Enter your statement

Limitations - Maximum output can be 1000000
              Entire sql should be typed without pressing Enter
              If any invalid column or table is present does not say which table or column
              If more throws exception ORU-100027

declare
q_cursor         integer;
column_val       varchar2(256);
tretval          integer;
col_cnt          integer;
desc_t           dbms_sql.desc_tab;
error_position   integer;
fileid           utl_file.file_type;
filedir          varchar2(250);
filename         varchar2(250);
t_query          varchar2(4000);
tstr             varchar2(200);
statement_error  exception;
pragma exception_init(statement_error,-921);
begin
  t_query := ltrim(rtrim('&qry'));
  dbms_output.enable(1000000);
  if substr(t_query,length(t_query),1) in (';','/') then
     t_query := substr(t_query,1,length(t_query)-1);
  end if;
  q_cursor  := dbms_sql.open_cursor;
  dbms_sql.parse(q_cursor,t_query,dbms_sql.v7);
  error_position := dbms_sql.last_error_position;
  dbms_sql.describe_columns(q_cursor,col_cnt,desc_t);
  for i in 1..col_cnt loop
    dbms_sql.define_column(q_cursor,i,column_val,255);
  end loop;
  tretval := dbms_sql.execute(q_cursor);
  loop
    tretval := dbms_sql.fetch_rows(q_cursor);
    if tretval = 0 then
       exit;
    end if;
    for i in 1..col_cnt loop
      dbms_sql.column_value(q_cursor, i,column_val);
      dbms_output.put_line(desc_t(i).col_name || ' - '||column_val);
    end loop;
    dbms_output.put_line('<-----Record End ----------->');
  end loop;
  exception when others then
  error_position := dbms_sql.last_error_position;
  raise_application_error(-20001,sqlcode||' '||sqlerrm||' Error at '||error_position);
end;
/

Return to : Oracle Database, SQL, Application, Programming Tips