Patrico,
Thank you for your insight. It has helped me alot, along with
everything else i have been able to read from the forums.
I feel i am alot closer now than i was before. I'm working in TOAD
(v9.7.2.5) for Oracle (v11.5.10)
Is there anyway that i can test this without packing it, or setting it
into a procedure while i'm running it...
Second, I'm having a problem (and i'm trying to test it before
packing)
I have crafted the following :
TYPE T_METER IS RECORD(
Effective_Date eb_division_order_summaries.effective_date%TYPE,
Internal_GWI eb_division_order_summaries.internal_gwi%TYPE,
AFE_Number ee_itd_rc_associations.afe_number%TYPE);
TYPE TBL_METER IS TABLE OF T_METER;
TMETER TBL_METER;
Select * from TBL_METER;
Technically, that should have returned me a resultset of
effective_date, intern_gwi, afe_number with no rows.
Then, if i put this into it:
SELECT MAX(eb_division_order_summaries.effective_date)
Effective_Date,
MAX(eb_division_order_summaries.internal_gwi)
Internal_GWI,
ee_itd_rc_associations.afe_number AFE_Number
BULK COLLECT into TBL_METER
FROM eb.eb_division_order_summaries,
eb.eb_doi_report_centers,
ee.ee_itd_rc_associations
WHERE (eb_doi_report_centers.do_uid =
eb_division_order_summaries.do_uid)
AND (ee_itd_rc_associations.rc_uid =
eb_doi_report_centers.rc_uid)
GROUP BY ee_itd_rc_associations.afe_number;
And then run
Select * from TBL_METER;
I should get all of my columns. Instead it tells me that :
Bulk Collect feature has not been implemented yet, Invalid query,
etc.
If you know of a way i can run it without packing it would be great.
I think i'm on the right track as far as code goes.
On Aug 10, 10:53 am, Patricio Rodriguez <[email protected]>
wrote:
> If you want to store in an array, you have to declare (inside the package
> for example)
>
> CREATE OR REPLACE PACKAGE pck_addresses
> AS
> -- this is a record structure where the table is based --
> TYPE r_addresses IS RECORD (
> name tbladdress.name%TYPE
> ,phone tbladdress.phone%TYPE
> ,address1 tbladdress.address1%TYPE
> ,city tbladdress.city%TYPE
> ,state tbladdress.state%TYPE
> );
>
> -- this is the table of the record you declare before
> TYPE t_addresses
> IS
> TABLE OF r_addreses
> INDEX BY PLS_INTEGER;
>
> --INDEX BY PLS_INTEGER --WITH THIS THE TABLE IS INDEXED USING NUMBERS IN
> INTERNAL REPRESENTATION
> -- VARCHAR2(10) -- WITH THIS THE TABLE IS INDEXED USING
> VARCHAR2 DATA
> PROCEDURE get_rec_for_city (p_city IN tbladdresses.city%TYPE
> ,p_tab_of_add OUT t_addresses
> );
> END pck_addresses;
>
> CREATE OR REPLACE PACKAGE BODY pck_addresses
> AS
> l_table_of_addreses t_addresses;
> l_record_of_addresses r_addresses;
>
> PROCEDURE get_rec_for_city (p_param IN tbladdresses.city%TYPE
> ,p_tab_of_add OUT t_addresses
> )
> IS
> BEGIN
> -- this is just an example --
> -- dont do this in production, you have to limit the bulk
> -- into a collection
> -- or fill the pl/sql table one by one, extending the array.
> SELECT name, phone, address1, city, state
> BULK COLLECT
> INTO p_tab_of_add
> FROM tbladdress
> WHERE city = p_param;
> EXCEPTION
> WHEN OTHERS THEN
> --- tell the error
> raise_application_error (-20000, 'Error, something happened');
> END;
> END pck_addresses;
>
> If you want to use a temporary table (like in sqlserver i think it use) ,
> you have to create that table first, then fill that table and work like any
> other table.
> Hope this would help you.
>
> Sorry for my english, if there is some sintactic error.
>
>
>
>
>
> On Mon, Aug 10, 2009 at 10:48 AM, DanRegalia <[email protected]> wrote:
>
> > Hey Everyone.
> > I'm a MS Dev, thats been asked to do some Oracle work.. It's not a
> > problem really, but I'm running into a problem trying to understand
> > how the Declare Type Table works.
>
> > If anyone would have the time to show me how this would work, I would
> > appreciate it.
>
> > We'll say we have a simple query:
>
> > Select Name, Phone, Address1, City, state from tblAddress;
>
> > We will also say that:
>
> > Name is a varchar(20)
> > Phone is a varchar(10)
> > Address is a varchar(25)
> > City is varchar(25)
> > State is char(2)
>
> > So... With these pieces of information, How would I Declare a Table
> > for use inside of a procedure?
> > Hopefully this will help me understand how the syntax works for the
> > Declare Table type. Also, if anyone knows of any information that I
> > might be able to read/skim up on, i'd appreciate it.
>
> > ~Dan
>
> --
> Patricio Rodriguez- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
