How can i pass in a ROWTYPE to an procedure an populate it into an ARRAY?

I have a procedure called A_LOAD and I want to pass a record(%ROWTYPE) it an load the record type into an array.
Table_a  is defined as
Empno number
dept number
empname

I want to take all the records beign passed into the procedure and load it into the table. then I want to take the table a FETCH each column from it. I just dont know if i need a CURSOR or not.

ANy help will be appreciated.


Create or replace procedure A_LOAD
  (P_rec_in	 Table_A%rowtype) is
 
 TYPE C1_record_table is TABLE OF P_REC_IN%rowtype by binary_integer;
 C1_REC C1_record_table;
 
BEGIN

Open in new window

joekeriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
to keep the array between two load calls you wil need a package

create or replace package loadpck

procedure init;
procedure a_load   (P_rec_in      Table_A%rowtype) ;
procedure show;
end;

create or replace package body loadpck

 TYPE C1_record_table is TABLE OF Table_A%%rowtype by binary_integer;
 C1_REC C1_record_table;

procedure init
is
begin
  c1_rec.delete;
end;

procedure A_LOAD
  (P_rec_in      Table_A%rowtype) is
BEGIN
    c1_rec(c1_rec.count + 1) := p_rec_in;
END;

procedure show
is
begin
   if c1_rec.count = 0 then
     dbms_output.put_line('pl/sql table is emptye');
   else
       for i1 in 1 ..  c1_rec.count  loop
           dbms_output.put_line(c1_rec(i1).empname);
       end loop;
   end if;
end;
 
end;
/

i don't understand what you mean with
 then I want to take the table a FETCH each column from it. I just dont know if i need a CURSOR or not.

what do you want to do with the data in the internal table ?

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joekeriAuthor Commented:
What i wanted to to was to pass the entire table into the procedure and then Fetch/read each record and do a DBMS_OUPUT from it.

I undrstand your code, just wonder if i really need to use a package. Coulnt i blend your 3 procedures into a single procedure instead?
0
AndytwCommented:
joekeri:  The example that flow01 gave uses a package - and this is really the best way to do this - this is how I'd solve this.  There's nothing wrong with using packages.

However, if you *really* want to try this in a single procedure you could either:
1). Create database types and use that as type for your procedure parameter

2). pass a REF CURSOR (weak) instead of a collection in your procedure. You can do this in a single procedure since it isn't strongly typed

I've given a code example for option 2 below
CREATE OR REPLACE PROCEDURE a_load(cCursor IN OUT SYS_REFCURSOR) IS
  TYPE C1_record_table is TABLE OF table_A%rowtype INDEX BY binary_integer;
  records c1_record_table;
BEGIN
 
  FETCH cCursor BULK COLLECT INTO records;
  CLOSE cCursor;
 
  FOR i IN 1..records.COUNT
  LOOP
    dbms_output.put_line('empname: ' || records(i).empname);
    dbms_output.put_line('empno: ' || records(i).empno  || CHR(10) );
    
  END LOOP;
END;
/
 
SQL> DECLARE
  table_A_cursor SYS_REFCURSOR;
BEGIN
  OPEN table_A_cursor FOR SELECT * FROM table_a;
  
  a_load(table_a_cursor);
END;
/
empname: Andy Smith
empno: 20
 
empname: John Doe
empno: 30
 
empname: Jamie Edwards
empno: 40
 
empname: Stuart Jones
empno: 50
 
empname: Claire Guy
empno: 60
 
PL/SQL procedure successfully completed.
 
SQL>

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.