Home » SQL & PL/SQL » SQL & PL/SQL » Help w/ execute immediate
Help w/ execute immediate [message #258901] Mon, 13 August 2007 14:37 Go to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
I have a table that contains specific field names of another table that are to be included in a shipping label. I am building a dynamic query based on these field names. The query comes out looking like this...

 v_query := 'select partnum into v_info from pick_request where pick_san = 7376';



I know that the query will return a value, but I get an error message when I "execute immediate v_query" on this. How do I capture the returned value for this query (there will be only one value returned for the query)?

Thanks,
Ron
Re: Help w/ execute immediate [message #258911 is a reply to message #258901] Mon, 13 August 2007 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64101
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 7 Performing SQL Operations with Native Dynamic SQL
Section Using the EXECUTE IMMEDIATE Statement in PL/SQL

Regards
Michel
Re: Help w/ execute immediate [message #258912 is a reply to message #258901] Mon, 13 August 2007 15:05 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
why oh why oh why are you doing an execute immediate for a simple select statement ?
Re: Help w/ execute immediate [message #258921 is a reply to message #258912] Mon, 13 August 2007 15:30 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Well, probably because there are an unknown number of columns that I will need to retrieve information from for each company we ship something to. Each label we put on a shipment has different fields that are dependant on the shipping requirements.

The actual code to generate each query looks like this...

         for lbl_field in (select printform_field
                           from   pickreq_barcode_specfields
                           where  barcode_retval = v_lbl_field_data
                           order by order_by ) loop
          v_query := 'select '||lbl_field.printform_field||' ';
          v_query := v_query || 'into v_info ';
          v_query := v_query || 'from pick_request ';
          v_query := v_query || 'where pick_san = 7376'; 
          
          execute immediate v_query;
 


I'm using one specific request to debug everything and make it work (pick_san = 7376). v_lbl_field_data is the ID of the company we need to ship to. Printform_field is the field in the main request table. There are six fields for this example. Another company only has four fields, and a third has seven fields.

I need to retrieve the data from each field and print it out (in a barcode font) so that shipping can scan the request with a barcode reader and populate their label software with the proper values.

I'll have to study the info in the Chapter 7 referenced above because I have an "into" clause that doesn't seem to work right.

Ron
Re: Help w/ execute immediate [message #258922 is a reply to message #258911] Mon, 13 August 2007 15:36 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Yea!!! Thanks for the link to the PL/SQL User's Guide and Reference. Finally found that I needed...

execute immediate query into v_info;



Which took my return value and placed it into the v_info variable. Shipping will be very happy now!

Ron
Re: Help w/ execute immediate [message #258971 is a reply to message #258922] Mon, 13 August 2007 23:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you do with the results of these queries?
Since you have to handle them afterwards, (because you use pl/sql) could you not just 'select *' and use only those columns you need?
This would only require one single sql to the table and gets rid of the awful dynamic sql.
Re: Help w/ execute immediate [message #259143 is a reply to message #258971] Tue, 14 August 2007 09:11 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Okay, here's an example of the data in my "shipping table"


Company     Field       Order_reqd
----------------------------------
ABC         lot_num     2
ABC         qty_per     3
ABC         part_num    1
NOP         lot_num     1
NOP         qty_per     3
NOP         wafer_num   2
NOP         bin_num     4
NOP         date_code   5
ZAB         date_code   1
ZAB         lot_num     2
ZAB         qty_per     3
ZAB         pkg_type    4


This table tells me that, for ABC, I need to query the DATA table for lot_num, qty_per and part_num. I then need to print out the information in the "Order_reqd" order, or

Part_num
lot_num
qty_per

So, your suggestion would be...

select *
from   shipping_table
where  company = 'ABC'
order by Order_reqd


This results in three returned values for ABC, five for NOP and four for ZAB. With those results, For ABC, I need to generate a query to the data table that looks something like...

select part_num, lot_num, qty_per 
into   v_part_num, v_lot_num, v_qty_per
from   data_table
where  order_id = '11324'


For company NOP, I need...

select lot_num, wafer_num, qty_per,
       bin_num, date_code
into   v_lot_num, v_wafer_num, v_qty_per,
       v_bin_num, v_date_code
from   data_table
where  order_id = '11324'


So, as you can see, the results returned are different for each company. A single query is not going to return the info I need. Since there's a dynamic amount of information to return, a dynamic query is needed.

Besides, I got the "execute immediate" query to work properly and there are now barcoded labels printing out for shipping, which was the original intent. And, as we move more companies to barcode labels, all I need to do is update the "shipping table" with the requirements for each company.

Of course, I'm always willing to look at other suggestions that would make the procedure more efficient.

HTH,
Ron
Re: Help w/ execute immediate [message #259150 is a reply to message #258971] Tue, 14 August 2007 09:23 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Actually, there's a much shorter answer than the one I just posted. See the code below...

         for lbl_field in (select printform_field
                           from   pickreq_barcode_specfields
                           where  barcode_retval = v_lbl_field_data
                           order by order_by ) loop
          v_query := 'select '||lbl_field.printform_field||' ';
          v_query := v_query || 'from pick_request ';
          v_query := v_query || 'where pick_san = 7376'; 
          
          execute immediate v_query into v_info;

          htp.p('<tr><td>'||lbl_field.printform_field||'</td><td><font face="IDAutomationHC39M">'||v_info||'</td></tr>');
 



Note that I print the results of each "execute immediate" query in a barcode font.

Ron
Re: Help w/ execute immediate [message #259308 is a reply to message #259143] Wed, 15 August 2007 00:11 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
My suggestion was to select * from data_table.
Process the results based on the columns in the shipping table.
This would make the SQL dynamic, and make the handling of the returned data (a constant set) dynamic, for which PL/SQL was made.
Previous Topic: SUM, DISTINCT and GROUP BY CUBE problem
Next Topic: Find names containing letters (Merged)
Goto Forum:
  


Current Time: Fri Dec 02 13:46:34 CST 2016

Total time taken to generate the page: 0.10670 seconds