 |
| Total Answers and Comments: 3 |
Last Update: March 06, 2007 Asked by: ddkdhar |
|
| | |
|
Submitted by: Nikhil_4_Oracle
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).
Above answer was rated as good by the following members:
ratna82, samareshp | |
March 05, 2007 07:28:12 | #1 |
| Nikhil_4_Oracle |
|
|
RE: Is it possible create table in procedure or functi...
|
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).
| | | Is this answer useful? Yes | No |  2  0 | Overall Rating: +2 |
|
| |
Go To Top
|
