Praphullit

• Aug 9th, 2006

 

hi , you can run DDL (create) command in PL/SQL through EXECUTE IMMEDIATE command.

Sekhar

• Aug 12th, 2006

 

Hi, Can  you give me an example using EXECUTE IMMEDIATE Command

Dinesh

• Aug 13th, 2006

 

No, I tried this out but.... it give error...You just try it and give me the synatx and the code that u executed

Ram

• Aug 14th, 2006

 

CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)

AS

l_stmt VARCHAR2(200);

BEGIN

DBMS_OUTPUT.put_line('STARTING ');

l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';

execute IMMEDIATE l_stmt;

DBMS_OUTPUT.put_line('end ');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);

END;

chanti4u123

 Profile Answers by chanti4u123 

• Aug 14th, 2006

 

yes we can create a table in the PLSQL block very easly with the help of procedure

hint : here abcd is the procedure name of the stored procedure

create or replace procedure abcd is

begin

create table stud1(sno number(3),sname varchar2(10))

/

end abcd;

/

after that write a program to call the stored procedure

begin

abcd;

end;

/

dineshchaini

 Profile Answers by dineshchaini 

• Aug 15th, 2006

 

Dear friends...

  Whatever the programme u r writing thea .. is that working... NOOOOOOOO.. so plz try it then answer.... cause in Rams programme the procedure is created but not creating any table... and chanti.... this is impossible... go through the machine...

Regards

Dinesh

Maria Antony

• Aug 22nd, 2006

 

Hi,

Whatever Ram has given is the correct approach only. However, if you execute the procedure, you may be left with no table created. That may be because you won't have sufficient privileges. Look down how I tried to careate a procedure as Ram told and how I executed that and what problem I faced and how I rectified that.

SQL> conn maria/maria;
Connected.
SQL> set serverout on;
SQL> CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)
  2 
  3  AS
  4 
  5  l_stmt VARCHAR2(200);
  6 
  7  BEGIN
  8 
  9  DBMS_OUTPUT.put_line('STARTING ');
 10 
 11  l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';
 12 
 13  execute IMMEDIATE l_stmt;
 14 
 15  DBMS_OUTPUT.put_line('end ');
 16 
 17  EXCEPTION
 18 
 19  WHEN OTHERS THEN
 20 
 21  DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);
 22 
 23  END;
 24  /

Procedure created.

SQL> set serverout on
SQL> exec ddl_create_proc('tony_emp');
STARTING
exception ORA-01031: insufficient privilegesmessage-1031

PL/SQL procedure successfully completed.

SQL> conn system/manager;
Connected.
SQL> grant create table to maria identified by maria;

Grant succeeded.

SQL> conn maria/maria;
Connected.
SQL> set serverout on
SQL> exec ddl_create_proc('tony_emp');
STARTING
end

PL/SQL procedure successfully completed.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
TONY_1                         TABLE
TONY_4                         TABLE
TONY_EMP                       TABLE

SQL> select * from emp where rownum < 3;

        ID
----------
         1
         2

SQL> select * from tony_emp where rownum < 3;

        ID
----------
         1
         2

SQL> spool off

tdas2004

 Profile Answers by tdas2004 

• Sep 7th, 2006

 

You are right Maria Antony, but the question is, why is it not possible to create the table thru the procedure while you can create the same
table using create table SQL command.
The answer is, to create the table thru the procedure the create table privilege must have been granted directly to the user rather than a role. Remember the user scott having the emp table has already a create table privilege via the default role 'RESOURCE' allotted when the user was created, thats why u can create using SQL command.

dineshchaini

 Profile Answers by dineshchaini 

• Sep 9th, 2006

 

Dear Maria...

   I traied with the DBA previlage but... again the same prob.. proc created but table is not thea... ok I tried u r proc and mail back to you...