DBMS_SQL

• 동적인 SQL을 생성할 때 사용할 수 있는 패키지이다. 현재는 Native Dynamic SQL을 사용하지만, 그 이전에는 DBMS_SQL를 사용했다.
• 하지만, 필자의 경우 V$SQL_SHARED_CURSOR를 조회할때에는 이 패키지를 사용한다. V$SQL_SHARED_CURSOR는 많은 컬럼이 Y,N값을 가지기 때문에 가독성이 떨어지기 때문이다.
• DBMS_SQL패키지를 이용하여 컬럼 값이 Y인 컬럼만 출력하게 하면 가독성이 높혀진다.

SQL> set serveroutput on

SQL> declare
  c number;
  col_cnt number;
  col_rec dbms_sql.desc_tab;
  col_value varchar2(4000);
  ret_val number;

  begin
    c := dbms_sql.open_cursor;
      dbms_sql.parse(c, 'select q.sql_text, s.* from v$sql_shared_cursor s, v$sql q
      where s.sql_id = q.sql_id and s.child_number = q.child_number
      and s.sql_id = ''&sql_id''', dbms_sql.native);

      dbms_sql.describe_columns(c, col_cnt, col_rec);

      for idx in 1 .. col_cnt loop
        dbms_sql.define_column(c, idx, col_value, 4000);
      end loop;

      ret_val := dbms_sql.execute(c);

      while ( dbms_sql.fetch_rows(c) > 0 ) loop
        for idx in 1 .. col_cnt loop
          dbms_sql.column_value(c, idx, col_value);
          if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
          'CHILD_NUMBER','SQL_TEXT') then
          dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
          elsif col_value = 'Y' then
          dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);
          end if;
        end loop;

        dbms_output.put_line('----------------------------------------------------------');
      end loop;
      dbms_sql.close_cursor(c);
  end;
/

• 톰 카이트(Tom Kyte)가 작성한 PRINT_TABLE이라는 프로시져도 DBMS_SQL을 사용하고 있다.

create or replace procedure print_table(p_query in varchar2)
AUTHID CURRENT_USER
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
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native);
dbms_sql.describe_columns (l_theCursor, l_colCnt, l_descTbl);

for I int 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;
dbms_sql.close_cursor(l_theCursor);
exception
when others then
raise;
end;
/