Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement (oracle 10)
ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422242] Tue, 15 September 2009 04:32 Go to next message
lampione
Messages: 5
Registered: September 2009
Junior Member
Dear friends,
I ask you a suggestion:

The code below inserts in a table errors (errori_work), the IDs of the records repeated (in the table work), but give me this error:
ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement.

I could use a record declared as the cursor (rec_tabwork cu_tabwork%ROWTYPE;), but in the INSERT I need a field (ID_WORK) not present in the cursor.

declare
CURSOR cu_tabwork IS
SELECT CD_STRUTTURA,CD_SUB_STRUTTURA,NR_PRATICA, destinazione
FROM work
GROUP BY CD_STRUTTURA,CD_SUB_STRUTTURA,NR_PRATICA, destinazione
HAVING COUNT(rowid) > 1
UNION ALL
SELECT CD_STRUTTURA,CD_SUB_STRUTTURA,NR_PRATICA, destinazione
FROM work
GROUP BY CD_STRUTTURA,CD_SUB_STRUTTURA,NR_PRATICA, destinazione
HAVING COUNT(rowid) > 2;
rec_tabwork work%ROWTYPE;
BEGIN
open cu_tabwork;
loop
fetch cu_tabwork into rec_tabwork;
if cu_tabwork%FOUND then
dbms_output.put_line (rec_tabwork.ASL); -- test
INSERT INTO errori_work
(cd_errore, note, ID_WORK
)
VALUES ('CAC', 'Record ripetuto nella tabella Work', rec_tabwork.ID_WORK
);
end if;
EXIT WHEN cu_tabwork%NOTFOUND;
end loop;
close cu_tabwork;
end;


I explain better:
I search in a table (work) for records that have the same values in some fields (it is an error for me);
for doing it, I create a cursor associated to a query whit the matching fields;
if I find repeated records, then I must get the value of the field primary key (ID_WORK) of the repeated record (except one) and insert it in another table (errori_work).
The problem is this:
I cannot include ID_WORK in the query of the cursor because ID_WORK is a primary key and I could not get the repeated records, but I need this value for the insert into errori_work


can you help me?
thanks
leo
Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422250 is a reply to message #422242] Tue, 15 September 2009 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422293 is a reply to message #422242] Tue, 15 September 2009 06:51 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
DECLARE
   CURSOR cu_tabwork
   IS
      SELECT   cd_struttura, cd_sub_struttura, nr_pratica, destinazione
          FROM WORK
      GROUP BY cd_struttura, cd_sub_struttura, nr_pratica, destinazione
        HAVING COUNT (ROWID) > 1
      UNION ALL
      SELECT   cd_struttura, cd_sub_struttura, nr_pratica, destinazione
          FROM WORK
      GROUP BY cd_struttura, cd_sub_struttura, nr_pratica, destinazione
        HAVING COUNT (ROWID) > 2;

   rec_tabwork   WORK%ROWTYPE;
BEGIN
   OPEN cu_tabwork;

   LOOP
      FETCH cu_tabwork
       INTO rec_tabwork;

      IF cu_tabwork%FOUND
      THEN
         DBMS_OUTPUT.put_line (rec_tabwork.asl);                      -- test

         INSERT INTO errori_work
                     (cd_errore, note,
                      id_work
                     )
              VALUES ('CAC', 'Record ripetuto nella tabella Work',
                      rec_tabwork.id_work
                     );
      END IF;

      EXIT WHEN cu_tabwork%NOTFOUND;
   END LOOP;

   CLOSE cu_tabwork;
END;


Cursor cu_tabwork has cd_struttura, cd_sub_struttura, nr_pratica, destinazione fields

while work can have many columns except that
rec_tabwork   WORK%ROWTYPE;


HOw can you do this??
FETCH cu_tabwork
INTO rec_tabwork;

It will be like
rec_tabwork   cu_tabwork%ROWTYPE;


or the Cursor select should be expanded to select every column of work

[Updated on: Tue, 15 September 2009 06:55]

Report message to a moderator

Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422296 is a reply to message #422293] Tue, 15 September 2009 06:57 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Can you show the output of the command DESCRIBE WORK ?

regards,
Delna
Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422333 is a reply to message #422293] Tue, 15 September 2009 09:00 Go to previous messageGo to next message
lampione
Messages: 5
Registered: September 2009
Junior Member
scuse me for the delay;
I could use "rec_tabwork cu_tabwork%ROWTYPE;", but I have to use the field ID_WORK that I cannot include in the query of the cursor because ID_WORK is a primary key and I could not get the repeated records.



Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422338 is a reply to message #422296] Tue, 15 September 2009 09:14 Go to previous messageGo to next message
lampione
Messages: 5
Registered: September 2009
Junior Member

