Home » SQL & PL/SQL » SQL & PL/SQL » prob in EXECUTE IMMEDIATE
prob in EXECUTE IMMEDIATE [message #215561] Mon, 22 January 2007 22:30 Go to next message
sanammg
Messages: 31
Registered: June 2005
Member
i written one function to create table using execute immediate
i am getting below error,
can anyone help me.


create or replace function mnop(a number,b number) return number is
begin
EXECUTE IMMEDIATE 'CREATE TABLE MNO(
A NUMBER,B NUMBER,C NUMBER,D NUMBER)';
end mnop;
SQL> /

Function created.

SQL> select mnop(10,10) from dual;
select mnop(10,10) from dual
*
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "EXTOTSTESTUSER.MNOP", line 3

Re: prob in EXECUTE IMMEDIATE [message #215565 is a reply to message #215561] Mon, 22 January 2007 23:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Restrictions1
Restrictions2
By
Vamsi
Re: prob in EXECUTE IMMEDIATE [message #215570 is a reply to message #215561] Mon, 22 January 2007 23:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
For your information, description of the error can be found eg. here.
So either declare mnop autonomous (see Example 6-44) or call it in a PL/SQL block eg.
declare
  l_i integer;
begin
  l_i := mnop(10,10);
end;
/

Be aware of the fact that the parameters a,b are not used inside the function (so they have no effect on its functionality) and your function does not return anything (so if it will not fail it will most probably return null).
If you call it second time, you will get another error as the table will already exist. You should handle the exception.
However it is not a good idea to create tables dynamically (unless you have a VERY strong reason).
Re: prob in EXECUTE IMMEDIATE [message #215572 is a reply to message #215561] Mon, 22 January 2007 23:33 Go to previous message
sanammg
Messages: 31
Registered: June 2005
Member
thanks a lot, i will take care
Previous Topic: small problem in calling function
Next Topic: wat's i stands for in oracle 9i
Goto Forum:
  


Current Time: Fri Dec 02 20:20:08 CST 2016

Total time taken to generate the page: 0.09376 seconds