Home » SQL & PL/SQL » SQL & PL/SQL » Compilation unit analysis terminated error
Compilation unit analysis terminated error [message #392614] Wed, 18 March 2009 11:33 Go to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
i have the following code and keep getting the following errors:

0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00304 cannot compile body of 'manage_students' without its sepcs.
1/14 PLS-00905: object student.manage_students is invalid

I researched the internet and found the following possible resolutions:
1. VARCHAR2(100) should be just VARCHAR2
2.ORACLE_HOME and/or NLS_LANG
environment variables. These need to be set for your user account
running the web server BEFORE the web server is started. Don't try to
set these in your php script or you'll get strange results.

as far as 1. is concerned i do not have any VARCHAR2 in the code
2. i have no idea how to do it, besides it could be wrong and i do not wan to mess around with environment variables

please help.
thank you Smile


p.S. the code is straight from the text book

create or replace package BODY manage_students
as
	procedure find_sname
	(i_student_id IN student.student_id%TYPE,
	o_first_name OUT student.first_name%TYPE,
	o_last_name OUT student.last_name%TYPE)
	IS
	v_student_id student.student_id%TYPE;
	BEGIN
		select first_name, last_name
			into o_first_name, o_last_name
			from student
			where student_id = i_student_id;
	exception
		when others
		then
			DBMS_OUTPUT.PUT_LINE('Error in finding student_id: '||v_student_id);
	end find_sname;
	
	function id_is_good
		(i_student_id  in student.i_student_id%TYPE)
		return BOOLEAN
	IS
		v_id_cnt number;
	begin
		select count(*)
			into v_in_cnt
			from student
			where student_id = i_student_id;
			return 1 = v_id_cnt;
	EXCEPTION
	when others then
		return FALSE;
	end id_is_good;
END manage_students;
	
/
Re: Compilation unit analysis terminated error [message #392616 is a reply to message #392614] Wed, 18 March 2009 11:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>1/14 PLS-00304 cannot compile body of 'manage_students' without its sepcs.

The package has two 'parts'.
The package specification and package body.
In specification, you define the functions and procedures that the package will use.
In body, you will actually write the code for those units.
Re: Compilation unit analysis terminated error [message #392620 is a reply to message #392616] Wed, 18 March 2009 12:04 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
got it
i had package specification in a separate file:
set serveroutput on
create or replace package manage_students
as
	procedure find_sname
		(i_student_id IN student.student_id%TYPE,
		o_first_name OUT student.first_name%TYPE,
		o_last_name OUT student.last_name%TYPE
		);
	function id_is_good
		(i_student_id IN student.student_id%TYPE)
		RETURN BOOLEAN;
END manage_students;

/


i complied this file and a package body file (in my previous post)
gave out the following errors:
8/11 PLS-00323: subprogram or cursor 'ID_IS_GOOD' is declared in a package specification and must be defined in the package body
20/2 PL/SQl: Item ignored
21/29 PLS-00302: component 'i_student_id' must be declared
=====


this is what i do not understand. I did declare ID_IS_GOOD function in the project specification (see above), so why am i getting this error?

thank you again Smile
Re: Compilation unit analysis terminated error [message #392622 is a reply to message #392620] Wed, 18 March 2009 12:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
In body
>>function id_is_good
>> (i_student_id in student.i_student_id%TYPE)

In specification
>>function id_is_good
>> (i_student_id IN student.student_id%TYPE)


>>i had package specification in a separate file:
Have it in the same file and compile it.
Much easier.
Re: Compilation unit analysis terminated error [message #392623 is a reply to message #392620] Wed, 18 March 2009 12:10 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
i did research on the internet and two possible solutions were provided:
1. placed a subprogram specification in a package specification,
but neglected to place the corresponding subprogram body in the package body
2. missing BEGIN before procedure/function in the package BODY

Neither 1. nor 2. seem to be the solutions simple because: 1. i did place ID_IS_GOOD function into package body
2. there is a BEGIN statement

so i do not know...
Re: Compilation unit analysis terminated error [message #392624 is a reply to message #392614] Wed, 18 March 2009 12:14 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
3. Declared it in the spec. Put it in the body but included a typo that means the two no longer match.

Which is actually the most common cause of this error.
Re: Compilation unit analysis terminated error [message #392625 is a reply to message #392622] Wed, 18 March 2009 12:26 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
why should there be a mismatch in parameters:
In body
>>function id_is_good
>> (i_student_id in student.i_student_id%TYPE)

In specification
>>function id_is_good
>> (i_student_id IN student.student_id%TYPE)

should not they both be
(i_student_id IN student.student_id%TYPE)

as it is in the textbook?

in other words i_student_id is an input for the function to deal with and the function is going to compare it to student.student_id in the database???

i did try it your way and still the same error...

thank you
Re: Compilation unit analysis terminated error [message #392626 is a reply to message #392625] Wed, 18 March 2009 12:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You dont get it.
I pointed it.
Cookie monster clearly said it.
>>3. Declared it in the spec. Put it in the body but included a typo that means the two no longer match.

Your spec and body does not match.
THere is a typo.
student.i_student_id%TYPE

student.student_id%TYPE
Re: Compilation unit analysis terminated error [message #392627 is a reply to message #392614] Wed, 18 March 2009 12:30 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
(i_student_id in student.i_student_id%TYPE)


That's not something we are telling you to put.
That's something we're telling you you have put.
That's a direct copy and paste from your first post

Quote:

should not they both be
(i_student_id IN student.student_id%TYPE)

as it is in the textbook?



YES!
Re: Compilation unit analysis terminated error [message #392628 is a reply to message #392614] Wed, 18 March 2009 12:31 Go to previous messageGo to next message
cookiemonster
Messages: 12406
Registered: September 2008
Location: Rainy Manchester
Senior Member
Too slow
Confused
Re: Compilation unit analysis terminated error [message #392629 is a reply to message #392626] Wed, 18 March 2009 12:35 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
sorry i thought my BODY was
student.student_id

to begin with and you wanted me to insert into the BODY
i_student.student_id instead

that is why i was wondering about the input variable.
and, yes, i did understand what cookiemonster was saying. he happened to point out something that is prob. the most common cause of this error and yet, i have not seen it pointed out on the web up until now at least.

Smile
Re: Compilation unit analysis terminated error [message #392631 is a reply to message #392629] Wed, 18 March 2009 12:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Netiquette 101.
'>>' means quoting.
I was just quoting your statement. Not recommending anything.
May be I should start using QUOTE. Smile
Re: Compilation unit analysis terminated error [message #392660 is a reply to message #392629] Wed, 18 March 2009 16:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Got this wonderful E-Mail from OP.
And this time, I am quoting. Wink
Quote:

how are you?
i wanted to tell you that i do appreciate your help very much. However, i wanted to point out to you that I do come from the IT background although i have never worked in the field. I a Comp. Sci. graduate from one of the top Australian Universities and I have not seen ANYONE using >> when they wanted to quote something. Each time I received e-mail from my nerds in the University they used ' ' OR " ". So you may not have to write 'QUOTE', it may just be something like ' ' which is really a grammatically correct thing to do.
The only times I used to see '>>' being used was when I was using an old e-mail client which was not HTML compatible.

I am very new to Oracle. I only started reading the textbook approx. 4 weeks ago. Everyone has to start somewhere.

You were conducting yourself as if you are some kind of a devine Being. I do not know how you get along with people, my assumption is that you do, but you should know that
there are plenty of individuals out there who may know much more than you do whether we are talking about the DBA or other areas of science/life in general.

thank you again and please do not reply to my future posts Smile

[Updated on: Wed, 18 March 2009 16:07]

Report message to a moderator

Re: Compilation unit analysis terminated error [message #392664 is a reply to message #392660] Wed, 18 March 2009 16:30 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I didn't participate this discussion; however, I couldn't resist telling you, happyjoshua777, that - in my opinion - you are very, VERY wrong about Mahesh. I've been hanging around for a while now and ... I just can't imagine what made you make these assumptions. Was it quoting misunderstanding? If so, don't be silly and stop acting as a spoiled princess.
Re: Compilation unit analysis terminated error [message #392666 is a reply to message #392664] Wed, 18 March 2009 16:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Littlefoot wrote on Wed, 18 March 2009 21:30
I didn't participate this discussion; however, I couldn't resist telling you, happyjoshua777, that - in my opinion - you are very, VERY wrong about Mahesh. I've been hanging around for a while now and ... I just can't imagine what made you make these assumptions. Was it quoting misunderstanding? If so, don't be silly and stop acting as a spoiled princess.

+1
Re: Compilation unit analysis terminated error [message #392667 is a reply to message #392666] Wed, 18 March 2009 16:47 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I also see nothing wrong with Mahesh Rajendran's answers. He just pointed out errors and typos.

If you don't want people to point out errors, happyjoshua777, then what's the point of asking for advise?
Previous Topic: Restricting Data using LIKE
Next Topic: Audit triggers (merged 4)
Goto Forum:
  


Current Time: Tue Dec 06 04:47:31 CST 2016

Total time taken to generate the page: 0.21666 seconds