CODEWITHSUNDEEP
oracle-databaseoracle12c
oula alshiekh
oula alshiekh

Reputation: 893

Dynamic select execution missing expression error

I am using Oracle 12, and I want to make a dynamic procedure which selects rows from specific table but according to an unknown conditio. That condition will be specified as input parameter.

Suppose I have a column called employee id and I want to call the procedure with the following condition 

execute s('employeeid = 2')

My code is 

create or replace procedure s (condition varchar)
as
   TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   emp_cv   EmpCurTyp;  -- declare cursor variable
   my_ename VARCHAR2(15);
   my_sal   NUMBER := 2;
   mycondition varchar2(100):=condition;
BEGIN
   OPEN emp_cv FOR  -- open cursor variable
      'SELECT employeeid, employeename FROM employees WHERE  = :s' USING mycondition;
END;

but I am getting an error

missing expression

What am I doing wrong, and will the result of this procedure be selected rows from employees table that satisfy applied condition ?

Upvotes: 0

Views: 331

Answers (2)

Thomas Naessens
Thomas Naessens

Reputation: 71

This is not embedded in a procedure yet but I tested this and works:

DECLARE
   TYPE OUT_TYPE IS TABLE OF VARCHAR2 (20)
      INDEX BY BINARY_INTEGER;

   l_cursor         INTEGER;
   l_fetched_rows   INTEGER;
   l_sql_string     VARCHAR2 (250);
   l_where_clause   VARCHAR2 (100);
   l_employeeid     VARCHAR2 (20);
   l_employeename   VARCHAR2 (20);
   l_result         INTEGER;
   o_employeeid     OUT_TYPE;
   o_employeename   OUT_TYPE;
BEGIN
   l_cursor := DBMS_SQL.OPEN_CURSOR;
   l_sql_string := 'SELECT employeeid, employeename FROM employees WHERE ';
   l_where_clause := 'employeeid = 2';
   l_sql_string := l_sql_string || l_where_clause;
   DBMS_SQL.PARSE (l_cursor, l_sql_string, DBMS_SQL.V7);
   DBMS_SQL.DEFINE_COLUMN (l_cursor,
                           1,
                           l_employeeid,
                           20);
   DBMS_SQL.DEFINE_COLUMN (l_cursor,
                           2,
                           l_employeename,
                           20);
   l_fetched_rows := 0;
   l_result := DBMS_SQL.EXECUTE_AND_FETCH (l_cursor);

   LOOP
      EXIT WHEN l_result = 0;
      DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_employeeid);
      DBMS_SQL.COLUMN_VALUE (l_cursor, 2, l_employeename);
      l_fetched_rows := l_fetched_rows + 1;
      o_employeeid (l_fetched_rows) := l_employeeid;
      o_employeename (l_fetched_rows) := l_employeename;
      l_result := DBMS_SQL.FETCH_ROWS (l_cursor);
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR (l_cursor);

   DBMS_OUTPUT.PUT_LINE (o_employeeid (1));
   DBMS_OUTPUT.PUT_LINE (o_employeename (1));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('GENERAL FAILURE: ' || SQLERRM);
END;

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

The USING is meant to handle values, not pieces of code; if you need to edit your query depending on an input parameter ( and I believe this is a very dangerous way of coding), you should treat the condition as a string to concatenate to the query.

For example, say you have this table:

create table someTable(column1 number)

This procedure does somthing similar to what you need:

create or replace procedure testDyn( condition IN varchar2) is
    cur sys_refcursor;
begin
    open cur for 'select column1 from sometable where ' || condition;
    /* your code */ 
end;

Hot it works:

SQL> exec testDyn('column1 is null');

PL/SQL procedure successfully completed.

SQL> exec testDyn('column99 is null');
BEGIN testDyn('column99 is null'); END;

*
ERROR at line 1:
ORA-00904: "COLUMN99": invalid identifier
ORA-06512: at "ALEK.TESTDYN", line 4
ORA-06512: at line 1

Upvotes: 1

Related Questions