以单个记录方式查看数据--print过程

 

CREATE OR REPLACE PROCEDURE etl.print_table(

/*********************************************************************

*功能：横向显示数据，避免sqlplus显示折行的问题

*作者：XXX

*时间：2009-06-14

*备注：输入的sql语句，如有单引号则需要变为双引号

*********************************************************************/       

  p_query         VARCHAR2,   --输入的sql语句，如有单引号则需要变为双引号

  rownum_flag        INTEGER DEFAULT 0 -- 返回的rownum行数限制

)

AS

  l_pro_name    VARCHAR2(400);

  l_thecursor   INTEGER DEFAULT dbms_sql.open_cursor;

  l_columnvalue VARCHAR2(4000);

  l_status      INTEGER;

  l_desctbl     dbms_sql.desc_tab;

  l_colcnt      NUMBER;

  l_query       VARCHAR2(4000);

BEGIN

  dbms_output.enable(1000000);

  EXECUTE IMMEDIATE 'alter session set nls_date_format=''yyyymmdd hh24:mi:ss'' ';

 

  l_query:=p_query;

  l_pro_name:='check rownum_flag ' || rownum_flag;

  IF rownum_flag<>0 THEN

    IF INSTR(UPPER(p_query),'WHERE')>0 THEN

          l_query:=p_query || ' and rownum<=' || rownum_flag;

    ELSE

            l_query:=p_query || ' where rownum<=' || rownum_flag;

    END IF;

  END IF;

 

  l_pro_name:='parse ' || l_query;

  dbms_sql.parse(l_thecursor, l_query, dbms_sql.native);

  dbms_sql.describe_columns(l_thecursor, l_colcnt, l_desctbl);

 

  FOR i IN 1 .. l_colcnt

  LOOP

    dbms_sql.define_column(l_thecursor, i, l_columnvalue, 4000);

  END LOOP;

 

  l_status := dbms_sql.EXECUTE(l_thecursor);

  l_pro_name:='display ' || p_query;

  WHILE (dbms_sql.fetch_rows(l_thecursor) > 0)

  LOOP

    FOR i IN 1 .. l_colcnt

    LOOP

      dbms_sql.column_value(l_thecursor, i, l_columnvalue);

      dbms_output.put_line(rpad(l_desctbl(i).col_name, 30) || ': ' || l_columnvalue);

    END LOOP;

    dbms_output.put_line('-----------------');

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    RAISE_APPLICATION_ERROR(-20099,'Segment is '||l_pro_name||' Error Msg is '||SQLERRM);

END;

/

SHOW ERROR

 

--EXEC etl.print_table('SELECT * FROM scott.emp');

--EXEC etl.print_table('SELECT * FROM scott.emp',1);

--EXEC etl.print_table(q'{SELECT * FROM scott.emp where ename like '%smith%'}');

 

GRANT EXECUTE ON etl.print_table TO PUBLIC;

 

CREATE OR REPLACE PUBLIC SYNONYM print FOR etl.print_table;