Home » SQL & PL/SQL » SQL & PL/SQL » Compilation Err in Procedure
Compilation Err in Procedure [message #337746] Fri, 01 August 2008 00:39 Go to next message
alammas
Messages: 46
Registered: July 2008
Member
HI,
I try to create procedure but it returns error Warning: Procedure created with compilation errors.
Code is Here ,Please rectify

CREATE OR REPLACE PROCEDURE "CJP"."STATUSUPDATEP" (pidvar in
varchar2) IS
cursor partialsales is select assetsinfoid from solddtbl where fp='p'
and dateofsold > (select strdate from periodtbl where periodid=pidvar);
itemcode varchar2;
begin
open partialsales;
loop
fetch partialsales into itemcode;
exit when partialsales%notfound;
--update fixed assets set status to partial sales
update fixedassetsd set fpe='p';
end loop;
close partialsales;
end statusupdate;

[Updated on: Fri, 01 August 2008 00:54] by Moderator

Report message to a moderator

Re: Compilation Err in Procedure [message #337748 is a reply to message #337746] Fri, 01 August 2008 00:40 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above
ERROR? What error?


[Updated on: Fri, 01 August 2008 00:41] by Moderator

Report message to a moderator

Re: Compilation Err in Procedure [message #337752 is a reply to message #337746] Fri, 01 August 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
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 the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

"show error"

Regards
Michel
Re: Compilation Err in Procedure [message #337770 is a reply to message #337746] Fri, 01 August 2008 02:22 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Apart from the original message being defective (no Oracle database version, unformatted code, lack of valuable information (which error is it, again?)), I believe that Alammas should be pointed into the right direction.

Reading PL/SQL User's Guide would probably help a lot; that's out of question. Perhaps you could pay attention to cursor FOR loops, which would make your code easier to read and maintain. Here's an example:
create procedure prc_test (par_deptno in number) is
begin
  for cur_r in (select empno from emp 
                where deptno = par_deptno
               )
  loop
    update emp set
      sal = sal * 1.1
      where deptno = par_deptno;        --> is your UPDATE missing the WHERE clause, perhaps?
  end loop;
end;

It does the same as your procedure, but in a "better" fashion (kind of). Take a look, study, notice and understand the differences, think it over, act!
Re: Compilation Err in Procedure [message #337777 is a reply to message #337746] Fri, 01 August 2008 03:34 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I agree with Littlefoot, a little consideration for newbies will make them feel more welcome.

Lucky for us the code provided isn't that much to read, now is it.

The fact that we as experts would write it differently is actually off topic Smile

Answering the question at hand is always more important than putting down comments/remarks and all.

So: after reading the code, without trying to understand what it does, I think the error will have to do with the name of the procedure:

CREATE OR REPLACE PROCEDURE "CJP"."STATUSUPDATEP"
...
end statusupdate;


The names don't match: STATUSUPDATEP and statusupdate

This doesn't mean that all other comments aren't valid ones, but they don't answer the question posted Smile

Re: Compilation Err in Procedure [message #337784 is a reply to message #337746] Fri, 01 August 2008 03:54 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

I agree with Littlefoot, a little consideration for newbies will make them feel more welcome.
Well said.. I believe if the same person keeps on repeating the same mistake, then he/she should not be replied back Cool

Moreover, to see the compilation error, you can use the SHOW ERROR command which can help you to trace the exact problem (atleast the coding error)... Though sometimes it doesn't gives any error, so you can make use of DBA_ERRORS/ALL_ERRORS/USER_ERRORS table to see the error description.

I hope it helps...

Re: Compilation Err in Procedure [message #337798 is a reply to message #337777] Fri, 01 August 2008 04:14 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
MarcS
Answering the question at hand is ...


OK then, here's another one: cursor variable, declared in the original procedure code, is invalid: length is missing:
itemcode varchar2;

Furthermore, it would be better to declare it as a reference to the table column datatype, such as
itemcode solddtbl.assetsinfoid%type;
Re: Compilation Err in Procedure [message #337814 is a reply to message #337777] Fri, 01 August 2008 05:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
MarcS wrote on Fri, 01 August 2008 10:34

So: after reading the code, without trying to understand what it does, I think the error will have to do with the name of the procedure:

CREATE OR REPLACE PROCEDURE "CJP"."STATUSUPDATEP"
...
end statusupdate;


The names don't match: STATUSUPDATEP and statusupdate



Although I do agree on most part of your reply, I don't agree on this part.
I think that showing this to the original poster is of no use whatsoever. It would be far more helpful to show the use of SHOW ERRORS or select from user_errors.
Those would also reveal this, since this error causes quite a descriptive error-message.
The benefit would be in the original poster being able to track it down using his own tools, instead of being bound to OraFAQ for the rest of his career.
Re: Compilation Err in Procedure [message #338469 is a reply to message #337814] Tue, 05 August 2008 03:41 Go to previous message
alammas
Messages: 46
Registered: July 2008
Member
Thank you respectable responders i got it very Clearly.
Here is another problem for me,That procedures are better at back-end level or front-end level as regard performance.
Previous Topic: Confusion Regarding Rownum
Next Topic: How to use bankers' rounding in sql
Goto Forum:
  


Current Time: Mon Dec 05 15:14:43 CST 2016

Total time taken to generate the page: 0.10576 seconds