This discussion is archived
4 Replies Latest reply: Dec 6, 2007 9:46 AM by vpolasa RSS

execute immediate

vpolasa Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot Billy, your message was much helpful for my other tasks too.

    Regards.