how to run ddl statement inside an oracle stored procedure

Hi experts
            i want to create a user inside an oracle stored procedure. ii know that one normally cannot run ddl queries inside stored proceudres and one requires dbms_sql package to do this.
         can anyone tell me the code of the oracle sp to create user.
                            thanks in advance
LVL 1
rajneesh75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

catchmeifuwantCommented:
You can use a dynamic query to run ddl statements...

create or replace procedure sp_ddl
as
v_sql varchar2(1000);
begin
v_sql := 'create user scott identified by tiger';

execute immediate v_sql;

exception
when others then
dbms_output.put_line(sqlerrm);
end;
/

Execute the procedure as, from sqlplus,(you need to have the privileges to create a user)
exec sp_ddl;

The user will be created
0
andrewstCommented:
It is simply:

BEGIN
  EXECUTE IMMEDIATE 'create user xxx identified by yyy';
END;
/
0
andrewstCommented:
If you can't use EXECUTE IMMEDIATE and must use DBMS_SQL then do this:

declare
  c integer := dbms_sql.open_cursor;
begin
  dbms_sql.parse( c, 'create user x identified by y', dbms_sql.native );
  dbms_sql.close_cursor(c);
end;
/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

catchmeifuwantCommented:
andrewst,

The question is how to run inside a procedure.

My assumption is if you can run it from an anonymous block,you might as well run from sqlplus.

0
andrewstCommented:
It is a simple step to turn any anonymous PL/SQL block into a procedure.  Just replace "DECLARE" by "CREATE OR REPLACE PROCEDURE <name> IS".  You can also pass useful arguments.

So:

CREATE OR REPLACE
PROCEDURE create_user( p_username IN VARCHAR2, p_password IN VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'create user ' || p_username || ' identified by ' || p_password;
END;
/

Or:

CREATE OR REPLACE
PROCEDURE create_user( p_username IN VARCHAR2, p_password IN VARCHAR2) IS
  c integer := dbms_sql.open_cursor;
BEGIN
  dbms_sql.parse( c, 'create user ' || p_username || ' identified by ' || p_password, dbms_sql.native );
  dbms_sql.close_cursor(c);
END;
/



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andrewstCommented:
catchmeifuwant,

I hadn't seen your first post when I posted earlier.  What is the purpose of the exception section? :-

exception
when others then
dbms_output.put_line(sqlerrm);

All it does is potentially hide the error from the caller.
0
riazpkCommented:
Here is procedure i use:


CREATE OR REPLACE  PROCEDURE "CR"."CREATE_USER"  (pusername in
    varchar2,
                    ppassword in varchar2,put_role in varchar2) iS

   begin

 execute immediate 'Create User '||pusername||
                ' Identified by '||ppassword
                ||' Default Tablespace users Temporary Tablespace temp';

 execute immediate 'grant '||put||' to '||pusername;

 
 execute immediate 'grant create session to '||pusername;
 
 execute immediate 'grant connect to '||pusername;

end;


This procedure accepts three parameters:
username
password
Role (to be granted to the user)


Riaz
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.