Oracle ODBC define variable [Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

0
votes

I am using Oracle ODBC driver and want to dynamically pass variables. This is how my query looks like: 

define val = 'Test';
select * from Table where Name = '&val'

While this query runs fine in Oracle SQL Developer, I think I am not writing the query right for ODBC driver to execute right. Any suggestions?

from ODBC logs:

EXIT SQLExecDirectW with return code -1 (SQL_ERROR) "define val = 'Test';\ aselect * from Table where Name = '&val';\ 0" DIAG [42000] [Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

1
oracleodbc

1 Answers

1
votes

DEFINE is valid in SQLPlus and SQLDeveloper, but it's not a valid PL/SQL statement.

In PL/SQL you'd do something like

DECLARE
  val   VARCHAR2(25) := 'Test';
  aRow  SOME_TABLE%ROWTYPE;
BEGIN
  SELECT *
    INTO aRow
    FROM SOME_TABLE
    WHERE NAME = val;
END;

Best of luck.