oracle - populate placeholders for a column with values dynamically -

I have the following table structure, which holds the error code and related error messages:

  ERR_CODE ERR_MESSAGE CN001 Invalid user name: USERNM CN002 Invalid password: PWD  

In my PLSQL code, I want to change values ​​dynamically for username and password.

I think that the replacement can be replaced by the clause but in that case the query should be stable.

I am looking for a taste to execute immediately in which it can:

  select ERR_MESSAGE from ERROR_MESSAGES where ERR_CODE = ' CN001 '}' I_LL_RMS_USG is using;  

Or maybe I can break it in 2 steps:

  Select ERR_MESSAGE from ERROR_MESSAGES to err_msg_var where ERR_CODE = 'CN001'; Use l_err_msg in EXECUTE_IMMDIATE err_msg_var l_curr_user;  

Actually, I'm trying to reduce the number of steps involved or maybe get a better performance query or approach.

Thanks in advance!

No, in this situation, there will be no help in the statement execute immediately . Apart from this, there is no need to actually use it - everything (selection list, table name) is known at compile time, it comes down only for string replacement, use static query, not dynamic replacement (or string formatting ) You have at least two options to do:

1. Just replace () function:

     Set server output; clear screen; Declared l_result varchar2 (50); Select err_message from error_messages in l_result where err_code = 'CN001'; Dbms_output.put_line (replace (l_result, ': USERNM', 'new value')); End;  

   Result:

     Invalid user name new value  

2. If possible, Update err_message of your error_message table by transferring : USERNM and : PWD and other similar sub-strings. The column placeholder denotes that with the % s (placeholder for one letter) or % d (placeholder for any integer, if you have one) and the package Use and special The format_message () function:

     set Srhwarautput; clear screen; Declared l_result varchar2 (50); Select err_message from error_messages in l_result where err_code = 'CN001'; Dbms_output.put_line (utl_lms.format_message (l_result, 'new_value_goes_here')); End;  

   Result:

     invalid user name new_value_goes_here