Home » SQL & PL/SQL » SQL & PL/SQL » Help me! 06546. 00000 - "DDL statement is executed in an illegal context" (Oracle 11g r2)
Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621352] Tue, 12 August 2014 13:44 Go to next message
baoph9it
Messages: 4
Registered: August 2014
Location: Viet Nam
Junior Member
create or replace procedure Create_User1(strUserName CHAR, strPass CHAR)
as
  strSQL varchar(256);
Begin
    strSQL := 'CREATE USER ":strUserName" IDENTIFIED BY ":strPass" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"';
    execute immediate strSQL Using strUserName, strPass;
end Create_User1;


---> PROCEDURE CREATE_USER1 compiled

execute CREATE_USER1('user01', 'Orcl1234');


---> Error starting at line : 9 in command -
execute CREATE_USER1('user01', 'Orcl1234')
Error report -
ORA-06546: DDL statement is executed in an illegal context
ORA-06512: at "SYSTEM.CREATE_USER1", line 6
ORA-06512: at line 1
06546. 00000 - "DDL statement is executed in an illegal context"
*Cause: DDL statement is executed dynamically in illegal PL/SQL context.
- Dynamic OPEN cursor for a DDL in PL/SQL
- Bind variable's used in USING clause to EXECUTE IMMEDIATE a DDL
- Define variable's used in INTO clause to EXECUTE IMMEDIATE a DDL
*Action: Use EXECUTE IMMEDIATE without USING and INTO clauses to execute
the DDL statement.


Everybody, help me.
Thanks you very much!!!!!!
  • Attachment: tled.png
    (Size: 34.11KB, Downloaded 19 times)
Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621353 is a reply to message #621352] Tue, 12 August 2014 13:47 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
welcome to this forum.

The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.

Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621354 is a reply to message #621353] Tue, 12 August 2014 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
something is amiss

  1  create or replace procedure Create_User1(strUserName CHAR, strPass CHAR)
  2  as
  3    strSQL varchar(256);
  4  Begin
  5      strSQL := 'CREATE USER ":strUserName" IDENTIFIED BY ":strPass" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"';
  6      --execute immediate strSQL Using strUserName, strPass;
  7      dbms_output.put_line(strSQL);
  8* end Create_User1;
SQL> /

Procedure created.

SQL> execute CREATE_USER1('user01', 'Orcl1234');
CREATE USER ":strUserName" IDENTIFIED BY ":strPass" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"

PL/SQL procedure successfully completed.

Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621355 is a reply to message #621352] Tue, 12 August 2014 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot use bind variables for object names...

Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621356 is a reply to message #621355] Tue, 12 August 2014 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
rarely, if ever, is it necessary (or wise) to use double quote marks [CHR(34)] with Oracle.
They usually cause more problems in the long run, than they "solve" in the short run.

I suggest that you would be better off in the long run to avoid using PL/SQL to CREATE USER;
but you are free to (ab)use your database any way you deem appropriate.
Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621357 is a reply to message #621355] Tue, 12 August 2014 14:23 Go to previous messageGo to next message
baoph9it
Messages: 4
Registered: August 2014
Location: Viet Nam
Junior Member
Michel Cadot. Why?

[Updated on: Tue, 12 August 2014 14:24]

Report message to a moderator

Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621358 is a reply to message #621357] Tue, 12 August 2014 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because it is as it.

In addition, to add to BlackSwan's answer, here " are the root of the error you have otherwise you'd have:
SQL> create or replace procedure Create_User1(strUserName VARCHAR2, strPass VARCHAR2)
  2  as
  3    strSQL varchar(256);
  4  Begin
  5      strSQL := 'CREATE USER :strUserName IDENTIFIED BY :strPass';
  6      execute immediate strSQL Using strUserName, strPass;
  7  end Create_User1;
  8  /

Procedure created.

SQL> execute CREATE_USER1('user01', 'Orcl1234');
BEGIN CREATE_USER1('user01', 'Orcl1234'); END;

*
ERROR at line 1:
ORA-01935: missing user or role name
ORA-06512: at "MICHEL.CREATE_USER1", line 6
ORA-06512: at line 1

Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621359 is a reply to message #621358] Tue, 12 August 2014 14:55 Go to previous messageGo to next message
baoph9it
Messages: 4
Registered: August 2014
Location: Viet Nam
Junior Member
Thank you! Michel & BlackSwan.
In the long run, why i should not use PL/SQL to CREATE USER? I see very handy, i can 'EXECUTE PROc' (easy).
I don't understand, what is 'missing user or role name'? help me!!!!
*
ERROR at line 1:
ORA-01935: missing user or role name
ORA-06512: at "MICHEL.CREATE_USER1", line 6
ORA-06512: at line 1
Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621360 is a reply to message #621359] Tue, 12 August 2014 14:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because:

Michel Cadot wrote on Tue, 12 August 2014 20:56

You cannot use bind variables for object names...


Re: Help me! 06546. 00000 - "DDL statement is executed in an illegal context" [message #621361 is a reply to message #621360] Tue, 12 August 2014 15:14 Go to previous message
baoph9it
Messages: 4
Registered: August 2014
Location: Viet Nam
Junior Member
if so, I'll try something different.
Thank you!

It is 13/08/2014 3:14am Viet Nam.
Not sleeping all night --> I go to bed now.
Previous Topic: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT
Next Topic: SQL
Goto Forum:
  


Current Time: Thu Sep 18 03:14:54 CDT 2014

Total time taken to generate the page: 0.09795 seconds