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  |
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 #283267 is a reply to message #283265] |
Mon, 26 November 2007 10:02  |
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 20:44:44 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
) 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