You Asked

Hey Tom -

I'm facing a situation where a function I need to call can vary from DB to DB. The signature will remain the same, but the name might vary. The name is stored in a table in the DB, with some of the signature.

So:

create or replace function f1 ( id in number, base in number ) return numb
as
    x number;
begin
    x := to_char(sysdate,'MM');
    return x;
end;
/

create table t1 ( udf_function varchar2(50) );

insert into t1 values ('f1( id, base )');

commit;

create table t2 ( id number );

insert into t2 values ( 1 );

commit;

So, I can get the function out with a substr, and attach the value I need from the other table:

select substr(t1.udf_function,1,instr(t1.udf_function,'('))||t2.id||','||1||')' myfunc
  from t1,
       t2
 where t2.id = 1
   and t1.udf_function is not null
/

But can I call the function in my SQL - can I get it to return 8, in this case?

and Tom said...

...
I'm facing a situation where a function I need to call can vary from DB to DB.
....

sounds like you actually want to do this as part of an INSTALLATION routine then if you ask me.

eg: instead of "insert foo into a table", you "create or replace ...." and reference foo directly.

In another database, you use bar

and so on.

I hate that you are not using bind variables, period.

You should just store (if you do this dynamically, i would counsel STRONGLY against that) the function name with bind names at most.

ops$tkyte%ORA10GR2> declare
  2      l_return number;
  3  begin
  4      for x in ( select udf_function, t2.id t2id
  5                   from t1, t2
  6                  where t2.id = 1
  7                    and t1.udf_function is not null )
  8      loop
  9          execute immediate 'begin :x := ' || x.udf_function || '; end;' using OUT l_return, IN x.t2id, IN 1;
 10          dbms_output.put_line( l_return );
 11      end loop;
 12  end;
 13  /
8

PL/SQL procedure successfully completed.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

If wishes were horses

Stephan Uzzell, August 21, 2007 - 10:12 pm UTC

I agree completely, Tom, unfortunately, it is out of my hands. The actual function is going to vary from install to install (based on tax laws). Generally, it is one thing, but there's no guarantee to that effect.

Given the way it is stored, do you see any way to call it?

As always, thanks.

August 22, 2007 - 1:30 pm UTC

how do you know what to replace in there? If it is always "I supply a single input, it gives me a single output" then you should be storing:

begin :x := f( :y, 1,2,3,4 ); end;

then you can select that out, and execute it. As is,

execute immediate l_str using OUT p_return, IN p.id;

just bear in mind, you'll be parsing this way more than you should, I still think this is an INSTALL TIME THING, at the time you INSTALL, you hard code the right function in there, done, no worries.

How about dynamic SQL?

Pedro M. Guerra, August 22, 2007 - 6:36 am UTC

Stephan,

If I understood your question, you need a way to call a function from SQL instructions, and that function name (anda parameters) are stored somewhere.
So, my first thoughts go to using dynamic SQL. Supose you simply want to get the return from f1 (in your example) SELECTed from DUAL. Just do something like (again, referring to your example on how to get the function name):

EXECUTE IMMEDIATE 'SELECT '||
select substr(t1.udf_function,1,instr(t1.udf_function,'('))||t2.id||','||1||')' myfunc
from t1,
t2
where t2.id = 1
and t1.udf_function is not null||' FROM DUAL';

This is just a incomplete thought; no bind variables, etc... Use it as a starting point. The idea is to dynamically assemble SQL instructions with calls to «dynamic» functions...

DBMS_SQL comes to mind also, but haven't used it yet...

Cheers,
PMGuerra

Functions

Mike, August 22, 2007 - 7:45 am UTC

If you only need this function to be different at the database level, why not use a synonym ? It sounds like you are making the problem harder than it needs to be.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

If wishes were horses

How about dynamic SQL?

Functions

More to Explore

PL/SQL demos

PL/SQL docs

Call a function name pulled from a table

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

If wishes were horses

How about dynamic SQL?

Functions

More to Explore

PL/SQL demos

PL/SQL docs