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 <drega...@gmail.com> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
