CODEWITHSUNDEEP
sqloracle-database
Rohit Shinde
Rohit Shinde

Reputation: 1603

Binding variables in Oracle Pl/SQL

I need to use binding in Oracle to get over a roundabout way of doing something. I understand the way of doing it. But when I use it in my program, it gives me an error.

My code is this: 

CREATE OR REPLACE PROCEDURE FREQUENCY
IS
VISITOR CUSTOMER%ROWTYPE;
TYPE FREQ IS VARRAY(4) OF NUMBER(4);
CURSOR CUSTOMERS IS
 SELECT * FROM CUSTOMER;
FREQUENT FREQ:=(0,0,0,0);

BEGIN
 OPEN CUSTOMER;
 LOOP
  FETCH CUSTOMERS INTO VISITOR;
  EXIT WHEN CUSTOMERS%NOTFOUND;
  FREQUENT(VISITOR.BRANCHID):=FREQUENT(VISITOR.BRANCHID)+1;
 END LOOP;
END;

When I use a colon before the VISITOR.BRANCHID variable, I get the error Bad Bind Variable VISITOR and if I don't use a colon, I get the error Wrong Expression Type.

What can I possibly be doing wrong? BranchId is an integer.

Upvotes: 1

Views: 143

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132580

The "wrong expression type" error refers to this line:

FREQUENT FREQ:=(0,0,0,0);

It should be:

FREQUENT FREQ:=FREQ(0,0,0,0);

(What tool are you using to run this that doesn't show line numbers or all the messages?)

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

The full error stack for this code is:

7/16           PLS-00382: expression is of wrong type
7/10           PL/SQL: Item ignored
10/7           PLS-00456: item 'CUSTOMER' is not a cursor
10/2           PL/SQL: SQL Statement ignored
14/3           PLS-00320: the declaration of the type of this expression is incomplete or malformed
14/3           PL/SQL: Statement ignored

You have two mistakes. The OPEN is using the wrong name, it should be OPEN CUSTOMERS rather then OPEN CUSTOMER. The PLS-00382 is coming from the declaration, as the PLS-00320 suggests. You're missing the type; instead of:

FREQUENT FREQ:=(0,0,0,0);

you should have:

FREQUENT FREQ:=FREQ(0,0,0,0);

With those two changes your procedure compiles, assuming you have a customer table with a branchid column.

This is nothing to do with bind variables. If you referred to :VISITOR.BRANCHID then you would be trying to refer to a bind variable called VISITOR, but you don't have one (so you'd get PLS-00049); and you don't need one for this code. I think you're confusing terminology and syntax here.

Upvotes: 1

Related Questions