problem when passing values to Execute immediate [message #342699] |
Sun, 24 August 2008 06:47  |
tarekfathi2003
Messages: 23 Registered: August 2008 Location: kw
|
Junior Member |
|
|
Dear friends
I have two bind varaibles for a function I create it.
During the transaction, the value of these two parameters are the same all the time.then :P_TRX_ID represents the transaction id, and the :P_TRX_TYPE represents the transaction type.
I nees that these two parameters take the values that i pass whenever found in the statement.
--------------------------------------------
declare
vRESULT NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT (
RSK_GET_PKG.F_GET_ELM_VALUE(:P_TRX_ID,:P_TRX_TYPE,6)
/RSK_GET_PKG.F_GET_ELM_VALUE(:P_TRX_ID,:P_TRX_TYPE,4))
*100
FROM DUAL'
INTO vRESULT
USING 1,1;
DBMS_OUTPUT.PUT_LINE(vRESULT);
END;
/
ORA-01008: not all variables bound
ORA-06512: at line 4
---------------------------------------------------------
I need to pass the value 1,1 only one time. I do not want to repeat it.
Please help
|
|
|
|
Re: problem when passing values to Execute immediate [message #342704 is a reply to message #342701] |
Sun, 24 August 2008 07:46   |
tarekfathi2003
Messages: 23 Registered: August 2008 Location: kw
|
Junior Member |
|
|
dear Michel
thanks for reply;
-My oracle database version is 10g
-I can not use STATIC SQL because the SQL statement is compsed on runtime.
I make a form in whaich the user can compose a SQL statement and store it on the table s a String value.
Example:
-----------------------------------------------
name formula
-----------------------------------------------
cal salary f_get_val(:p_trx_id,:p_trx_type,1)/100
cal commission f_get_val(:p_trx_id,:p_trx_type,2)/f_get_val (:p_trx_id,:p_trx_type,6)*10
and so on.
so the formual is not fixed. the only thing which is fixed is the function f_get_val which thake three parameters
And i have a report that retrive this String and execute it on the run time and pass only two bing values to the Sql statement by executing EXECUTE Immediate command.
These two values are transaction id and transaction type.
All i need is that If the same bind varaiable appears two or more times
It should take the same value i passed
thanks in advance
|
|
|
|
Re: problem when passing values to Execute immediate [message #342708 is a reply to message #342704] |
Sun, 24 August 2008 08:46   |
annagel
Messages: 220 Registered: April 2006
|
Senior Member |
|
|
Like Michel said when using execute immediate to execute SQL names of he binds are ignored they will simply be matched one after the other.
An anonymous PL/SQL block on the other hand would pay attention to the variable names and first occurrence order would determine what is bound to what. Of course this would require changing around your code a bit and perhaps adding a third bind for the results.
|
|
|
Re: problem when passing values to Execute immediate [message #342736 is a reply to message #342708] |
Sun, 24 August 2008 15:03  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Like annagel said, you can change your code to dynamically execute a pl/sql block, so that you only have to pass each variable once, and add a third bind for the result. This is explained and demonstrated in the documentation:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#BHCGEFCA
I have provided a reproduction and correction of something similar to your code below, since your first post and last post are a little different.
-- function and forumla variable for demo:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION f_get_val
2 (p_trx_id IN NUMBER,
3 p_trx_type IN NUMBER,
4 p_num IN NUMBER)
5 RETURN NUMBER
6 AS
7 v_result NUMBER;
8 BEGIN
9 -- calculation for demo:
10 v_result := p_trx_id * p_trx_type * p_num;
11 RETURN v_result;
12 END f_get_val;
13 /
Function created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE formula VARCHAR2 (2000)
SCOTT@orcl_11g> EXEC :formula := 'f_get_val(:p_trx_id,:p_trx_type,2)/f_get_val (:p_trx_id,:p_trx_type,6)*10'
PL/SQL procedure successfully completed.
-- reproduction of problem:
SCOTT@orcl_11g> declare
2 vRESULT NUMBER;
3 BEGIN
4 EXECUTE IMMEDIATE
5 'SELECT ' || :formula || ' FROM DUAL'
6 INTO vRESULT
7 USING 1, 1;
8 DBMS_OUTPUT.PUT_LINE('Result: ' || vRESULT);
9 END;
10 /
declare
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 4
-- solution:
SCOTT@orcl_11g> declare
2 vRESULT NUMBER;
3 BEGIN
4 EXECUTE IMMEDIATE
5 'BEGIN
6 SELECT ' || :formula || ' INTO :b_result FROM DUAL;
7 END;'
8 USING IN 1, 1, OUT vRESULT;
9 DBMS_OUTPUT.PUT_LINE('Result: ' || vRESULT);
10 END;
11 /
Result: 3.33333333333333333333333333333333333333
PL/SQL procedure successfully completed.
SCOTT@orcl_11g>
|
|
|