Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ % d
PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ % d [message #283259] Mon, 26 November 2007 09:33 Go to next message
philostik
Messages: 2
Registered: November 2007
Junior Member
Hi All:

I am getting an error mentioned in the subject line i.e.

Line 1 Col 57 -> PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ % default character

for the following procedure:

===========================================
CREATE OR REPLACE PROCEDURE prc_avg_product_unitcost(NEW_PRD_SIZE VARCHAR2(4) DEFAULT,NEW_PRD_COLOR VARCHAR2(15) DEFAULT,NEW_PRD_CAT_ID INTEGER DEFAULT,NEW_PRD_GENDER CHAR(6) DEFAULT,NEW_PRD_UNITCOST FLOAT(6) DEFAULT,NEW_PRD_QUANTITY INTEGER DEFAULT) AS
PRAGMA AUTONOMOUS_TRANSACTION;
db_prd_size VARCHAR2(4);
db_prd_color VARCHAR2(15);
db_prd_cat_id INTEGER;
db_prd_gender CHAR(6);
db_prd_unitcost FLOAT(6);
db_prd_quantity INTEGER;
db_upd_flag INTEGER;
--declare a cursor for table product
CURSOR existingproducts IS SELECT PRD_SIZE, PRD_COLOR, PRD_CAT_ID, PRD_GENDER, PRD_UNITCOST, PRD_QUANTITY FROM ALT_PRODUCT;

BEGIN
--Open the cursor, and get the value
OPEN existingproducts;
LOOP
FETCH existingproducts INTO db_prd_size, db_prd_color, db_prd_cat_id, db_prd_gender, db_prd_unitcost, db_prd_quantity;
EXIT WHEN existingproducts%NOTFOUND;
IF db_prd_size = NEW_PRD_SIZE AND db_prd_color = NEW_PRD_COLOR AND db_prd_cat_id = NEW_PRD_CAT_ID AND db_prd_gender = NEW_PRD_GENDER THEN
UPDATE ALT_PRODUCT SET prd_quantity = (db_prd_quantity + new_prd_quantity) WHERE DB_PRD_SIZE = new_prd_size AND DB_PRD_COLOR = new_prd_color AND DB_PRD_CAT_ID = new_prd_cat_id AND DB_PRD_GENDER = new_prd_gender;
UPDATE ALT_PRODUCT SET prd_unitcost = ((db_prd_unitcost * db_prd_quantity) + (new_prd_unitcost * new_prd_quantity))/(db_prd_quantity + new_prd_quantity ) WHERE DB_PRD_SIZE = new_prd_size AND DB_PRD_COLOR = new_prd_color AND DB_PRD_CAT_ID = new_prd_cat_id AND DB_PRD_GENDER = new_prd_gender;
COMMIT;
END IF;
END LOOP;
CLOSE existingproducts;

EXCEPTION
WHEN NO_DATA_FOUND THEN
insert into alt_product(PRD_SIZE,PRD_COLOR,PRD_CAT_ID,PRD_GENDER, PRD_UNITCOST, PRD_QUANTITY) values(NEW_PRD_SIZE,NEW_PRD_COLOR,NEW_PRD_CAT_ID,NEW_PRD_GENDER,NEW_PRD_UNITCOST,NEW_PRD_QUANTITY);
END prc_avg_product_unitcost;
/
======================================

I have tried adding the word 'IN' to segregate whether its a IN parameter or output parameter in the procedure declaration, but that dint work. I sense that there is some syntex error on the first line but am unable to locate that. Can someone please help!

Rgds,
Manish.
Re: PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ [message #283261 is a reply to message #283259] Mon, 26 November 2007 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in above URL.

Correctng syntax errors does NOT require expert advice.
Re: PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ [message #283265 is a reply to message #283259] Mon, 26 November 2007 09:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I'll give you a hint (I'm generous that way Smile ) Have a look at the syntax for assigning default values to parameters (you'll find it in the pl/sql reference which you can download from http://tahiti.oracle.com
Re: PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ [message #283267 is a reply to message #283265] Mon, 26 November 2007 10:02 Go to previous messageGo to next message
philostik
Messages: 2
Registered: November 2007
Junior Member
@pablolee :

Thank you very much for this quick reply. I tried following after your suggestion:

===============
CREATE OR REPLACE PROCEDURE prc_avg_product_unitcost (NEW_PRD_SIZE IN VARCHAR2(4) DEFAULT ‘S’,NEW_PRD_COLOR IN VARCHAR2(15) DEFAULT ‘BLUE’,NEW_PRD_CAT_ID IN INTEGER DEFAULT 1,NEW_PRD_GENDER IN CHAR(6) DEFAULT ‘Unisex’, NEW_PRD_UNITCOST IN FLOAT(6) DEFAULT 100,NEW_PRD_QUANTITY IN INTEGER DEFAULT 10) AS
PRAGMA AUTONOMOUS_TRANSACTION;
db_prd_size VARCHAR2(4);
db_prd_color VARCHAR2(15);
db_prd_cat_id INTEGER;
db_prd_gender CHAR(6);
db_prd_unitcost FLOAT(6);
db_prd_quantity INTEGER;
db_upd_flag INTEGER;
--declare a cursor for table product
CURSOR existingproducts IS SELECT PRD_SIZE, PRD_COLOR, PRD_CAT_ID, PRD_GENDER, PRD_UNITCOST, PRD_QUANTITY FROM ALT_PRODUCT;

BEGIN
--Open the cursor, and get the value
OPEN existingproducts;
LOOP
FETCH existingproducts INTO db_prd_size, db_prd_color, db_prd_cat_id, db_prd_gender, db_prd_unitcost, db_prd_quantity;
EXIT WHEN existingproducts%NOTFOUND;
IF db_prd_size = NEW_PRD_SIZE AND db_prd_color = NEW_PRD_COLOR AND db_prd_cat_id = NEW_PRD_CAT_ID AND db_prd_gender = NEW_PRD_GENDER THEN
UPDATE ALT_PRODUCT SET prd_quantity = (db_prd_quantity + new_prd_quantity) WHERE DB_PRD_SIZE = new_prd_size AND DB_PRD_COLOR = new_prd_color AND DB_PRD_CAT_ID = new_prd_cat_id AND DB_PRD_GENDER = new_prd_gender;
UPDATE ALT_PRODUCT SET prd_unitcost = ((db_prd_unitcost * db_prd_quantity) + (new_prd_unitcost * new_prd_quantity))/(db_prd_quantity + new_prd_quantity ) WHERE DB_PRD_SIZE = new_prd_size AND DB_PRD_COLOR = new_prd_color AND DB_PRD_CAT_ID = new_prd_cat_id AND DB_PRD_GENDER = new_prd_gender;
COMMIT;
END IF;
END LOOP;
CLOSE existingproducts;

EXCEPTION
WHEN NO_DATA_FOUND THEN
insert into alt_product(PRD_SIZE,PRD_COLOR,PRD_CAT_ID,PRD_GENDER, PRD_UNITCOST, PRD_QUANTITY) values(NEW_PRD_SIZE,NEW_PRD_COLOR,NEW_PRD_CAT_ID,NEW_PRD_GENDER,NEW_PRD_UNITCOST,NEW_PRD_QUANTITY);
END prc_avg_product_unitcost;
/
===============

But I still get the error:
Line 1 Col 61 : PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ % default character

Do you have any suggestion that will make it work
Re: PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ [message #283269 is a reply to message #283259] Mon, 26 November 2007 10:13 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

If you look at this Oracle manual and search for the part on datatype you should find the answer Smile
Re: PLS-00103: Encountered the symbol "(" when expecting one of the f ollowing: := . ) , @ [message #283270 is a reply to message #283267] Mon, 26 November 2007 10:14 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
1. Please read and follow the link that ana posted. It is important and it will help you immeasurably in the future. (The post was created to make everyones' life easier)
2. Now look at the syntax for declaring datatypes for parameters, pay particular attention to the sizing of parameters
Previous Topic: sqlca.sqlcode when oracle goes off
Next Topic: how to find out client version from SQL command.
Goto Forum:
  


Current Time: Sun Dec 04 15:01:08 CST 2016

Total time taken to generate the page: 0.09289 seconds