Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure compilation error
Stored procedure compilation error [message #306094] Wed, 12 March 2008 15:23 Go to next message
RiverX
Messages: 9
Registered: March 2008
Junior Member
Hi all,

I have the following stored procedure and i have no idea what is wrong with it. The first part uptil the insert compiled successfully but when i add the insert part it give me a compilation error.

Here is the Procedure

CREATE OR REPLACE PROCEDURE EV_Update
(p_CERTNUM IN VARCHAR2, p_ELECTION IN VARCHAR2, p_PRECINCT IN NUMBER, 
p_GRP IN VARCHAR2, p_SEQ_STYLE IN VARCHAR2, p_SEL_CODE IN VARCHAR2, 
p_PARTY IN VARCHAR2, p_EARLY_LOC IN VARCHAR2, 
p_DT_VOTE IN DATE,p_APPNUM IN NUMBER) 
IS
BEGIN
UPDATE evhist 
SET CERTNUM = p_CERTNUM, ELECTION = p_ELECTION, 
PRECINCT = p_PRECINCT, GRP = p_GRP, SEQ_STYLE = p_SEQ_STYLE, 
SEL_CODE = p_SEL_CODE, PARTYCODE = p_PARTY, EARLY_LOC = p_EARLY_LOC, 
DT_VOTE = p_DT_VOTE, APPNUM = p_APPNUM 
WHERE (CERTNUM = p_CERTNUM AND ELECTION = p_ELECTION);

IF (SQL%ROWCOUNT = 0 ) THEN
INSERT INTO evhist 
(CERTNUM, ELECTION, PRECINCT, GRP, SEQ_STYLE,SEL_CODE,  PARTYCODE, EARLY_LOC, DT_VOTE, APPNUM) 
VALUES 
(p_CERTNUM, p_ELECTION, p_PRECINCT, 
p_GRP,p_SEQ_STYLE,p_SEL_CODE, p_PARTYCODE, 
p_EARLY_LOC, p_DT_VOTE, p_APPNUM);

END IF;
END EV_Update;
/



Thanks alot
Re: Stored procedure compilation error [message #306095 is a reply to message #306094] Wed, 12 March 2008 15:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is no error in your post
2/ Can you explain how is this an expert question?

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them.

Regards
Michel
Re: Stored procedure compilation error [message #306099 is a reply to message #306095] Wed, 12 March 2008 16:09 Go to previous messageGo to next message
RiverX
Messages: 9
Registered: March 2008
Junior Member
My bad i posted in a haste without reading the rules.
Maybe the moderators can move this to the newbie section?
I apologize once again.


Take care
Re: Stored procedure compilation error [message #306139 is a reply to message #306094] Thu, 13 March 2008 01:08 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

No errors in your code.
better to post the error message you got when compilation. So,that we can get an idea about it.
Re: Stored procedure compilation error [message #306251 is a reply to message #306094] Thu, 13 March 2008 07:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Looks to me like this should be a MERGE statement.
Re: Stored procedure compilation error [message #306259 is a reply to message #306094] Thu, 13 March 2008 08:38 Go to previous messageGo to next message
RiverX
Messages: 9
Registered: March 2008
Junior Member
how would i display the compilation errors in detail?
I tried prompt: and then the code and i got

SQL> prompt: CREATE OR REPLACE PROCEDURE EV_Update
: CREATE OR REPLACE PROCEDURE EV_Update
SQL> (p_CERTNUM IN VARCHAR2, p_ELECTION IN VARCHAR2, p_PRECINCT IN NUMBER, 
  2  p_GRP IN VARCHAR2, p_SEQ_STYLE IN VARCHAR2, p_SEL_CODE IN VARCHAR2, 
  3  p_PARTY IN VARCHAR2, p_EARLY_LOC IN VARCHAR2, 
  4  p_DT_VOTE IN DATE,p_APPNUM IN NUMBER) 
  5  IS
  6  BEGIN
  7  UPDATE evhist 
  8  SET CERTNUM = p_CERTNUM, ELECTION = p_ELECTION, 
  9  PRECINCT = p_PRECINCT, GRP = p_GRP, SEQ_STYLE = p_SEQ_STYLE, 
 10  SEL_CODE = p_SEL_CODE, PARTYCODE = p_PARTY, EARLY_LOC = p_EARLY_LOC, 
 11  DT_VOTE = p_DT_VOTE, APPNUM = p_APPNUM 
 12  WHERE (CERTNUM = p_CERTNUM AND ELECTION = p_ELECTION);
(p_CERTNUM IN VARCHAR2, p_ELECTION IN VARCHAR2, p_PRECINCT IN NUMBER,
 *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> 
SQL> IF (SQL%ROWCOUNT = 0 ) THEN
SP2-0734: unknown command beginning "IF (SQL%RO..." - rest of line ignored.
SQL> INSERT INTO evhist 
  2  (CERTNUM, ELECTION, PRECINCT, GRP, SEQ_STYLE,SEL_CODE,  PARTYCODE, EARLY_LOC, DT_VOTE, APPNUM) 

  3  VALUES 
  4  (p_CERTNUM, p_ELECTION, p_PRECINCT, 
  5  p_GRP,p_SEQ_STYLE,p_SEL_CODE, p_PARTYCODE, 
  6  p_EARLY_LOC, p_DT_VOTE, p_APPNUM);
p_EARLY_LOC, p_DT_VOTE, p_APPNUM)
                        *
ERROR at line 6:
ORA-00984: column not allowed here


SQL> 
SQL> END IF;
SP2-0042: unknown command "END IF" - rest of line ignored.
SQL> END EV_Update;
SP2-0734: unknown command beginning "END EV_Upd..." - rest of line ignored.



is the merge statement better performance wise? Cause this will be executing for a bunch of records at once.

take care

[Updated on: Thu, 13 March 2008 08:52]

Report message to a moderator

Re: Stored procedure compilation error [message #306294 is a reply to message #306259] Thu, 13 March 2008 10:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Do not use prompt. Use:

SQL> SHOW ERRORS PROCEDURE ev_update

Merge is better.
Re: Stored procedure compilation error [message #306302 is a reply to message #306259] Thu, 13 March 2008 10:44 Go to previous messageGo to next message
josephbautista
Messages: 3
Registered: March 2008
Location: Pasadena, CA
Junior Member
Programming can be hard Smile

It looks, though, like you forgot to include the END statement at close out your procedure.

Joe

[Updated on: Thu, 13 March 2008 10:45]

Report message to a moderator

Re: Stored procedure compilation error [message #306330 is a reply to message #306094] Thu, 13 March 2008 12:58 Go to previous messageGo to next message
RiverX
Messages: 9
Registered: March 2008
Junior Member
SQL> CREATE OR REPLACE PROCEDURE EV_Update
  2  (p_CERTNUM IN VARCHAR2, 
  3  p_ELECTION IN VARCHAR2, 
  4  p_PRECINCT IN NUMBER, 
  5  p_GRP IN VARCHAR2, 
  6  p_SEQ_STYLE IN VARCHAR2, 
  7  p_SEL_CODE IN VARCHAR2, 
  8  p_PARTY IN VARCHAR2, 
  9  p_EARLY_LOC IN VARCHAR2, 
 10  p_DT_VOTE IN DATE,
 11  p_APPNUM IN NUMBER) 
 12  IS
 13  BEGIN
 14  UPDATE evhist 
 15  SET CERTNUM = p_CERTNUM, ELECTION = p_ELECTION, 
 16  PRECINCT = p_PRECINCT, GRP = p_GRP, SEQ_STYLE = p_SEQ_STYLE, 
 17  SEL_CODE = p_SEL_CODE, PARTYCODE = p_PARTY, EARLY_LOC = p_EARLY_LOC, 
 18  DT_VOTE = p_DT_VOTE, APPNUM = p_APPNUM 
 19  WHERE (CERTNUM = p_CERTNUM AND ELECTION = p_ELECTION);
 20  
 21  IF (SQL%ROWCOUNT = 0 ) THEN
 22  INSERT INTO evhist 
 23  (CERTNUM, ELECTION, PRECINCT, GRP, SEQ_STYLE,SEL_CODE,  PARTYCODE, EARLY_LOC, DT_VOTE, APPNUM) 

 24  VALUES 
 25  (p_CERTNUM, p_ELECTION, p_PRECINCT, 
 26  p_GRP,p_SEQ_STYLE,p_SEL_CODE, p_PARTYCODE, 
 27  p_EARLY_LOC, p_DT_VOTE, p_APPNUM);
 28  END IF;
 29  END;
 30  /

Warning: Procedure created with compilation errors.

SQL> show errors procedure ev_update;
Errors for PROCEDURE EV_UPDATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/1     PL/SQL: SQL Statement ignored
26/31    PL/SQL: ORA-00984: column not allowed here


That is what i got when i did show errors on the procedure..

Still confused. I also tried the merge into and it confused me even more lol.

Take care and thanks

[Updated on: Thu, 13 March 2008 12:59]

Report message to a moderator

Re: Stored procedure compilation error [message #306331 is a reply to message #306330] Thu, 13 March 2008 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
p_PARTYCODE should be p_PARTY, doesn't it?

Regards
Michel
Re: Stored procedure compilation error [message #306332 is a reply to message #306330] Thu, 13 March 2008 13:27 Go to previous message
RiverX
Messages: 9
Registered: March 2008
Junior Member
Doh! I am such an idiot.
Thanks Michel!
Previous Topic: Question with an Analytical Query.
Next Topic: is this a bug? (about analytic function) and "order by" in partitioning clause (merged)
Goto Forum:
  


Current Time: Tue Dec 06 06:39:30 CST 2016

Total time taken to generate the page: 0.16705 seconds