Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

Oracle - Query link database loop

I have multiple databases that is the same structure/schema.  I can query the link databases, but I would like to create a loop so that if I type in the database from a list table, it would query one or more database to get records I need for that database.

I was thinking of the following and I know it's not working.  However, I need help with something similar.

 
BEGIN

DECLARE CURSOR C1 IS

SELECT DB_ID FROM DB_LIST WHERE DB_ID IN ('DB1', 'DB2');

BEGIN
  FOR I IN C1
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT ID, DESCRIPTION FROM MATL_MASTER@'||I.DB_ID||' WHERE ID LIKE ''EE%''';
    END;
  END LOOP
END;

END;

Open in new window

Avatar of Sean Stuber
Sean Stuber

are DB1 and DB2  valid database links?

If so,  what is "not working" about it?

error? no results? wrong results? keyboard catches fire?
I see 2 approaches here:

* create a view, by code, that does the UNION ALL as needed from all the db links
* create synonyms for the table, one per dblink, and you could then union all from all the synonmys
* create a function, pipelined, to return the data with the query done dynamically over each db link.


the third approach has several advantages, from my point of view:
* can be tolerant for db links being down, and still provide the data from the other db links.
* can take data from additional db links without delay, once the entry in the table is there.

do you need help with those approaches?
Avatar of holemania

ASKER

Sorry, I should've been a lot more clearer.  It's my SYS_REFCURSOR since I am turning this into a stored procedure and returning my result.

CREATE OR REPLACE SP_MATL_INFO(P_RESULTS OUT SYS_REFCURSOR)
AS
BEGIN
DECLARE CURSOR C1 IS

SELECT DB_ID FROM DB_LIST WHERE DB_ID IN ('DB1', 'DB2');

BEGIN
  FOR I IN C1
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'OPEN P_RESULTS FOR SELECT ID, DESCRIPTION FROM MATL_MASTER@'||I.DB_ID||' WHERE ID LIKE ''EE%''';
    END;
  END LOOP
END;

END;

Open in new window



I can compile the SP, but when I try to execute I am getting error:

Filed to retrieve data from the database.  ORA-00900:Invalid SQL STatemtn at line 17.

Guy with your function/pipeline suggestion, I had found your article on that and using it with the "SELECT DB_ID FROM DB_LIST WHERE DB_ID IN ('DB1', 'DB2');".  I just didn't incorporate it here because I am testing to make sure it works before adding in your solution.
That doesn't make sense.

A sys_refcursor returns "A" cursor, i.e. a single select.

If you are looping, then you are, obviously, going to be opening more than one query.

A pipeline function could, sort of, do what you are asking by concatenating the results of each query but that changes the result structure/type and the usage for the calling routines.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you.  That's what I was trying to achieve, just didn't know how.  Thanks again.
sdstuber,

If i want to add in a parameter for the  where clause with "WHERE ID LIKE ''EE%'''", how would I incorporate that?  Would it be like below?

CREATE OR REPLACE PROCEDURE sp_matl_info(p_matl_id in varchar2, p_results OUT SYS_REFCURSOR)
AS
    v_sql   VARCHAR2(32767);
BEGIN
    FOR d IN (SELECT db_id
                FROM db_list
               WHERE db_id IN ('DB1', 'DB2'))
    LOOP
        IF v_sql IS NOT NULL
        THEN
            v_sql := v_sql || ' UNION ALL ';
        END IF;

        v_sql :=
               v_sql
            || 'SELECT ID, DESCRIPTION FROM MATL_MASTER@'
            || d.db_id
            || ' WHERE ID LIKE '||p_matl_id||' ''%''';
    END LOOP;

    OPEN p_results, p_matl_id FOR v_sql;
END;

Open in new window

That really should be a new question, but in general it's easiest to generate and show your sql before opening it, that way you can see what the sql syntax is and then adjust the construction accordingly.

Do be careful with string concatenation that you're not opening yourself up to sql injection