1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to fetch single value of a cursor in the for loop in oracle

Discussion in 'SQL PL/SQL' started by Ram mady, Jul 9, 2016.

  1. Ram mady

    Ram mady Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    germany
    Hi All,

    I've a cursor c_incid and I want to fetch single row from cursor c_incid in the FOR LOOP ....

    I tried to fetch single value from cusrosr c_incid in the FOR LOOP but I'm getting below error :: '



    • Error(93,14): PLS-00225: subprogram or cursor 'C_INCID' reference is out of scope




    (i)##############

    My cursor is ::

    CURSOR c_incid IS

    SELECT inc.incident_id, inc.original_id, inc.incidents_bound anz_reportit, rel.anz_i2i anz_psc,rel.anz_i2i-inc.incidents_bound psc_minus_reportit

    FROM

    ( SELECT DISTINCT original_id, sum_inc2inc anz_i2i

    FROM(

    SELECT src original_id,

    dpd,

    SUM(SUM(is_inc2inc)) over (PARTITION BY src) sum_inc2inc

    FROM(

    SELECT source src,

    depend dpd,

    source_filename,

    depend_filename,

    CASE WHEN depend_filename='problem' AND source_filename='problem' THEN 1 ELSE 0 END is_inc2inc

    FROM screlationm1@psc6

    ) GROUP BY src, dpd

    )

    WHERE sum_inc2inc > 0

    ) rel,

    smrdwh.tbincid inc

    WHERE rel.original_id = inc.original_id AND rel.anz_i2i <> inc.incidents_bound;





    (ii)##############

    and I'm fetching single vaclue fron cursor c_incid like below::







    FOR c_loop IN count_incid2incid
    LOOP
    IF c_incid.psc_minus_reportit>=1 THEN
    UPDATE smrdwh.tbincid
    SET incidents_bound = c_incid.anz_psc
    WHERE smrdwh.temp_tbincid.incident_id=c_incid.incident_id;

    -- INSERT INTO smrdwh.tbincid2incid VALUES IN (SEQ_INCID.NEXTVAL,c_loop.incident_id);
    MERGE INTO smrdwh.tbincid2incid dest
    USING v_incid2incid val
    ON (dest.owner_incid_id = val.src_incident_id)
    WHEN NOT matched THEN INSERT
    ( owner_incid_id, incident_id)
    VALUES (val.src_incident_id, val.dpd_incident_id);

    CURSOR c_incid2incid is
    CURSOR c_count_diff IS
    SELECT rel.anz_i2i-inc.incidents_bound psc_minus_reportit
    FROM
    ( SELECT DISTINCT original_id, sum_inc2inc anz_i2i
    FROM(
    SELECT src original_id,
    dpd,
    SUM(SUM(is_inc2inc)) over (PARTITION BY src) sum_inc2inc
    FROM(
    SELECT source src,
    depend dpd,
    source_filename,
    depend_filename,
    CASE WHEN depend_filename='problem' AND source_filename='problem' THEN 1 ELSE 0 END is_inc2inc
    FROM screlationm1@psc6
    ) GROUP BY src, dpd
    )
    WHERE sum_inc2inc > 0
    ) rel,
    smrdwh.tbincid inc
    WHERE rel.original_id = inc.original_id AND rel.anz_i2i <> inc.incidents_bound;


    Please suggest and guide.




    Thanks & Regards

    Ram
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Don't use row-by-row from of cursor processing, it is "slow by slow".

    Can you provide the complete text of your unit of a code?
    Declaration of cursors shall is in declaration section.
     
    Ram mady likes this.
  3. Ram mady

    Ram mady Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    germany
    Hi krasnoslobodtsev_si
    Greeting of the day and thanks for your reply.
    The basic pupose of my code is to create a procedure that compare two tables (screlationm1@psc6--remote table) and tbincid(local/target table) and check if there is any discrepency in the number of rows between two tables (after compare delete or insert extra value from end table tbincid2incid)
    incidents_bound (anz_reportit-alia) is the column in table tbincid and it indicates the no of rows available in the table tbincid.

    I want to check if there is any

    (i) Extra row in the table tbincid as compare to screlationm1@psc6 then first it will update the column incidents_bound in the table tbincid and then it will delete extra value from table tbincid2incid.
    OR
    (ii) Less rows in the table tbincid as compare to screlationm1@psc6 then insert rows in final table tbincid2incid.

    Data flow from screlationm1--to--tbincid---to---tbincid2incid
    There is no direct connection between table tbincid2incid and screlationm1 and hence we are first comparing rows between tbincid and screlationm1 and then performing operation on final table TBINCID2INCID.



    Below is my complete procedure code ::

    ------------------------------------START OF THE CODE-------------------------------------------------------
    ------------------------------------START OF THE CODE-------------------------------------------------------
    CREATE OR REPLACE PROCEDURE Rel_Sync_tbincid2incid(p_itil_type IN varchar2,p_i_since IN DATE,p_i_till IN DATE) IS
    v_count_screl NUMBER;
    v_count_incd NUMBER;
    c_log_table_name CONSTANT VARCHAR2(100) := 'SMRDWH.TBLOGS';


    TYPE owner_incid_id_tab IS TABLE OF
    smrdwh.tbincid2incid.owner_incid_id%TYPE;
    TYPE incident_id_tab IS TABLE OF
    smrdwh.tbincid2incid.incident_id%TYPE;
    t_owner_incid_id owner_incid_id_tab;
    t_incident_id incident_id_tab;

    CURSOR c1 IS SELECT owner_incid_id, incident_id
    FROM smrdwh.tbincid2incid;
    CURSOR c_incid IS
    SELECT inc.incident_id, inc.original_id, inc.incidents_bound anz_reportit, rel.anz_i2i anz_psc,rel.anz_i2i-inc.incidents_bound psc_minus_reportit
    FROM
    ( SELECT DISTINCT original_id, sum_inc2inc anz_i2i
    FROM(
    SELECT src original_id,
    dpd,
    SUM(SUM(is_inc2inc)) over (PARTITION BY src) sum_inc2inc
    FROM(
    SELECT source src,
    depend dpd,
    source_filename,
    depend_filename,
    CASE WHEN depend_filename='problem' AND source_filename='problem' THEN 1 ELSE 0 END is_inc2inc
    FROM screlationm1@psc6
    ) GROUP BY src, dpd
    )
    WHERE sum_inc2inc > 0
    ) rel,
    smrdwh.tbincid inc
    WHERE rel.original_id = inc.original_id AND rel.anz_i2i <> inc.incidents_bound;

    CURSOR c_incid2incid IS
    SELECT tmp_rel.src, src_incid.incident_id src_incident_id,
    tmp_rel.dpd, dpd_incid.incident_id dpd_incident_id
    FROM SMRDWH.tmp_relation_check tmp_rel,
    smrdwh.tbincid src_incid,
    smrdwh.tbincid dpd_incid
    WHERE tmp_rel.sum_inc2inc=1 and substr(tmp_rel.src,1,2)='IM'
    AND substr(tmp_rel.dpd,1,2)='IM'
    and tmp_rel.src = src_incid.original_id
    and tmp_rel.dpd = dpd_incid.original_id;

    SELECT rel.anz_i2i-inc.incidents_bound psc_minus_reportit
    FROM
    ( SELECT DISTINCT original_id, sum_inc2inc anz_i2i
    FROM(
    SELECT src original_id,
    dpd,
    SUM(SUM(is_inc2inc)) over (PARTITION BY src) sum_inc2inc
    FROM(
    SELECT source src,
    depend dpd,
    source_filename,
    depend_filename,
    CASE WHEN depend_filename='problem' AND source_filename='problem' THEN 1 ELSE 0 END is_inc2inc
    FROM screlationm1@psc6
    ) GROUP BY src, dpd
    )
    WHERE sum_inc2inc > 0
    ) rel,
    smrdwh.tbincid inc
    WHERE rel.original_id = inc.original_id AND rel.anz_i2i <> inc.incidents_bound;
    CURSOR c_tbincid2incid IS SELECT owner_incid_id,incident_id FROM smrdwh.tbincid2incid;

    BEGIN
    ---------/*START --TRUNCATE AND INSERT INTO TBINCID2INCID*/
    EXECUTE IMMEDIATE 'TRUNCATE TABLE smrdwh.temp_tbincid2incid';
    ----------/*START*/-----*/INSERT VALUES INTO TEMP RELATIONSHIP TABLES */--------------

    OPEN c1;
    FETCH c1 BULK COLLECT INTO t_owner_incid_id, t_incident_id;
    CLOSE c1;
    -- bulk insert
    FORALL indx IN t_owner_incid_id.FIRST..t_owner_incid_id.LAST
    INSERT INTO rmaddheshiya.temp_tbincid2incid VALUES
    ( t_owner_incid_id(indx), t_owner_incid_id(indx));

    ----------/*END*/-----*/INSERT VALUES INTO TEMP RELATIONSHIP TABLES */--------------

    -----/*TBINCID2INCID(INCIDENT_BOUND) */START---------Recalculate Bound columns and Insert missing values from Source to relation tables-----------------



    FOR c_loop IN c_incid2incid
    LOOP
    IF c_incid.psc_minus_reportit >=1 THEN
    UPDATE smrdwh.tbincid
    SET incidents_bound = c_incid.anz_psc
    WHERE smrdwh.temp_tbincid.incident_id=c_incid.incident_id;

    -- INSERT INTO smrdwh.tbincid2incid VALUES IN (SEQ_INCID.NEXTVAL,c_loop.incident_id);
    MERGE INTO smrdwh.tbincid2incid dest
    USING v_incid2incid val
    ON (dest.owner_incid_id = val.src_incident_id)
    WHEN NOT matched THEN INSERT
    ( owner_incid_id, incident_id)
    VALUES (val.src_incident_id, val.dpd_incident_id);


    ELSIF c_count_diff.psc_minus_reportit <=-1 THEN
    UPDATE smrdwh.tbincid
    SET indcident_bound = c_incid.anz_psc
    WHERE temp_tbincid.incident_id=c_incid.incident_id;

    MERGE INTO smrdwh.tbincid2incid dest
    USING v_incid2incid val
    ON (dest.owner_incid_id = val.src_incident_id)
    WHEN matched THEN
    UPDATE SET dest.incident_id=val.dpd_incident_id
    DELETE WHERE dest.incident_id=val.dpd_incident_id;

    --DELETE FROM smrdwh.tbincid2incid a where c_loop.incident_id=a.incident_id;

    ELSE
    SMRDWH.TOOLS.OUTPUT_LOG ( 'some message...');
    END IF;
    END LOOP;
    COMMIT;


    EXCEPTION
    WHEN OTHERS THEN
    SMRDWH.TOOLS.OUTPUT_LOG ( 'some message...');
    END Rel_Sync_tbincid2incid;


    ------------------------------------END OF THE CODE-------------------------------------------------------
    ------------------------------------END OF THE CODE-------------------------------------------------------


    Thanks & Regards
    Ram