Solved

how to run ddl statement inside an oracle stored procedure

Posted on 2003-12-03
7
3,162 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
'G_F01' is not a procedure or is undefined 3 34
Create table from select - oracle 6 53
MULTIPLE DATE QUERY 15 91
Character matching different date formats for dates between 6 59
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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 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