populate placeholders for a column with values dynamically

Question

I have the following table structure that holds error codes and related error messages:

ERR_CODE    ERR_MESSAGE
CN001       Invalid Username :USERNM
CN002       Invalid Password :PWD

In my PLSQL code, i would like to dynamically substitute the values for username and password.

I understand that EXECUTE IMMEDIATE can do substitutions by the USING clause but the query has to be static in that case.

I am looking for a flavor similar to execute immediate wherein can do this:

SIMILAR_EXECUTE_IMMDIATE q'{select ERR_MESSAGE from ERROR_MESSAGES where ERR_CODE = 'CN001'}' INTO l_err_msg USING l_curr_user;

OR maybe i can break it down in to 2 steps:

select ERR_MESSAGE into err_msg_var from ERROR_MESSAGES where ERR_CODE='CN001';

EXECUTE_IMMDIATE err_msg_var INTO l_err_msg USING l_curr_user;

Basically, I am trying to reduce the number of steps involved or maybe get a better performing query or approach.

Thanks in advance !

Not sure that I'd want any system reporting a user's password in plaintext in any context. Your system should process the password and then discard it without logging it or exposing it in an error message. — Jeffrey Kemp, Feb 18 '14 at 06:52

score 2 · Accepted Answer · answered Feb 18 '14 at 06:10

No, execute immediate statement will be of no help in this situation. Besides there is really no need to use it - everything(select list, table names) is known at compile time, it only comes down to the string substitution. Use static query, not dynamic. In order to do the substitution(or string formatting) you have at least two choices:

Simply use replace() function:

set serveroutput on;
clear screen;

declare
   l_result varchar2(50);
begin
   select err_message
     into l_result
     from error_messages
    where err_code = 'CN001';

   dbms_output.put_line(replace(l_result, ':USERNM', 'new value')); 
end;

Result:

Invalid Username new value

If it possible, update err_message column of your error_messages table by replacing :USERNM and :PWD and other similar sub-strings thta denote placeholders with %s(placeholder for a character literal) or %d (placeholder for an integer literal if you have any) and use utl_lms package and specifically format_message() function:
```
set serveroutput on;
clear screen;

declare
  l_result varchar2(50);
begin
  select err_message
    into l_result
    from error_messages
   where err_code = 'CN001';

  dbms_output.put_line(
     utl_lms.format_message(l_result, 'new_value_goes_here')
   ); 
end;
```
Result:
```
Invalid Username new_value_goes_here
```

Thanks Nicholas ! The second approach sounds good to me. I used similar stuff in Java (MessageFormat.format()), never knew they had similar one in DB. — Mohd Asim, Feb 18 '14 at 18:29

score 0 · Answer 2 · answered Feb 18 '14 at 06:03

You can just get the ERR_MESSAGE from your table via SELECT and append userName or PWD to it. Below is a snippet.

select ERR_MESSAGE into err_msg_var from ERROR_MESSAGES where ERR_CODE='CN001';
dbms_output.put_line(err_msg_var||l_curr_user)

You may return value of dbms_output if you want to define this as a function.

populate placeholders for a column with values dynamically

2 Answers2