How to use DB link in a variable in for loop

Question

I have below query which I am trying to write in a procedure after begin clause. I dont want to use it as a cursor because of some dependency.

I want to make my db link dynamic instead of hardcoding it and for this reason i put my entire for loop in variable. If i take the variable out then my procedure is working fine. I dont want to change logic of my code while trying to make dblink dynamic.

But this part of loop is not working and throwing an error as

encounter the symbol end of the file when expecting one of the following:

    PROCEDURE   TMP_CHECK 
IS

open CS for NESS_QUERY;
loop
 fetch CS into REC;
exit when CS%notfound;

 INSERT INTO TMP_Data(ID,NAME,ID_TST,CHK_DATE,VALUE,CHECK,SOURCE) VALUES
 (IN_SEQ_NO,DB_NAME,DB_ID,REC.DAY_ID,REC.nb_ord,'ORDS','LEOSOFT');


COMMIT;
END LOOP;
CLOSE CS;

END LOOP;

END;

Answer 1

Dynamic SQL is hard because it turns compilation errors into runtime errors. It looks like your query has several compilation errors: duplicate table aliases, out-of-scope alias references, cross joins between the remote tables (unless that is deliberate, in which case yuck!). So the first thing to do is get the query running as straight SQL, only then make it dynamic.

Also don't include commented code in your template SQL. Things are already hard enough, why make them even harder by doing stuff like this?

ORDER BY  
-- TE.market asc,  
-- TE.entity asc,  
TE.dayiid ASC)'  

So, now we've got that out of the way let's look at the logic of what you're trying to do. We cannot drop dynamic segments of PL/SQL into a program. This just won't work ...

LQUERY='  
    FOR REC IN(  
    SELECT  

... because you have not written a complete PL/SQL statement. But there is a way to do what you want: use a cursor variable. We can open a ref cursor for static and dynamic queries. Find out more.

The following is for illustrative purposes only: you haven't explained your business logic, so this is not necessarily the best way of doing things. But it should solve your immediate problem:

declare
    ....
    l_order number;
    l_dayiid number;
    l_ety_id number;
    rc sys_refcursor;
begin
    ...
    FOR IIS_DB IN C_DB   
    LOOP  
        IN_DB_LINK:=LEO_DB.DATABASE_LINK;  
        IN_DAY:=LEO_DB.DAY_ID;  
    open rc for
        'SELECT   order,dayiid,ety_id  
        from ...  
        ORDER BY TE.dayiid ASC)';
    loop
        fetch rc into l_order, l_dayiid, l_ety_id;
        exit when rc%notfound;
        ...
    end loop;
    close rc;

---

" PLS-00487: Invalid reference to variable 'REC'"

I think your problem is this:

fetch CS into REC;

You have defined REC as a string but clearly it should be a record type, which needs to match the projection of the query you're fetching. So you need to define something like this:

Type rec_t is record (
     nb_ord number,
     day_id number, 
     entity number
);
REC rec_t;

Now you can fetch a record into REC and reference its attributes.

Incidentally the nvl() you've written to supply NB_ORD is wrong. The first argument is the one you are testing for null: 500 will never be null so that's what you'll get for every row. You need to swap the parameters round.