DYNAMIC SQL - GLOBAL VARIABLES [message #345707] |
Thu, 04 September 2008 10:17  |
andydba
Messages: 34 Registered: September 2008
|
Member |
|
|
Hi,
Attached is my code.
I am not able to understand why my dynamic SQL is not executing. I have opened a cursor and passing values fetched from the cursor to dynamic sql. Dynamic SQL is within the scope of the cursor. While executing dynamic SQL it says "xxx variable not declared"
Please suggest what to do?
-
Attachment: test.sql
(Size: 1.48KB, Downloaded 886 times)
|
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345719 is a reply to message #345709] |
Thu, 04 September 2008 10:42   |
andydba
Messages: 34 Registered: September 2008
|
Member |
|
|
Hi,
I have a table where some conditions are stroed. You can see that in the formula table
X1 > X2
There would be many conditions like this in this table. For example I have just added two.
I need to process these conditions for a given set of data. This data would come from a cursor. Cursor columns will have same alias name as mentioned in the condition.
I want to replace X1 > X2 with CURSOR VALUES, so that the conditoin can be processed and result is obtrained.
Andy
|
|
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345740 is a reply to message #345728] |
Thu, 04 September 2008 12:33   |
andydba
Messages: 34 Registered: September 2008
|
Member |
|
|
Okay. Let me explain in detail.
I have a table in which few conditions would be stroed. This table will have two columns, "condtion", "value". Value column would be returned if condition is true.
Example of condition and value is:
X1 > X2 0.5
X1 > X2 AND X1 > X3 1.5
X1, X2, X3 does not hold any value. These variables will be replaced with some data fetched from another table and condition will be processed using dynamic sql. If any of the stored condition is ture, I need to return value column.
To acheive this, I created a cursor:
cursor c1 is
select 10 X1, 5 X2, 7 X3
from dual;
When I open this cursor and also read data from condition table I need to replace X1 given in the condition with X1 coming from cursor.
How do I do that?
There would be lot of conditions and variables. For testing purpose I am only using two conditions with three variables.
Thanks
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345796 is a reply to message #345740] |
Thu, 04 September 2008 18:12   |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Andy, try rewording the test so that the EXECUTE IMMEDIATE
executes the condition using SQL instead of the IF statement.
I don't have the syntax off the top of my head for the
EXECUTE IMMEDIATE/USING structure but i'm pretty sure the error is because you need bind variables for the R1.X's.
I suggest transforming something a little easier first to get the syntax down.
Like a "SELECT 1 into FLAG from dual where R1.X1 > R1.X2".
You would replace the X with :somevar and
the R1.X1 and R1.X2 would be assigned in the 'USING'
clause.
This is a topic that intrigues me because I wanted to go this route for some dynamic rules processing. Love to exchange ideas,
would like to know a little more project background.
Is there a necessity to absolutely store the conditions the way
you are in the table? Things as you may have speculated will get
a bit hairy when your conditions can hold one or many variables
and you are parsing the and/or's.
I originally conceptualized a pre-processing routine that would
take business rules and for various types of common validations store them as prepared DECODE statements from an input
template.
However, my thought was - how about grouping your AND's with multiple rows on the table - one condition per row.
Get the initial test you have working with proper bind variable assignment and then you can avoid probs when the conditions expand.
I'm going to do a little Execute Immediate reading right now!
What I really want to know is how you determine what condition will apply to what cursor or process - there is a whole
other route to implement this that pivots on this answer.
What will dictate when or where a condition will apply
for variable substitution?
Regards,
Harry
|
|
|
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345891 is a reply to message #345886] |
Fri, 05 September 2008 03:59   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
I am sorry, I did not read the accompanying comments, just focused on that conditions.
So, it was not so difficult to miss it, especially as it was not formatted. Do you not think, that following part would be more convenient to read?
Condition Val
------------------- ---
X1 > X2 0.5
X1 > X2 AND X1 > X3 1.5
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345894 is a reply to message #345798] |
Fri, 05 September 2008 04:06   |
andydba
Messages: 34 Registered: September 2008
|
Member |
|
|
Let me explain the exact requirement.
My company is into leasing business. Whenever a new lease is signed up, the sales person gets commission for that.
I need to process commssion for each lease signed in a particular month. For example if 100 leases are signed in the month of September, I need to process commission for each of these 100 leases.
Query to fetch this lease information is stored in a table and can only be configured by DBA team through backend.
We have provided end users (commissioning department) with a GUI to define commssion rules. They will have predefined fields in the front end and various operators (> , < , - , * ). Users use this GUI to define various conditions by using this GUI. These conditions are stored in the database. There would be n number of conditions.
Now on where this x1 , x2 and other variables came from?
The front end that is used to define conditions actually picks up all the fields from a database table and this table has a mapping of variables and database fields, e.g.
x1 refers to table_a.column_name
This table is also configured through backend and number of variables are predefined.
At runtime, I don't have any idea which condition would apply to which lease. When I start reading lease data, I also read all the conditions from the database table and start replacing variables withe lease values.
What I tried doing was, declaring a cursor having column alias same as variables defined in the mapping table (table that stores x1 mapping with column name).
My dynamic sql is within the scope of OPEN and CLOSE cursor. I thought dynamic sql will automatically use cursor columns as columns have alias same as variables.
Whenever a conditons is satisified, I need to retun the retun values for the satisfied condition and stop the execution of the code.
Let me know if I was able to explain the requirement.
Andy
[Updated on: Fri, 05 September 2008 04:13] by Moderator Report message to a moderator
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345912 is a reply to message #345894] |
Fri, 05 September 2008 04:29   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here is a version of your code that will work with your tables, and gets you round the variable number of Bind variables problem:
All you need to do is ensure that in the definition of Sqlstr there are as many Xn variables defined as there are columns in the C1 cursor;declare
cursor c1 is
select 10 X1
,5 X2
,7 X3
from dual;
type rec_formula is record
(formula_condition formula.FORMULA_CONDITION%type,
return_value formula.RETURN_VALUE%type);
type t1 is table of rec_formula index by binary_integer;
a1 t1;
sqlstr varchar2(4000);
flag number;
begin
select formula_condition, return_value
bulk collect into a1
from formula;
for r1 in c1 loop
for k in 1..a1.count loop
dbms_output.put_line('R1.X1 : '||R1.X1);
dbms_output.put_line('R1.X2 : '||R1.X2);
dbms_output.put_line('R1.X3 : '||R1.X3);
sqlstr := 'declare x1 number := :x1; x2 number := :x2; x3 number := :x3;'||
' begin if '||a1(k).formula_condition||' then :flag := 0; else :flag := 1; end if; end;';
dbms_output.put_line(sqlstr);
execute immediate sqlstr using in r1.x1,r1.x2,r1.x3, out flag;
if flag = 0 then
dbms_output.put_line('Passed');
else
dbms_output.put_line('Failed');
end if;
end loop;
end loop;
end;
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345914 is a reply to message #345894] |
Fri, 05 September 2008 04:34   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
sqlstr :=
'begin if '||
REPLACE(
REPLACE
(
REPLACE(a1(k).formula_condition,
'X1',R1.X1),
'X2',R1.X2),
'X3',R1.X3)||'
then :flag := 0;
else
:flag := 1;
end if;
end;';
Try this.
For n number of varibles you have to store
a counter in table that will tell how many
varibles in formula_condition.
Regards,
Rajat
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345948 is a reply to message #345914] |
Fri, 05 September 2008 06:55   |
andydba
Messages: 34 Registered: September 2008
|
Member |
|
|
Solution posted by JRowBottom is not gonna work. USING in in dynamic sql can not work because every condition stored in the table will have different number of variables. I am not sure if oracle will compalin in case condition only has two variables and I am passing 5 with USING clause .
Solution posted by Rajatratewal could work. In this case I will have to initially write a long replace statement with all the variables defined in the table. What will happen if I go and introduce a new variable and column mapping. I will have to go and modify my code (replace statement) everytime a new variable and column mapping is added.
Andy
|
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345976 is a reply to message #345707] |
Fri, 05 September 2008 07:52   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> I am not sure if oracle will compalin in case condition only has two variables and I am passing 5 with USING clause .
As all (in JRowbottom's example 3) parameters are passed to local variables of the dynamic PL/SQL block, there is no reason to complain.
As for REPLACE solution, you have to be very careful when the variable names are prefixes of another variable names:
SQL> select replace( replace( replace( 'X11 < X1 + X2', 'X1', 5 ), 'X2', 2 ), 'X11', 3 )
2 from dual;
REPLACE(RE
----------
51 < 5 + 2
SQL> select replace( replace( replace( 'X11 < X1 + X2', 'X11', 3 ), 'X1', 5 ), 'X2', 2 )
2 from dual;
REPLACE(R
---------
3 < 5 + 2
SQL> But, instead of messing with variable order, I would rather use REGEXP_REPLACE (available from 10g).
|
|
|
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345984 is a reply to message #345948] |
Fri, 05 September 2008 08:07  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
My solution will work, as long as you have the Sqlstring defined in such a way that every bind variable is loaded innto the Declare section of the Pl/sql block.
As long as you know the maximum number of individual bind variables you will have,you can make it work.
|
|
|