Toad World® Forums

Execute package menu - number of proc/fnc problem

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'


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

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’

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

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.

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)

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

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

Seems that this is Oracle bug…