Home » SQL & PL/SQL » SQL & PL/SQL » problem when passing values to Execute immediate
problem when passing values to Execute immediate [message #342699] Sun, 24 August 2008 06:47 Go to next message
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 #342701 is a reply to message #342699] Sun, 24 August 2008 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

2/ always post your Oracle version (4 decimals).

3/ they are 4 variables in your statement and you give 2 values (the variable name inside the string does not matter)

4/ why don't you use a STATIC SQL, nothing is dynamic?

5/ why do you SQL to make a simple operation? PL/SQL knows how to do it.

Regards
Michel
Re: problem when passing values to Execute immediate [message #342704 is a reply to message #342701] Sun, 24 August 2008 07:46 Go to previous messageGo to next message
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 #342707 is a reply to message #342699] Sun, 24 August 2008 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Re: problem when passing values to Execute immediate [message #342708 is a reply to message #342704] Sun, 24 August 2008 08:46 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 8636
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> 

Previous Topic: dynamic decode
Next Topic: Question on RefCursor result count
Goto Forum:
  


Current Time: Sat Dec 10 03:10:01 CST 2016

Total time taken to generate the page: 0.04669 seconds