CREATE TABLE WORK
(
ID_WORK NUMBER(20),
ENTE VARCHAR2(3 BYTE),
CD_PRESIDIO VARCHAR2(6 BYTE),
DT_PRENOTAZIONE VARCHAR2(8 BYTE),
DT_CONTATTO VARCHAR2(8 BYTE),
NR_RICETTA VARCHAR2(16 BYTE),
CD_PROVEN VARCHAR2(1 BYTE),
CD_REGIME VARCHAR2(1 BYTE),
CD_MED_PRES VARCHAR2(16 BYTE),
ID_PREST VARCHAR2(1 BYTE),
CD_POSIZ_TICKET VARCHAR2(2 BYTE),
NR_IMP_PAGATO VARCHAR2(7 BYTE),
CD_PRESTAZIONE_1 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_1 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_1 VARCHAR2(9 BYTE),
CD_PRESTAZIONE_2 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_2 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_2 VARCHAR2(9 BYTE),
CD_PRESTAZIONE_3 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_3 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_3 VARCHAR2(9 BYTE),
CD_PRESTAZIONE_4 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_4 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_4 VARCHAR2(9 BYTE),
CD_PRESTAZIONE_5 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_5 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_5 VARCHAR2(9 BYTE),
CD_PRESTAZIONE_6 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_6 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_6 VARCHAR2(9 BYTE),
CD_PRESTAZIONE_7 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_7 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_7 VARCHAR2(9 BYTE),
CD_PRESTAZIONE_8 VARCHAR2(5 BYTE),
NR_QTA_EROGATA_8 VARCHAR2(3 BYTE),
NR_VALORE_AMB2_8 VARCHAR2(9 BYTE),
CD_CONT_PRESCR VARCHAR2(8 BYTE),
NR_PROG_INT VARCHAR2(2 BYTE),
CD_DIAGNOSI VARCHAR2(5 BYTE),
TIPO_DI_RECORD VARCHAR2(1 BYTE),
NR_CONCORDATO VARCHAR2(7 BYTE),
INDIC_NEUROPS VARCHAR2(1 BYTE),
DATA_CONTR VARCHAR2(8 BYTE),
RILEVATORE VARCHAR2(5 BYTE),
ESITO VARCHAR2(1 BYTE),
DESTINAZIONE VARCHAR2(1 BYTE),
VALORE_ASS VARCHAR2(9 BYTE),
TIPO_CAMP VARCHAR2(1 BYTE),
VALORE_OR VARCHAR2(9 BYTE),
ASL VARCHAR2(3 BYTE),
GGMM_CONTATTO VARCHAR2(4 BYTE),
MESE VARCHAR2(2 BYTE),
ANNO VARCHAR2(4 BYTE),
FL_ERRORE CHAR(1 BYTE) DEFAULT NULL,
NOME_FILE VARCHAR2(20 BYTE),
DT_INSERT DATE,
DT_UPDATE DATE,
STATO INTEGER DEFAULT 4,
FL_FALSO_ERRORE CHAR(1 BYTE),
STATO_ELAB INTEGER DEFAULT 4
)
Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422340 is a reply to message #422296] Tue, 15 September 2009 09:20 Go to previous messageGo to next message
lampione
Messages: 5
Registered: September 2009
Junior Member
sorry, the right table is this:

