Calling a procedure through a database link

Daggers

I want to be able to execute procedures on remote databases. I do this after some lov's and text feilds have been entered and the submit button is clicked(this is a page process running PL/SQL anonymous block). Problem is the name of the procedure is dependant on the name of the database selected. For example this works:

[email protected](
pi_username => :USERID
,pi_type => 'SUPPORT'
,pi_dbname => 'IB');

but this does not:

[email protected]:P1_DATABASE(
pi_username => :USERID
,pi_type => 'SUPPORT'
,pi_dbname => 'IB');

Does anyone know how I can generate the name of a procedure depending on a variable ?

Thanks
Steve

rwendel

Have you tried with execute immediate in your pl/sql block?

execute immediate '[email protected]' || :P1_DATABASE || '(
pi_username => ''' || :USERID || '''
,pi_type => ''SUPPORT''
,pi_dbname => ''IB'');';

Daggers

Hi, thanks I ended up doing something very similar. Just needed a begin and end inside the exec immediate.

Cheers