CODEWITHSUNDEEP
oracle-databaseplsql
Naresh Aligeti
Naresh Aligeti

Reputation: 45

parameterized cursors in PL/SQL

I have the following errors when I run the code below in sql developer. I can't find what the problem is with this code. Concept: The sql inside cursor Base1 returs around 100 rows. I would like to use each of these rows as an input to the sql inside target cusor, which further returns several rows. The errors i get are :

Encountered the symbol "(" when expecting one of the following: . into bulk

Encountered the symbol ";" when expecting one of the following: . ( , % from

Encountered the symbol "CLOSE" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map

    set serveroutput ON

Declare
    Type Beg_Ser_Tab1 Is Table Of DUMMY%Rowtype Index By Pls_Integer;
    Type Beg_Ser_Tab2 Is Table Of DUMMY%Rowtype INDEX BY PLS_INTEGER;
    L_Beg_Ser_Tab Beg_Ser_Tab1;
    L_Beg_Ser_Tab_Fin Beg_Ser_Tab2;

    result number;

    CURSOR  Base1 IS 

        select * from DUMMY c1
        where status=976 and for_class_loc_project=1
        and not exists 
            (select * from DUMMY c2 
            where c2.status=976 and c2.for_class_loc_project=1
            and c2.end_series=c1.COLOUMN_X and c2.end_station=c1.beg_station)
        and not exists 
            (select * from mv_station_Series t 
            where t.status in (SELECT ID FROM list_domain 
            WHERE LOWER (domainvalue) IN ('active', 'preliminary as-built', 'idle', 'construction'))
            and c1.COLOUMN_X=t.id and c1.beg_station=t.beg_station 
            )     
        order by c1.id;



    CURSOR  target(v_id NUMBER) IS

        Select *  
            From DUMMY Where COLOUMN_X in (
            Select ID From Station_Series Where 
            Status = 976
            And Discharge_Subsys = (Select Discharge_Subsys From Station_Series Where Id = v_id ) 
            And Line_Loop = (Select Line_Loop From Station_Series Where Id = v_id)) And Status In
            (Select Id From List_Domain 
            Where Lower (Domainvalue) In ('active', 'preliminary as-built', 'idle', 'construction'))
            Order By Beg_Station Asc;

BEGIN
    OPEN Base1;
    FETCH Base1 BULK COLLECT INTO l_beg_ser_tab1;
    EXIT WHEN l_beg_ser_tab1.count = 0;
    FOR index1 IN 1..l_beg_ser_tab1.count
        LOOP
        Dbms_Output.Put_Line('For Beg Series '|| L_Beg_Ser_Tab1(Index1));
        Open Target(L_Beg_Ser_Tab1.COLOUMN_X);
        FETCH target(l_beg_ser_tab1.COLOUMN_X) BULK COLLECT INTO l_beg_ser_tab_fin;
        FOR index2 IN 1..l_beg_ser_tab_fin.count
            LOOP
            DBMS_OUTPUT.PUT_LINE('              '||l_beg_ser_tab_fin(index2));
            END LOOP;
        CLOSE target;

        DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');
        END LOOP;
    CLOSE Base1;
END

Upvotes: 0

Views: 490

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

I would think that once you have:

Open Target(L_Beg_Ser_Tab1.COLOUMN_X);

You would not need to specify the parameter again in:

FETCH target(l_beg_ser_tab1.COLOUMN_X) B

Also, implicit cursors are generally faster, and easier to code. Why not use them? In fact, why not use a single implicit cursor for the entire logic?

Upvotes: 2

Related Questions