I'm having some difficulty passing session substitution strings in APEX 5.
I have a process in my APEX application which will change the password of a user on a remote database. The username is set from the local LDAP authentication so is held in the APEX substitution string :APP_USER
I'd like to pass this string to the remote database so that I can change the password of the currently logged in user (hope that makes sense!)
I have this process which is executed when the "change password" button is pressed on the APEX page:
DECLARE
cursor_handle INTEGER;
cursor_handle_tmp INTEGER;
BEGIN
-- Open a Cursor on the REMOTE database
cursor_handle := DBMS_SQL.OPEN_CURSOR@remote_db;
-- Parse the "change password"
DBMS_SQL.PARSE@remote_db(cursor_handle,'ALTER USER MYUSER IDENTIFIED BY mypassword',DBMS_SQL.NATIVE);
-- Execute the cursor
cursor_handle_tmp := DBMS_SQL.EXECUTE@remote_db(cursor_handle);
-- Close the cursor
DBMS_SQL.CLOSE_CURSOR@remote_db(cursor_handle);
END;
This executes fine as a proof of concept is both the user and password are hard-coded amd the password of the remote user is changed as expected.
However, if I then use the substitution strings :APP_USER and :P111_PASSWORD I get the error message:
missing user or role name
Which implies that the string isn't being passed correctly to the remote DB.
If I use the v function V('APP_USER'), my code will not validate correctly in the editor as APEX flags it with the followng error:
DBMS_SQL.PARSE@passman_bandev(cursor_handle,'ALTER USER v('APP_USER') IDENTIFIED BY P111_RE_PASSWORD',DBMS_SQL.NATIVE);
ORA-06550: line 11, column 63: PLS-00103: Encountered the symbol "APP_USER" when expecting one of the following: ) , * & = - + < / > at in is mod remainder not rem => <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol ", was inserted before "APP_USER" to continue.
So it looks like I might not be escaping the necessary characters correctly?
However, I've tried many different possible combinations without success.
'ALTER USER v('
followed by the wordAPP_USER
then another string') IDENTIFIED BY P111_RE_PASSWORD'
, which makes no sense to the parser. – William Robertson&APP_USER.
is a substitution variable, and:APP_USER
is a bind variable. You can't usev('APP_USER')
in this case because thev
function would be called from the remote server, which does not have access to the local APEX session data. – Jeffrey Kemp