How to execute packaged stored procedure from SQL*Plus

[gnagesh]

Hi

How to execute packaged stored procedure from SQL*Plus. My main concen is, to know execution elapsed time for each packaged stored procedure.

For example I have following packaged procedure

PROCEDURE twoStage_firstAuthorize(
p_ani IN VARCHAR2,
p_dnis IN VARCHAR2,
p_MaxPort IN NUMBER,
p_STATUS OUT INT,
p_SERVICE_TYPE OUT PLS_INTEGER,
p_ISSUER_ID OUT VARCHAR2,
p_PLAY_MSG OUT PLS_INTEGER,
p_GREETING_MSG OUT VARCHAR2,
p_LANG_SELECTION OUT VARCHAR2,
p_ANI_AUTHORIZATION OUT PLS_INTEGER,
p_PIN_AUTHORIZATION OUT PLS_INTEGER,
p_ACC_AUTHORIZATION OUT PLS_INTEGER,
p_LANGUAGE_CODE OUT VARCHAR2,
p_BILLING_GROUP OUT VARCHAR2,
p_ANI_TYPE OUT PLS_INTEGER,
p_Warning_Threshold OUT PLS_INTEGER,
p_userNo OUT VARCHAR2
)

After completion of the above procedure, i will pass some of the output parameters returned by the above procedure to another packaged stored procedure.. and so on.

So, these tasks, would like to done through SQL*PLUS. So that I can get the elapsed time for each packaged procedure execution.

Any one can suggest me the code to solve my problem.

Thanks In Advance

[pando]

set timing on

as for passing parameters type OUT you could use bind variables for example

variable p_STATUS number

PROCEDURE twoStage_firstAuthorize(
p_ani IN VARCHAR2,
p_dnis IN VARCHAR2,
p_MaxPort IN NUMBER,
v_STATUS OUT INT,
p_SERVICE_TYPE OUT PLS_INTEGER,
p_ISSUER_ID OUT VARCHAR2,
p_PLAY_MSG OUT PLS_INTEGER,
p_GREETING_MSG OUT VARCHAR2,
p_LANG_SELECTION OUT VARCHAR2,
p_ANI_AUTHORIZATION OUT PLS_INTEGER,
p_PIN_AUTHORIZATION OUT PLS_INTEGER,
p_ACC_AUTHORIZATION OUT PLS_INTEGER,
p_LANGUAGE_CODE OUT VARCHAR2,
p_BILLING_GROUP OUT VARCHAR2,
p_ANI_TYPE OUT PLS_INTEGER,
p_Warning_Threshold OUT PLS_INTEGER,
p_userNo OUT VARCHAR2
)
............
:v_STATUS := v_STATUS;
............
/

then next procedure would use :v_STATUS as parameter IN

[gnagesh]

Hi

Thank You Very Much. I have done same thing. Thanks once again.