4 Replies Latest reply: Dec 6, 2007 11:46 AM by vpolasa RSS

    execute immediate

    vpolasa
      I'm trying to add/subtract two numbers with the help of an operand which I pass it dynamically. This is the errror I got while trying to execute..

      ORA-00923: FROM keyword not found where expected

      DECLARE

      sortOrd_in NUMBER;
      v_operand VARCHAR2(1);
      v_result NUMBER;
      stmt VARCHAR2(4000);

      BEGIN

      sortOrd_in := 3;
      v_operand := '+';

      stmt := ('SELECT '||sortOrd_in||':1'||24||' FROM DUAL');

      EXECUTE IMMEDIATE stmt INTO v_result USING v_operand;

      dbms_output.put_line (v_result);

      END;

      I guess the error is in the stmt. But I'm unable to figure it out the exact syntax. What would be the correct syntax. Thanks and Regards.
        • 1. Re: execute immediate
          damorgan
          Your statement doesn't make sense. Here's what you wrote after doing substitutions.

          SELECT '||3||':1'||24||' FROM DUAL

          What do you expect Oracle to do with the operand?

          Beyond that take a good look at your attempt to concatenate a number as a character string. Implicit conversion is not your friend.
          • 2. Re: execute immediate
            vpolasa
            I may be wrong. I'm trying to implement the below concept by passing the number 5 and '+' operand dynamically. The operand may vary with '+' or '-'. What could the best way to solve this issue?

            DECLARE
            v_result NUMBER;
            BEGIN
            SELECT 5+4 INTO v_result FROM dual;
            dbms_output.put_line (v_result);
            END;

            Regards.
            • 3. Re: execute immediate
              Billy~Verreynne
              Math operands can be bind variables for a static formula. E.g.
              select :1 * :2 / :3 from dual

              But the operators cannot be bind variables. Thus if the formula itself is dynamic then you need to use dynamic SQL. E.g.

              declare
              formula varchar2(100);
              res number;
              begin
              formula := '5 + 4 - 2';
              execute immediate 'select '||formula||' from dual' into res;
              dbms_output.put_line( res );
              end;
              This is however not a good idea as this will generate unique and non-sharable SQL. Which in turn mucks up the Shared Pool, causes fragmentation and all kinds of ugly performance knocks.

              A better idea would be to use DBMS_SQL. Make the formula string include bind variables. Then use DBMS_SQL to create a dynamic SQL statement, parse it to obtain the bind variables. Bind these dynamically. Then execute the DBMS_SQL cursor and retrieve the result.

              Here is such an example. Note that TCursor in this example is a custom written o-o wrapper (using Oracle User Defined Types) for DBMS_SQL. Even so, the approach show remains the same if using DBMS_SQL directly:

              SQL> create or replace package lib_h as
              2
              3 type TNameValue is table of number index by varchar2(30);
              4 end;
              5 /

              Package created.

              SQL>
              SQL> create or replace procedure W( cLine varchar2 ) is
              2 begin
              3 DBMS_OUTPUT.put_line( cLine );
              4 exception when OTHERS then
              5 NULL;
              6 end;
              7 /

              Procedure created.

              SQL>
              SQL> create or replace function Calc( cExpression varchar2, uParams lib_h.TNameValue ) return number is
              2 cur TCursor; -- a custom developed ADT object class that wraps DBMS_SQL
              3 name varchar2(30);
              4 value number;
              5 rows$ number;
              6 begin
              7 cur := TCursor( 'select '||cExpression||' from dual' );
              8
              9 -- The TCursor class parses the SQL string, looking for bind variables.
              10 -- It publishes the list of bind variable names via the property VARIABLE.
              11 for i in 1..cur.Variable.Count
              12 loop
              13 name := cur.Variable(i); -- the name of the bind variable
              14 begin
              15 value := uParams( name ); -- find the value in the NameValue list
              16 exception when NO_DATA_FOUND then
              17 W( 'Error. Bind variable '||name||' was not found in the parameter NameValue list' );
              18 raise;
              19 end;
              20
              21 -- now we bind that value to the bind variable
              22 cur.Bind( name, value );
              23 end loop;
              24
              25 -- okay, let's try and execute the SQL
              26 cur.Execute;
              27
              28 -- fetch the first (and only) row
              29 cur.Next;
              30
              31 -- grab the 1st column from the cursor as a number data type
              32 value := cur.ColumnAsNumber(1);
              33
              34 -- close the cursor
              35 cur.Close;
              36
              37 -- return the value
              38 return( value );
              39 end;
              40 /

              Function created.

              SQL>
              SQL> declare
              2 v lib_h.TNameValue;
              3 n number;
              4 begin
              5 v('QTY') := 1; -- 1 widgets
              6 v('AMT') := 10; -- $10 per widget
              7 v('PROFIT') := 10/100; -- selling at 10% profit margin (i.e. $1 profit/widget)
              8
              9 -- This is what we want to do dynamically:
              10 -- SQL> select (:QTY * :AMT) + (:AMT * :QTY) * :PROFIT into N from dual USING 1, 20, (10/100)
              11 n := Calc( '(:QTY * :AMT) + (:AMT * :QTY) * :PROFIT', v );
              12 W( 'answer is '||n );
              13
              14 end;
              15 /
              answer is 11

              PL/SQL procedure successfully completed.

              SQL>
              • 4. Re: execute immediate
                vpolasa
                Thanks a lot Billy, your message was much helpful for my other tasks too.

                Regards.