Home » SQL & PL/SQL » SQL & PL/SQL » Createing a schema with execute immediate
Createing a schema with execute immediate [message #186564] Tue, 08 August 2006 09:09 Go to next message
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
Junior Member
Hi there I have a procuedure tio create a schema like such :-

CREATE OR REPLACE PROCEDURE New_Geneva_User(v_userName IN VARCHAR2) IS

BEGIN
EXECUTE IMMEDIATE 'CREATE USER '||v_userName||' IDENTIFIED BY TALKTALK DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE LOW_PROFILE PASSWORD EXPIRE ACCOUNT UNLOCK ' ;

END ;
/

If I try this it comes back with insufficient privileges; however if I run the code in SQL signed on as the same user it runs ok.


If I change the syntax to
EXECUTE IMMEDIATE 'CREATE USER '||v_userName||' IDENTIFIED BY TALKTALK DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE LOW_PROFILE PASSWORD EXPIRE ACCOUNT UNLOCK; ' ;

It comes back with invalid character.

Does any one know what I am doing wrong as it is giving me fits

regards
Bob.....
Re: Createing a schema with execute immediate [message #186566 is a reply to message #186564] Tue, 08 August 2006 09:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The second statement has a ';' in the execute immediate.

The problem with the first statement is that you need to have the 'CREATE USER' privilege granted to you directly, rather than via a Role.
Re: Createing a schema with execute immediate [message #186575 is a reply to message #186566] Tue, 08 August 2006 09:58 Go to previous message
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
Junior Member
Hi JRowbottom,
That works. Hopefully the DBA's will give me the same privs on PROD fingers crossed.


thanks for you help
Previous Topic: SQL Confirm
Next Topic: object types in oracle.
Goto Forum:
  


Current Time: Mon Dec 05 05:08:22 CST 2016

Total time taken to generate the page: 0.11171 seconds