2

I've written the following PL/SQL to dynamically execute a formula given to me as a string. The final output from the formula should return 19, which I was hoping would be returned into my o_ variable.

The code actually runs without error, but doesn't give me the result I was expecting. Am I using the DBMS_SQL package correctly?

Please note that a complicating factor to this problem, is that I don't know how many bind-variables will be included in the input string (or formula). Therefore, I can't use the EXECUTE IMMEDIATE strategy of executing dynamic PL/SQL, because EXECUTE IMMEDIATE assumes that you know ahead of time how many variables need binding.

Am I approaching the problem in the correct way? Is there a better way of doing this?

DECLARE
    cur_  INTEGER;
    r_    NUMBER;
    str_  VARCHAR2(2000) := 'BEGIN :out := :x * 3 + :y; END;';
    x_    NUMBER := 3;
    y_    NUMBER := 10;
    o_    NUMBER;
BEGIN
    cur_ := Dbms_SQL.open_cursor;
    Dbms_SQL.Parse (cur_, str_, Dbms_SQL.Native);
    Dbms_SQL.Bind_Variable (cur_, ':out', o_);
    Dbms_SQL.Bind_Variable (cur_, ':x', x_);
    Dbms_SQL.Bind_Variable (cur_, ':y', y_);
    r_ := Dbms_SQL.Execute (cur_);
    Dbms_SQL.Close_Cursor (cur_);
    Dbms_Output.Put_Line ('Your variables: ' || x_ || ', ' || y_ || ', and out: ' || o_ || ', and R: ' || r_);
END;
cartbeforehorse
  • 3,045
  • 1
  • 34
  • 49
  • Is your string a fixed string? If its not, you can use something like what's present in the answer of [this question](http://stackoverflow.com/questions/5209981/use-of-bind-variable). – Nitish Mar 23 '17 at 05:45
  • @Nitish I'm really not sure what you're referring to. I can't find anything on that page that would even come close to the question I'm asking. – cartbeforehorse Mar 23 '17 at 11:19

2 Answers2

3

You missed the DBMS_SQL.VARIABLE_VALUE call. 

DECLARE
    cur_  INTEGER;
    r_    NUMBER;
    str_  VARCHAR2(2000) := 'BEGIN :out := :x * 3 + :y; END;';
    x_    NUMBER := 3;
    y_    NUMBER := 10;
    o_    NUMBER;
BEGIN
    cur_ := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE (cur_, str_, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE (cur_, ':out', o_);
    DBMS_SQL.BIND_VARIABLE (cur_, ':x', x_);
    DBMS_SQL.BIND_VARIABLE (cur_, ':y', y_);
    r_ := DBMS_SQL.EXECUTE (cur_);
    DBMS_SQL.VARIABLE_VALUE(cur_, ':out', o_);
    DBMS_SQL.CLOSE_CURSOR (cur_);
    DBMS_OUTPUT.PUT_LINE ('Your variables: ' || x_ || ', ' || y_ || ', and out: ' || o_ || ', and R: ' || r_);
END;

Your variables: 3, 10, and out: 19, and R: 1
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

Try it using a "select [your calculation] from dual", Fetch the resulting row and get the column value.

declare
   res  number;
   cur_ integer;
   r_   number;
   str_ varchar2(2000) := 'select :x * 3 + :y from dual';
   x_   number := 3;
   y_   number := 10;
   o_   number;
begin
   cur_ := dbms_sql.open_cursor;
   dbms_sql.parse(cur_, str_, dbms_sql.native);
   dbms_sql.bind_variable(cur_, ':x', x_);
   dbms_sql.bind_variable(cur_, ':y', y_);
   dbms_sql.define_column(cur_, 1, o_);
   r_  := dbms_sql.execute(cur_);
   res := dbms_sql.fetch_rows(cur_); -- Fetch only the first row, no loop required
   dbms_sql.column_value(cur_, 1, o_);
   dbms_sql.close_cursor(cur_);
   dbms_output.put_line('Your variables: ' || x_ || ', ' || y_ || ', and out: ' || o_ || ', and R: ' || r_);
end;
Rene
  • 10,391
  • 5
  • 33
  • 46
  • 1
    I think instead of `dbms_sql.execute()` and `dbms_sql.fetch_rows` you can use single `dbms_sql.EXECUTE_AND_FETCH()` – Wernfried Domscheit Mar 23 '17 at 08:53
  • @Rene I posted this question before going to sleep, and when I woke up, I also thought of this solution (I must have been dreaming code). It does indeed work (at the expense of having to modify the input string from the user, of course). But I'm afraid I'm not going to mark this solution as "accepted answer", because the solution form Wernfried answers the question more directly. But +1 for thinking a little outside the box. Your idea much appreciated. – cartbeforehorse Mar 23 '17 at 11:01