Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00597: PLSQL Record and Nested (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production, Windows 7)
PLS-00597: PLSQL Record and Nested [message #633145] Wed, 11 February 2015 10:03 Go to next message
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 #633146 is a reply to message #633145] Wed, 11 February 2015 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NEVER do in PL/SQL that which can be done in plain SQL

INSERT INTO TEST_IDS SELECT * FROM TEST_TABLE; -- adjust columns appropriately
Re: PLS-00597: PLSQL Record and Nested [message #633151 is a reply to message #633146] Wed, 11 February 2015 11:19 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
Why we are doing in PLSQL is that as and when this table is getting populated , it also needs to generate some Random UID and populate one of the columns with these Random UIDs and at the same time the same Random UID generated we need to map and populate to another mapping table for the corresponding record which is getting inserted to the First table.

The Random UID is generated by a function so when we populate the second table we need to store the same UID generated for the first one.

A different call to the Random UID generator for populating the second table will generate a new Random UID. As we do in SQL it is not possible to maintain the first one in a Array sort of thing and populate in the Second table, thus it is required to have a PL/SQL.

So as and when we select from the Source table to insert into the Target table we will generate the UID and when it is getting inserted to the Target table through the use of FOR ALL and INSERT ALL want to insert into the Second Table. I think this is possible through only PL/SQL isn't it?



Re: PLS-00597: PLSQL Record and Nested [message #633152 is a reply to message #633151] Wed, 11 February 2015 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/database/121/LNPLS/returninginto_clause.htm#LNPLS01354
Re: PLS-00597: PLSQL Record and Nested [message #633157 is a reply to message #633152] Wed, 11 February 2015 12:33 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
Coming back to the topic, What is the cause of this error .

PLS-00597 expression ' ' in the INTO list is of wrong type with the following PLSQL Block


Is that I am using a Record type in the below format. Oracle Version is 11.2

--------------------------------------------------------
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;


--------------------------------------------------------------------------
Is this not allowed in Oracle 11.2?

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

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 #633158 is a reply to message #633157] Wed, 11 February 2015 12:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
consider using GTT instead
Re: PLS-00597: PLSQL Record and Nested [message #633163 is a reply to message #633157] Wed, 11 February 2015 18:21 Go to previous messageGo to next message
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 #633168 is a reply to message #633163] Wed, 11 February 2015 20:47 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
Hi SY,

Thanks for your solution.

I had one question on the same. This is part of a Data Migration activity which we are doing this, I had just given a sample column in my code earlier, If I have 'n' number of columns in that table is it possible to declare TYPE as below. With individual column definition this solution will work, how to make it work with ROWTYPE ?

-----------------------------------------
DECLARE
TYPE r IS RECORD(
test_t TEST_TABLE%ROWTYPE,
y VARCHAR2 (100)
);
TYPE rt IS TABLE OF r
INDEX BY PLS_INTEGER;
d rt;

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


Thanks,
Ninan.
Re: PLS-00597: PLSQL Record and Nested [message #633173 is a reply to message #633168] Thu, 12 February 2015 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Re: PLS-00597: PLSQL Record and Nested [message #633187 is a reply to message #633173] Thu, 12 February 2015 02:15 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: convert string values into rows
Next Topic: Need help on using regexp_substr in SQL
Goto Forum:
  


Current Time: Thu Mar 28 06:51:19 CDT 2024