Forum Stats

  • 3,734,235 Users
  • 2,246,914 Discussions
  • 7,857,193 Comments

Discussions

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

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?

Sign In or Register to comment.