Home » SQL & PL/SQL » SQL & PL/SQL » What is wrong with this code ?
What is wrong with this code ? [message #147603] Fri, 18 November 2005 08:27 Go to next message
rajavu1
Messages: 1573
Registered: May 2005
Location: Bangalore , India
Senior Member

Hello everybody,

On executing the attatchedpeict of code i am getting an error
"pls-00386 type mismatch fount at 'RCPT' beteween fetch cursor and into variable" .. But on going through the structures I couldn't get the mismatch...


Can anybody help me ..

Rajuvan.


  • Attachment: code.txt
    (Size: 9.98KB, Downloaded 310 times)
Re: What is wrong with this code ? [message #147644 is a reply to message #147603] Fri, 18 November 2005 14:06 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
To my knowledge, the table used for the BULK COLLECT must either be defined using the associated CURSOR or independent tables must be specified for each column. I could be wrong about this as I did not verify, but here is an illustration. I'm sure someone will correct me if I am mistaken.

DROP TABLE t PURGE;
CREATE TABLE t
 (x NUMBER
 ,y NUMBER);

REM Populate the table with numeric data
INSERT INTO t
SELECT LEVEL, LEVEL*2
FROM dual
CONNECT BY LEVEL < 100;

REM Object type with same definition as the table (and cursor below)
CREATE TYPE obj_ty AS OBJECT 
 (x NUMBER
 ,y NUMBER);
/

REM ------- INVALID SOLUTION, SIMILAR TO SUPPLIED CODE --------
CREATE OR REPLACE PROCEDURE p IS
  CURSOR c IS
  SELECT x, y
  FROM t;

  -- CREATE TABLE WITH AN OBJECT TYPE
  TYPE t_ty IS TABLE OF obj_ty INDEX BY PLS_INTEGER;
  tab t_ty;
BEGIN
  OPEN c;
  FETCH c BULK COLLECT INTO tab;

  FOR i IN tab.FIRST..tab.LAST LOOP
    dbms_output.put_line (tab(i).x || ' - ' || tab(i).y);
  END LOOP;
END p;
/
sho err

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/3     PL/SQL: SQL Statement ignored
10/29    PLS-00386: type mismatch found at 'TAB' between FETCH cursor and
         INTO variables


REM ------ FIRST OPTION USING TABLE DEFINED WITH %ROWTYPE ------
CREATE OR REPLACE PROCEDURE p IS
  CURSOR c IS
  SELECT x, y
  FROM t;

  -- DEFINE THE TABLE USING THE CURSOR
  TYPE t_ty IS TABLE OF c%ROWTYPE INDEX BY PLS_INTEGER;
  tab t_ty;

BEGIN
  OPEN c;
  FETCH c BULK COLLECT INTO tab;

  FOR i IN tab.FIRST..tab.LAST LOOP
    dbms_output.put_line (tab(i).x || ' - ' || tab(i).y);
  END LOOP;
END p;
/

Procedure created.

REM --------- SECOND OPTION USING INDEPENDENT TABLES -----------
CREATE OR REPLACE PROCEDURE p IS
  CURSOR c IS
  SELECT x, y
  FROM t;

  TYPE t_ty IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  -- DEFINE A TABLE FOR EACH COLUMN IN THE CURSOR
  tab1 t_ty;
  tab2 t_ty;
BEGIN
  OPEN c;
  FETCH c BULK COLLECT INTO tab1, tab2;

  FOR i IN tab1.FIRST..tab1.LAST LOOP
    dbms_output.put_line (tab1(i) || ' - ' || tab2(i));
  END LOOP;
END p;
/

Procedure created.

SQL> EXEC p
1 - 2
2 - 4
3 - 6
4 - 8
5 - 10
6 - 12
7 - 14
8 - 16
....
93 - 186
94 - 188
95 - 190
96 - 192
97 - 194
98 - 196
99 - 198

PL/SQL procedure successfully completed.


We just love options, another approach would be to use a strong typed CURSOR that is defined in a package spec if you want to reuse the CURSOR definition in other pieces of code.
Previous Topic: XML read from Oracle
Next Topic: Avoiding mutation errors
Goto Forum:
  


Current Time: Sat Feb 23 07:31:50 CST 2019