CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
Man HOOD
Man HOOD

Reputation: 51

PLS-00103: Encountered the symbol "(" when expecting one of the following:

I am trying to retrieve a customer id pk column from a report page to a form page that gets automatically incremented whenever the user moves from the report page to the form page using oracle application express.So i am trying to create a trigger that returnns a number having the value of the final row along with a +1 increment to it. However i get this error

Error at line 8: PLS-00103: Encountered the symbol "(" when expecting one of the following:
   , from
6. INTO number
7. FROM (SELECT a.cust_id, max(cust_id) over() as max_pk FROM customer a)
8. WHERE cust_id = max_pk;
9. number:=(cust_id+1);
10. END;

This is my PL/SQL procedure.

CREATE OR REPLACE FUNCTION cust_id_incremental(cust_id IN number)
RETURN number;


BEGIN

SELECT cust_id
INTO number
FROM (SELECT a.cust_id, max(cust_id) over() as max_pk FROM customer a)
WHERE cust_id = max_pk;`enter code here`
number:=(cust_id+1);

END;

Upvotes: 0

Views: 33302

Answers (2)

Allan
Allan

Reputation: 17429

You need to define a variable to contain the result, populate it, then return it. The corrected procedure might look like this:

CREATE OR REPLACE FUNCTION cust_id_incremental (cust_id IN NUMBER)
   RETURN NUMBER IS
   v_cust_id NUMBER;
BEGIN
   SELECT cust_id
   INTO   v_cust_id
   FROM   (SELECT a.cust_id, MAX (cust_id) OVER () AS max_pk
           FROM   customer a)
   WHERE  cust_id = max_pk;

   v_cust_id := v_cust_id + 1;
   RETURN v_cust_id;
END;

Taking a second look, the structure of this procedure is far more convoluted than it needs to be. Unless I'm missing something, you could accomplish the same thing with a procedure that looked like this:

CREATE OR REPLACE FUNCTION cust_id_incremental
   RETURN NUMBER IS
   v_cust_id NUMBER;
BEGIN
   SELECT MAX (cust_id) + 1
   INTO   v_cust_id
   FROM   customer a;

   RETURN v_cust_id;
END;

It occurred to me as I was writing this that you may have namespace issue: your original function accepts a parameter CUST_ID, then queries a table with column CUST_ID. All of the references to CUST_ID in the query will refer to the column, not the parameter. If the parameter serves a purpose, it is being obscured.

However, you really shouldn't be doing this. If two sessions call this procedure simultaneously and insert the resulting value into a new row, you'll have a primary key violation. This is the entire reason that sequences exist. As sequences are not transactional, multiple sessions that access the same sequence will get different values.

Upvotes: 2

mmmmmpie
mmmmmpie

Reputation: 3019

number is a reserve word, you need to call it something else like l_number to be a variable name.

Upvotes: 5

Related Questions