vivi leds
asked on
Wrong number of values in the INTO list of a FETCH statement
Hi,
I'm new here and hope someone can answer me. I have a probleme on a pl/psql function it turns me out :
Wrong number of values in the INTO list of a FETCH statement.
I don't know where is the problem, can anyone help me? There is the code :
Declare
Cursor Mesage Is
select F_PRI.PRI_ID_TEC_FK,F_PRI.
Mon_Essai Essai%Rowtype;
BEGIN
Open Mesage;
Loop
Fetch Mesage Into Mon_Essai;
Dbms_Output.Put_Line('Tout
Exit When Mesage%Notfound;
END LOOP;
Close Mesage;
End;
I'm new here and hope someone can answer me. I have a probleme on a pl/psql function it turns me out :
Wrong number of values in the INTO list of a FETCH statement.
I don't know where is the problem, can anyone help me? There is the code :
Declare
Cursor Mesage Is
select F_PRI.PRI_ID_TEC_FK,F_PRI.
Mon_Essai Essai%Rowtype;
BEGIN
Open Mesage;
Loop
Fetch Mesage Into Mon_Essai;
Dbms_Output.Put_Line('Tout
Exit When Mesage%Notfound;
END LOOP;
Close Mesage;
End;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi thanks for your response,
Yes ! I was wrong and put the bad name but there is an another problem which came : there is too mush data so I tried to do a rownum on the query but there is an error.
I can't put a rownum <= 10 in the query of a cursor?
Yes ! I was wrong and put the bad name but there is an another problem which came : there is too mush data so I tried to do a rownum on the query but there is an error.
I can't put a rownum <= 10 in the query of a cursor?
Assuming that MON_ESSAI is a table, then the number and/or types that you are selecting from the cursor don't match up with the table columns.
You really don't need to do all the work yourself, let the database do it:
You really don't need to do all the work yourself, let the database do it:
begin
for Mon_Essai in (SELECT f_pri.pri_id_tec_fk,
f_pri.pri_num_ctr_fk,
f_pri.pri_dat_deb_fk,
lov_situ_prm_prst.sitprm_lbl,
Inst.id,
Inst.dat,
Inst.sit
FROM collect.f_pri,
collect.lov_situ_prm_prst,
(SELECT f_pri.pri_id_tec_fk id,
f_pri.pri_num_ctr_fk Ctr,
f_pri.pri_dat_deb_fk dat,
lov_situ_prm_prst.sitprm_lbl sit
FROM collect.f_pri,
collect.lov_situ_prm_prst
WHERE ( f_pri.pri_cod_situ_fk = lov_situ_prm_prst.sitprm_cod_pk )
AND lov_situ_prm_prst.sitprm_lbl = 'En instance') Inst
WHERE ( f_pri.pri_cod_situ_fk = lov_situ_prm_prst.sitprm_cod_pk )
AND lov_situ_prm_prst.sitprm_lbl IN ( 'Soldée', 'Annulée' )
AND f_pri.pri_num_ctr_fk = Inst.ctr
AND f_pri.pri_dat_deb_fk > Inst.dat
ORDER BY f_pri.pri_num_ctr_fk,
f_pri.pri_dat_deb_fk DESC ) loop
...
END LOOP;
END;
/
>>I can't put a rownum <= 10 in the query of a cursor?
It is valid syntax so sure you can. You just need to make sure the SQL syntax is correct.
Post what you have and the error it generates.
It is valid syntax so sure you can. You just need to make sure the SQL syntax is correct.
Post what you have and the error it generates.
ASKER
thx so I tried again the rownum there is my query :
The error is :
ORA-00933: la commande SQL ne se termine pas correctement
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Erreur à la ligne 1, colonne 717
BEGIN
for Mon_Essai in (select F_PRI.PRI_ID_TEC_FK,F_PRI.PRI_NUM_CTR_FK,F_PRI.PRI_DAT_DEB_FK,LOV_SITU_PRM_PRST.SITPRM_LBL,
Inst.id,Inst.dat, Inst.Sit FROM Collect.F_PRI, Collect.LOV_SITU_PRM_PRST, (SELECT F_PRI.PRI_ID_TEC_FK id,
F_PRI.PRI_NUM_CTR_FK Ctr, F_PRI.PRI_DAT_DEB_FK dat,LOV_SITU_PRM_PRST.SITPRM_LBL sit FROM Collect.F_PRI,
Collect.LOV_SITU_PRM_PRST WHERE ( F_PRI.PRI_COD_SITU_FK=LOV_SITU_PRM_PRST.SITPRM_COD_PK ) and
LOV_SITU_PRM_PRST.SITPRM_LBL = 'En instance' ) Inst WHERE ( F_PRI.PRI_COD_SITU_FK=LOV_SITU_PRM_PRST.SITPRM_COD_PK )
and LOV_SITU_PRM_PRST.SITPRM_LBL IN ('Soldée', 'Annulée') and F_PRI.PRI_NUM_CTR_FK=Inst.ctr and F_PRI.PRI_DAT_DEB_FK > Inst.dat
[b]and rownum <= 10[/b]
order by F_PRI.PRI_NUM_CTR_FK,F_PRI.PRI_DAT_DEB_FK desc) loop
Dbms_Output.Put_Line('Tout va bien');
END LOOP;
End;The error is :
ORA-00933: la commande SQL ne se termine pas correctement
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Erreur à la ligne 1, colonne 717
If I remove the bold tags I don't get a syntax error using sqlplus.
ASKER
I put the bold tags but the error is there, i'm on sql developper does it change something ? is there any other syntax ? or another solution
SQL Developer shouldn't have any problems.
I don't have your tables so I cannot fully test the SQL. I can test the SQL for syntax issues and I don't get any.
So it runs if you remove "and rownum <= 10" and change nothing else?
I don't have your tables so I cannot fully test the SQL. I can test the SQL for syntax issues and I don't get any.
So it runs if you remove "and rownum <= 10" and change nothing else?
ASKER
I understand that "unterstanding the problem" is a little difficult, in fact when I remove the "and rownum <= 10" it work but I have an error message like :
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 32
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 97
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 112
ORA-06512: à ligne 6
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.
ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 32
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 97
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 112
ORA-06512: à ligne 6
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.
>> I have an error message like
You've exceeded the amount of buffer dbms_output can use. Depending on your database version, you can make this unlimited.
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG099
Back to the rownum issue: I don't see where just adding that will cause a syntax error. Hopefully one of the other Experts will see what I'm missing.
You've exceeded the amount of buffer dbms_output can use. Depending on your database version, you can make this unlimited.
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG099
Back to the rownum issue: I don't see where just adding that will cause a syntax error. Hopefully one of the other Experts will see what I'm missing.
ASKER
I hope so, in any ase thanks to everyone who helped me.
Bye ;)
Bye ;)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>It is caused by bold
See above. It has already been commented on: vivi added that.
See above. It has already been commented on: vivi added that.
ASKER
Hey, I really don't know what the difference between your version and mine but it works ! So thanks a lot
The original you posted works as well, I just built this case off of that (remember we are testing syntax, so data types don't matter):
create table f_pri (pri_id_tec_fk number, pri_num_ctr_fk number, pri_dat_deb_fk number, pri_cod_situ_fk number);
create table lov_situ_prm_prst (sitprm_lbl number, sitprm_cod_pk number);
SELECT f_pri.pri_id_tec_fk,
f_pri.pri_num_ctr_fk,
f_pri.pri_dat_deb_fk,
lov_situ_prm_prst.sitprm_lbl,
Inst.id,
Inst.dat,
Inst.sit
FROM f_pri,
lov_situ_prm_prst,
(SELECT f_pri.pri_id_tec_fk id,
f_pri.pri_num_ctr_fk Ctr,
f_pri.pri_dat_deb_fk dat,
lov_situ_prm_prst.sitprm_lbl sit
FROM f_pri,
lov_situ_prm_prst
WHERE f_pri.pri_cod_situ_fk = lov_situ_prm_prst.sitprm_cod_pk
AND lov_situ_prm_prst.sitprm_lbl = 'En instance') Inst
WHERE f_pri.pri_cod_situ_fk = lov_situ_prm_prst.sitprm_cod_pk
AND lov_situ_prm_prst.sitprm_lbl IN ( 'Soldée', 'Annulée' )
AND f_pri.pri_num_ctr_fk = Inst.ctr
AND f_pri.pri_dat_deb_fk > Inst.dat
AND ROWNUM <= 10
ORDER BY f_pri.pri_num_ctr_fk,
f_pri.pri_dat_deb_fk DESC
;
Does it have exactly 7 columns?
You are selecting 7 values in the cursor, the into object needs 7 places to store them.