| How to create table in Stored Procedure? [message #38480] |
Sun, 21 April 2002 21:07  |
m.a
Messages: 2
Registered: April 2002
|
Junior Member |
|
|
How to create table's name inside SP? The table name will be create depends on the current years, and i am using &nam for the name of the table.
this is my code:
procedure cretab_cond
IS
v_sql_cretab varchar2(2000);
nam VARCHAR2(90);
flag number := 0;
cursor try IS
select table_name from user_tables;
begin
nam := 'TEMP' || to_char(sysdate, 'yyyy');
for try_r in try
loop
if try_r.table_name = nam
then
flag := 1;
exit;
end if;
end loop;
if flag =0
then
v_sql_cretab := 'create table &nam
(
i varchar2(10));
execute immediate v_sql_cretab;
commit;
end;
thanks,
|
|
|
|
| Re: How to create table in Stored Procedure? [message #38484 is a reply to message #38480] |
Mon, 22 April 2002 09:14  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
you need to make use of Dynamic sql.
for all DDL (inside pl/sql blocks) you have to make use of dynamic sql.
the following is an example to rebuild the indexes online using pl/sql.
SQL> get in
1 create or replace procedure index_rebuild
2 AUTHID CURRENT_USER
3 is
4 cursor c1 is select index_name from user_indexes where blevel>=3;
5 str varchar2(200);
6 begin
7 for mag in c1 loop
8 exit when c1%notfound;
9 str:='alter index '||mag.index_name || ' rebuild online';
10 EXECUTE IMMEDIATE str;
11 end loop;
12* end;
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
