DB Link as a PL/SQL variable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DB Link as a PL/SQL variable

  1. #1
    Join Date
    Sep 2000
    Posts
    26
    I have created a database link that works correctly when the following statement is run:

    select * from user.table@dblink

    Now I want the name of the dblink to be able to be passed into a PL/SQL Procedure such that:

    Procedure myProc(dblink varchar2) IS

    begin
    select * from user.table@dblink;
    end;

    and calling myProc('aValidDBLink') would return all records from the table in the database linked to via that aValidDBLink.

    I've tried several attempts to concatenate the dblink string onto the end of the from clause, but none of it is valid. Aside from resorting to Dynamic PL/SQL is there any way of doing this? In the event that Dynamic PL/SQL is required could you give me some sample code that would achieve the desired effect, as I've never touched on it before.

    Thanks in advance

    Matt


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Dynamic SQL is your best bet. If you are on 8.1+, you can use Native Dynamic SQL which is much easier to use then the old dbms_sql package.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2000
    Posts
    26
    Yeah, after browsing a bit longer I found an example of using Execute Immediate. So I've now built up the string successfully (e.g. in v_sql_string) so now if I do an "execute immediate v_sql_string;" inside TOAD then I get no complaints/warnings, etc. However, the results aren't displayed in the data table as they would be if I ran the equivalent static SQL statement. Do dynamic queries *have* to be handled using cursors - or is this just TOAD playing games with me?

    Thanks again

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width