Home » SQL & PL/SQL » SQL & PL/SQL » DYNAMIC SQL - GLOBAL VARIABLES
DYNAMIC SQL - GLOBAL VARIABLES [message #345707] Thu, 04 September 2008 10:17 Go to next message
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 238 times)
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345709 is a reply to message #345707] Thu, 04 September 2008 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Please explain what you hope to accomplish by this procedure.

[Updated on: Thu, 04 September 2008 10:23] by Moderator

Report message to a moderator

Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345719 is a reply to message #345709] Thu, 04 September 2008 10:42 Go to previous messageGo to next message
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 #345725 is a reply to message #345707] Thu, 04 September 2008 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

I see your words, but have no idea what is input, what processing is desired, what is expected output/results.

You're On Your Own (YOYO)!
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345728 is a reply to message #345725] Thu, 04 September 2008 11:09 Go to previous messageGo to next message
andydba
Messages: 34
Registered: September 2008
Member

I have uploaded everything. Please see attachment. I am uploading it once again.
  • Attachment: test.sql
    (Size: 1.48KB, Downloaded 118 times)
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345740 is a reply to message #345728] Thu, 04 September 2008 12:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #345798 is a reply to message #345707] Thu, 04 September 2008 18:58 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>cursor c1 is select 10 X1, 5 X2, 7 X3 from dual;
When are you going to stop hard coding & provide a truly flexible solution?

How do you plan on handling the case where the cursor above provides 1 - N variables?
Will this require more dynamic code?

Dynamic code scales slightly better than a pig can fly & can never be fully tested since results depend upon dynamic input data.

Good Luck!
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345812 is a reply to message #345707] Thu, 04 September 2008 22:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
This thread just reminded me these previous ones:
http://www.orafaq.com/forum/mv/msg/123830/342915/96705/
http://www.orafaq.com/forum/mv/msg/120234/326540/96705/
Enjoy it.

By the way, "X1 > X2 0.5" is not valid condition in any language I know; it at least misses operator after X2.


[Edit: Comparison operator can be used in conditions, not expressions. Corrected.]

[Updated on: Thu, 04 September 2008 22:30]

Report message to a moderator

Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345886 is a reply to message #345812] Fri, 05 September 2008 03:47 Go to previous messageGo to next message
andydba
Messages: 34
Registered: September 2008
Member

0.5 is the return value if x1 > x2
Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345891 is a reply to message #345886] Fri, 05 September 2008 03:59 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #345955 is a reply to message #345948] Fri, 05 September 2008 07:04 Go to previous messageGo to next message
anuragsv
Messages: 5
Registered: September 2008
Junior Member
Can't you make generic function for generating
replace statement.

You have to maintain the variable counter.

Depending on that your function will generate
replace statements.

Regards,
Anuragsv

[Updated on: Fri, 05 September 2008 07:06]

Report message to a moderator

Re: DYNAMIC SQL - GLOBAL VARIABLES [message #345976 is a reply to message #345707] Fri, 05 September 2008 07:52 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
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.

Previous Topic: Using updated values and inserting into table (merged)
Next Topic: Which is better? For loop or Using cursor
Goto Forum:
  


Current Time: Wed Dec 07 16:37:30 CST 2016

Total time taken to generate the page: 0.32084 seconds