CODEWITHSUNDEEP
oracle-databaseplsql
varnit
varnit

Reputation: 1897

Error(14,7): PLS-00049: bad bind variable

I have tried everything but Oracle 11g Express Edition gives me this error for the procedure.

Error(14,7): PLS-00049: bad bind variable

Here is my procedure 

create or replace procedure select_member_data
AS
 memberdata      MEMBER%rowtype;
 member_ID    MEMBER.MEMBER_ID%TYPE;
 first_name   MEMBER.FIRST_NAME%TYPE;
 last_name    MEMBER.LAST_NAME%TYPE;
 address      MEMBER.ADDRESS%TYPE;
 total_rows number(2);
BEGIN
 SELECT MEMBER_ID,first_name,last_name,address
 INTO :memberdata
 FROM MEMBER where member_id= 1 ;
 IF sql%notfound THEN  
  dbms_output.put_line('no records fetched');  
 ELSIF sql%found THEN     
  dbms_output.put_line( total_rows ||' fetched');  
 END IF;   
END;

here is my table 

CREATE TABLE MEMBER (
MEMBER_ID  INT PRIMARY KEY,
LAST_NAME VARCHAR2(25) NOT NULL,
FIRST_NAME varchar2(25),
ADDRESS varchar2(100),
CITY varchar2(30),
PHONE varchar2(15),
JOIN_DATE  date  DEFAULT SYSDATE NOT NULL  
);

Upvotes: 0

Views: 966

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

You are doing:

 SELECT MEMBER_ID,first_name,last_name,address
 INTO :memberdata
 ...

but memberdata is a local PL/SQL variable, not a bind variable, so it should just be:

 SELECT MEMBER_ID,first_name,last_name,address
 INTO memberdata
 ...

You also aren't fetching enough columns into your %rowtype variable. You table has seven columns, so your memberdata record variable has seven fields as well; but your query is only selecting four columns - which isn't enough. (Slightly confusingly, this is reported as too many values, rather than not enough values).

You can list all of the column names, but this is a rare occasion where it may be better (or at least justifiable) to use *:

 SELECT *
 INTO memberdata
 ...

Or as you have the other variables defined already you could do:

 SELECT MEMBER_ID,first_name,last_name,address
 INTO member_ID, first_name, last_name, address
 ...

although I'd stongly recommend you don't have variable names that are the same as column names; it's common to prefix local variable with something to identify them, e.g. l_member_id.

Your logic is flawed though. If your query doesn't get exactly one row then a no-data-found or too-many-rows exception will be thrown. (You can't have more than one because of the primary key here, but you could find none). The sql%notfound check won't be reached.

Upvotes: 3

Related Questions