How to work on a table type in a procedure [message #395857] |
Thu, 02 April 2009 22:45  |
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   |
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   |
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   |
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   |
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   |
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   |
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   |
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 #395929 is a reply to message #395927] |
Fri, 03 April 2009 02:23   |
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   |
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 #396202 is a reply to message #395857] |
Sat, 04 April 2009 22:29  |
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" );
|
|
|