Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2013
    Posts
    8

    Unanswered: Getting errors while comipling the cursor

    Hi,

    Currently I am trying to take the output of the query in a cursor.

    Once receving the data. I want to perform the delete operation on the fetched rows.

    For this I have built Package containing :-

    1. Procedure Spefification :-

    CREATE OR REPALCE
    PACKAGE MAIN_1 AS

    PROCEDURE MAIN_2
    (

    CHILD_ID VARCHAR2,
    DMN VARCHAR2,
    PARENT_ID OUT VARCHAR2,
    PARENT_ID OUT VARCHAR2,
    C_CR OUT VARCHAR2

    );



    END MAIN_1;


    --------------------

    2. Procedure Body


    create or replace
    PACKAGE BODY MAIN_1 AS

    PROCEDURE MAIN_2

    (

    CHILD_ID VARCHAR2,
    DMN VARCHAR2,
    PARENT_ID OUT VARCHAR2,
    C_CR OUT VARCHAR2

    )

    IS



    CURSOR C1 IS

    SELECT H.CHILD_ID,CONNECT_BY_ISLEAF AS NO_CHILDREN_EXIST
    FROM MY_EX_TBL H
    START WITH PRNT_ID = CHILD_ID
    CONNECT BY PRNT_ID = CHILD_ID ;

    C_ROW C1%ROWTYPE;


    BEGIN


    IF (
    CHILD_ID IS NULL
    OR
    DMN IS NULL

    )

    THEN

    DBMS_OUTPUT.PUT_LINE ('NO DATA');




    ELSE


    --few lines of code


    END IF;


    OPEN C1;

    LOOP

    FETCH C1 INTO C_ROW;



    EXIT WHEN C1%NOTFOUND;


    FOR C_ROW IN C1

    LOOP

    DELETE FROM MY_EX_TBL WHERE CURRENT OF C1;

    END LOOP;
    END LOOP;

    CLOSE C1;






    END MAIN_2;

    END MAIN_1;



    But when i try to compile it i am getting the following errors:-

    1. Error(): PL/SQL: SQL Statement ignored
    2. Error(): PLS-00404: cursor 'C1' must be declared with FOR UPDATE to use with CURRENT OF
    3. Error(): PL/SQL: ORA-00904: : invalid identifier


    I tried to debug it but not able to resolve it ...

    can anyone would plz. mind to provide a helping hand in this ....plz.

    Thnks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >For this I have built Package containing :-
    >1. Procedure Spefification :-
    >CREATE OR REPALCE

    I don't believe you since you can not correctly spell REPLACE.

    use COPY & PASTE to show what exactly you do & how Oracle responds.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2013
    Posts
    8
    Hi,

    Sincere apologies for making out mistakes while posting the query ....

    My Req. :-

    During the runtime i will be getting the Parent_ID. Based on the Parent_ID, I need to search for the Child_ID's ( Child_ID's which are associated with the Parent_ID i.e all Child records )


    Once the Child records are fetched then I need to delete them from my Main table.


    So, built the below code :-

    HTML Code:
    
    CREATE OR REPLACE
    PACKAGE MAIN_1 AS
     
    PROCEDURE MAIN_2 
    (
     
    CHILD_ID VARCHAR2,
    DMN VARCHAR2,
    PARENT_ID  OUT VARCHAR2,
                             
    );
     
     
     
    END  MAIN_1;   
     
     
     
    create or replace
    PACKAGE BODY MAIN_1 AS 
     
    PROCEDURE MAIN_2 
     
    (
     
    CHILD_ID VARCHAR2,
    DMN VARCHAR2,
    PARENT_ID  OUT VARCHAR2
     
     
    )
     
    IS 
     
          
     
          CURSOR C1 IS
          
                       SELECT H.CHILD_ID,CONNECT_BY_ISLEAF AS NO_CHILDREN_EXIST
                       FROM MY_EX_TBL H
                       START WITH  PRNT_ID = CHILD_ID
                       CONNECT BY PRNT_ID = CHILD_ID ;
     
          C_ROW C1%ROWTYPE;
     
     
    BEGIN
     
     
    IF (
        CHILD_ID IS NULL 
        OR 
        DMN IS NULL
           
        )
     
       THEN 
     
             DBMS_OUTPUT.PUT_LINE ('NO DATA');
     
     
     
            
    ELSE
     
     
                 --few lines of code
     
     
    END IF; 
     
     
           OPEN C1;
           
                    LOOP
           
                         FETCH C1 INTO C_ROW;
                         
                        
           
                         EXIT WHEN  C1%NOTFOUND;
           
           
                                        FOR C_ROW IN C1
          
                                        LOOP
                
                                             DELETE FROM MY_EX_TBL WHERE CURRENT OF C1;
           
                                        END LOOP;
                    END LOOP;
           
           CLOSE C1;
     
     
     
     
     
     
    END MAIN_2;
    

    Errors while compiling the Stored Procedure :-


    1. Error(): PL/SQL: SQL Statement ignored
    2. Error(): PLS-00404: cursor 'C1' must be declared with FOR UPDATE to use with CURRENT OF
    3. Error(): PL/SQL: ORA-00904: : invalid identifier


    I have the following data in my Main table

    HTML Code:
    ------------------------------------------
    CHILD_ID      PARENT_ID
    
    -------------------------------------------
    ASD0l12eds    ASD0l12edn
    ASD0l58ppo    ASD0l16uui
    ASD0l17jji      ASD0l19nnk
    ASD0l12edm   ASD0l12edn
    ASD0l19dds    ASD0l12edn
    ASD0l16tdp    ASD0l16uui
    ASD0l10kko    ASD0l16uui
    
    Expected Result :-



    HTML Code:
    ---------------------------------------
    CHILD_ID      PARENT_ID
    
    ----------------------------------------
    ASD0l12eds      ASD0l12edn
    ASD0l12edm     ASD0l12edn 
    ASD0l19dds      ASD0l12edn 
    ASD0l17jji        ASD0l19nnk
    ASD0l58ppo      ASD0l16uui
    ASD0l16tdp      ASD0l16uui
    ASD0l10kko      ASD0l16uui
    

    WHAT I GOT (ACTUAL RESULT) :-

    HTML Code:
    
    ------------------------------------------------
    CHILD_ID   PARENT_ID   NO_CHILDREN_EXIST
    ------------------------------------------------
    ASD0l17jji   ASD0l19nnk   0
    ASD0l17jji   ASD0l19nnk   0
    ASD0l17jji   ASD0l19nnk   0
    ASD0l17jji   ASD0l19nnk   0
    ASD0l17jji   ASD0l19nnk   0
    ASD0l17jji   ASD0l19nnk   0
    .
    .
    .
    .
    .
    ASD0l17jji ASD0l19nnk 0
    NOTE:-
    NO_CHILDREN_EXIST = 0 WHEN CHILD Record is present
    NO_CHILDREN_EXIST = 1 WHEN CHILD Record is not present





    EXPECTED RESULT :-


    HTML Code:
    ---------------------------------------------
    CHILD_ID   PARENT_ID   NO_CHILDREN_EXIST
    ---------------------------------------------
     ASD0l17jji    ASD0l19nnk   0

    Please correct me if I am wrong somewhere. As i am inexperienced to this PL/SQL world.


    Thnks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This:
    Code:
    cursor 'C1' must be declared with FOR UPDATE to use with CURRENT OF
    sounds quite obvious.

    As of a job you are trying to accomplish (which is deleting ... what exactly? Only child records, or parents as well?), perhaps you could create a foreign key constraint with the ON DELETE CASCADE option, which would make sure that as soon as you delete a parent record, all his child records would be deleted as well. No further programming is necessary.

  5. #5
    Join Date
    Apr 2013
    Posts
    8
    Hi,

    M Req. :-

    During the run time i will be provided an ID i.e. ParentID.

    Now on based of this ID i need to find all the child records associated with this ID.

    Once obtained, then i need to delete all those ChilRecords along with the ParentID.

    Over here i can't create any other column.

    plz. suugest some way to resolve this...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Once again: a foreign key with ON DELETE CASCADE.

  7. #7
    Join Date
    Apr 2013
    Posts
    8
    There is no way i can use
    HTML Code:
    Foreign Key .. ON DELETE CASCADE
    The reason I can't use is bcz. :-

    1. In the current table structure there is no Primary Key and Foreign key relationship established

    2. Both the columns are existing within the same table

    3. Even I can't alter the things in the existing table structure i.e. can't make columns as Primary Key and Foreign Key.


    plz. suggest some other way round .....

    I was trying with Cursor but couldn't do much in it ...

    plz. help ...

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    1. So add them
    2. Really? What is CHILD_ID doing in the parent table?
    3. Why not?

    Other way is PL/SQL code you tried to write, but failed. What do you need a package for? A simple procedure would do. As far as I understood, it should accept one parameter: PARENT_ID. Then, the procedure body would
    1. delete child records
    2. delete parent record
    You don't need a cursor to do that, two DELETE statements are enough.

  9. #9
    Join Date
    Apr 2013
    Posts
    8
    Thanks for ur suggestions .... but there are some business restrictions due to which I am unable to implement your suggestions ....


    The current constraints remains as it is ...

    Yes, during rumtime I will be getting the Parent_ID then ...

    1. Based on this ID (Parent_ID) the Child_Records need to be searched ... here i am need to put iteration as the business req. is to find all the records i.e. Child records from the table

    2. Then i am need to delete all these Child records as well as the Parent_ID

    3. Since, I am not aware that how many child records we have for the corresponding Parent_ID in the table.

    So, need to put iteration logic (loop) for this....

    has anybody got a clue ..in solving this prblm .. ???

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What do you need all child records for? You are deleting them anyway.

    A loop (i.e. row-by-row) is slow-by-slow. As I said, I don't understand what that "business restriction" is. If you want to know number of deleted records, Oracle offers an attribute which *knows* how many records you deleted. Its name is SQL%ROWCOUNT.

    Code:
    SQL> create or replace procedure prc_del_dept (par_deptno in number)
      2  is
      3  begin
      4    delete from emp where deptno = par_deptno;
      5    dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' records from the EMP table');
      6
      7    delete from dept where deptno = par_deptno;
      8  end;
      9  /
    
    Procedure created.
    
    SQL> exec prc_del_dept(20);
    Deleted 5 records from the EMP table
    
    PL/SQL procedure successfully completed.
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •