-
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
-
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;
/
-
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
-
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
-
Try doing the following in sqlplus and posting the results.
Code:
desc StudentUpdate_t
desc studentupdate_type
desc student
-
Try doing the following in sqlplus and posting the results.
Code:
desc StudentUpdate_t
desc studentupdate_type
desc student
-
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)
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|