Home » SQL & PL/SQL » SQL & PL/SQL » How to work on a table type in a procedure (Oracle 10g)
How to work on a table type in a procedure [message #395857] Thu, 02 April 2009 22:45 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to return 3 parameteres from a procedure. A number type, a varchar type and last is a table type.

I know how to return the first two, for the table type record I got a good example but its a function which would return only one parameter whereas I need all 3.

I tried to the same in my procedure but it says that "PIPELINED" can be used only in functions. So I can't use the below exmaple in my procedure.
Pease suggest me how to do the same in a procedure.

I know how to return a single dimension array and a recordset but I am stuck with the table type where this table type won't be coming from a single table but it would be 3 different values that come dynamically.

Please suggest me a way to implement the same in a procedure.

create type lookup_row as 
  record ( idx number, text varchar2(20) );

create type lookups_tab as table of lookup_row;

create or replace function Lookups_Fn
  return lookups_tab
  pipelined
is
  v_row lookup_row;
begin
  for j in 1..10
  loop
    v_row := 
      case j
        when 1 then lookup_row ( 1, 'one' )
        when 2 then lookup_row ( 2, 'TWO' )
        when 3 then lookup_row ( 3, 'three' )
        when 4 then lookup_row ( 4, 'FOUR' )
        when 5 then lookup_row ( 5, 'five' )
        when 6 then lookup_row ( 6, 'SIX' )
        when 7 then lookup_row ( 7, 'seven' )
        else        lookup_row ( j, 'other' )
      end;
    pipe row ( v_row );
  end loop;
  return;
end Lookups_Fn;


Regards,
Mahi
Re: How to work on a table type in a procedure [message #395869 is a reply to message #395857] Thu, 02 April 2009 23:39 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
why not use out parameter in your procedure?

I have not tested the code below but I guess it may give you some pointers.


create or replace procedure Lookups_pr (p_v_row out nocopy lookups_tab)
is
 
begin
  for j in 1..10
  loop
	p_v_row.extend;	
    p_v_row(p_v_row.last) := 
      case j
        when 1 then lookup_row ( 1, 'one' )
        when 2 then lookup_row ( 2, 'TWO' )
        when 3 then lookup_row ( 3, 'three' )
        when 4 then lookup_row ( 4, 'FOUR' )
        when 5 then lookup_row ( 5, 'five' )
        when 6 then lookup_row ( 6, 'SIX' )
        when 7 then lookup_row ( 7, 'seven' )
        else        lookup_row ( j, 'other' )
      end;

  end loop;

end Lookups_pr;

[Updated on: Thu, 02 April 2009 23:43]

Report message to a moderator

Re: How to work on a table type in a procedure [message #395892 is a reply to message #395869] Fri, 03 April 2009 00:20 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Bonker,
Thanks for looking into this.

I have to return a table type record from procedure.

I tried declaring these variables in a package:-
type lookup_row is record ( Application_no VARCHAR2(50), Line_No  NUMBER,error_desc  VARCHAR2(100) );
  type lookups_tab is table of lookup_row;



Now I want to use them in my procedure as :-
I want to insert values into this type dynamically as :-
There will be three columns as given above.
I have given this in procedure for OUT parameter:-
arr_lookups_tab OUT pks_ams_upload.lookups_tab 

Based on some condition I have to insert values in the type as below :-


IF is_number(TRIM (c2.application_no)) = FALSE THEN
     Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 2;
                  
                   arr ( 'Application_no' )  := TRIM(c2.Application_no);
                   arr ( 'Line_No' )  := TO_CHAR(v_tot_rec);
                   arr ( 'error_desc' )    := v_error_desc;
               END IF;


Again in other IF condition I have to write :-
IF length(TRIM (c2.application_no)) > 8 THEN
     Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 3;
                  
                   arr ( 'Application_no' )  := TRIM(c2.Application_no);
                   arr ( 'Line_No' )  := TO_CHAR(v_tot_rec);
                   arr ( 'error_desc' )    := v_error_desc;
               END IF;



So I have insert values in the table type array on runtime.
I am not sure how to use "EXTEND" here to flush the different records into the array.

Please help me on this as I am stuck on this since long.

Thanks again,
Mahi

[Updated on: Fri, 03 April 2009 00:24]

Report message to a moderator

Re: How to work on a table type in a procedure [message #395897 is a reply to message #395892] Fri, 03 April 2009 00:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
In your first post you declared the variable as SQL types and in second post you have declared as PLSQL record type and associative array, I am not sure which one is correct. Any way based on your second post you
could do something like this

type lookup_row is record ( Application_no VARCHAR2(50), Line_No  NUMBER,error_desc  VARCHAR2(100) );
type lookups_tab is table of lookup_row index by pls_integer;
lv_arrcnt int;
IF is_number(TRIM (c2.application_no)) = FALSE THEN
     Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 2;
lv_arrcnt := arr.count+1;

                  
                   arr ( lv_arrcnt ).application_no  := TRIM(c2.Application_no);
                   arr ( lv_arrcnt).line_no  := TO_CHAR(v_tot_rec);
                   arr ( lv_arrcnt).error_desc     := v_error_desc;
               END IF;


IF length(TRIM (c2.application_no)) > 8 THEN
     Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 3;

lv_arrcnt := arr.count+1;

                  
                   arr ( lv_arrcnt ).application_no  := TRIM(c2.Application_no);
                   arr ( lv_arrcnt).line_no  := TO_CHAR(v_tot_rec);
                   arr ( lv_arrcnt).error_desc     := v_error_desc;
               END IF;

[Updated on: Fri, 03 April 2009 00:35]

Report message to a moderator

Re: How to work on a table type in a procedure [message #395902 is a reply to message #395897] Fri, 03 April 2009 00:56 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks Bonker, It compiled.

My procedure is something like this now :-
CREATE OR REPLACE PROCEDURE AMSUAT.SP_AMS_DECISION_PARKING(p_file_id IN VARCHAR2,
                                                           p_failed_rec OUT NUMBER,                                                        
                                                           arr  OUT  nocopy  pks_ams_upload.lookups_tab )                                                   
IS

  c3                          pks_ams_upload.c1; 
  c2                          AMS_IA_PROFIL_DECISION_PARKING%ROWTYPE; 
  v_query                     VARCHAR2 (500);
  v_dupl_appl_no              NUMBER;
  v_recno_commit              NUMBER;
  v_keycd_appl_length         NUMBER;
  v_cnt_applno                NUMBER;
  lv_arrcnt                   NUMBER;
  
  c_status           CONSTANT CHAR (1)                         := 'U'; 
  c_valid_rec                 VARCHAR2 (10)              := 'SUCCESS';
  c_keycd_commit              NUMBER                           := 113;
  c_keycd_appl_length         NUMBER                           := 114;
  v_error_id                  VARCHAR2 (100)                  := NULL;
  v_tot_rec                   NUMBER                             := 0;
  v_error_desc                VARCHAR2 (100)                  := '';              
  
  BEGIN
     p_failed_rec := 0;
      
     
     v_query :=
            'SELECT  *  FROM AMS_IA_PROFIL_DECISION_PARKING  WHERE FILE_ID = '
         || p_file_id
         || ' AND status = '''
         || c_status
         || '''';
         
      OPEN c3 FOR v_query;
      SELECT TO_NUMBER(value) into v_recno_commit FROM amsconfig WHERE keycd = c_keycd_commit;
      LOOP
        BEGIN
           FETCH c3 INTO c2;
           EXIT WHEN c3%NOTFOUND;
             -----FIRST CHECK IF application number is not duplicate...if its then return error message
            v_tot_rec := v_tot_rec + 1;
             c_valid_rec := 'SUCCESS'; 
              
            -- Validate Application Number being numeric and NOT NULL
            IF TRIM (c2.application_no) IS NULL THEN
               Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 1;
               lv_arrcnt := arr.count+1;
               arr ( lv_arrcnt ).application_no  := TRIM(c2.Application_no);
               arr ( lv_arrcnt).line_no  := TO_CHAR(v_tot_rec);
               arr ( lv_arrcnt).error_desc     := v_error_desc;

              
            ELSE 
               IF is_number(TRIM (c2.application_no)) = FALSE THEN
                  Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 2;
                  lv_arrcnt := arr.count+1;
                  arr ( lv_arrcnt ).application_no  := TRIM(c2.Application_no);
                  arr ( lv_arrcnt).line_no  := TO_CHAR(v_tot_rec);
                  arr ( lv_arrcnt).error_desc     := v_error_desc;
               END IF;
           
              SELECT value INTO v_keycd_appl_length FROM amsconfig where keycd = c_keycd_appl_length;
              IF LENGTH (TRIM (c2.application_no)) > v_keycd_appl_length THEN
                Select error_desc INTO v_error_desc from AMS_UPLOAD_ERROR_LU WHERE error_id = 2;
                   lv_arrcnt := arr.count+1;
                   arr ( lv_arrcnt ).application_no  := TRIM(c2.Application_no);
                   arr ( lv_arrcnt).line_no  := TO_CHAR(v_tot_rec);
                   arr ( lv_arrcnt).error_desc     := v_error_desc;
               END IF;
            END IF; 
            
            
         END;  
       END LOOP;
      
      COMMIT;
      
      IF C3%ISOPEN THEN
         CLOSE  C3;
      END IF;
   
  END;
/


Now I want to test if the values are getting inserted to the array table.

I am trying like this :-
declare
failed_rec number;
arr_lookups_tab pks_ams_upload.lookups_tab;
begin
SP_AMS_DECISION_PARKING('1' , failed_rec,  arr_lookups_tab );
for j in arr_lookups_tab.first..arr_lookups_tab.last
loop
dbms_output.put_line('application_no'|| arr_lookups_tab(j).application_no);
dbms_output.put_line('line number'|| arr_lookups_tab(j).line_no);
dbms_output.put_line('line number'|| arr_lookups_tab(j).error_desc);
end loop;
end;      

But its giving error
                                              


ORA-06531: Reference to uninitialized collection
ORA-06512: at line 7

[/code]

I am missing something. the columns are already there while declaring the types :-
type lookup_row is record ( Application_no VARCHAR2(50), Line_No  NUMBER,error_desc  VARCHAR2(100) );
  type lookups_tab is table of lookup_row;


Mahi

[Updated on: Fri, 03 April 2009 01:01]

Report message to a moderator

Re: How to work on a table type in a procedure [message #395904 is a reply to message #395902] Fri, 03 April 2009 01:08 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Did you change your arr_lookups_tab definition in pks_ams_upload as

type lookups_tab is table of lookup_row index by pls_integer;

If not then you have to give to .extend command explicitly in your procedure.

on side note please use bind variable in your cursor query

 query :=
            'SELECT  *  FROM AMS_IA_PROFIL_DECISION_PARKING  WHERE FILE_ID = :p_file_id  AND status = :c_status';

OPEN c3 FOR v_query using p_file_id,c_status

Re: How to work on a table type in a procedure [message #395910 is a reply to message #395904] Fri, 03 April 2009 01:20 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Bonker,
I changed the defination as :-
 type lookup_row is record ( Application_no VARCHAR2(50), Line_No  NUMBER,error_desc  VARCHAR2(100) );
   type lookups_tab is table of lookup_row index by pls_integer;


And I have not made any changes in my procedure.Its as same as above.
But its giving the error :-
ORA-06550: line 6, column 1:
PLS-00306: wrong number or types of arguments in call to 'SP_AMS_DECISION_PARKING'
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
ORA-06550: line 7, column 26:
PLS-00302: component 'FIRST' must be declared
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored


I am trying to test it as :-
declare
failed_rec number;
arr_lookups_tab pks_ams_upload.lookup_row;
j number;
begin
SP_AMS_DECISION_PARKING('1' , failed_rec,  arr_lookups_tab );
for j in arr_lookups_tab.first..arr_lookups_tab.last
loop
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).application_no);
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).line_no);
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).error_desc);
dbms_output.put_line('failed_rec'|| failed_rec);
end loop;
end;


Am I making any mistake in declaring anything!!

Mahi
Re: How to work on a table type in a procedure [message #395912 is a reply to message #395910] Fri, 03 April 2009 01:28 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member

declare
failed_rec number;
/* arr_lookups_tab pks_ams_upload.lookup_row ;*/ -- This is not correct
arr_lookups_tab pks_ams_upload.lookups_tab;
j number;
begin
SP_AMS_DECISION_PARKING('1' , failed_rec,  arr_lookups_tab );
for j in arr_lookups_tab.first..arr_lookups_tab.last
loop
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).application_no);
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).line_no);
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).error_desc);
dbms_output.put_line('failed_rec'|| failed_rec);
end loop;
end;


[Updated on: Fri, 03 April 2009 01:32]

Report message to a moderator

Re: How to work on a table type in a procedure [message #395927 is a reply to message #395912] Fri, 03 April 2009 02:16 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
It gives the error:-

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7


Re: How to work on a table type in a procedure [message #395929 is a reply to message #395927] Fri, 03 April 2009 02:23 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I think your array collection is empty and hence getting the error. Change it like this the error will go away



declare
failed_rec number;
arr_lookups_tab pks_ams_upload.lookups_tab;
j number;
begin
SP_AMS_DECISION_PARKING('1' , failed_rec,  arr_lookups_tab );
for j in 1..arr_lookups_tab.count
loop
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).application_no);
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).line_no);
dbms_output.put_line('arr_lookups_tab.first'|| arr_lookups_tab(j).error_desc);
dbms_output.put_line('failed_rec'|| failed_rec);
end loop;
end;



However note that you may not see any output because essentially your array is empty.

[Updated on: Fri, 03 April 2009 02:27]

Report message to a moderator

Re: How to work on a table type in a procedure [message #395992 is a reply to message #395929] Fri, 03 April 2009 08:01 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Bonker,
Thanks a ton for your help. I am able to do this and I am able to test it too.

Now this procedure is being called in Java and they are finding it difficult...getting errors after errors.
They are asking me that what data type should they register for this. I asked them to register it as array type.

Can you help me on this if I am giving them correct information.


Mahi
Re: How to work on a table type in a procedure [message #395999 is a reply to message #395992] Fri, 03 April 2009 08:23 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
sorry I do not have any expertise on Java. I guess some other members in this forum who are aware about this can provide you answer on this.
Re: How to work on a table type in a procedure [message #396202 is a reply to message #395857] Sat, 04 April 2009 22:29 Go to previous message
aravazhi.a
Messages: 1
Registered: February 2009
Junior Member
Hi,

Java does not have any direct type to accept the oracle table type returned from PL/SQL procedure or function.

Rather it has to be registered as OracleTypes.ARRAY with the typename used in Oracle procedure.

In this case, I think Java can accept the result if registered as:

stmt.registerOutParameter( 3, OracleTypes.ARRAY, "lookups_tab" );
Previous Topic: Problem In Triggers
Next Topic: crazy usage with association table cause ORA-06500: PL/SQL: storage error
Goto Forum:
  


Current Time: Wed Dec 07 06:51:42 CST 2016

Total time taken to generate the page: 0.23905 seconds