Create a test procedure with SQL Navigator 7.1 or 7.2. Procedure has a parameter of type RECORD based on a table (%Rowtype).
Result: The procedure is not called with the entered values.
Steps to reproduce the issue:
1.Execute script below
--------------------------------------
-- CREATE TABLE
--------------------------------------
create table w_rec_input (
text_in_1 VARCHAR2(10),
text_in_2 VARCHAR2(10)
);
create table w_rec_output (
text_out_1 VARCHAR2(10),
text_out_2 VARCHAR2(10)
);
--------------------------------------
-- CREATE SPECIFACTION
--------------------------------------
CREATE OR REPLACE PACKAGE PAC_TEST
IS
TYPE typ_rec_input IS RECORD (
text_in_1 VARCHAR2(10),
text_in_2 VARCHAR2(10)
);
TYPE typ_rec_output IS RECORD (
text_out_1 VARCHAR2(10),
text_out_2 VARCHAR2(10)
);
SUBTYPE typ_subrec_input IS typ_rec_input;
SUBTYPE typ_subrec_output IS typ_rec_output;
SUBTYPE typ_tabrec_input IS w_rec_input%rowtype;
SUBTYPE typ_tabrec_output IS w_rec_output%rowtype;
-- FUNCTION WITH TYPE RECORDS / OK - WORKS
FUNCTION STF_TEST_TYPE
(
i_rec_input IN typ_rec_input,
o_rec_output OUT typ_rec_output
)
RETURN NUMBER;
-- FUNCTION WITH SUBTYPE RECORDS / OK - WORKS
FUNCTION STF_TEST_SUBTYPE
(
i_rec_input IN typ_subrec_input,
o_rec_output OUT typ_subrec_output
)
RETURN NUMBER;
-- FUNCTION WITH ROWTYPE RECORDS / ERROR - DO NOT WORK
FUNCTION STF_TEST_TABTYPE
(
i_rec_input IN typ_tabrec_input,
o_rec_output OUT typ_tabrec_output
)
RETURN NUMBER;
END PAC_TEST;
/
--------------------------------------
-- CREATE BODY
--------------------------------------
CREATE OR REPLACE PACKAGE BODY PAC_TEST IS
-- FUNCTION WITH TYPE RECORDS / OK - WORKS
FUNCTION STF_TEST_TYPE
(
i_rec_input IN typ_rec_input,
o_rec_output OUT typ_rec_output
)
RETURN NUMBER IS
BEGIN
o_rec_output.text_out_1 := i_rec_input.text_in_1;
o_rec_output.text_out_2 := 'TEST';
return 0;
END;
-- FUNCTION WITH SUBTYPE RECORDS / OK - WORKS
FUNCTION STF_TEST_SUBTYPE
(
i_rec_input IN typ_subrec_input,
o_rec_output OUT typ_subrec_output
)
RETURN NUMBER IS
BEGIN
o_rec_output.text_out_1 := i_rec_input.text_in_1;
o_rec_output.text_out_2 := 'TEST';
return 0;
END;
-- FUNCTION WITH ROWTYPE RECORDS / ERROR - DO NOT WORK
FUNCTION STF_TEST_TABTYPE
(
i_rec_input IN typ_tabrec_input,
o_rec_output OUT typ_tabrec_output
)
RETURN NUMBER IS
BEGIN
o_rec_output.text_out_1 := i_rec_input.text_in_1;
o_rec_output.text_out_2 := 'TEST';
return 0;
END;
END PAC_TEST;
/
2.
3.
4. The generate script is:
DECLARE
TYPE Record_Type_2 IS RECORD (
TEXT_IN_1 VARCHAR2(40),
TEXT_IN_2 VARCHAR2(40));
TYPE Record_Type_3 IS RECORD (
TEXT_OUT_1 VARCHAR2(40),
TEXT_OUT_2 VARCHAR2(40));
I_REC_INPUT Record_Type_2;
O_REC_OUTPUT Record_Type_3;
"[Function return value]" NUMBER;
BEGIN
-- Now Call the stored program
"[Function return value]" := pac_test.stf_test_tabtype
(i_rec_input=>i_rec_input,
o_rec_output=>o_rec_output);
-- Output the results
:b0 := "[Function return value]";
:b1 := o_rec_output.TEXT_OUT_1;
:b2 := o_rec_output.TEXT_OUT_2;
END;
Note that the a and the b values are not passed .
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center