oracle procedure

모든 procedure 확인하기

select text from user_source where name in (select object_name from user_objects where object_type = 'PROCEDURE')

오라클 mysql \G

create or replace PROCEDURE TABLE_PRINT ( p_query in varchar2 )
IS
l_theCursor     integer default dbms_sql.open_cursor;
l_columnValue   varchar2(4000);
l_status        integer;
l_descTbl       dbms_sql.desc_tab;
l_colCnt        number;
BEGIN
execute immediate
'alter session set 
nls_date_format=''yyyy-mon-dd hh24:mi:ss'' ';

dbms_sql.parse(  l_theCursor,  p_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);

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;
execute immediate
'alter session set nls_date_format=''yyyy-mon-dd hh24:mi:ss'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''yyyy-mon-dd hh24:mi:ss'' ';
raise;
END TABLE_PRINT;

SQL> SET SERVEROUT ON SIZE 1000000

또는

SQL> SET SERVEROUTPUT ON

exec table_print('select * from tab');

procedure 생성시 error가 발생하면

show errors

로 확인할 수 있다.

오라클 mysql의 \G 옵션 따라하기

참조 url : http://asktom.oracle.com/pls/asktom/f?p=100:11:1527471567674194::::P11_QUESTION_ID:1035431863958