Dynamic SQL in cursor [message #642570] |
Mon, 14 September 2015 11:15 |
|
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 |
|
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 #642610 is a reply to message #642574] |
Tue, 15 September 2015 08:49 |
|
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 |
|
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 #642615 is a reply to message #642613] |
Tue, 15 September 2015 09:32 |
|
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 #642618 is a reply to message #642617] |
Tue, 15 September 2015 09:39 |
|
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 #642620 is a reply to message #642618] |
Tue, 15 September 2015 09:49 |
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 #642625 is a reply to message #642623] |
Tue, 15 September 2015 10:23 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
phiroc wrote on Tue, 15 September 2015 16:07OK, 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 #642631 is a reply to message #642626] |
Tue, 15 September 2015 11:05 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
phiroc wrote on Tue, 15 September 2015 16:29Ah, 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.
|
|
|