CODEWITHSUNDEEP
oracle-databaseplsql
Joby Wilson Mathews
Joby Wilson Mathews

Reputation: 11116

Execute Pl SQL conditions in a column and get return value

Tables used:

RULE ENGINE Table:

enter image description here

POINT Table

enter image description here

I am trying to execute rules in RULE ENGINE table where some of the data(d1.sm.n1,d2.sm.n2..etc) are referred from POINT table.

The value of 'XSGSY' depends upon rule in RULE column.

Note: These rules follow plsql syntax.

I tried to extract data values(d1.sm.n1) and added to rule:

EXECUTE IMMEDIATE 'BEGIN If ''Yes'' = ''Yes'' OR 
''Yes'' = ''Yes'' then return ''Y''; else return ''N''; end if; END;'

then execute rules using EXECUTE IMMEDIATE , but it was not working.

ERROR: In a procedure, RETURN statement cannot contain an expression

Also tried with function and got error:

EXECUTE IMMEDIATE 'create or replace function express return 
VARCHAR2(10)  
  BEGIN If ''Yes'' = ''Yes'' OR ''Yes'' = ''Yes'' 
 then return ''Y''; else return ''N''; end if; 
END express;' INTO l_output;

ERROR: ORA-06546: DDL statement is executed in an illegal context

Upvotes: 0

Views: 890

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Your design looks flawed.I presume you want to extract result of a dynamically constructed PL/SQL block into a local variable. If so, you may use an USING OUT variable option.

SET SERVEROUTPUT ON
DECLARE
l_x  VARCHAR2(10);
BEGIN
EXECUTE IMMEDIATE 'BEGIN If ''Yes'' = ''Yes'' OR 
''Yes'' = ''Yes'' then :x := ''Y''; else :x := ''N''; end if; END;' USING OUT l_x;
dbms_output.put_line(l_x);
END;
/

Y


PL/SQL procedure successfully completed.

Upvotes: 3

Related Questions