Oracle compare two numbers in select

Question

I would like to execute pl sql query below and get result of the comparison.

 EXECUTE IMMEDIATE  'select 5<6 FROM DUAL' ;

Executing error is something like below:

ORA-06550: line 1, column 18:
PLS-00103: Encountered the symbol "select 5<6 FROM DUAL" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "select 5<6 FROM DUAL" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action

:

My math operator between two numbers is dynamic , meaning it can be any other operator like < , > , <=, >=, = etc. And it comes from parameter to my stored procedure.

For Instance : There is 3 in parameters in my SP.
number1 , number2 and math operator.

Actually what am ı looking for is somith like eval() function in javascript which is automatically execute string operation and return its result . Eval("5<3")

The words 'true' or 'false', displayed on the screen? That won’t happen because SQL has no Boolean type, and in any case I don’t see any code to capture and display the result. — William Robertson, Jun 02 '22 at 07:10

score 2 · Accepted Answer · answered Jun 01 '22 at 19:21

Here's one option:

SQL> set serveroutput on
SQL> declare
  2    retval varchar2(20);
  3  begin
  4    execute immediate q'[select case when 5 < 6 then 'true'
  5                                     else 'false'
  6                                end
  7                         from dual]'
  8            into retval;
  9
 10    dbms_output.put_Line('Result: ' || retval);
 11  end;
 12  /
Result: true

PL/SQL procedure successfully completed.

SQL>

score 0 · Answer 2 · answered Jun 02 '22 at 12:11

0

This is my working code sample.

execute immediate 'select case when ' || SQL1R  || item.operator||  SQL2R || ' then  1 else 0 end from dual'  INTO COMP;

Thanks to @Littlefoot

answered Jun 02 '22 at 12:11

ilhan

125
2
13

Oracle compare two numbers in select

2 Answers2