APEX - passing substitution string in PL/SQL

Question

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.

Use bind variables :http://www.java2s.com/Code/Oracle/System-Packages/ExecutingPLSQLBlocksanduseBINDVARIABLEtobindvariable.htm — Rene, Jan 26 '18 at 10:23
Did you mean to quote the quotes, or embed a bind variable, or concatenate the output from a local function, or have it call the function at the remote site? Currently you have one string `'ALTER USER v('` followed by the word `APP_USER` then another string `') IDENTIFIED BY P111_RE_PASSWORD'`, which makes no sense to the parser. — William Robertson, Jan 26 '18 at 10:35
Note that `&APP_USER.` is a *substitution variable*, and `:APP_USER` is a *bind variable*. You can't use `v('APP_USER')` in this case because the `v` function would be called from the remote server, which does not have access to the local APEX session data. — Jeffrey Kemp, Jan 30 '18 at 01:52

score 2 · Accepted Answer · answered Jan 26 '18 at 10:34

2

How about this? Prepare ALTER USER statement in a local variable, and then use it in DBMS_SQL.PARSE.

declare
  l_str         varchar2(100);
  l_my_password varchar2(30) := 'pwd';
begin                             
  l_str := 'alter user ' || :APP_USER || ' identified by ' || l_my_password;
  DBMS_SQL.PARSE@remote_db(cursor_handle, l_str, DBMS_SQL.NATIVE); 
  ...
end;

answered Jan 26 '18 at 10:34

Littlefoot

131,892
15
35
57

2

Note: I would surround the password with double-quotes in case someone puts spaces in their password, e.g. ...`' identified by "' || l_my_password || '"';` – Jeffrey Kemp Jan 30 '18 at 01:54

APEX - passing substitution string in PL/SQL

1 Answers1