Need to concat a dblink on to a function

[llmclaughlin]

With the below code, I'm passing in the P_DBNumber to the SP. What I need to have happen is instead of having the @CB185p1 that it have the variable L_DBNumber, as it could be a different database number each time the sp is called. Is it possible to have a variable like this.

L_DBNumber VARCHAR2(10);

BEGIN


L_DBNumber := P_DBName;

Utility.Grab_Recpt_Number@CB185P1(P_Station,L_Receipt_Number);

Thanks

Louie

 

[markrem]

That requires dynamic SQL. Create a variable such as var_sql varchar2(4000). Then in your code write the command into this variable. When you issue the command EXECUTE IMMEDIATE VAR_SQL the system will resolve your dblink and other parameters into real values, construct a real SQL statement, and run it.

Same concept used to query against a table when you don't know what the table name is in advance. Works very well.

Function return values. Your EXECUTE IMMEDIATE will probably need an INTO clause. See the docs for details.


MarkRem
Author, Oracle Database 10g: From Nuts to Soup

http://www.remidata.com/book_nuts2soup.htm