How to execute packaged stored procedure from SQL*Plus
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to execute packaged stored procedure from SQL*Plus

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    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

    Nagesh

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi

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


    Nagesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width