Solved

how to run ddl statement inside an oracle stored procedure

Posted on 2003-12-03
7
3,220 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:rajneesh75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9865722
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
 
LVL 15

Expert Comment

by:andrewst
ID: 9865760
It is simply:

BEGIN
  EXECUTE IMMEDIATE 'create user xxx identified by yyy';
END;
/
0
 
LVL 15

Expert Comment

by:andrewst
ID: 9865793
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9865861
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
 
LVL 15

Accepted Solution

by:
andrewst earned 125 total points
ID: 9865897
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
 
LVL 15

Expert Comment

by:andrewst
ID: 9865942
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
 
LVL 13

Expert Comment

by:riazpk
ID: 9866075
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question