CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
ConfusedDeer
ConfusedDeer

Reputation: 3415

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

In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable? The code below does not work.

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;

Upvotes: 0

Views: 1168

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50007

You'd need to use the variable properly in your script:

DEFINE MY_SCHEMA_NAME = 'SCHEMA1'
/
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;
/

Best of luck.

Upvotes: 2

Related Questions