5 Replies Latest reply: Oct 22, 2013 8:03 AM by Hugo_4711 RSS

    Need example to create table through DBMS_SQL Package

    nagarw31

      Hi Guru's

       

      Could you please suggest me how to create table by using DBMS_SQL Package.I created the below Procedure and its executed successfully but when I query on table it says"Table Doesn't Exists".please Advice

       

      Below is the procedure I created.

       

      Create or replace procedure test(p_table_name varchar2) AS

      cursor_name integer;

      row_processed integer;

      begin

      cursor_name :=dbms_sql.open_cursor;

      dbms_sql.parse(cursor_name,'create table :t (id number,name varchar2(50),location varchar2(50))',dbms_sql.native);

      dbms_sql.bind_variables(cursor_name,':t',p_table_name);

      row_processed:=dbms_sql.execute(cursor_name);

      dbms_sql.close_cursor(cursor_name);

      exception

      when others then

      dbms_sql.close_cursor(cursor_name);

      end;

      /

      this procedure executed successfully

      exec test('TEST_N12345')

       

      but when I do select * from test_n12345 it says "Table Doesn't exists"

       

      please advice

       

      Thanks in Advance!!