Home » SQL & PL/SQL » SQL & PL/SQL » trying to call but got error
trying to call but got error [message #201013] Thu, 02 November 2006 04:25 Go to next message
hiip
Messages: 78
Registered: October 2006
Member
hi everyone, i got few problems while i run my procedure..

create or replace PROCEDURE list_campaign(cname IN campaign.companyname%Type)
	IS
		CURSOR list_campaign_cur 
		IS
			SELECT title, estimatedcost, actualcost, companyname
			FROM campaign;
	BEGIN
		FOR list_campaign_rec IN list_campaign_cur LOOP
			IF cname = list_campaign_rec.companyname THEN
				DBMS_OUTPUT.PUT_LINE( list_campaign_rec.title || ' ' || list_campaign_rec.estimatedcost || ' ' || list_campaign_rec.actualcost );
			END IF;
		END LOOP;
	END;

declare
v_companyname campaign.companyname%Type := &companyName;
begin
list_campaign(v_title);
end;
/



the error is

old 16: v_companyname campaign.companyname%Type := &companyName;
new 16: v_companyname campaign.companyname%Type := Bing Lee;
Warning: Procedure created with compilation errors.
Errors for PROCEDURE LIST_CAMPAIGN:

LINE/COL ERROR
15/1 PLS-00103: Encountered the symbol "DECLARE"
16/49 PLS-00103: Encountered the symbol "LEE" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ The symbol "." was substituted for "LEE" to continue.

can someone fix that for me ... thank you
Re: trying to call but got error [message #201018 is a reply to message #201013] Thu, 02 November 2006 04:32 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Create the procedure first.

Then call it.

Or put a '/' between
END;

declare


It is treating the entire script as one.

By
Vamsi
Re: trying to call but got error [message #201021 is a reply to message #201018] Thu, 02 November 2006 04:41 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
yap i did that seperately but here is the error ..

old 2: v_companyname campaign.companyname%Type := &companyName;
new 2: v_companyname campaign.companyname%Type := Domayne;

v_companyname campaign.companyname%Type := Domayne;
*

ERROR at line 2:
ORA-06550: line 2, column 44:
PLS-00201: identifier 'DOMAYNE' must be declared
ORA-06550: line 2, column 15:
PL/SQL: Item ignored
ORA-06550: line 4, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

[Updated on: Thu, 02 November 2006 04:43]

Report message to a moderator

Re: trying to call but got error [message #201023 is a reply to message #201021] Thu, 02 November 2006 04:48 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 just put single quotes around the string 'Domayne' 
and see if it works.



regards,
Re: trying to call but got error [message #201025 is a reply to message #201021] Thu, 02 November 2006 04:51 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Reading your last posts on this Forum, I thought that perhaps you should go to Tahiti and spend some time there. Here's a nice hotel.

I have this strange feeling that most things you've done weren't product of your knowledge, but result of a blind hen phenomenon - it will (the hen, I mean), eventually, pick that seed. Sooner or later.
Re: trying to call but got error [message #201030 is a reply to message #201025] Thu, 02 November 2006 05:07 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
yap it says successfully completed
but dont have display...
Re: trying to call but got error [message #201031 is a reply to message #201030] Thu, 02 November 2006 05:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
If you are using sql*plus,
have you heard of 'serveroutput'?
o.w., (any GUI) check for dbms output on / off switch

By
Vamsi
Re: trying to call but got error [message #201033 is a reply to message #201013] Thu, 02 November 2006 05:19 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
yap i done that...


set serveroutput on
execute end_campaign(&title);

Re: trying to call but got error [message #201044 is a reply to message #201033] Thu, 02 November 2006 06:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Did it complete successfully?

I suppose campaign.companyname is a character.
And you should also pass the same.
A static string should be enclosed by single quotes(').

By
Vamsi

[Updated on: Thu, 02 November 2006 06:14]

Report message to a moderator

Re: trying to call but got error [message #201045 is a reply to message #201013] Thu, 02 November 2006 06:15 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
yes it is successful but nothing comeout

i do this

set serveroutput on
execute list_campaigns(&companyname)

and it display this

PL/SQL procedure successfully completed.

instead of displaying wat is coded.

help help....!!!

[Updated on: Thu, 02 November 2006 06:17]

Report message to a moderator

Re: trying to call but got error [message #201047 is a reply to message #201045] Thu, 02 November 2006 06:18 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It means that
cname <> list_campaign_rec.companyname
for all records retreived by a cursor.
Re: trying to call but got error [message #201056 is a reply to message #201047] Thu, 02 November 2006 06:54 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
you mean this part

IF cname = list_campaign_rec.companyname THEN

=

cname <> list_campaign_rec.companyname???
Re: trying to call but got error [message #201059 is a reply to message #201056] Thu, 02 November 2006 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is the procedure:
PROCEDURE list_campaign(cname IN campaign.companyname%Type) IS
CURSOR list_campaign_cur IS
  SELECT title, estimatedcost, actualcost, companyname
  FROM campaign;
BEGIN
  FOR list_campaign_rec IN list_campaign_cur LOOP
    IF cname = list_campaign_rec.companyname THEN
      DBMS_OUTPUT.PUT_LINE( list_campaign_rec.title || ' ' || 
        list_campaign_rec.estimatedcost || ' ' || 
        list_campaign_rec.actualcost );
    END IF;
  END LOOP;
END;
If you executed it, and DBMS_OUTPUT.PUT_LINE didn't print anything, it means that "cname" was not equal to any of "list_campaign_rec.companyname" fetched by the cursor.
Re: trying to call but got error [message #201063 is a reply to message #201013] Thu, 02 November 2006 07:13 Go to previous messageGo to next message
hiip
Messages: 78
Registered: October 2006
Member
so how should i use to make it equal ?? := ??
Re: trying to call but got error [message #201066 is a reply to message #201063] Thu, 02 November 2006 07:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

hiip:
set serveroutput on
execute end_campaign(&title);



Can you confirm that you have calling the procedure, which has been created by you only?

By
Vamsi
Re: trying to call but got error [message #201070 is a reply to message #201066] Thu, 02 November 2006 07:24 Go to previous message
hiip
Messages: 78
Registered: October 2006
Member
ok i already get it fixed.. thank you..and really appreciated..on your help..
Previous Topic: Using alias
Next Topic: SQLLDR - How to import and decode my fields
Goto Forum:
  


Current Time: Tue Dec 06 04:19:49 CST 2016

Total time taken to generate the page: 0.12947 seconds