CODEWITHSUNDEEP
sqloracle-database
Justin Homes
Justin Homes

Reputation: 3799

Declaring multiple variable oracle sql script

Using oracle 11g 64bit and sqldeveloper 4.0. Trying to run the below sql script:

variable  v_sak_id NUMBER; 
variable  v_idNum varchar; 
exec :v_idNum := 'A1111200';
select sak_id into :v_sak_id from MemerId where idNum= :v_idNum;

Getting error message:

Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) | VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) | NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

anonymous block completed Error starting at line : 6 in command - select sak_id into :v_sak_id from MemerId where idNum= :v_idNum Error report - SQL Error: ORA-01006: bind variable does not exist 01006. 00000 - "bind variable does not exist" *Cause:
*Action:

Any help?

Upvotes: 0

Views: 9014

Answers (3)

Nick Krasnov
Nick Krasnov

Reputation: 27251

  1. When you declare a string variable, you have to specify the size of that variable.

  2. Although as of now the varchar and varchar2 are synonyms, please do not use varchar use varchar2 datatatype. Oracle is planing to redefine varchar datatype as a separate datatype.

  3. As it already has been told select into statement is used in Pl/SQL, so you should put your select statement in a begin end block or execute your select statement by using exec command, which, under the hood, wraps your select statement in begin end block:

Example(I assume that there was a typo and the table's name should be memBerid, but anyway. Left as it was.):

create table  memerid(
 sak_id number,
 idNum varchar2(123)
);

insert into MemerId(sak_id, idNum)
  values(1, 'A1111200');

commit;

variable  v_sak_id number; 
variable  v_idNum  varchar2(123); 

exec :v_idNum := 'A1111200';
exec select sak_id into :v_sak_id from MemerId  where idNum= :v_idNum;

print v_sak_id;

Result:

table MEMERID created.
1 rows inserted.
committed.
anonymous block completed
anonymous block completed

V_SAK_ID
-
1

Upvotes: 2

René Nyffenegger
René Nyffenegger

Reputation: 40499

Try something like

variable  v_sak_id NUMBER; 
variable  v_idNum varchar; 
exec :v_idNum := 'A1111200';

begin
   select sak_id into :v_sak_id from MemerId where idNum= :v_idNum;
end;

That is, the select ... into construct/statement needs to be enclosed in a PL/SQL block which is achieved by embedding the statement within a begin .. end.

Upvotes: 0

eaolson
eaolson

Reputation: 15094

SELECT ... INTO ... is PL/SQL syntax, not SQL. Since you're running in SQL Developer, you can just use select sak_id from MemerId where idNum= :v_idNum; and you will get prompted for the value of v_idNum. Additional executions with the bind variable will retain the previous value, or you can change it.

Upvotes: 0

Related Questions