Nikhil_4_Oracle

• Mar 5th, 2007

 

Hi,

Its possible to create a table using procedures.
There are two ways,

 #1
      Use PRAGMA AUTONOMOUS_TRANSACTION;

 Code:

   Create or replace procedure Create_tab

    As

    PRAGMA AUTONOMOUS_TRANSACTION;

    Begin

     EXECUTE IMMEDIATE
    ‘Create TABLE ProcT(col1 number(2), col2 varchar2(10))’;

     End;

   #2

     Use Dbms_sql Package 

 Code:


 Create or Replace procedure Dynamic_Tab
 
(TabName in varchar2)

 as
 
V_cursor INTEGER;
Ddl_Execute INTEGER;

begin

V_cursor:=Dbms_sql.Open_cursor;

Dbms_sql.Parse(V_cursor,'Create Table  '||TabName||' (col1 number(2),col2 varchar2

(10))',Dbms_sql.native);

Ddl_Execute:=dbms_sql.Execute(V_cursor);

Dbms_output.put_line(TabName||' Created.');

If Dbms_sql.Is_open(V_cursor) then

Dbms_sql.Close_cursor(V_Cursor);

end if;

Exception

when Others then

Dbms_output.put_line(sqlerrm);

End Dynamic_Tab;

Regards,

Nikhil (iflex).



 

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Mar 5th, 2007

 

thank you very mech nikhil

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Mar 6th, 2007

 

one thing nikhil handle sqlerrm with variables .that increases perfmance ok.