Invoking a function call in a string in an Oracle Procedure

Question

I writing an application using Oracle 10g.

I am currently facing this problem. I take in "filename" as parameter of type varchar2.

A sample value that filename may contain is: 'TEST || to_char(sysdate, 'DDD')'.

In the procedure, I want to get the value of this file name as in TEST147. When i write:

select filename
into ffilename
from dual;

I get the value ffilename = TEST || to_char(sysdate, 'DDD') whick makes sense. But how can I get around this issue and invoke the function in the string value?

Help appreciated. Thanks.

Solution 1

It's easy enough to dynamically execute a string ...

create or replace function fmt_fname (p_dyn_string in varchar2)
    return varchar2
is
    return_value varchar2(128);
begin
    execute immediate 'select '||p_dyn_string||' from dual'
        into return_value;
    return  return_value;
end fmt_fname;
/

The problem arises where your string contains literals, with the dreaded quotes ...

SQL> select fmt_fname('TEST||to_char(sysdate, 'DDD')') from dual
  2  /
select fmt_fname('TEST||to_char(sysdate, 'DDD')') from dual
                                          *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>

So we have to escape the apostrophes, all of them, including the ones you haven't included in your posted string:

SQL> select * from t34
  2  /

        ID FILENAME
---------- ------------------------------
         1 APC001
         2 XYZ213
         3 TEST147


SQL> select * from t34
  2  where filename = fmt_fname('''TEST''||to_char(sysdate, ''DDD'')')
  3  /

        ID FILENAME
---------- ------------------------------
         3 TEST147

SQL>

EDIT

Just for the sake of fairness I feel I should point out that Tony's solution works just as well:

SQL> create or replace function fmt_fname (p_dyn_string in varchar2)
  2      return varchar2
  3  is
  4      return_value varchar2(128);
  5  begin
  6      execute immediate 'begin :result := ' || p_dyn_string || '; end;'
  7          using out return_value;
  8      return  return_value;
  9  end;
 10  /

Function created.

SQL> select fmt_fname('''TEST''||to_char(sysdate, ''DDD'')') from dual
  2  /

FMT_FNAME('''TEST''||TO_CHAR(SYSDATE,''DDD'')')
--------------------------------------------------------------------------------
TEST147

SQL>

In fact, by avoiding the SELECT on DUAL it is probably better.

Solution 2

The string value in your example is an invalid expression; it should be: 'TEST' || to_char(sysdate, 'DDD')

To evaluate that you could do this:

execute immediate 'begin :result := ' || filename || '; end;'
  using out v_string;

v_string will then contain 'TEST147'.

Comments

[DMS]

Thanks for your help. It is generating the value 'TEST147' yet it returns an error on dbms_output.put_line('Result: ' || v_string); Error: PLS-00201: identifier 'TEST147' must be declared.

[Tony Andrews]

I don't understand. What do you get if you put dbms_output.put_line(filename) BEFORE the execute immediate?

[DMS]

the error is thrown when encountering the execute immediate statment. the other method provided by APC works. Thanks for your time.

[Gary Myers]

Executing unvalidated user input through SQL can lead to SQL injection attacks. Using SELECT at least provides some protection as it limits what the function can do. Allowing PL/SQL injection can lead to all sorts of nasties.