Re: using dblinks in stored procedures?

scott.micciche_at_sbcglobal.net wrote:

> I'm having a problem passing an arg ( a valid dblink ) to a pl/sql function. The problem is that I'm using the variable in it's place and the procedure won't compile with errors. The error states that the dblink is invalid. The code is similar to the following:
>
> v_link VARCHAR2;
>
> select sysdate
> from dual_at_v_link;
>
> The query is a bit different, but passing this invalid dblink using a variable doesn't work, so I'm having to create a function for each db I do have valid links for and hardcoding the dblink in the function.
>
> I've looked in many pl/sql books and have found no answers, if this is the wrong NG for this question, can someone please point me to the correct forum? Thanks in advance.
>
> Scott
>
> please post back to the NG, thanks.

What you are doing requires native dynamic sql.

sqlstr VARCHAR2(1000);

BEGIN
    sqlstr := 'your sqlstatement using bind variables such as :B'     EXECUTE IMMEDIATE sqlstr
    USING your_variable_to_replace_the_bind_variable; END;

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)