Re: dynamic pl/sql

From: <asprusch_at_my-dejanews.com>
Date: Sat, 25 Jul 1998 13:05:26 GMT
Message-ID: <6pcl6l$4d$1@nnrp1.dejanews.com>

Yes, it's dbms_sql. Here's a little example on the famous SCOTT schema. You must grant execute on dbms_sql to scott.

create or replace procedure select_any_table(sql_stmt varchar2) is   cur integer := dbms_sql.open_cursor;

  all_def_column varchar2(2000);
  col_no integer := 0;
  var_not_in_select_list exception;

  pragma exception_init(var_not_in_select_list,-1007);   ret integer := 0;
  output_line varchar2(32000);
begin
  dbms_output.enable(150000);
  dbms_sql.parse(cur, sql_stmt, dbms_sql.v7);   begin
    loop

      col_no := col_no + 1;
      dbms_sql.define_column(cur, col_no, all_def_column, 2000);

    end loop;
  exception
    when var_not_in_select_list then
      null;
  end;
  ret := dbms_sql.execute(cur);

  while dbms_sql.fetch_rows(cur) > 0 loop     output_line := '';
    for i in 1 .. (col_no-1) loop

      dbms_sql.column_value(cur,i,all_def_column);
      output_line := output_line || rpad(all_def_column,20) || ' ';

    end loop;
    dbms_output.put_line(output_line);
  end loop;

  dbms_sql.close_cursor(cur);
end;
/
show errors
execute SELECT_ANY_TABLE('select rowid, m.* from emp m for update of m.sal'); commit;

Hope this will help.
Andreas Prusch

In article <6pbc4a$m21_at_newsb.netnews.att.com>,   "shailesh" <svaikul_at_att.com> wrote:
> hi,
> It's urgent !!!
> can anybody tell me which oracle utility is used for writing DYNAMIC PL/SQL.
> Is it DBMS_SQL.
> thanks in advance.
> shailesh
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Sat Jul 25 1998 - 08:05:26 CDT