HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable:

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
theupdatecreatesqlprocedureschemanamehowanddeveloper

Problem

In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable:

define my_schema_name = 'schema_1';  

    CREATE OR REPLACE PROCEDURE my_schema_name.updateDBUSER(
         p_userid IN DBUSER.USER_ID%TYPE,
         p_username IN DBUSER.USERNAME%TYPE)
    IS
    BEGIN

      UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

      COMMIT;

    END;

Solution

define my_schema_name = 'SCHEMA_1';

CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER(
     p_userid IN DBUSER.USER_ID%TYPE,
     p_username IN DBUSER.USERNAME%TYPE)
IS
BEGIN

  UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

  COMMIT;

END;

Code Snippets

define my_schema_name = 'SCHEMA_1';

CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER(
     p_userid IN DBUSER.USER_ID%TYPE,
     p_username IN DBUSER.USERNAME%TYPE)
IS
BEGIN

  UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

  COMMIT;

END;

Context

StackExchange Database Administrators Q#124100, answer score: 5

Revisions (0)

No revisions yet.