This content has been marked as final. Show 4 replies
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.
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?
SELECT 5+4 INTO v_result FROM dual;
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.
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.
formula := '5 + 4 - 2';
execute immediate 'select '||formula||' from dual' into res;
dbms_output.put_line( res );
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
3 type TNameValue is table of number index by varchar2(30);
SQL> create or replace procedure W( cLine varchar2 ) is
3 DBMS_OUTPUT.put_line( cLine );
4 exception when OTHERS then
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;
7 cur := TCursor( 'select '||cExpression||' from dual' );
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
13 name := cur.Variable(i); -- the name of the bind variable
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' );
21 -- now we bind that value to the bind variable
22 cur.Bind( name, value );
23 end loop;
25 -- okay, let's try and execute the SQL
28 -- fetch the first (and only) row
31 -- grab the 1st column from the cursor as a number data type
32 value := cur.ColumnAsNumber(1);
34 -- close the cursor
37 -- return the value
38 return( value );
2 v lib_h.TNameValue;
3 n number;
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)
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 );
answer is 11
PL/SQL procedure successfully completed.
Thanks a lot Billy, your message was much helpful for my other tasks too.