Home » SQL & PL/SQL » SQL & PL/SQL » Compilation errors
Compilation errors [message #312654] Wed, 09 April 2008 09:49 Go to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
Hello,

Am getting compilation errors when running the following code...

CREATE FUNCTION sp_Insertion_Coda (
p_CODA_IMP_COMPTE_TIERS IN varchar(10),
p_CODA_IMP_NO_PIECE IN varchar(15) ,
p_CODA_IMP_CENTRE_ANA IN varchar(10) ,
p_CODA_IMP_DATE_COMPTA IN DATE ,
p_CODA_IMP_EXER_COMPTA IN varchar(6) ,
p_CODA_IMP_DATE_PREST IN DATE ,
p_CODA_IMP_CODE_GROUPE IN varchar(3) ,
p_CODA_IMP_LIB_ECR IN varchar(30) ,
p_CODA_IMP_CODE_RGLT IN varchar(5) ,
p_CODA_IMP_MONTANTHT IN NUMBER(15,0) ,
p_CODA_IMP_CODE_DEVISE IN varchar(3) )

RETURN NUMBER

AS
v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;

BEGIN
SELECT Seq_TAB_CODA_IMPORT_ID.nextval INTO lastCodaIDcreated;

INSERT INTO TAB_CODA_IMPORT (
CODA_IMP_ID,
CODA_IMP_COMPTE_TIERS,
CODA_IMP_NO_PIECE,
CODA_IMP_CENTRE_ANA,
CODA_IMP_DATE_COMPTA,
CODA_IMP_EXER_COMPTA,
CODA_IMP_DATE_PREST,
CODA_IMP_CODE_GROUPE,
CODA_IMP_LIB_ECR,
CODA_IMP_CODE_RGLT,
CODA_IMP_MONTANTHT,
CODA_IMP_CODE_DEVISE )
VALUES (
v_lastCodaIDcreated,
p_CODA_IMP_COMPTE_TIERS,
p_CODA_IMP_NO_PIECE,
p_CODA_IMP_CENTRE_ANA,
p_CODA_IMP_DATE_COMPTA,
p_CODA_IMP_EXER_COMPTA,
p_CODA_IMP_DATE_PREST,
p_CODA_IMP_CODE_GROUPE,
p_CODA_IMP_LIB_ECR,
p_CODA_IMP_CODE_RGLT,
p_CODA_IMP_MONTANTHT,
p_CODA_IMP_CODE_DEVISE )

RETURN (v_lastCodaIDcreated);

END;

Just can't understand why this doesn't work! Could someone please help me find out what is the problem.... please...


Many many thanks!
Sweety.
Re: Compilation errors [message #312656 is a reply to message #312654] Wed, 09 April 2008 09:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You are getting errors. Now what information do you think might be helpful to allow others to help you?
Re: Compilation errors [message #312657 is a reply to message #312654] Wed, 09 April 2008 09:55 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
It would be very helpful for us if you would have posted the compilation error(s) as well.
Re: Compilation errors [message #312659 is a reply to message #312657] Wed, 09 April 2008 09:59 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
Yeah that's right....
Am sorry.

The message goes as follows:
"Function created with compilation errors"...

In fact when I try to run the "Show Err;" command in SQL+, it says :

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/36 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

3/33 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

4/35 PLS-00103: Symbole "(" rencontré à la place d'un des symboles

LINE/COL ERROR
-------- -----------------------------------------------------------------
suivants :
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

6/36 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

8/36 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :

LINE/COL ERROR
-------- -----------------------------------------------------------------
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

9/32 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

10/34 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :
:= . ) , @ % default character

LINE/COL ERROR
-------- -----------------------------------------------------------------
Symbole ":=" a été substitué à "(" pour continuer.

11/33 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

12/36 PLS-00103: Symbole "(" rencontré à la place d'un des symboles
suivants :
:= . ) , @ % default character
Symbole ":=" a été substitué à "(" pour continuer.

LINE/COL ERROR
-------- -----------------------------------------------------------------

20/63 PLS-00103: Symbole ";" rencontré à la place d'un des symboles
suivants :
. ( , % from

49/31 PLS-00103: Symbole ";" rencontré à la place d'un des symboles
suivants :
, * & - + / at mod rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
into || bulk year DAY_
Re: Compilation errors [message #312661 is a reply to message #312654] Wed, 09 April 2008 10:04 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
CREATE FUNCTION sp_Insertion_Coda (
	p_CODA_IMP_COMPTE_TIERS IN varchar(10),
	p_CODA_IMP_NO_PIECE 	IN varchar(15) , 
	p_CODA_IMP_CENTRE_ANA 	IN varchar(10) , 
	p_CODA_IMP_DATE_COMPTA 	IN DATE  , 
	p_CODA_IMP_EXER_COMPTA 	IN varchar(6) , 
	p_CODA_IMP_DATE_PREST 	IN DATE , 
	p_CODA_IMP_CODE_GROUPE 	IN varchar(3)  , 
	p_CODA_IMP_LIB_ECR 	IN varchar(30) , 
	p_CODA_IMP_CODE_RGLT 	IN varchar(5) , 
	p_CODA_IMP_MONTANTHT 	IN NUMBER(15,0) , 
	p_CODA_IMP_CODE_DEVISE 	IN varchar(3) )


You don't have to - even may not - specify length & precision in a function-header

CREATE OR REPLACE FUNCTION sp_Insertion_Coda (
	p_CODA_IMP_COMPTE_TIERS IN varchar),
	p_CODA_IMP_NO_PIECE 	IN varchar) , 
	p_CODA_IMP_CENTRE_ANA 	IN varchar) , 
	p_CODA_IMP_DATE_COMPTA 	IN DATE  , 
	p_CODA_IMP_EXER_COMPTA 	IN varchar , 
	p_CODA_IMP_DATE_PREST 	IN DATE , 
	p_CODA_IMP_CODE_GROUPE 	IN varchar  , 
	p_CODA_IMP_LIB_ECR 	IN varchar , 
	p_CODA_IMP_CODE_RGLT 	IN varchar , 
	p_CODA_IMP_MONTANTHT 	IN NUMBER , 
	p_CODA_IMP_CODE_DEVISE 	IN varchar )

Re: Compilation errors [message #312662 is a reply to message #312659] Wed, 09 April 2008 10:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
1. Do not set the size of a parameter.
2. Use Varchar2 not Varchar
3. SELECT Seq_TAB_CODA_IMPORT_ID.nextval INTO lastCodaIDcreated;
In Oracle we need to select FROM something

4. To assign a value to avariable:
variable name := value, therfore make
SELECT Seq_TAB_CODA_IMPORT_ID.nextval INTO lastCodaIDcreated;

lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval;

5. When posting code please enclose it in code tags (see the forum guidelines for advice)

That's all I've got for now.
Re: Compilation errors [message #312669 is a reply to message #312662] Wed, 09 April 2008 10:19 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
Thank you both for your pieces of advice. I tried to correct my code but am still getting compilation errors. The thing is that a friend a mine had tried to process the same code and he didn't have any trouble with it...
Re: Compilation errors [message #312672 is a reply to message #312669] Wed, 09 April 2008 10:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And since you posted neither the changed code (with added CODE tags to make it readable) nor the error messages, we have no way of knowing whether you accidentally misspelled

CREATE FUNCTION sp_Insertion_Coda


as

my hovercraft is full of eels


or not.
Re: Compilation errors [message #312673 is a reply to message #312669] Wed, 09 April 2008 10:28 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Then either it's not the same code or your friend lied to you.
Post the code that you used with the changes included and the errors. Remember to enclose the code in code tags.
Re: Compilation errors [message #312684 is a reply to message #312673] Wed, 09 April 2008 10:44 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
hmmm.. the corrected code I used :

CREATE FUNCTION sp_Insertion_Coda (
p_CODA_IMP_COMPTE_TIERS IN varchar2,
p_CODA_IMP_NO_PIECE  IN varchar2, 
p_CODA_IMP_CENTRE_ANA  IN varchar2, 
p_CODA_IMP_DATE_COMPTA  IN DATE, 
p_CODA_IMP_EXER_COMPTA  IN varchar2, 
p_CODA_IMP_DATE_PREST  IN DATE, 
p_CODA_IMP_CODE_GROUPE  IN varchar2, 
p_CODA_IMP_LIB_ECR  IN varchar2, 
p_CODA_IMP_CODE_RGLT  IN varchar2, 
p_CODA_IMP_MONTANTHT  IN NUMBER, 
p_CODA_IMP_CODE_DEVISE  IN varchar2)

RETURN NUMBER 
 
AS
v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;

BEGIN
lastCodaIDcreated = Seq_TAB_CODA_IMPORT_ID.nextval ;
 
INSERT INTO TAB_CODA_IMPORT (
CODA_IMP_ID,
CODA_IMP_COMPTE_TIERS, 
CODA_IMP_NO_PIECE, 
CODA_IMP_CENTRE_ANA, 
CODA_IMP_DATE_COMPTA, 
CODA_IMP_EXER_COMPTA, 
CODA_IMP_DATE_PREST, 
CODA_IMP_CODE_GROUPE, 
CODA_IMP_LIB_ECR, 
CODA_IMP_CODE_RGLT, 
CODA_IMP_MONTANTHT, 
CODA_IMP_CODE_DEVISE )
VALUES (
v_lastCodaIDcreated,
p_CODA_IMP_COMPTE_TIERS, 
p_CODA_IMP_NO_PIECE, 
p_CODA_IMP_CENTRE_ANA, 
p_CODA_IMP_DATE_COMPTA, 
p_CODA_IMP_EXER_COMPTA, 
p_CODA_IMP_DATE_PREST, 
p_CODA_IMP_CODE_GROUPE, 
p_CODA_IMP_LIB_ECR, 
p_CODA_IMP_CODE_RGLT, 
p_CODA_IMP_MONTANTHT, 
p_CODA_IMP_CODE_DEVISE ) 

RETURN (v_lastCodaIDcreated);
   
END;
/


and the message is the same "Function creation with compilation errors".

But when I try "show err;", I now get:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/21    PLS-00103: Symbole "=" rencontré à la place d'un des symboles
         suivants :
         := . ( @ % ;

22/3     PLS-00103: Symbole "INSERT" rencontré
49/31    PLS-00103: Symbole ";" rencontré à la place d'un des symboles
         suivants :
         , * & - + / at mod rem <an identifier>
         <a double-quoted delimited-identifier> <an exponent (**)> as
         into || bulk year DAY_
Re: Compilation errors [message #312687 is a reply to message #312684] Wed, 09 April 2008 10:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thank you for formatting. Wink

pablolee's example Code :

lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval;


Your code :

lastCodaIDcreated = Seq_TAB_CODA_IMPORT_ID.nextval;


See the difference?

[Updated on: Wed, 09 April 2008 10:49]

Report message to a moderator

Re: Compilation errors [message #312690 is a reply to message #312687] Wed, 09 April 2008 10:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Which happens to be one of my own most common typos. Smile
Re: Compilation errors [message #312692 is a reply to message #312690] Wed, 09 April 2008 10:57 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
Yep! Got it!
But then still some errors...

SQL> CREATE FUNCTION sp_Insertion_Coda (
  2  p_CODA_IMP_COMPTE_TIERS IN varchar2,
  3  p_CODA_IMP_NO_PIECE  IN varchar2, 
  4  p_CODA_IMP_CENTRE_ANA  IN varchar2, 
  5  p_CODA_IMP_DATE_COMPTA  IN DATE, 
  6  p_CODA_IMP_EXER_COMPTA  IN varchar2, 
  7  p_CODA_IMP_DATE_PREST  IN DATE, 
  8  p_CODA_IMP_CODE_GROUPE  IN varchar2, 
  9  p_CODA_IMP_LIB_ECR  IN varchar2, 
 10  p_CODA_IMP_CODE_RGLT  IN varchar2, 
 11  p_CODA_IMP_MONTANTHT  IN NUMBER, 
 12  p_CODA_IMP_CODE_DEVISE  IN varchar2)
 13  
 14  RETURN NUMBER 
 15   
 16  AS
 17  v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;
 18  
 19  BEGIN
 20  lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval ;
 21   
 22  INSERT INTO TAB_CODA_IMPORT (
 23  CODA_IMP_ID,
 24  CODA_IMP_COMPTE_TIERS, 
 25  CODA_IMP_NO_PIECE, 
 26  CODA_IMP_CENTRE_ANA, 
 27  CODA_IMP_DATE_COMPTA, 
 28  CODA_IMP_EXER_COMPTA, 
 29  CODA_IMP_DATE_PREST, 
 30  CODA_IMP_CODE_GROUPE, 
 31  CODA_IMP_LIB_ECR, 
 32  CODA_IMP_CODE_RGLT, 
 33  CODA_IMP_MONTANTHT, 
 34  CODA_IMP_CODE_DEVISE )
 35  VALUES (
 36  v_lastCodaIDcreated,
 37  p_CODA_IMP_COMPTE_TIERS, 
 38  p_CODA_IMP_NO_PIECE, 
 39  p_CODA_IMP_CENTRE_ANA, 
 40  p_CODA_IMP_DATE_COMPTA, 
 41  p_CODA_IMP_EXER_COMPTA, 
 42  p_CODA_IMP_DATE_PREST, 
 43  p_CODA_IMP_CODE_GROUPE, 
 44  p_CODA_IMP_LIB_ECR, 
 45  p_CODA_IMP_CODE_RGLT, 
 46  p_CODA_IMP_MONTANTHT, 
 47  p_CODA_IMP_CODE_DEVISE ) 
 48  
 49  RETURN (v_lastCodaIDcreated);
 50     
 51  END;
 52  /


Avertissement : Fonction créée avec erreurs de compilation.

SQL> show err;
Erreurs pour FUNCTION SP_INSERTION_CODA :

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/1     PL/SQL: SQL Statement ignored
49/29    PL/SQL: ORA-00925: Mot-clé INTO absent
SQL> 

Re: Compilation errors [message #312694 is a reply to message #312692] Wed, 09 April 2008 11:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Missing semi colon at line 47
Re: Compilation errors [message #312695 is a reply to message #312690] Wed, 09 April 2008 11:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
@pablolee :
Yep. Mine, too. Closely followed by writing "form" instead of "from". Very Happy

@sweety83 :
There is an insert statement. At the very end of that insert statement there is a ";" missing.
Re: Compilation errors [message #312697 is a reply to message #312695] Wed, 09 April 2008 11:14 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
Right!
Corrected that too...

SQL> CREATE FUNCTION sp_Insertion_Coda (
  2  p_CODA_IMP_COMPTE_TIERS IN varchar2,
  3  p_CODA_IMP_NO_PIECE  IN varchar2, 
  4  p_CODA_IMP_CENTRE_ANA  IN varchar2, 
  5  p_CODA_IMP_DATE_COMPTA  IN DATE, 
  6  p_CODA_IMP_EXER_COMPTA  IN varchar2, 
  7  p_CODA_IMP_DATE_PREST  IN DATE, 
  8  p_CODA_IMP_CODE_GROUPE  IN varchar2, 
  9  p_CODA_IMP_LIB_ECR  IN varchar2, 
 10  p_CODA_IMP_CODE_RGLT  IN varchar2, 
 11  p_CODA_IMP_MONTANTHT  IN NUMBER, 
 12  p_CODA_IMP_CODE_DEVISE  IN varchar2)
 13  
 14  RETURN NUMBER 
 15   
 16  AS
 17  v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;
 18  
 19  BEGIN
 20  lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval ;
 21   
 22  INSERT INTO TAB_CODA_IMPORT (
 23  CODA_IMP_ID,
 24  CODA_IMP_COMPTE_TIERS, 
 25  CODA_IMP_NO_PIECE, 
 26  CODA_IMP_CENTRE_ANA, 
 27  CODA_IMP_DATE_COMPTA, 
 28  CODA_IMP_EXER_COMPTA, 
 29  CODA_IMP_DATE_PREST, 
 30  CODA_IMP_CODE_GROUPE, 
 31  CODA_IMP_LIB_ECR, 
 32  CODA_IMP_CODE_RGLT, 
 33  CODA_IMP_MONTANTHT, 
 34  CODA_IMP_CODE_DEVISE )
 35  VALUES (
 36  v_lastCodaIDcreated,
 37  p_CODA_IMP_COMPTE_TIERS, 
 38  p_CODA_IMP_NO_PIECE, 
 39  p_CODA_IMP_CENTRE_ANA, 
 40  p_CODA_IMP_DATE_COMPTA, 
 41  p_CODA_IMP_EXER_COMPTA, 
 42  p_CODA_IMP_DATE_PREST, 
 43  p_CODA_IMP_CODE_GROUPE, 
 44  p_CODA_IMP_LIB_ECR, 
 45  p_CODA_IMP_CODE_RGLT, 
 46  p_CODA_IMP_MONTANTHT, 
 47  p_CODA_IMP_CODE_DEVISE ); 
 48  
 49  RETURN (v_lastCodaIDcreated);
 50     
 51  END;
 52  /

Avertissement : Fonction créée avec erreurs de compilation.

SQL> show err;
Erreurs pour FUNCTION SP_INSERTION_CODA :

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/1     PLS-00201: l'identificateur 'LASTCODAIDCREATED' doit être déclaré
20/1     PL/SQL: Statement ignored


Guess that I need to declare the "lastCodaIDcreated" ....
Re: Compilation errors [message #312700 is a reply to message #312654] Wed, 09 April 2008 11:17 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Guess that I need to declare the "lastCodaIDcreated" ....
Only if you want to use it.
Re: Compilation errors [message #312703 is a reply to message #312700] Wed, 09 April 2008 11:25 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
Ok!
Thanks a lot to everyone...
Re: Compilation errors [message #312704 is a reply to message #312697] Wed, 09 April 2008 11:26 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
It amazes me how many eyes missed such a minor detail...


blah blah blah...

 16  AS
 17  v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;
 18  
 19  BEGIN
 20  lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval;

yada yada yada...


Okay, does anyone see a problem with this assignment and whether it matches the defined variable?

In fact...

SELECT Seq_TAB_CODA_IMPORT_ID.nextval INTO lastCodaIDcreated;


Would probably have worked too (I would add "from dual" because you have to have a from clause) if you were putting the value into a variable you actually defined.

Regards,
Ron
Re: Compilation errors [message #312709 is a reply to message #312704] Wed, 09 April 2008 11:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think nobody is really looking for details like that, since the compiler will find them sooner or later anyway, when all the other errors are cleared up that prevented it from parsing that far.

So only when the compiler complains about lastCodaIDcreated not being declared people take a second look at the actual spelling.

Re: Compilation errors [message #312711 is a reply to message #312704] Wed, 09 April 2008 11:37 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Okay, does anyone see a problem with this assignment and whether it matches the defined variable?... blah blah


It amazes me that you missed my post addressing this (5th or 6th post point 3 and 4) And
Quote:
blah blah Would probably have worked too (I would add "from dual" because you have to have a from clause) if you were putting the value into a variable you actually defined

It would've worked yes, but it would also have been a bit pointless to code a context switch when none was required.
Re: Compilation errors [message #312718 is a reply to message #312704] Wed, 09 April 2008 11:47 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
I did see that too Ron
I mean, correcting
lastCodaIDcreated := seq_TAB_CODA_IMPORT_ID.nextval;

and putting
v_lastCodaIDcreated := seq_TAB_CODA_IMPORT_ID.nextval;

instead. It still didn't work. But now I've corrected my code again buy adding the "from dual" statement and removing the details concerning the size of the parameters and adding the missing ";"....
and now IT WORKS!!!!!

Thank you, thank you, thank you to all!!!!

Here's the correct code!!!

CREATE FUNCTION sp_Insertion_Coda (
	p_CODA_IMP_COMPTE_TIERS IN varchar2,
	p_CODA_IMP_NO_PIECE 	IN varchar2, 
	p_CODA_IMP_CENTRE_ANA 	IN varchar2, 
	p_CODA_IMP_DATE_COMPTA 	IN DATE, 
	p_CODA_IMP_EXER_COMPTA 	IN varchar2, 
	p_CODA_IMP_DATE_PREST 	IN DATE, 
	p_CODA_IMP_CODE_GROUPE 	IN varchar2, 
	p_CODA_IMP_LIB_ECR 	IN varchar2, 
	p_CODA_IMP_CODE_RGLT 	IN varchar2, 
	p_CODA_IMP_MONTANTHT 	IN NUMBER, 
	p_CODA_IMP_CODE_DEVISE 	IN varchar2)

	RETURN NUMBER 
	
	AS
		v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;

	BEGIN
		SELECT Seq_TAB_CODA_IMPORT_ID.nextval INTO v_lastCodaIDcreated from dual;
			
		INSERT INTO TAB_CODA_IMPORT (
			CODA_IMP_ID,
			CODA_IMP_COMPTE_TIERS, 
			CODA_IMP_NO_PIECE, 
			CODA_IMP_CENTRE_ANA, 
			CODA_IMP_DATE_COMPTA, 
			CODA_IMP_EXER_COMPTA, 
			CODA_IMP_DATE_PREST, 
			CODA_IMP_CODE_GROUPE, 
			CODA_IMP_LIB_ECR, 
			CODA_IMP_CODE_RGLT, 
			CODA_IMP_MONTANTHT, 
			CODA_IMP_CODE_DEVISE )
			VALUES (
				v_lastCodaIDcreated,
				p_CODA_IMP_COMPTE_TIERS, 
				p_CODA_IMP_NO_PIECE, 
				p_CODA_IMP_CENTRE_ANA, 
				p_CODA_IMP_DATE_COMPTA, 
				p_CODA_IMP_EXER_COMPTA, 
				p_CODA_IMP_DATE_PREST, 
				p_CODA_IMP_CODE_GROUPE, 
				p_CODA_IMP_LIB_ECR, 
				p_CODA_IMP_CODE_RGLT, 
				p_CODA_IMP_MONTANTHT, 
				p_CODA_IMP_CODE_DEVISE );	

		RETURN (v_lastCodaIDcreated);
			
	END;


yep, guess my friend lied to me... snif Confused

Best regards,
Sweety.
Re: Compilation errors [message #312719 is a reply to message #312718] Wed, 09 April 2008 11:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
SELECT Seq_TAB_CODA_IMPORT_ID.nextval INTO v_lastCodaIDcreated from dual;

Do NOT do this. You are introducing a pointless and unneccessary context switch into your code.
a simple

v_lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval
is the correct way to populate this variable.
Re: Compilation errors [message #312723 is a reply to message #312719] Wed, 09 April 2008 11:54 Go to previous messageGo to next message
sweety83
Messages: 10
Registered: April 2008
Junior Member
I tried what u told me but then I get other errors:

DROP FUNCTION sp_Insertion_Coda;

CREATE FUNCTION sp_Insertion_Coda (
	p_CODA_IMP_COMPTE_TIERS IN varchar2,
	p_CODA_IMP_NO_PIECE 	IN varchar2, 
	p_CODA_IMP_CENTRE_ANA 	IN varchar2, 
	p_CODA_IMP_DATE_COMPTA 	IN DATE, 
	p_CODA_IMP_EXER_COMPTA 	IN varchar2, 
	p_CODA_IMP_DATE_PREST 	IN DATE, 
	p_CODA_IMP_CODE_GROUPE 	IN varchar2, 
	p_CODA_IMP_LIB_ECR 	IN varchar2, 
	p_CODA_IMP_CODE_RGLT 	IN varchar2, 
	p_CODA_IMP_MONTANTHT 	IN NUMBER, 
	p_CODA_IMP_CODE_DEVISE 	IN varchar2)

	RETURN NUMBER 
	
	AS
		v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;

	BEGIN
		v_lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval;
			
		INSERT INTO TAB_CODA_IMPORT (
			CODA_IMP_ID,
			CODA_IMP_COMPTE_TIERS, 
			CODA_IMP_NO_PIECE, 
			CODA_IMP_CENTRE_ANA, 
			CODA_IMP_DATE_COMPTA, 
			CODA_IMP_EXER_COMPTA, 
			CODA_IMP_DATE_PREST, 
			CODA_IMP_CODE_GROUPE, 
			CODA_IMP_LIB_ECR, 
			CODA_IMP_CODE_RGLT, 
			CODA_IMP_MONTANTHT, 
			CODA_IMP_CODE_DEVISE )
			VALUES (
				v_lastCodaIDcreated,
				p_CODA_IMP_COMPTE_TIERS, 
				p_CODA_IMP_NO_PIECE, 
				p_CODA_IMP_CENTRE_ANA, 
				p_CODA_IMP_DATE_COMPTA, 
				p_CODA_IMP_EXER_COMPTA, 
				p_CODA_IMP_DATE_PREST, 
				p_CODA_IMP_CODE_GROUPE, 
				p_CODA_IMP_LIB_ECR, 
				p_CODA_IMP_CODE_RGLT, 
				p_CODA_IMP_MONTANTHT, 
				p_CODA_IMP_CODE_DEVISE );	

		RETURN (v_lastCodaIDcreated);
			
	END;



LINE/COL ERROR
-------- -----------------------------------------------------------------
20/3     PL/SQL: Statement ignored
20/49    PLS-00357: Référence de Table, Vue ou Séquence
         'SEQ_TAB_CODA_IMPORT_ID.NEXTVAL' non autorisée dans ce contexte
Re: Compilation errors [message #312725 is a reply to message #312723] Wed, 09 April 2008 11:57 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
That is because I am an idiot and you cannot do this. Complete brain meltdown there. My apologies to both you and Ronald.
Re: Compilation errors [message #312790 is a reply to message #312654] Wed, 09 April 2008 14:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> That is because I am an idiot and you cannot do this.

Only before 11g, at least based on this example.
Re: Compilation errors [message #312791 is a reply to message #312725] Wed, 09 April 2008 14:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
It is a new feature of 11g that you can now assign sequence.nextval without selecting into, as demonstrated below. So, the friend with no problem executing the code is probably using 11g and pablolee is just ahead of his version.

SCOTT@orcl_11g> CREATE TABLE TAB_CODA_IMPORT (
  2  			     CODA_IMP_ID	    NUMBER,
  3  			     CODA_IMP_COMPTE_TIERS  VARCHAR2 (1),
  4  			     CODA_IMP_NO_PIECE	    VARCHAR2 (1),
  5  			     CODA_IMP_CENTRE_ANA    VARCHAR2 (1),
  6  			     CODA_IMP_DATE_COMPTA   DATE,
  7  			     CODA_IMP_EXER_COMPTA   VARCHAR2 (1),
  8  			     CODA_IMP_DATE_PREST    DATE,
  9  			     CODA_IMP_CODE_GROUPE   VARCHAR2 (1),
 10  			     CODA_IMP_LIB_ECR	    VARCHAR2 (1),
 11  			     CODA_IMP_CODE_RGLT     VARCHAR2 (1),
 12  			     CODA_IMP_MONTANTHT     NUMBER,
 13  			     CODA_IMP_CODE_DEVISE   VARCHAR2 (1))
 14  /

Table created.

SCOTT@orcl_11g> CREATE SEQUENCE Seq_TAB_CODA_IMPORT_ID
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE FUNCTION sp_Insertion_Coda (
  2  	     p_CODA_IMP_COMPTE_TIERS IN varchar2,
  3  	     p_CODA_IMP_NO_PIECE     IN varchar2,
  4  	     p_CODA_IMP_CENTRE_ANA   IN varchar2,
  5  	     p_CODA_IMP_DATE_COMPTA  IN DATE,
  6  	     p_CODA_IMP_EXER_COMPTA  IN varchar2,
  7  	     p_CODA_IMP_DATE_PREST   IN DATE,
  8  	     p_CODA_IMP_CODE_GROUPE  IN varchar2,
  9  	     p_CODA_IMP_LIB_ECR      IN varchar2,
 10  	     p_CODA_IMP_CODE_RGLT    IN varchar2,
 11  	     p_CODA_IMP_MONTANTHT    IN NUMBER,
 12  	     p_CODA_IMP_CODE_DEVISE  IN varchar2)
 13  
 14  	     RETURN NUMBER
 15  
 16  	     AS
 17  		     v_lastCodaIDcreated TAB_CODA_IMPORT.CODA_IMP_ID%TYPE;
 18  
 19  	     BEGIN
 20  		     v_lastCodaIDcreated := Seq_TAB_CODA_IMPORT_ID.nextval;
 21  
 22  		     INSERT INTO TAB_CODA_IMPORT (
 23  			     CODA_IMP_ID,
 24  			     CODA_IMP_COMPTE_TIERS,
 25  			     CODA_IMP_NO_PIECE,
 26  			     CODA_IMP_CENTRE_ANA,
 27  			     CODA_IMP_DATE_COMPTA,
 28  			     CODA_IMP_EXER_COMPTA,
 29  			     CODA_IMP_DATE_PREST,
 30  			     CODA_IMP_CODE_GROUPE,
 31  			     CODA_IMP_LIB_ECR,
 32  			     CODA_IMP_CODE_RGLT,
 33  			     CODA_IMP_MONTANTHT,
 34  			     CODA_IMP_CODE_DEVISE )
 35  			     VALUES (
 36  				     v_lastCodaIDcreated,
 37  				     p_CODA_IMP_COMPTE_TIERS,
 38  				     p_CODA_IMP_NO_PIECE,
 39  				     p_CODA_IMP_CENTRE_ANA,
 40  				     p_CODA_IMP_DATE_COMPTA,
 41  				     p_CODA_IMP_EXER_COMPTA,
 42  				     p_CODA_IMP_DATE_PREST,
 43  				     p_CODA_IMP_CODE_GROUPE,
 44  				     p_CODA_IMP_LIB_ECR,
 45  				     p_CODA_IMP_CODE_RGLT,
 46  				     p_CODA_IMP_MONTANTHT,
 47  				     p_CODA_IMP_CODE_DEVISE );
 48  
 49  		     RETURN (v_lastCodaIDcreated);
 50  
 51  	     END;
 52  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE new_id_from_seq NUMBER
SCOTT@orcl_11g> EXEC :new_id_from_seq := sp_insertion_coda ('a', 'b', 'c', SYSDATE, 'd', SYSDATE, 'e', 'f', 'g', 2, 'h')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT new_id_from_seq

NEW_ID_FROM_SEQ
---------------
              1

SCOTT@orcl_11g> SELECT * FROM TAB_CODA_IMPORT
  2  /

CODA_IMP_ID C C C CODA_IMP_ C CODA_IMP_ C C C CODA_IMP_MONTANTHT C
----------- - - - --------- - --------- - - - ------------------ -
          1 a b c 09-APR-08 d 09-APR-08 e f g                  2 h

SCOTT@orcl_11g>

Re: Compilation errors [message #312939 is a reply to message #312725] Thu, 10 April 2008 03:04 Go to previous message
sweety83
Messages: 10
Registered: April 2008
Junior Member
> That is because I am an idiot and you cannot do this.

It's ok pablolee. Don't you worry... the other members and you have helped me a lot in resolving my problem!

To Barbara and flyboy: Yep, I do believe my friend and I don't have the same version. Thank you for this explanation!

Best regards!
Sweety.
Previous Topic: FN return error : invalid identifier
Next Topic: why order by is not wroking in pl/sql or form.
Goto Forum:
  


Current Time: Sat Dec 03 01:25:15 CST 2016

Total time taken to generate the page: 0.10392 seconds