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.
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;
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?
* 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?
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.
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.
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;
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. That's what I was trying to achieve, just didn't know how. Thanks again.
ASKER
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?
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;
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
Do be careful with string concatenation that you're not opening yourself up to sql injection
If so, what is "not working" about it?
error? no results? wrong results? keyboard catches fire?