CODEWITHSUNDEEP
oracle-databaseplsqloracle11gsubstitutionexecute-immediate
Mohd Asim
Mohd Asim

Reputation: 45

populate placeholders for a column with values dynamically

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 !

Upvotes: 0

Views: 579

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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:

  1. 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

  2. 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

Upvotes: 2

Ram Dwivedi
Ram Dwivedi

Reputation: 470

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.

Upvotes: 0

Related Questions