Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL in cursor
Dynamic SQL in cursor [message #642570] Mon, 14 September 2015 11:15 Go to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
Hello,

the following function does not work, probably because of the following line:

Quote:
lv_return_value := r1.':col_name_in';


How can I bind the col_name_in variable in this case?

Many thanks.

phiroc




	FUNCTION vf_compute_field_value ( vendor_form_id_in IN vendor_form.id%TYPE, col_name_in IN VARCHAR2(30) )
		RETURN VARCHAR2 IS
		lv_return_value political_union.goods_declaration_point%TYPE;
		CURSOR c1 IS
			EXECUTE IMMEDIATE
			'SELECT political_union.' || col_name_in || ' INTO lv_return_value FROM '
			|| 'vendor_form INNER JOIN vendor ON vendor_form.vendor_id = vendor.id ' 
			|| 'INNER JOIN address ON vendor.address_id = address.id ' 
			|| 'INNER JOIN country ON address.country_id = country.id ' 
			|| 'INNER JOIN political_union ON country.political_union_id = political_union.id ' 
			|| 'WHERE vendor_form.id = ' || vendor_form_id_in
			;
	BEGIN
				FOR r1 IN c1 LOOP
        EXIT WHEN c1%notfound;
        lv_return_value := r1.':col_name_in';
    END LOOP;
		RETURN lv_return_value;
	END;	





EDIT: changed quotes tags to code tags

[Updated on: Tue, 15 September 2015 09:02] by Moderator

Report message to a moderator

Re: Dynamic SQL in cursor [message #642572 is a reply to message #642570] Mon, 14 September 2015 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't. You have to write it another way.

Note that your function does not compile:
SQL> create or replace FUNCTION vf_compute_field_value ( vendor_form_id_in IN vendor_form.id%TYPE, col_name_in IN VARCHAR2(30) )
  2  RETURN VARCHAR2 IS
  3  lv_return_value political_union.goods_declaration_point%TYPE;
  4  CURSOR c1 IS
  5  EXECUTE IMMEDIATE
  6  'SELECT political_union.' || col_name_in || ' INTO lv_return_value FROM '
  7  || 'vendor_form INNER JOIN vendor ON vendor_form.vendor_id = vendor.id '
  8  || 'INNER JOIN address ON vendor.address_id = address.id '
  9  || 'INNER JOIN country ON address.country_id = country.id '
 10  || 'INNER JOIN political_union ON country.political_union_id = political_union.id '
 11  || 'WHERE vendor_form.id = ' || vendor_form_id_in
 12  ;
 13  BEGIN
 14  FOR r1 IN c1 LOOP
 15  EXIT WHEN c1%notfound;
 16  lv_return_value := r1.':col_name_in';
 17  END LOOP;
 18  RETURN lv_return_value;
 19  END;
 20  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION VF_COMPUTE_FIELD_VALUE:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
1/100    PLS-00103: Encountered the symbol "(" when expecting one of the following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.
5/1      PLS-00103: Encountered the symbol "EXECUTE" when expecting one of the following:
         ( select <a SQL statement>

[Updated on: Mon, 14 September 2015 11:25]

Report message to a moderator

Re: Dynamic SQL in cursor [message #642573 is a reply to message #642570] Mon, 14 September 2015 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And if you want to continue to get help you should think about feedback and thanks people in your previous topic BEFORE posting another one.

Re: Dynamic SQL in cursor [message #642574 is a reply to message #642570] Mon, 14 September 2015 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What problem are you trying to solve?

How will you & I know when it has been solved?


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Dynamic SQL in cursor [message #642610 is a reply to message #642574] Tue, 15 September 2015 08:49 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
There are numerous syntax errors in your code. Your function returns a VARCHAR2 value, but your code implies your "lv_return_value" variable should be a collection - not typed off a column value. You can't put more than one row value into this type of variable unless you contatenate them together. Also, you can't use a Named Cursor with Native Dynamic SQL (NDS). And, since the goal of your function appears to be "Return the record set produced by your NDS query", you don't need to loop through the result set, you simply need to Execute you NDS statement and return it INTO a collection type variable so you can return it to whatever is calling your function.

Rather than try to help you with the logic of this function, perhaps you could tell what you need to accomplish and we can offer you suggestions on how to accomplish it. As BlackSwan stated, "What is the problem you are trying to solve?" Once we know what you're trying to solve, we can tell you if you are going about it the right way.

Craig...
Re: Dynamic SQL in cursor [message #642612 is a reply to message #642610] Tue, 15 September 2015 09:09 Go to previous messageGo to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
Hi CraigB,

thanks for your feedback.

In my database design, a VENDOR_FORM has a VENDOR associated with it. Each VENDOR as a single ADDRESS, and each ADDRESS has a COUNTRY which is part of of POLITICAL_UNION.

A POLITICAL_UNION contains several columns, e.g., vat_registration, vat_default, vat_serv_treat_seller_location, etc.

What I am trying to do here to create a generic PL/SQL function which will return the value stored in the POLITICAL_UNION column whose name I pass as parameter 2 (col_name_in).

The reason why I am using a cursor here is that, if the vendor_form_id passed as parameter 1 does not exists, Oracle throws a "unset variable" exception (I borrowed this code from Stack Overflow).

Please note that the cursor code works when the PL/SQL function does not have any dynamic SQL code (EXECUTE IMMEDIATE, variables, etc.)

Cheers,

Phiroc





Re: Dynamic SQL in cursor [message #642613 is a reply to message #642612] Tue, 15 September 2015 09:16 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is the dynamic query only ever supposed to return a single row?
And how many columns are we talking about?
Re: Dynamic SQL in cursor [message #642615 is a reply to message #642613] Tue, 15 September 2015 09:32 Go to previous messageGo to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
Yes, a single row, because a Vendor Form is associated with only one Vendor who only has one Address, one Country and one Political Union.

We are talking about 18 columns (not my design. The "inspiration" for this table design is a legacy spreadsheet which contains 18+ fields with computed values).

I have created 18+ functions, each retrieving data from a particular POLITICAL_UNION column. But a dynamic function would be better, if feasible.





[Updated on: Tue, 15 September 2015 09:33]

Report message to a moderator

Re: Dynamic SQL in cursor [message #642616 is a reply to message #642613] Tue, 15 September 2015 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>A POLITICAL_UNION contains several columns, e.g., vat_registration, vat_default, vat_serv_treat_seller_location, etc.
You get lucky if all the columns (above) are VARCHAR2, but have a (serious?) problem if/when multiple datatype exist.
Re: Dynamic SQL in cursor [message #642617 is a reply to message #642616] Tue, 15 September 2015 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
So, per Blackswan, are the columns the same datatype?
Re: Dynamic SQL in cursor [message #642618 is a reply to message #642617] Tue, 15 September 2015 09:39 Go to previous messageGo to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
I have decided to make them all VARCHAR2(20), for simplicity's sake. Some may contain 2 or 3 characters, whereas others may contain more than 10 (and <= 20).

[I am referring to POLITICAL_UNION's columns, of course, in case that's not clear].

[Updated on: Tue, 15 September 2015 09:43]

Report message to a moderator

Re: Dynamic SQL in cursor [message #642619 is a reply to message #642618] Tue, 15 September 2015 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT all 18 columns & only return the requested column
Re: Dynamic SQL in cursor [message #642620 is a reply to message #642618] Tue, 15 September 2015 09:49 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You overcomplicated it originally, it's simply this:


FUNCTION vf_compute_field_value ( vendor_form_id_in IN vendor_form.id%TYPE, col_name_in IN VARCHAR2(30) )
RETURN VARCHAR2 IS

  lv_return_value political_union.goods_declaration_point%TYPE;

BEGIN
  
  EXECUTE IMMEDIATE
      'SELECT political_union.' || col_name_in || ' INTO lv_return_value FROM '
      || 'vendor_form INNER JOIN vendor ON vendor_form.vendor_id = vendor.id ' 
      || 'INNER JOIN address ON vendor.address_id = address.id ' 
      || 'INNER JOIN country ON address.country_id = country.id ' 
      || 'INNER JOIN political_union ON country.political_union_id = political_union.id ' 
      || 'WHERE vendor_form.id = ' || vendor_form_id_in INTO lv_return_value;
      
  RETURN lv_return_value;

END;  



You shouldn't use it though. It's a performance nightmare waiting to happen.
Say you have a query that selects n records from vender_form and calls this function to get some column.
That means it'll scan 5 tables n times each.
Say you have a query that selects n records from vender_form and calls this function twice to get two different columns.
It'll scan 5 tables 2n times each.
Say you have a query that selects n records from vender_form and calls this function three times to get three different columns.
It'll scan 5 tables 3n times each.
.....
etc, etc

The correct solution is to modify the orginal query to join to political_union.
Re: Dynamic SQL in cursor [message #642621 is a reply to message #642620] Tue, 15 September 2015 09:58 Go to previous messageGo to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
cookiemonster,

are you suggesting I do something like this?

SELECT political_union.* FROM vendor_form INNER JOIN vendor ON vendor_form.vendor_id = vendor.id
...

IF (col_name_in = 'vat_registration') THEN
	RETURN political_union.vat_registration
ElSEIF (col_name_in = 'vat_default') THEN
	RETURN political_union.vat_default
	...
Re: Dynamic SQL in cursor [message #642622 is a reply to message #642621] Tue, 15 September 2015 10:02 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
No - that's Blackswan's suggestion.
I'm suggesting you flat out avoid having a generic function in the first place.
Re: Dynamic SQL in cursor [message #642623 is a reply to message #642622] Tue, 15 September 2015 10:07 Go to previous messageGo to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
OK, many thanks. I will therefore stick to my 18+ functions.
Re: Dynamic SQL in cursor [message #642624 is a reply to message #642623] Tue, 15 September 2015 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And where is the feedback in your previous topic you promised us?

[Updated on: Tue, 15 September 2015 10:17]

Report message to a moderator

Re: Dynamic SQL in cursor [message #642625 is a reply to message #642623] Tue, 15 September 2015 10:23 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
phiroc wrote on Tue, 15 September 2015 16:07
OK, many thanks. I will therefore stick to my 18+ functions.

You don't want any functions.
You want joins in existing queries.
The performance problem doesn't come from generic function vs 18 functions.
It comes from functions vs joins.

The explanation I gave above about how many table scans (or index scans) you will do remains just as true with 18 functions.
Re: Dynamic SQL in cursor [message #642626 is a reply to message #642625] Tue, 15 September 2015 10:29 Go to previous messageGo to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
Ah, I see. My Java/Hibernate code should do the joins, not depend on PL/SQL functions.
Re: Dynamic SQL in cursor [message #642627 is a reply to message #642626] Tue, 15 September 2015 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 15 September 2015 17:15

And where is the feedback in your previous topic you promised us?

Re: Dynamic SQL in cursor [message #642629 is a reply to message #642627] Tue, 15 September 2015 10:41 Go to previous messageGo to next message
phiroc
Messages: 10
Registered: September 2015
Junior Member
I have just fed it back.
Re: Dynamic SQL in cursor [message #642631 is a reply to message #642626] Tue, 15 September 2015 11:05 Go to previous message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
phiroc wrote on Tue, 15 September 2015 16:29
Ah, I see. My Java/Hibernate code should do the joins, not depend on PL/SQL functions.


If that's where you're defining your queries then yes.
Previous Topic: Using constants
Next Topic: Options for definitions of INDEXES used by CONSTRAINTS
Goto Forum:
  


Current Time: Fri Apr 26 16:48:06 CDT 2024