Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Can I create a local stored procedure and have it called by another script?

AxDMay 25 2021

I envision the following:
I want to create a number of users in a script. Due to the plethora of grants to provide I'd like to split each indivisual user into a separate .sql file, like this:

|-- User1.sql
|-- User2.sql
|-- User3.sql
|-- User4.sql

All these files have in common that I first drop the corresponding user account if it already exists and then recreate that user account and add individual rights to it.
So, I'd like to create a procedure that's supposed to drop and create a user. This procedure is supposed to be stored in some kind of "main" script and called by each of the User scripts.
The Main SQL file is supposed to look like this:

DECLARE
PROCEDURE CREATE_USER
( USERNAME IN VARCHAR2(200)
, TABLESPACE IN VARCHAR2(200)
)
BEGIN
  DROP USER ...
  CREATE USER ...
  END CREATE_USER;

@User1.sql
@User2.sql
@User3.sql
@User4.sql

Each of the User files is then supposed call the procedure and add grants to the user:

DEFINE USERNAME = 'User1'

CREATE_USER(&USERNAME);

GRANT ... TO &USERNAME;
GRANT ... TO &USERNAME;

The folder structure would look something like this then:

|-- Main.sql
|-- User1.sql
|-- User2.sql
|-- User3.sql
|-- User4.sql

Can this be done?

Comments

Post Details

Added on May 25 2021
0 comments
139 views