Skip to Main Content
  • Questions
  • when I drop a plsql function, 1 view goes invalid, 1 view and 1 procedure remain valid

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jeffrey.

Asked: February 08, 2017 - 1:34 pm UTC

Last updated: February 20, 2017 - 1:16 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

We recently upgraded to database version 12c...

We found an anomaly that we can't explain and wondering if this is a bug or a misunderstanding of new functionality within 12c.

We can reproduce this issue on demand with the following example.

We have a function called: LION_CONFIG_LINE_ID
CREATE OR REPLACE FUNCTION APPS.LION_CONFIG_LINE_ID(P_LINE_ID IN NUMBER)
RETURN NUMBER
IS
V_LINE_ID number;
BEGIN
SELECT line_id
INTO V_LINE_ID
FROM OE_TABLE
WHERE TOP_LINE_ID=P_LINE_ID;
RETURN (V_LINE_ID);
END LION_CONFIG_LINE_ID;
/


This function is called by 2 views and 1 procedure.

Procedure: LION_DS_EXTRACT_CSTMORD this remains valid

CREATE OR REPLACE PROCEDURE APPS.LION_DS_EXTRACT_CSTMORD (errc OUT NUMBER, errm OUT VARCHAR2, p_item_id IN VARCHAR2, p_line_id IN VARCHAR2)
AS

v_config_line_id NUMBER;
v_item_type VARCHAR2(20);

BEGIN
v_inv_type := Lion_Get_Category_fn ('Inventory', 'LN1', p_item_id);
v_config_line_id := Lion_Config_Line_Id(p_line_id);

INSERT INTO demand_solutions_cstm (ITEM_TYPE, CONFIG_LINE, LINE_ID) values (v_inv_type, v_config_line_id, p_line_id);
commit;
END;
/


View: LION_ORDER_REP_DISP_LINES_V this view goes invalid
CREATE OR REPLACE FORCE VIEW APPS.LION_ORDER_REP_DISP_LINES_V
(
HEADER_ID,
ORDER_NUMBER,
DISP_LINE_ID
)
BEQUEATH DEFINER
AS
SELECT oeh.header_id HEADER_ID,
order_number ORDER_NUMBER,
NVL (LION_CONFIG_LINE_ID (LINE_ID), LINE_ID) disp_line_id,
FROM oe_order_lines_all oel, oe_order_headers_all oeh
WHERE oeh.header_id = oel.header_id
AND ITEM_TYPE_CODE IN ('MODEL', 'STANDARD')
AND NVL (oeh.cancelled_flag, 'N') != 'Y'
AND oeh.org_id = 1
ORDER BY line_number;


View: XXLN_ORDER_TRACKER_DETAIL_V this remains valid
CREATE OR REPLACE FORCE VIEW APPS.XXLN_ORDER_TRACKER_DETAIL_V
(
HEADER_ID,
ORDER_TYPE_ID,
)
BEQUEATH DEFINER
AS
SELECT oh.header_id AS header_id,
oh.order_type_id AS order_type_id,
FROM oe_order_headers_all oh,
hz_cust_accounts hca,
oe_order_lines_all ol,
oe_order_lines_all ol_disp
WHERE 1 = 1
AND oh.sold_to_org_id = hca.cust_account_id
AND oh.header_id = ol.header_id
AND ol.service_reference_line_id IS NULL
AND ol.item_type_code IN ('MODEL', 'STANDARD')
AND ol.header_id = ol_disp.header_id
AND ol_disp.line_id = NVL (lion_config_line_id (ol.line_id), ol.line_id);


When we drop the function, only of these 3 plsql objects goes invalid. 1 view and the 1 procedure remain valid.

We can view their status as follows:
select *
from all_objects
where object_name in ('LION_DS_EXTRACT_CSTMORD','LION_ORDER_REP_DISP_LINES_V','XXLN_ORDER_TRACKER_DETAIL_V')

Owner Type Name Status
APPS PROCEDURE LION_DS_EXTRACT_CSTMORD VALID
APPS VIEW LION_ORDER_REP_DISP_LINES_V INVALID
APPS VIEW XXLN_ORDER_TRACKER_DETAIL_V VALID

If we attempt to use the view or procedure they do not work and error that they are invalid, and if we manually recompile them, they go invalid.
Our question is why don't all 3 objects go invalid as soon as the function is dropped?



UPDATE to question
------------------
We have worked with our dba and appear to have discovered the cause.
We can reproduce quite easily with this scenario:
create a simple function that has 1 parameter
create a simple view that calls that function as a selected column
create a new database edition, finalize it....
in the new edition session, drop the function
The view will remain valid, as it still belongs to the previous edition and is not actualized in edition #2.
If you attempt to alter/compile the view it will go invalid
Our new question, knowing this...
Is there a way to query the database dictionary to find these invalids, even though the all_objects or dba_objects table still show them as valid?

and Connor said...

Ah...things are different when editions come into play.

From

http://www.oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf

"It turns out that, because of various internal optimizations, an invalid object that is the result of consequential invalidation does not show up immediately in the DBA_Objects and DBA_Objects_AE catalog view families. However, it will show up after a call to DBMS_Utility.Compile_Schema() or to one of the Utl_Recomp APIs. It will show up, too, after an attempt to reference it (in either a compilation or an execution context)."


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Edition lesson learned

Jeff, February 20, 2017 - 2:41 am UTC

Thank you for the answer. We are discovering many interesting changes around the editioning features. It's a bit like learning the database all over in some arenas. Any bit of knowledge and experience is of help.
Connor McDonald
February 20, 2017 - 1:16 pm UTC

Glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library