Solved

how to run ddl statement inside an oracle stored procedure

Posted on 2003-12-03
7
3,093 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now