Adding a new function in a package requires PL/SQL knowledge. For more details how to create and use customer-installed procedures (CIPs), see Customer-installed procedure (CIP) Tasks.
Extract the M_PCK_CUSTOM_ATTRS package from the database to a file (m_pck_custom_attrs.pck), and add a function, for example, the part_code function as described below.
In the package specification section, add the function definition:
FUNCTION Part_code /* Example function */
(p_ident IN m_idents.ident%TYPE)
RETURN VARCHAR2;
PRAGMA restrict_references(part_code, WNDS, WNPS);
In the package body section, add the function:
FUNCTION part_code
(p_ident IN m_idents.ident%TYPE)
RETURN VARCHAR2
IS
return_part_code VARCHAR2(2000);
BEGIN
SELECT p.part_code
INTO return_part_code
FROM m_parts p,
m_commodity_codes cc,
m_idents i
WHERE i.ident = p_ident
and i.commodity_id= cc.commodity_id
and cc.group_id = p.group_id
and cc.part_id = p.part_id
;
RETURN return_part_code;
EXCEPTION
WHEN no_data_found THEN RETURN txt_no_data_found;
WHEN too_many_rows THEN RETURN txt_too_many_rows;
WHEN others THEN RETURN txt_other_error || to_char(sqlcode);
END part_code;
Install the package as follows:
-
Save the changes you have made in the m_pck_custom_attrs.pck file.
-
Launch a SQL*Plus session and login as M_SYS user into the Smart Materials database.
-
Click File > Open and navigate to the folder where you saved the m_pck_custom_attrs.pck file.
-
Click Cancel.
-
Type Start m_pck_custom_attrs.pck; and press ENTER.
The package is successfully installed in the Smart Materials database.