Re: Help with stored procedure calling another stored procedure.

You have to declare a variable to hold the returned value. Before your BEGIN statement, insert a DECLARE section containing a variable declaration of the same type as V_NEW_USER_ID. Then, when you call the CREATE_CIS_USER procedure, substitute the variable name for V_NEW_USER_ID in the parameter list.

"Steve" <spamsbucciacchio_at_hotmail.com> wrote in message news:rwYf6.4514$vs2.470755_at_carnaval.risq.qc.ca...
> Hi folks,
>
> Newbie to PL/SQL so bear with me. I have a stored procedure (let's call it
> SP_A) that calls another stored procedure (let's call it SP_B) all within
> the same package. SP_B has one out parameter, so my question is how do I
 get
> that return value in SP_A. Here is an example of what I'm trying to do;
>
> PROCEDURE CREATE_USER (V_PERSON_ID IN VARCHAR,
> V_UPDATED_COUNT IN NUMBER,
> V_CREDENTIAL_CODE IN VARCHAR2,
> V_CREDENTIAL_ID IN VARCHAR2,
> V_FIRST_NAME IN VARCHAR2,
> V_LAST_NAME IN VARCHAR2,
> V_ESTABLISHMENT_CODE IN VARCHAR2,
> V_HOME_SITE IN VARCHAR2,
> V_ADMIN_GROUP IN VARCHAR2,
> V_ADMIN_SUB_GROUP IN VARCHAR2,
> V_JOB_TITLE IN VARCHAR2,
> V_TELEPHONE IN VARCHAR2,
> V_ENCRYPTED_PASSWORD IN VARCHAR2,
> V_ID_EXPIRY_DATE IN VARCHAR2,
> V_CONFIDENTIALITY_FORM_SIGNED IN CHAR,
> V_LOGGED_ON_USER IN VARCHAR2) IS
>
> BEGIN
>
> Create_Cis_User(V_PERSON_ID, V_UPDATED_COUNT, V_CREDENTIAL_CODE,
> V_CREDENTIAL_ID, V_FIRST_NAME, V_LAST_NAME,
> V_ESTABLISHMENT_CODE, V_HOME_SITE, V_ADMIN_GROUP,
> V_ADMIN_SUB_GROUP, V_JOB_TITLE, V_TELEPHONE,
> V_ENCRYPTED_PASSWORD, V_ID_EXPIRY_DATE,
> V_CONFIDENTIALITY_FORM_SIGNED,
> V_LOGGED_ON_USER, V_NEW_USER_ID);
>
> END CREATE_USER;
>
> V_NEW_USER_ID is an out parameter which I need to access, how do I do
 this?
>
> Thanks,
> Steve
> --
> Note: To reply by email, simply remove the word "spam" from my address.
>
>
Received on Tue Feb 06 2001 - 14:15:33 CST