Execute package menu - number of proc/fnc problem

Toad for Oracle & DevOps Toolkit
1

Hi,

Toad 21.1.0.22 x64 on Win7 Pro Sp1 x64, Oracle 11.2

When I open menu (on packages) and select "Execute package", do not get all procedures/functions from the package but only first 4. here is a picture from Oracle retail, RMS schema:

1057.package_executions2.png
1057.package_executions2.png1101×898 150 KB

here is Toad spool of that action:

Session: DVADAS@XXX
Timestamp: 11:57:49.801
Select
object_name, position, decode(data_type, 'REF CURSOR', 'SYS_REFCURSOR', data_type) data_type, overload, argument_name,
data_level, data_length, data_precision, data_scale, type_name,
in_out, object_id, sequence
from all_arguments
where object_id = (select object_id
from sys.DBA_OBJECTS
where owner = :OWNER
and object_name =:PKG
and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION'))
order by Object_Name, Overload, Sequence
:OWNER(VARCHAR[3],IN/OUT)='RMS'
:PKG(VARCHAR[15],IN/OUT)='FUTURE_COST_SQL'

Session: DVADAS@XXX
Timestamp: 11:57:49.820
Select TEXT
from SYS.DBA_SOURCE
where OWNER=:owner
and NAME=:name
and TYPE=:type
order by LINE
:owner(VARCHAR[3],IN/OUT)='RMS'
:name(VARCHAR[15],IN/OUT)='FUTURE_COST_SQL'
:TYPE(VARCHAR[12],IN/OUT)='PACKAGE BODY'

Session: DVADAS@XXX
Timestamp: 11:57:49.972
Select last_ddl_time
FROM sys.DBA_OBJECTS
WHERE object_type = :type AND owner = :owner AND object_name = :name
:TYPE(VARCHAR[12])='PACKAGE BODY'
:owner(VARCHAR[3])='RMS'
:name(VARCHAR[15])='FUTURE_COST_SQL'

Session: DVADAS@XXX
Timestamp: 11:57:51.386
Select directory_name from sys.DBA_DIRECTORIES
order by 1

I was connect as DBA user throgh proxy user (RMS) who owns package ... if this has anything with it?

Brg,

Damir

2

What do you get if you execute that SQL manually?

Select

   object_name, position, decode(data_type, 'REF CURSOR', 'SYS_REFCURSOR', data_type) data_type, overload, argument_name,

   data_level, data_length, data_precision, data_scale, type_name,

   in_out, object_id, sequence

from all_arguments

where object_id = (select object_id

     from sys.DBA_OBJECTS

     where owner = 'RMS'

     and object_name ='FUTURE_COST_SQL'

     and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION'))

order by Object_Name, Overload, Sequence

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Tuesday, October 15, 2013 6:12 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Execute package menu - number of proc/fnc problem

Execute package menu - number of proc/fnc problem

Thread created by damir.vadas_531

Hi,

Toad 21.1.0.22 x64 on Win7 Pro Sp1 x64, Oracle 11.2

When I open menu (on packages) and select “Execute package”, do not get all procedures/functions from the package but only first 4. here is a picture from Oracle retail, RMS schema:

here is Toad spool of that action:

Session: DVADAS@XXX

Timestamp: 11:57:49.801

Select

   object_name, position, decode(data_type, 'REF CURSOR', 'SYS_REFCURSOR', data_type) data_type, overload, argument_name,

   data_level, data_length, data_precision, data_scale, type_name,

   in_out, object_id, sequence

from all_arguments

where object_id = (select object_id

     from sys.DBA_OBJECTS

     where owner = :OWNER

     and object_name =:PKG

     and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION'))

order by Object_Name, Overload, Sequence

:OWNER(VARCHAR[3],IN/OUT)=‘RMS’

:PKG(VARCHAR[15],IN/OUT)=‘FUTURE_COST_SQL’

Session: DVADAS@XXX

Timestamp: 11:57:49.820

Select TEXT

from SYS.DBA_SOURCE

where OWNER=:owner

and NAME=:name

and TYPE=:type

order by LINE

:owner(VARCHAR[3],IN/OUT)=‘RMS’

:name(VARCHAR[15],IN/OUT)=‘FUTURE_COST_SQL’

:TYPE(VARCHAR[12],IN/OUT)=‘PACKAGE BODY’

Session: DVADAS@XXX

Timestamp: 11:57:49.972

Select last_ddl_time

FROM sys.DBA_OBJECTS

WHERE object_type = :type AND owner = :owner AND object_name = :name

:TYPE(VARCHAR[12])=‘PACKAGE BODY’

:owner(VARCHAR[3])=‘RMS’

:name(VARCHAR[15])=‘FUTURE_COST_SQL’

3

OBJECT_NAME POSITION DATA_TYPE OVERLOAD ARGUMENT_NAME DATA_LEVEL DATA_LENGTH DATA_PRECISION DATA_SCALE TYPE_NAME IN_OUT OBJECT_ID SEQUENCE
PROCESS_COST_EVENTS 1 NUMBER 1 I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 1
PROCESS_COST_EVENTS 2 NUMBER 1 I_THREAD_ID 0 22 10 IN 1324508 2
PROCESS_COST_EVENTS 3 NUMBER 1 O_RETURN_CODE 0 22 OUT 1324508 3
PROCESS_COST_EVENTS 4 VARCHAR2 1 O_ERROR_MESSAGE 0 255 IN/OUT 1324508 4
PROCESS_COST_EVENTS 1 NUMBER 2 I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 1
PROCESS_COST_EVENTS 2 NUMBER 2 I_THREAD_ID 0 22 10 IN 1324508 2
PURGE_DEAL_ITEM_LOC_EXPLODE 0 PL/SQL BOOLEAN 0 OUT 1324508 1
PURGE_DEAL_ITEM_LOC_EXPLODE 1 VARCHAR2 O_ERROR_MESSAGE 0 255 IN/OUT 1324508 2
PURGE_DEAL_ITEM_LOC_EXPLODE 2 NUMBER I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 3
PURGE_DEAL_ITEM_LOC_EXPLODE 3 NUMBER I_THREAD_ID 0 22 10 IN 1324508 4
PURGE_FUTURE_COST 0 PL/SQL BOOLEAN 0 OUT 1324508 1
PURGE_FUTURE_COST 1 VARCHAR2 O_ERROR_MESSAGE 0 255 IN/OUT 1324508 2
PURGE_FUTURE_COST 2 NUMBER I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 3
PURGE_FUTURE_COST 3 NUMBER I_THREAD_ID 0 22 10 IN 1324508 4

4

That explains what we’re showing. Now to figure out why we’re not showing all of your procs/functions.

“I was connect as DBA user through proxy user (RMS) who owns package … if this has anything with it?”

            This is probably the reason but I'll have to do a bit of research as to the who/what/where/when/why it's not pulling all of the information.

Greg

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Wednesday, October 16, 2013 8:07 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Execute package menu - number of proc/fnc problem

RE: Execute package menu - number of proc/fnc problem

Reply by damir.vadas_531

OBJECT_NAME POSITION DATA_TYPE OVERLOAD ARGUMENT_NAME DATA_LEVEL DATA_LENGTH DATA_PRECISION DATA_SCALE TYPE_NAME IN_OUT
OBJECT_ID SEQUENCE

PROCESS_COST_EVENTS 1 NUMBER 1 I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 1

PROCESS_COST_EVENTS 2 NUMBER 1 I_THREAD_ID 0 22 10 IN 1324508 2

PROCESS_COST_EVENTS 3 NUMBER 1 O_RETURN_CODE 0 22 OUT 1324508 3

PROCESS_COST_EVENTS 4 VARCHAR2 1 O_ERROR_MESSAGE 0 255 IN/OUT 1324508 4

PROCESS_COST_EVENTS 1 NUMBER 2 I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 1

PROCESS_COST_EVENTS 2 NUMBER 2 I_THREAD_ID 0 22 10 IN 1324508 2

PURGE_DEAL_ITEM_LOC_EXPLODE 0 PL/SQL BOOLEAN 0 OUT 1324508 1

PURGE_DEAL_ITEM_LOC_EXPLODE 1 VARCHAR2 O_ERROR_MESSAGE 0 255 IN/OUT 1324508 2

PURGE_DEAL_ITEM_LOC_EXPLODE 2 NUMBER I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 3

PURGE_DEAL_ITEM_LOC_EXPLODE 3 NUMBER I_THREAD_ID 0 22 10 IN 1324508 4

PURGE_FUTURE_COST 0 PL/SQL BOOLEAN 0 OUT 1324508 1

PURGE_FUTURE_COST 1 VARCHAR2 O_ERROR_MESSAGE 0 255 IN/OUT 1324508 2

PURGE_FUTURE_COST 2 NUMBER I_COST_EVENT_PROCESS_ID 0 22 15 IN 1324508 3

PURGE_FUTURE_COST 3 NUMBER I_THREAD_ID 0 22 10 IN 1324508 4

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Hi,

THX for your reply.

When I connect as clean DBA user (no proxy) your sql gives the same result!
8(

here is specification of that package:

CREATE OR REPLACE PACKAGE FUTURE_COST_SQL AS

– Procedure Name: PROCESS_COST_EVENTS
– Purpose : Calls the appropriate function based on cost event type to process event.

PROCEDURE PROCESS_COST_EVENTS(I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE,
O_return_code OUT NUMBER,
O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE);

– Procedure Name: PROCESS_COST_EVENTS
– Purpose : Wrapper procedure to call FUTURE_COST_SQL.PROCESS_COST_EVENTS internally .
– This procedure is called from DBMS_SCHEDULER

PROCEDURE PROCESS_COST_EVENTS(I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE);

– Function Name: PURGE_FUTURE_COST
– Purpose : Purge data from the future cost table based.

FUNCTION PURGE_FUTURE_COST(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: PURGE_DEAL_ITEM_LOC_EXPLODE
– Purpose : Purge data from the deal_item_loc_explode table.

FUNCTION PURGE_DEAL_ITEM_LOC_EXPLODE(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– alter session set plsql_ccflags = ‘UTPLSQL:TRUE’
– compile private functions as public for unit testing
$if $$UTPLSQL=TRUE $then

– Function Name: EXPLODE_NIL
– Purpose : Adds new item/location records into future cost table.

FUNCTION EXPLODE_NIL(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: MERGE_NIL_CC
– Purpose : Adds future cost changes for new item/location.

FUNCTION MERGE_NIL_CC(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: MERGE_NIL_RECLASS
– Purpose : Adds future reclass records for new item/location.

FUNCTION MERGE_NIL_RECLASS(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: MERGE_DEAL
– Purpose : Adds all applicable deal records for new item/location.

FUNCTION MERGE_DEAL(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: EXPLODE_COST_CHANGE
– Purpose : Calls appropriate function to process cost change based on the action type.

FUNCTION EXPLODE_COST_CHANGE(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_action IN COST_EVENT.ACTION%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: REMOVE_CC
– Purpose : Removes cost changes from the future cost table.

FUNCTION REMOVE_CC(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: ADD_CC
– Purpose : Adds cost changes to the future cost table.

FUNCTION ADD_CC(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: EXPLODE_CC
– Purpose : Populate future_cost_temp with future cost records related to the cost
– change on item/supplier/country and item/supplier/country/location.

FUNCTION EXPLODE_CC(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: MERGE_CC
– Purpose : Find future cost event records on the timeline on the same date or prior
– to the cost change. Records on the same date as the cost change will have
– their cost values updated with the values on the cost change. If only
– prior records exists, then insert a new row for the cost change.

FUNCTION MERGE_CC(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: EXPLODE_RECLASS
– Purpose : Calls appropriate function to process reclass based on the action type.

FUNCTION EXPLODE_RECLASS(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_action IN COST_EVENT.ACTION%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: ADD_RECLASS
– Purpose : Insert rows into future cost records affected by reclass.

FUNCTION ADD_RECLASS(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: REMOVE_RECLASS
– Purpose : Remove reclass records from future cost table.

FUNCTION REMOVE_RECLASS(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)
RETURN BOOLEAN;

– Function Name: MERGE_RECLASS
– Purpose : Insert a row for the reclass on the timeline or if a row exists on the
– same date as the reclass, update the row with the reclass number and
– the new merch hier.

FUNCTION MERGE_RECLASS(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
I_cost_event_process_id IN COST_EVENT.COST_EVENT_PROCESS_ID%TYPE,
I_thread_id IN COST_EVENT_THREAD.THREAD_ID%TYPE)

6

The best of all. Look for next query …

select object_name, procedure_name,subprogram, overload
from dba_procedures
where owner = ‘RMS’
and object_name = ‘FUTURE_COST_SQL’

OBJECT_NAME PROCEDURE_NAME SUBPROGRAM_ID OVERLOAD

FUTURE_COST_SQL PROCESS_COST_EVENTS 1 1

FUTURE_COST_SQL PROCESS_COST_EVENTS 2 2

FUTURE_COST_SQL PURGE_DEAL_ITEM_LOC_EXPLODE 4

FUTURE_COST_SQL PURGE_FUTURE_COST 3

FUTURE_COST_SQL 0

7

SELECT *
FROM SYS.DBA_PROCEDURES
WHERE OBJECT_TYPE = ‘PACKAGE’ AND
OWNER=‘RMS’ AND
OBJECT_NAME = ‘FUTURE_COST_SQL’ AND
PROCEDURE_NAME IS NOT NULL;

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_ID

RMS FUTURE_COST_SQL PROCESS_COST_EVENTS 1324508
RMS FUTURE_COST_SQL PROCESS_COST_EVENTS 1324508
RMS FUTURE_COST_SQL PURGE_DEAL_ITEM_LOC_EXPLODE 1324508
RMS FUTURE_COST_SQL PURGE_FUTURE_COST 1324508

8

Seems that this is Oracle bug…