PLS-00597: PLSQL Record and Nested [message #633145] |
Wed, 11 February 2015 10:03 |
|
ninan
Messages: 163 Registered: June 2011 Location: Noida
|
Senior Member |
|
|
Getting error PLS-00597 expression ' ' in the INTO list is of wrong type with the following PLSQL Block:-
The requirement is to populate the data from the source table to a target table, We are using BULK COLLECT. While populating we want to generate a sequence RANDOM ID and store in the Target table which will be used to populate another table in the Target Database for analysing.
What is that am doing wrong here. We cannot store this Random ID in the source table. So need to including this in the processing PLSQL block.
How can we include additional fields in the the PLSQL record, in combination with %ROWTYPE.?
------------------------------------------------------------------------------------------------------------
/* Formatted on 2/11/2015 9:23:18 PM (QP5 v5.240.12305.39476) */
DECLARE
TYPE rate_t IS TABLE OF TEST_TABLE%ROWTYPE
INDEX BY PLS_INTEGER;
l_test rate_t;
TYPE r IS RECORD
(
x rate_t,
y VARCHAR2 (100)
);
TYPE rt IS TABLE OF r
INDEX BY PLS_INTEGER;
d rt;
CURSOR test_cur
IS
SELECT a.*, RANDOMUUID rndid
FROM TEST_TABLE a;
BEGIN
OPEN test_cur;
LOOP
FETCH test_cur
BULK COLLECT INTO d
LIMIT 100;
FORALL indx IN 1 .. d.COUNT SAVE EXCEPTIONS
INSERT INTO test_ids (d (indx).rate_id, d(indx).rndid);
END LOOP;
CLOSE test_cur;
end;
|
|
|
|
|
|
|
|
Re: PLS-00597: PLSQL Record and Nested [message #633163 is a reply to message #633157] |
Wed, 11 February 2015 18:21 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
DROP TABLE TEST_TABLE PURGE
/
DROP TABLE TEST_IDS PURGE
/
CREATE TABLE TEST_TABLE(
rate_id number,
rate_name varchar2(10)
)
/
CREATE TABLE TEST_IDS(
rate_id number,
rndid VARCHAR2 (100)
)
/
DECLARE
TYPE r IS RECORD(
rate_id number,
rate_name varchar2(10),
y VARCHAR2 (100)
);
TYPE rt IS TABLE OF r
INDEX BY PLS_INTEGER;
d rt;
CURSOR test_cur
IS
SELECT a.*,
'RANDOMUUID' rndid
FROM TEST_TABLE a;
BEGIN
OPEN test_cur;
LOOP
FETCH test_cur
BULK COLLECT INTO d
LIMIT 100;
FORALL indx IN 1 .. d.COUNT SAVE EXCEPTIONS
INSERT INTO test_ids values(d(indx).rate_id,d(indx).y);
EXIT WHEN d.COUNT < 100;
END LOOP;
CLOSE test_cur;
END;
/
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
|
Re: PLS-00597: PLSQL Record and Nested [message #633187 is a reply to message #633173] |
Thu, 12 February 2015 02:15 |
|
sss111ind
Messages: 634 Registered: April 2012 Location: India
|
Senior Member |
|
|
This might help you.
DECLARE
CURSOR c1 IS
SELECT ROWNUM srno, emp.* FROM emp; --declare a cursor with extra fields (srno)
TYPE recrow_tab
IS TABLE OF c1%ROWTYPE; --includes all 9 fields with srno as well
t_recrow_tab RECROW_TAB;
BEGIN
NULL;
OPEN c1;
LOOP
FETCH c1 bulk collect INTO t_recrow_tab limit 5;
exit WHEN t_recrow_tab.count = 0;
FOR i IN t_recrow_tab.first .. t_recrow_tab.last LOOP
dbms_output.Put_line(T_recrow_tab(i).srno ||' ' ||T_recrow_tab(i).ename);
END LOOP;
END LOOP;
CLOSE c1;
END;
[Updated on: Thu, 12 February 2015 02:16] Report message to a moderator
|
|
|
Re: PLS-00597: PLSQL Record and Nested [message #633235 is a reply to message #633168] |
Thu, 12 February 2015 10:42 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
It doesn't appear you understand a difference between:
TYPE r IS RECORD(
test_t TEST_TABLE%ROWTYPE,
y VARCHAR2 (100)
);
And:
TYPE r IS RECORD(
rate_id number,
rate_name varchar2(10),
y VARCHAR2 (100)
);
Former it a record with two attributes: test_t and y, where test_t itself is a record with two attributes. And latter is a record with three attributes. So when you issue:
SELECT a.*,
'RANDOMUUID' rndid
FROM TEST_TABLE a;
select list has three expressions and therefore INTO clause must have either three variables or a record with three attributes. So it is obvious SELECT will fail if record is declared the way you do.
SY.
|
|
|