Home » SQL & PL/SQL » SQL & PL/SQL » ERROR PLS-00428 (Oracle 11g, Sql developer 1.5.3)
ERROR PLS-00428 [message #381568] Sun, 18 January 2009 10:47 Go to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
Hi, i have a little problem with my function, when i coompile it, it gives me PLS-00428 ERROR.

this is my code

CREATE OR REPLACE FUNCTION fn_ca_fournisseur (codfou NUMBER, année NUMBER)
RETURN REAL IS
BEGIN
SELECT f.numfou, f.nomfou, sum(qtecde*priuni*1.2060) AS CHIFFRE_D_AFFARE
FROM fournis f, ligcom l, entcom e
WHERE codfou = f.numfou
AND f.numfou= e.numfou
AND e.numcom= l.numcom
AND extract(year FROM datcom) = année
GROUP BY codfou, f.nomfou;
END fn_ca_fournisseur;


Error(5,1): PLS-00428: une clause INTO est attendue dans cette instruction SELECT
 


thanks for your helps

PS : Sorry for my english, i'm french

[Updated on: Sun, 18 January 2009 10:52]

Report message to a moderator

Re: ERROR PLS-00428 [message #381575 is a reply to message #381568] Sun, 18 January 2009 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in the error message?
Maybe you should read:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

You can also have a look at error messages book:
Quote:
PLS-00428: an INTO clause is expected in this SELECT statement
Cause: The INTO clause of a SELECT INTO statement was omitted. For example, the code might look like SELECT deptno, dname, loc FROM dept WHERE ... instead of SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE ... In PL/SQL, only a subquery is written without an INTO clause.
Action: Add the required INTO clause.


Regards
Michel
Re: ERROR PLS-00428 [message #381576 is a reply to message #381568] Sun, 18 January 2009 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Within PL/SQL the SELECT must include INTO clause & return only 1 row.
Re: ERROR PLS-00428 [message #381577 is a reply to message #381576] Sun, 18 January 2009 12:04 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
i try this code but i have 2 others errors. this is my code

create or replace
FUNCTION fn_ca_fournisseur (codfou NUMBER, année NUMBER)

RETURN NUMBER IS

f_numfou fournis.numfou%type;
f_nomfou fournis.nomfou%type;
l_qtecde ligcom.qtecde%type;
l_priuni ligcom.priuni%type;

BEGIN

SELECT f.numfou, f.nomfou, sum(l.qtecde* l.priuni*1.2060) AS CHIFFRE_D_AFFARE

INTO f_numfou, f_nomfou, l_qtecde, l_priuni

FROM fournis f, ligcom l, entcom e

WHERE codfou = f.numfou
AND f.numfou= e.numfou
AND e.numcom= l.numcom
AND extract(year FROM datcom) = année
GROUP BY codfou, f.nomfou;

END fn_ca_fournisseur;



and i have theses errors

Error(13,1): PL/SQL: SQL Statement ignored
Error(16,2): PL/SQL: ORA-00913: too many values.
Re: ERROR PLS-00428 [message #381578 is a reply to message #381577] Sun, 18 January 2009 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 18 January 2009 18:31
What don't you understand in the error message?
Maybe you should read:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Just read what you wrote:
SELECT 3 expressions INTO 4 variables

By the way, this is not the only error, follow my previous advices.

Regards
Michel

[Updated on: Sun, 18 January 2009 12:30]

Report message to a moderator

Re: ERROR PLS-00428 [message #381579 is a reply to message #381578] Sun, 18 January 2009 12:37 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
i select 4 expressions into 4 variables, it's normal:

SELECT f.numfou, f.nomfou, sum(l.qtecde* l.priuni*1.2060) AS CHIFFRE_D_AFFARE

INTO f_numfou, f_nomfou, l_qtecde, l_priuni

so explain me

[Updated on: Sun, 18 January 2009 12:38]

Report message to a moderator

Re: ERROR PLS-00428 [message #381581 is a reply to message #381579] Sun, 18 January 2009 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Execute your query and you will see 3 columns.
I recommend you to also read SQL Reference

Regards
Michel
Re: ERROR PLS-00428 [message #381582 is a reply to message #381581] Sun, 18 January 2009 13:03 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
i see it, but it's not clear for me and i'm not english so, a little hard.
Re: ERROR PLS-00428 [message #381583 is a reply to message #381582] Sun, 18 January 2009 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has nothing to do with English just with SQL and PL/SQL.
If you have problems with English you can post in French forum the bottom of Forum home page.

Regards
Michel
Re: ERROR PLS-00428 [message #381584 is a reply to message #381583] Sun, 18 January 2009 13:24 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
I think that in french, i'll not have the good answer like in english and when i saw french forum, i don't see PL/SQL post, there is all in the same time. I don't like it, there not difference in PL/SQL post and others.

[Updated on: Sun, 18 January 2009 13:24]

Report message to a moderator

Re: ERROR PLS-00428 [message #381585 is a reply to message #381568] Sun, 18 January 2009 13:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i select 4 expressions into 4 variables, it's normal:
>SELECT f.numfou, f.nomfou, sum(l.qtecde* l.priuni*1.2060) AS CHIFFRE_D_AFFARE

NO, SELECT returns THREE (3) values:
#1 - f.numfou
#2 - f.nomfou
#3 - CHIFFRE_D_AFFARE
Re: ERROR PLS-00428 [message #381685 is a reply to message #381585] Mon, 19 January 2009 04:23 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
Thanks, so you think that i must rewrite my code like that?

create or replace
FUNCTION fn_ca_fournisseur (codfou NUMBER, année NUMBER)
RETURN NUMBER IS
f_numfou fournis.numfou%type;
f_nomfou fournis.nomfou%type;
CA NUMBER;
BEGIN
SELECT f.numfou, f.nomfou, sum(l.qtecde* l.priuni*1.2060) AS CHIFFRE_D_AFFARE
INTO f_numfou, f_nomfou, CA
FROM fournis f, ligcom l, entcom e
WHERE codfou = f.numfou
AND f.numfou= e.numfou
AND e.numcom= l.numcom
AND extract(year FROM datcom) = année
GROUP BY f.numfou, f.nomfou;
RETURN (CA);
END fn_ca_fournisseur;
Re: ERROR PLS-00428 [message #381687 is a reply to message #381685] Mon, 19 January 2009 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Indent your code
2/ Yes, but it will not work if you have more than one group
3/ I doubt that "année" will compile

FIRST, read the manual, then try to program.

Regards
Michel

[Updated on: Mon, 19 January 2009 05:13]

Report message to a moderator

Re: ERROR PLS-00428 [message #381690 is a reply to message #381687] Mon, 19 January 2009 04:43 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
I read the manual and I think that the example in manual is in basic form, so if you push high your code you must be light by confirmed coder so why I'm here?? To find solution of my problem, to have propositions in my code, not to go to read a manual, I am a trainee in a formation school and read my lessons but I find nothing.
Thanks

[Updated on: Mon, 19 January 2009 04:48]

Report message to a moderator

icon14.gif  Re: ERROR PLS-00428 [message #381691 is a reply to message #381690] Mon, 19 January 2009 04:55 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
YES, it works. Thanks for your collaborations

CREATE OR REPLACE
FUNCTION fn_ca_fournisseur (codfou NUMBER, année NUMBER)
RETURN NUMBER IS
f_numfou fournis.numfou%type;
f_nomfou fournis.nomfou%type;
CA NUMBER;
BEGIN
SELECT f.numfou, f.nomfou, sum(l.qtecde* l.priuni*1.2060) AS CHIFFRE_D_AFFARE
INTO f_numfou, f_nomfou, CA
FROM fournis f, ligcom l, entcom e
WHERE codfou = f.numfou
AND f.numfou= e.numfou
AND e.numcom= l.numcom
AND extract(year FROM datcom) = année
GROUP BY f.numfou, f.nomfou;
RETURN (CA);
END fn_ca_fournisseur;
Re: ERROR PLS-00428 [message #381694 is a reply to message #381691] Mon, 19 January 2009 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2/ Yes, but it will not work if you have more than one group

In addition, it is highly recommended to NOT use locale characters in code, even if it works on you database it may not in your (future) customer.

Regards
Michel

[Updated on: Mon, 19 January 2009 05:15]

Report message to a moderator

icon14.gif  Re: ERROR PLS-00428 [message #381697 is a reply to message #381694] Mon, 19 January 2009 05:37 Go to previous messageGo to next message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
Thanks mike, I gonna look at and correct it, but when you say local character, what do you mean? codfou and année??

[Updated on: Mon, 19 January 2009 05:41]

Report message to a moderator

Re: ERROR PLS-00428 [message #381701 is a reply to message #381697] Mon, 19 January 2009 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"é" is not a character that is known everywhere.
Stay with A-Z characters (or a-z, Oracle does not care of case, by default).

Regards
Michel
Re: ERROR PLS-00428 [message #381706 is a reply to message #381701] Mon, 19 January 2009 06:20 Go to previous message
THE CHANGE
Messages: 10
Registered: January 2009
Location: in front of my computa
Junior Member
ok, thank you mike, I correct it Smile
Previous Topic: Can anyone help me (merged)
Next Topic: about sql update
Goto Forum:
  


Current Time: Thu Dec 08 00:21:15 CST 2016

Total time taken to generate the page: 0.08975 seconds