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;

It's hard to tell from that fragment, and it's not helped by having things like = for assignment instead of :=, and a missing semicolon, which would throw different errors. An MCVE would be helpful, along with the full error stack. You can open a cursor loop from a variable but not quite how you've shown it, but again unclear if your real code does that. — Alex Poole
– Alex Poole, Commented Dec 8, 2017 at 11:47
actually i can also separate select query from this for loop and want to use it inside the for loop but its generating duplicate data — Marcus
– Marcus, Commented Dec 8, 2017 at 12:48

APC · Accepted Answer · 2017-12-13 05:23:40Z

3

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.

edited Dec 13, 2017 at 5:23

answered Dec 9, 2017 at 11:00

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marcus Over a year ago

thanks for your reply. Just one question here. Can i use referance variable after exit statement to insert the data into tables and how can i use it ? For.eg INSERT into TEST_VAL(order,dayiid,ety_id) values(rec.l_order, ref.dayiid,rc.ety_id);

Marcus Over a year ago

thanks i have updated my question and mention complete store procedure.I have used your logic but getting error in the line where i have used INSERT statement after exit statement as Error(108,58): PLS-00487: Invalid reference to variable 'REC', Error(108,62): PL/SQL: ORA-00984: column not allowed here

Marcus Over a year ago

did you find the information which i have mentioned in question ? Thanks.

Marcus Over a year ago

actually you have mentioned in your first comment that i have not provided enough detail and my complete procedure so i have edited my entire procedure in question and now getting error as Error(108,58): PLS-00487: Invalid reference to variable 'REC', Error(108,62): PL/SQL: ORA-00984: column not allowed here

Collectives™ on Stack Overflow

How to use DB link in a variable in for loop

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related