snippetMinor
In Oracle SQL Developer, how do I create or update a procedure and have the schema name be a variable:
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.