Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Records question
PL/SQL Records question [message #268763] Wed, 19 September 2007 11:17 Go to next message
feign3
Messages: 5
Registered: September 2007
Junior Member
Hoping someone can help me with this:

I have a long list of parameters I need to pass to another procedure. Instead of passing 20 different parameters all to individual IN parameters in the called procedure, I'd like to just pass one record containing all parameters. I've never really worked with collections that much. Currently I have a this in my calling procedure containing all parameters to be passed:

TYPE lot_rectype IS RECORD
(t_cuspart VARCHAR2(240),
t_msi_item VARCHAR2(240),
t_del_id NUMBER,
t_v_3S_qty NUMBER,
t_3s_copies NUMBER,
t_cust_po VARCHAR2(240),
t_po_line_num NUMBER,
t_country VARCHAR2(240),
t_customer_number NUMBER,
t_ship_to_org_id NUMBER,
t_site_number NUMBER,
t_otlabel_name VARCHAR2(240),
t_kg NUMBER,
t_lb NUMBER,
t_tot_3s NUMBER,
t_3S_count NUMBER,
t_site_code VARCHAR2(240),
t_vendor_code VARCHAR2(240),
t_org_id NUMBER,
t_3S_group VARCHAR2(240),
t_otprinter VARCHAR2(240),
t_3Sgrp_id NUMBER
);

How do I now instantiate the variables within this collection with data to pass to the other procedure as one record?

[Updated on: Wed, 19 September 2007 11:18]

Report message to a moderator

Re: PL/SQL Records question [message #268766 is a reply to message #268763] Wed, 19 September 2007 11:30 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I don't think a record is a "collection" in pl/sql terms but you can confirm that.

http://sheikyerbouti.developpez.com/collections/collections.htm

Other alternatives to consider include
1.) "flexible paramater passing" where you pack arrays with parameter name and parameter values (all stuffed into text datatype).
http://technology.amis.nl/blog/?p=172

2.) just set global variables in a package from the one proc and reference them in the other proc. Of course you need to be careful with globals just like in any other language...

Re: PL/SQL Records question [message #268768 is a reply to message #268766] Wed, 19 September 2007 11:53 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
SQL> SET serveroutput on
SQL> DECLARE
  2     TYPE t_rec IS RECORD (
  3        empno   emp.empno%TYPE,
  4        ename   emp.ename%TYPE
  5     );
  6  
  7     l_rec   t_rec;
  8  
  9     PROCEDURE p2 (v_rec IN t_rec)
 10     IS
 11     BEGIN
 12        DBMS_OUTPUT.put_line ('P2 ename: ' || v_rec.ename);
 13     END;
 14  
 15     PROCEDURE p1
 16     IS
 17     BEGIN
 18        l_rec.empno := 123;
 19        l_rec.ename := 'scott';
 20        p2 (l_rec);
 21     END;
 22  BEGIN
 23     p1;
 24  END;
 25  /
P2 ename: scott

PL/SQL procedure successfully completed.

SQL> 
Re: PL/SQL Records question [message #268770 is a reply to message #268768] Wed, 19 September 2007 12:00 Go to previous message
feign3
Messages: 5
Registered: September 2007
Junior Member
Thanks Andrew...

Actualy I figured out the same solution about 20 minutes ago.

Thanks for your help... and yes, I think your correct. A PL/SQL Record does not qualify as a collection.

Thanks!
Craig
Previous Topic: varchar2 always inline?
Next Topic: EXECUTE IMMEDIATE statements do not work
Goto Forum:
  


Current Time: Sun Dec 11 00:41:48 CST 2016

Total time taken to generate the page: 0.08356 seconds