Home » SQL & PL/SQL » SQL & PL/SQL » Populating data into table type record and returning from procedure
Populating data into table type record and returning from procedure [message #395741] Thu, 02 April 2009 08:58 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to return the records for 3 columns.
I have declared the 'table type' in package as the three columns come from different sources.
One is the counter of the loop where the error validation failed.
Second is the application number and third is the error description for the data.

I created a package named "pks_ams_upload" where I declared the following:-

type lookup_row is record ( Application_no VARCHAR2(50), Line_No  NUMBER,error_desc  VARCHAR2(100) );
type lookups_tab is table of lookup_row;



Then I created the procedure as (only giving the pseudocode of the procedure):-
CREATE OR REPLACE PROCEDURE SP_AMS_DECISION_PARKING(p_file_id IN VARCHAR2,
                                                         p_failed_rec OUT NUMBER,
                                                    arr_lookups_tab OUT pks_ams_upload.lookups_tab )
IS

  
  v_query                     VARCHAR2 (500);
  v_row                       AMSUAT.pks_ams_upload.lookup_row;
  v_dupl_appl_no              NUMBER;
 

BEGIN
  OPEN cursor
  LOOP
   Fetch data
     IF data_1 is not valid then
         Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 1;
               
               v_row.Application_no := TRIM(c2.Application_no);
               v_row.Line_No := v_tot_rec;
               v_row.error_desc := v_error_desc;       

     end if;

     IF data_2 is not valid then
         Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 2;
               
               v_row.Application_no := TRIM(c2.Application_no);
               v_row.Line_No := v_tot_rec;
               v_row.error_desc := v_error_desc;       

     end if;
     IF data_3 is not valid then
         Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 3;
               
               v_row.Application_no := TRIM(c2.Application_no);
               v_row.Line_No := v_tot_rec;
               v_row.error_desc := v_error_desc;       

     end if;

end loop;
end;


If its an array then I know that I have to put
.extend
. But not sure how to keep the data populating in the table_type.
Also if its an array or ref cursor I know how to return. In ref cursor, I simply open the cursor.

Please advice as how to push the data in the table type object in the loop in different IF conditions and how to return it.


Regards,
Mahi
Re: Populating data into table type record and returning from procedure [message #395745 is a reply to message #395741] Thu, 02 April 2009 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>One is the counter of the loop where the error validation failed.
ERROR? What Error? I do not see any error.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Populating data into table type record and returning from procedure [message #395748 is a reply to message #395745] Thu, 02 April 2009 09:12 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I am not getting any error. I am asking you that how can I populate the table type array.
Its only data validation......like I have given you.
Its like
if application_no is null then 
   insert the error details in the array
end if;

if application_no is non-numeric then 
   insert the error details in the array
end if;


My concern is only how to populate table type record when we have to insert values into it in a loop in different IF conditions.
I hope I made it clear.


Mahi

[Updated on: Thu, 02 April 2009 09:14]

Report message to a moderator

Previous Topic: how to replace & with and in the given string
Next Topic: Last Value (merged)
Goto Forum:
  


Current Time: Fri Dec 09 23:12:23 CST 2016

Total time taken to generate the page: 0.07455 seconds