Oracle using multiple variables in select statement

Question

I am trying to retrieve the records from order_master table by two criteria.

select * from order_master 
where ContractNo = 'D00101'
and Contract_SubCode = 'A';

I can see the result from Oracle SQL Developer. But I want to retrieve the records by giving variables values rather than giving hard coded value.

So I try following way.

Define Var_ContractNo = 'D09119';
Define Var_Contract_SubCode = 'A';

select * from order_master 
where ContractNo = &Var_ContractNo
and Contract_SubCode = &Var_Contract_SubCode;

Then I received below error.

ORA-00904: "A": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 6 Column: 24

So I try another way.

var Var_ContractNo VARCHAR2(20);
var Var_Contract_SubCode VARCHAR(2);
BEGIN
    select 'D09119', 'A' into :Var_ContractNo, :Var_Contract_SubCode from dual;    
END;

select * from order_master 
where ContractNo = :Var_ContractNo    
and Contract_SubCode = :Var_Contract_SubCode;

Then i received different error message

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

Error starting at line : 3 in command -
BEGIN
    select 'D09119', 'A' into :Var_ContractNo, :Var_Contract_SubCode from dual;    
END;

select * from order_master 
where ContractNo = :Var_ContractNo    
and Contract_SubCode = :Var_Contract_SubCode;
Error report -
ORA-06550: line 5, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Please kindly advise. Thanks.

Popeye · Accepted Answer · 2020-06-04 06:22:24Z

2

You just need to wrap your substituion variable in the single quotes in your query as follows:

Define Var_ContractNo = 'D09119';
Define Var_Contract_SubCode = 'A';

select * from order_master 
where ContractNo = '&Var_ContractNo'
and Contract_SubCode = '&Var_Contract_SubCode';

answered Jun 4, 2020 at 6:22

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pifor · Accepted Answer · 2020-06-04 07:38:33Z

1

You can also use SQL*Plus variables:

SQL> select * from t;

     X Y
---------- ----------
     1 ONE

SQL> --
SQL> var v_in_y varchar2(10);
SQL> var v_out_x varchar2(10);
SQL> begin
  2   :v_in_y := 'ONE';
  3   select x into :v_out_x from t where y=:v_in_y;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print v_out_x

V_OUT_X
--------------------------------
1

SQL>

answered Jun 4, 2020 at 7:38

pifor

7,9722 gold badges11 silver badges18 bronze badges

Collectives™ on Stack Overflow

Oracle using multiple variables in select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related