Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to run ddl statement inside an oracle stored procedure

Posted on 2003-12-03
7
Medium Priority
?
3,404 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 375 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

572 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