Item Description Function Using EGO_COL_NAME_VALUE_PAIR_ARRAY Errors On Update: ORA-06550: PLS-00306: Wrong Number Or Types Of Arguments [Video]

(Doc ID 1199333.1)

Last updated on MAY 08, 2017

Applies to:

Oracle Product Hub - Version 12.1.2 and later
Information in this document applies to any platform.

Symptoms

Receive the following error attempting to create an item with a function generated description.

What is working
Works fine until the user marks the item catalog generation function as "Enable Key Attributes".
After this, receive the error.
Also works fine on creating the item, just not editing the attributes.

Error

The Function "JBP_Function1" threw the following exception: java.sql.SQLException:
ORA-06550: line 1, column 14: PLS-00306: wrong number or types of arguments in call to 'GENERATE_DESCRIPTION1'
ORA-06550: line 1, column 8: PL/SQL: Statement ignored


Movie
The following video / movie steps thru the issue.
Video - Show the issue (04:46)

Steps
Responsibility: Development Manager
Function: Create new Item
1) Create the Function in database
2) Setup the function in the Oracle PIM
3) Add parameters to function for passing “IN”parameters, include the Return Value

4) Assign the function to generate the Item Description.
5) Set "Enable Key Attributes" = Yes.

6) Create a new Item.
7) Works fine.
8) Attempt to update the attribute
9) Receive the error.

Example Function
You can find examples of how to setup the function in a demo package that shows the extra variable. The demo package is EGO_DEMO_PUB. Here is a snippet from the package showing the function generation specification:

FUNCTION Gen_Item_Num_With_Key_Attrs( p_Section_Code IN VARCHAR2
,p_Model_Code IN VARCHAR2
,p_Prototype_Code IN VARCHAR2
,p_col_name_value_array IN EGO_COL_NAME_VALUE_PAIR_ARRAY)


Here is an example of the function that could be used WITHOUT the hidden parameter and "Enable Key Attributes" = No:

CREATE OR REPLACE PACKAGE JBP_PIM AS
FUNCTION GENERATE_DESCRIPTION1 (p1 IN OUT VARCHAR2)RETURN VARCHAR2;
END JBP_PIM;
/
CREATE OR REPLACE PACKAGE BODY JBP_PIM AS
FUNCTION GENERATE_DESCRIPTION1 (p1 IN OUT VARCHAR2)RETURN VARCHAR2 AS
l_item_description VARCHAR2(240);
l_separator VARCHAR2(1) := '.';
BEGIN
l_item_description := p1 || l_separator || '2' || l_separator || '3'|| l_separator || '4' || l_separator || '5';
RETURN(l_item_description);
END GENERATE_DESCRIPTION1;
END JBP_PIM;
/


Here is an example WITH the hidden parameter to be used when "Enable Key Attributes" = Yes:

CREATE OR REPLACE PACKAGE JBP_PIM AS
FUNCTION GENERATE_DESCRIPTION1 (p1 IN OUT VARCHAR2,
p_col_name_value_array IN EGO_COL_NAME_VALUE_PAIR_ARRAY
)RETURN VARCHAR2;
END JBP_PIM;
/
CREATE OR REPLACE PACKAGE BODY JBP_PIM AS
FUNCTION GENERATE_DESCRIPTION1 (p1 IN OUT VARCHAR2,
p_col_name_value_array IN EGO_COL_NAME_VALUE_PAIR_ARRAY
)RETURN VARCHAR2 AS
l_item_description VARCHAR2(240);
l_separator VARCHAR2(1) := '.';
BEGIN
l_item_description := p1 || l_separator || '2' || l_separator || '3'|| l_separator || '4' || l_separator || '5';
RETURN(l_item_description);
END GENERATE_DESCRIPTION1;
END JBP_PIM;
/



Example #2: Here is another example that uses the EGO_COL_NAME_VALUE_PAIR_ARRAY value - Example. You can also see the demo code delivered in EGO_DEMO_PUB.Gen_Item_Num_With_Key_Attrs.



Business Impact
Cannot update attributes that change the description via function generation with "Enable Key Attributes". Want to use "Enable Key Attributes" because the function will use things like the item id or organization id to query values and then pass them back to the description. The simple example in this note does not use such advanced logic, but that is the real business requirement and why "Enable Key Attributes" is required.

Changes

Only when using "Enable Key Attributes" = Yes in the function assignment on the description. With "Enable Key Attributes" = No, it works fine. Require "Enable Key Attributes" = yes, however, to get item information. The setting passes an additional parameter that can be used for queries on the item.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms