Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Sql procedure cursor loops

    I have create the following tables and types....

    Code:
    CREATE TYPE ACTOR_QUOTE_TYPE AS OBJECT ( 
    Movie_Title CHAR(36),
    Year NUMBER,
    Role CHAR(36),
    Quote CHAR(255)
    )
    /
    
    CREATE TYPE AQ_NT AS TABLE OF ACTOR_QUOTE_TYPE
    /
    
    CREATE TABLE ACTOR_QUOTES (
    ACTORID CHAR(5),
    QUOTES AQ_NT
    ) NESTED TABLE QUOTES STORE AS ACTOR_QUOTES_NT
    /
    I need to create an.....

    A PL/SQL procedure called INIT_ACTOR_QUOTES with no parameters that:

    Reads ALL the ACTORIDs from the ACTOR table and INSERTs them into the ACTORID attribute for each row the ACTOR_QUOTES table (the tables have the same cardinality) and at the same time INSERTs the following initial values into the first row only of the QUOTES nested table into each row of the ACTOR_QUOTES table;

    (Movie_Title, Year, Role, Quote) are set respectively to (' ',NULL ,' ', ' ')

    Also and at the same time immediately after each INSERT use DELETE to delete ALL the rows from the nested table in each row belonging to each ACTORID in the ACTOR_QUOTES table.

    I'm getting a compilation error with the code....

    Code:
    CREATE OR REPLACE PROCEDURE INIT_ACTOR_QUOTES 
    AS
    CURSOR actorID_cursor IS
    SELECT actorID FROM Actor;
    BEGIN 
    FOR row IN actorID_cursor LOOP
    INSERT INTO ACTOR_QUOTES (ACTORID) VALUES (actorID);
    INSERT INTO actor_Quotes_NT VALUES ('', NULL, '', '');
    DELETE FROM actor_Quotes_NT WHERE ACTORID=actorID;
    END LOOP; 
    END INIT_ACTOR_QUOTES ;
    / 
    Warning: Procedure created with compilation errors.
    Code:
    SQL> show errors;
    Errors for PROCEDURE INIT_ACTOR_QUOTES:
    
    LINE/COL ERROR
    -----------------------------------------------------------------
    7/1 PL/SQL: SQL Statement ignored 
    7/44 PL/SQL: ORA-00984: column not allowed here
    8/1 PL/SQL: SQL Statement ignored
    8/13 PL/SQL: ORA-22812: cannot reference nested table column's storage table
    9/1 PL/SQL: PL/SQL: SQL Statement ignored
    7/9 PL/SQL: ORA-00925: missing INTO keyword
    9/13 PL/SQL: ORA-22812: cannot reference nested table column's storage table
    SQL>
    Any help would be appreciated

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by swfc4lyf View Post
    DELETE FROM actor_Quotes_NT WHERE ACTORID=actorID;
    What do you think this will do? If you can't tell right away, remember that SQL is not case-sensitive.

    It's not a good idea to create PL/SQL variables with the same names as database objects.

Tags for this Thread

Posting Permissions

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