CREATE TABLE WORK
(
ID_WORK NUMBER(20),
CD_STRUTTURA VARCHAR2(6 BYTE),
CD_SUB_STRUTTURA VARCHAR2(2 BYTE),
NR_PRATICA VARCHAR2(10 BYTE),
TIPREC VARCHAR2(1 BYTE),
MMGGRIC VARCHAR2(4 BYTE),
CD_REGIME_RICOVERO VARCHAR2(1 BYTE),
CD_REPARTO_ACCETTAZIONE VARCHAR2(4 BYTE),
CD_PROVENIENZA_ASSISTITO VARCHAR2(1 BYTE),
CD_TIPO_RICOVERO VARCHAR2(1 BYTE),
CD_ONERE_DEGENZA VARCHAR2(1 BYTE),
CD_MODALITA_TRAUMA VARCHAR2(1 BYTE),
DT_INGRESSO_REPARTO_1 VARCHAR2(8 BYTE),
CD_REPARTO_1 VARCHAR2(4 BYTE),
DT_INGRESSO_REPARTO_2 VARCHAR2(8 BYTE),
CD_REPARTO_2 VARCHAR2(4 BYTE),
CD_INGRESSO_REPARTO_3 VARCHAR2(8 BYTE),
CD_REPARTO_3 VARCHAR2(4 BYTE),
DT_DIMISSIONE VARCHAR2(8 BYTE),
CD_DIAGNOSI VARCHAR2(5 BYTE),
CD_DIAGNOSI_1 VARCHAR2(5 BYTE),
CD_DIAGNOSI_2 VARCHAR2(5 BYTE),
CD_DIAGNOSI_3 VARCHAR2(5 BYTE),
CD_DIAGNOSI_4 VARCHAR2(5 BYTE),
CD_DIAGNOSI_5 VARCHAR2(5 BYTE),
DT_INTERVENTO VARCHAR2(8 BYTE),
CD_INTERVENTO VARCHAR2(4 BYTE),
DT_INTERVENTO_1 VARCHAR2(8 BYTE),
CD_INTERVENTO_1 VARCHAR2(4 BYTE),
DT_INTERVENTO_2 VARCHAR2(8 BYTE),
CD_INTERVENTO_2 VARCHAR2(4 BYTE),
DT_INTERVENTO_3 VARCHAR2(8 BYTE),
CD_INTERVENTO_3 VARCHAR2(4 BYTE),
DT_INTERVENTO_4 VARCHAR2(8 BYTE),
CD_INTERVENTO_4 VARCHAR2(4 BYTE),
DT_INTERVENTO_5 VARCHAR2(8 BYTE),
CD_INTERVENTO_5 VARCHAR2(4 BYTE),
CD_MODALITA_DIMISSIONE VARCHAR2(1 BYTE),
FL_AUTOPSIA VARCHAR2(1 BYTE),
NR_GG_DAY_HOSPITAL VARCHAR2(3 BYTE),
CD_MOTIVO_DAYHOSPITAL VARCHAR2(1 BYTE),
DS_PESOP_NASCITA VARCHAR2(4 BYTE),
DT_EVENTO_INDICE_RIABILITAZ VARCHAR2(8 BYTE),
DT_INIZIO_PRERICOVERO VARCHAR2(8 BYTE),
DT_FINE_PRERICOVERO VARCHAR2(8 BYTE),
DT_PRENOTAZIONE_INTERVENTO VARCHAR2(8 BYTE),
TIPTAR VARCHAR2(1 BYTE),
IMPTNR VARCHAR2(6 BYTE),
NUM_GG_NOSSN VARCHAR2(3 BYTE),
ASLRES VARCHAR2(3 BYTE),
NUM_GG_DEGENZA VARCHAR2(4 BYTE),
CD_DRG VARCHAR2(3 BYTE),
NUM_IMPORTO_TOT VARCHAR2(7 BYTE),
NUM_IMPORTOC_TOT VARCHAR2(2 BYTE),
NUM_GG_DEGOVS VARCHAR2(4 BYTE),
VAL_AMB VARCHAR2(7 BYTE),
VAL_AMBC VARCHAR2(2 BYTE),
DATA_CONTR VARCHAR2(8 BYTE),
RILEVATORE VARCHAR2(5 BYTE),
TIPO_CAMP VARCHAR2(1 BYTE),
ESITO VARCHAR2(1 BYTE),
ESITO_ALTRO VARCHAR2(1 BYTE),
DESTINAZIONE VARCHAR2(1 BYTE),
REGIME_OR VARCHAR2(1 BYTE),
DRG_OR VARCHAR2(3 BYTE),
IMPTOT_OR VARCHAR2(7 BYTE),
IMPTOTC_OR VARCHAR2(2 BYTE),
ASL VARCHAR2(3 BYTE),
MESE VARCHAR2(2 BYTE),
ANNO VARCHAR2(4 BYTE),
FL_ERRORE CHAR(1 BYTE) DEFAULT NULL,
NOME_FILE VARCHAR2(20 BYTE),
DT_INSERT DATE,
DT_UPDATE DATE,
STATO INTEGER DEFAULT 4,
FL_FALSO_ERRORE CHAR(1 BYTE),
STATO_ELAB INTEGER DEFAULT 4
)
Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422346 is a reply to message #422242] Tue, 15 September 2009 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
alternatively do it as a single SQL statement

INSERT INTO errori_work (cd_errore, note, ID_WORK)
   select distinct  'CAC' cd_errore, 'Record ripetuto nella tabella Work' note, WORK_id
    from work
   where work_id in (select distinct work_id from work having count(work_id) > 1 group by  work_id)
Re: ORA-06550: PLS-00394: wrong number of values in the INTO list of a FETCH statement [message #422436 is a reply to message #422346] Wed, 16 September 2009 07:55 Go to previous message
lampione
Messages: 5
Registered: September 2009
Junior Member
"alternatively do it as a single SQL statement":
your idea is very good,
but work_id is primary key and so your code

"having count(work_id) > 1 group by work_id)"
is not possible;
but your is the right way:

INSERT INTO errori_work(cd_errore, note, ID_WORK)
SELECT 'CAC', 'Record ripetuto nella tabella Work',ID_WORK
FROM WORK A
WHERE A.ROWID > ANY (
SELECT B.ROWID
FROM WORK B
WHERE A.CD_STRUTTURA = B.CD_STRUTTURA
AND NVL(A.CD_SUB_STRUTTURA,0) = NVL(B.CD_SUB_STRUTTURA,0)
AND A.NR_PRATICA = B.NR_PRATICA
AND A.DESTINAZIONE = B.DESTINAZIONE
);

this code functions and gives back the right number of records because the field CD_SUB_STRUTTURA is often empty and now, with the NVL function added, is always counted.

many thanks, friend
leo
Previous Topic: Fetching the last DML statements exectuted.
Next Topic: Send Attachment in Email using PL/SQL
Goto Forum:
  


Current Time: Sat Dec 03 19:54:28 CST 2016

Total time taken to generate the page: 0.08680 seconds