DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Pls-00302 error :(

  1. #1
    Join Date
    Jan 2015
    Posts
    5

    Unhappy Pls-00302 error :(

    I know there are lot of resources online that talk about this error, but not even a single one helped me fix my error :(... Below is my story. I created a object StudentUpdate_t, a table with the objects StudentUpdate_Type and trying to load the values from the table into a cursor and return a cursor as an output parameter in procedure

    create or replace
    type StudentUpdate_t as object
    (
    "studentid" NUMBER(10),
    "firstname" VARCHAR2(40 BYTE),
    "lastname" VARCHAR2(40 BYTE)
    );

    create or replace
    TYPE StudentUpdate_Type as TABLE OF StudentUpdate_t;


    create or replace
    PROCEDURE BULK_PROCEDURE_TEST (p_schoolid NUMBER, student_updated_rec OUT SYS_REFCURSOR) IS

    StudentUpdate_tab StudentUpdate_Type;

    Cursor cur is SELECT StudentUpdate_t(studentid, firstname, lastname) FROM STUDENT where rownum<10;

    BEGIN
    OPEN cur;
    LOOP
    FETCH cur
    BULK COLLECT INTO StudentUpdate_tab;
    EXIT WHEN cur%NOTFOUND;
    FOR i in 1..StudentUpdate_tab.count
    LOOP
    DBMS_OUTPUT.PUT_LINE('no issue till here' || StudentUpdate_tab(i).firstname);
    END LOOP;
    END LOOP;
    CLOSE cur;

    OPEN student_updated_rec
    for select * from TABLE (StudentUpdate_tab) ;
    DBMS_OUTPUT.PUT_LINE('End of the process : '|| TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS'));
    END;


    My Error: Error(16,70): PLS-00302: component 'FIRSTNAME' must be declared

    I am in the same schema as my type and table are created

    Please help

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Code tags would have helped. You need to click on use advanced editor.
    I made some changes. you need the / for each type. I haven't used refcursors
    in a while. So I am not sure about that. But it should work.

    Code:
    CREATE OR REPLACE
    TYPE StudentUpdate_t AS OBJECT (
       studentid  NUMBER(10), 
       firstname  VARCHAR2(40), 
       lastname   VARCHAR2(40) 
    );
    /
    
    CREATE OR REPLACE TYPE studentupdate_type AS TABLE OF studentupdate_t;
    /
    
    CREATE OR REPLACE PROCEDURE bulk_procedure_test
         ( p_schoolid          IN  NUMBER, 
           student_updated_rec OUT SYS_REFCURSOR )
    IS
       studentupdate_tab studentupdate_type;
    
       CURSOR stundent_cur
           IS SELECT studentid, firstname, lastname
                FROM student;
    BEGIN
       OPEN stundent_cur;
       LOOP
          FETCH stundent_cur 
           BULK COLLECT INTO studentupdate_tab LIMIT 10;
    
          EXIT WHEN stundent_cur%NOTFOUND;
    
          FOR i IN 1 .. studentupdate_tab.COUNT
          LOOP
             DBMS_OUTPUT.put_line( 'no issue till here' ||
                                  studentupdate_tab( i ).firstname );
          END LOOP;
       END LOOP;
       CLOSE stundent_cur;
    
       OPEN student_updated_rec 
        FOR SELECT * FROM TABLE( studentupdate_tab );
    
       DBMS_OUTPUT.put_line( 'End of the process : ' ||
                              TO_CHAR( SYSDATE, 'MM/DD/YYYY HH:MI:SS' ) );
    END;
    /

  3. #3
    Join Date
    Jan 2015
    Posts
    5
    Thanks for formatting it...

    My issue is not with the SYS_REFCURSOR, i have issue accessing the table column in the for loop: studentupdate_tab( i ).firstname which throws an error:Error(16,70): PLS-00302: component 'FIRSTNAME' must be declared

  4. #4
    Join Date
    Jan 2015
    Posts
    5
    Thanks for formatting it...

    My issue is not with the SYS_REFCURSOR, i have issue accessing the table column in the for loop: studentupdate_tab( i ).firstname which throws an error:Error(16,70): PLS-00302: component 'FIRSTNAME' must be declared

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Try doing the following in sqlplus and posting the results.

    Code:
    desc StudentUpdate_t
    desc studentupdate_type
    desc student

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Try doing the following in sqlplus and posting the results.

    Code:
    desc StudentUpdate_t
    desc studentupdate_type
    desc student

  7. #7
    Join Date
    Jan 2015
    Posts
    5
    desc StudentUpdate_t
    user type definition
    --------------------------------
    type StudentUpdate_t as object
    (
    "studentid" NUMBER(10),
    "firstname" VARCHAR2(40 BYTE),
    "lastname" VARCHAR2(40 BYTE)
    );

    desc studentupdate_type
    user type definition
    ----------------------------------------------------
    TYPE StudentUpdate_Type as TABLE OF StudentUpdate_t;

    desc student
    Name Null Type
    -------------------- -------- -----------------
    STUDENTID NOT NULL NUMBER
    LASTNAME NOT NULL VARCHAR2(40)
    FIRSTNAME NOT NULL VARCHAR2(40)

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I rewrote the procedure and it works. But I am not sure what you are really
    trying to do and why you need to create the type objects. This looks like
    homework to me. If you are learning PL/SQL read Steven Feuerstein's PL/SQL Best Practices.


    Code:
    SQL > CREATE TABLE student (
      2     STUDENTID  NUMBER,
      3     LASTNAME   VARCHAR2(40),
      4     FIRSTNAME  VARCHAR2(40),
      5     CONSTRAINT student_pkey
      6     PRIMARY KEY ( STUDENTID ));
    
    Table created.
    
    Elapsed: 00:00:00.01
    SQL >
    SQL > CREATE UNIQUE INDEX student_idx1
      2      ON student ( LASTNAME, FIRSTNAME );
    
    Index created.
    
    Elapsed: 00:00:00.01
    SQL >
    SQL > CREATE SEQUENCE student_seq;
    
    Sequence created.
    
    Elapsed: 00:00:00.00
    SQL >
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'ADAM');
    
    1 row created.
    
    Elapsed: 00:00:00.01
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'JOHN');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'SAMUEL');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'TIM');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL >
    SQL > COMMIT;
    
    Commit complete.
    
    Elapsed: 00:00:00.00
    SQL >
    SQL > CREATE OR REPLACE TYPE StudentUpdate_t AS OBJECT (
      2     studentid  NUMBER(10),
      3     firstname  VARCHAR2(40),
      4     lastname   VARCHAR2(40)
      5  );
      6  /
    
    Type created.
    
    Elapsed: 00:00:00.02
    SQL >
    SQL > CREATE OR REPLACE TYPE studentupdate_type AS TABLE OF studentupdate_t;
      2  /
    
    Type created.
    
    Elapsed: 00:00:00.03
    SQL >
    SQL > CREATE OR REPLACE PROCEDURE bulk_procedure_test
      2       ( p_schoolid          IN  NUMBER,
      3         student_updated_rec OUT SYS_REFCURSOR )
      4  IS
      5
      6     TYPE studentupdate_tab_typ IS TABLE OF student%ROWTYPE
      7            INDEX BY PLS_INTEGER;
      8
      9     studentupdate_tab studentupdate_tab_typ;
     10
     11  BEGIN
     12     SELECT studentid, firstname, lastname
     13       BULK COLLECT INTO studentupdate_tab
     14       FROM student;
     15
     16     FOR i IN 1 .. studentupdate_tab.COUNT
     17     LOOP
     18        DBMS_OUTPUT.put_line( 'no issue till here' ||
     19                             studentupdate_tab( i ).firstname );
     20     END LOOP;
     21
     22     OPEN student_updated_rec
     23      FOR SELECT * FROM student;
     24
     25     DBMS_OUTPUT.put_line( 'End of the process : ' ||
     26                            TO_CHAR( SYSDATE, 'MM/DD/YYYY HH:MI:SS' ) );
     27  END;
     28  /
    
    Procedure created.
    
    Elapsed: 00:00:00.12

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I rewrote the procedure and it works. But I am not sure what you are really
    trying to do and why you need to create the type objects. This looks like
    homework to me. If you are learning PL/SQL read Steven Feuerstein's PL/SQL Best Practices.


    Code:
    SQL > CREATE TABLE student (
      2     STUDENTID  NUMBER,
      3     LASTNAME   VARCHAR2(40),
      4     FIRSTNAME  VARCHAR2(40),
      5     CONSTRAINT student_pkey
      6     PRIMARY KEY ( STUDENTID ));
    
    Table created.
    
    Elapsed: 00:00:00.01
    SQL >
    SQL > CREATE UNIQUE INDEX student_idx1
      2      ON student ( LASTNAME, FIRSTNAME );
    
    Index created.
    
    Elapsed: 00:00:00.01
    SQL >
    SQL > CREATE SEQUENCE student_seq;
    
    Sequence created.
    
    Elapsed: 00:00:00.00
    SQL >
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'ADAM');
    
    1 row created.
    
    Elapsed: 00:00:00.01
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'JOHN');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'SAMUEL');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL > INSERT INTO student VALUES ( student_seq.NEXTVAL, 'SMITH', 'TIM');
    
    1 row created.
    
    Elapsed: 00:00:00.00
    SQL >
    SQL > COMMIT;
    
    Commit complete.
    
    Elapsed: 00:00:00.00
    SQL >
    SQL > CREATE OR REPLACE TYPE StudentUpdate_t AS OBJECT (
      2     studentid  NUMBER(10),
      3     firstname  VARCHAR2(40),
      4     lastname   VARCHAR2(40)
      5  );
      6  /
    
    Type created.
    
    Elapsed: 00:00:00.02
    SQL >
    SQL > CREATE OR REPLACE TYPE studentupdate_type AS TABLE OF studentupdate_t;
      2  /
    
    Type created.
    
    Elapsed: 00:00:00.03
    SQL >
    SQL > CREATE OR REPLACE PROCEDURE bulk_procedure_test
      2       ( p_schoolid          IN  NUMBER,
      3         student_updated_rec OUT SYS_REFCURSOR )
      4  IS
      5
      6     TYPE studentupdate_tab_typ IS TABLE OF student%ROWTYPE
      7            INDEX BY PLS_INTEGER;
      8
      9     studentupdate_tab studentupdate_tab_typ;
     10
     11  BEGIN
     12     SELECT studentid, firstname, lastname
     13       BULK COLLECT INTO studentupdate_tab
     14       FROM student;
     15
     16     FOR i IN 1 .. studentupdate_tab.COUNT
     17     LOOP
     18        DBMS_OUTPUT.put_line( 'no issue till here' ||
     19                             studentupdate_tab( i ).firstname );
     20     END LOOP;
     21
     22     OPEN student_updated_rec
     23      FOR SELECT * FROM student;
     24
     25     DBMS_OUTPUT.put_line( 'End of the process : ' ||
     26                            TO_CHAR( SYSDATE, 'MM/DD/YYYY HH:MI:SS' ) );
     27  END;
     28  /
    
    Procedure created.
    
    Elapsed: 00:00:00.12

  10. #10
    Join Date
    Jan 2015
    Posts
    5
    What I sent you was only a SNIPPET of the code, simplied to an extent that the focus is just on the error. Student table was just an example, my query is lot more complicated joining 5 tables and getting data from different tables, so i cannot do a rowtype TYPE studentupdate_tab_typ IS TABLE OF student%ROWTYPE INDEX BY PLS_INTEGER; I need to use a type so that i can define the columns as i wanted and then insert the data. Is there a way to do it other than using a type? Please advice

Posting Permissions

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


Click Here to Expand Forum to Full Width