String as condition inside IF statement in PLSQL

Question

I want to substitute a string as a condition in IF statement in Oracle PLSQL. Please look at the anonymous block below and advise how I can achieve the requirement.

DECLARE
var_bool1 varchar2(100);
BEGIN
var_bool1 := '3<5 AND 5<8';
IF (var_bool1) THEN
DBMS_OUTPUT.PUT_LINE('SUCCESS');
END IF;
END;

I know I can write the IF statement as below but I'm looking for a specific req like the above.

IF (3<5 AND 5<8) THEN
...
END IF;

Need help please...

I get below errors-ORA-06550: line 5, column 4: PLS-00382: expression is of wrong type ORA-06550: line 5, column 1: PL/SQL: Statement ignored — Witty Gritty

Marcin Wroblewski Marcin Wroblewski · Accepted Answer · 2014-12-03T14:03:28

IF expects boolean expression, so you have to translate the string into something which returns true or false (as Dhaval wrote). You can for example define a function which evaluates the expression, like this (function bool):

DECLARE
  var_bool1 varchar2(100);

  function bool(p_expression in varchar2) return boolean is
    l_result number;
  begin
    execute immediate 'select count(*) from dual where ' || p_expression
      into l_result;
    return l_result = 1;
  end;
BEGIN
  var_bool1 := '3<5 AND 5<8';
  IF bool(var_bool1) THEN
    DBMS_OUTPUT.PUT_LINE('SUCCESS');
  END IF;
END;

but to be honest I find this solution "ugly" (and dangerous). Why do you need to do this? What is the problem you are trying to solve?

String as condition inside IF statement in PLSQL

2 Answers