Call an SP from another SP ORACLE

Question

Call an SP from another SP ORACLE

Navigation

#1 by (1 votes)

0

I have the following SP,

create or replace PROCEDURE INUP_PARTY(
    o_PARTYID2010 OUT VARCHAR )
AS
  v_partyMAX NUMBER :=0;
BEGIN
  SELECT MAX(PARTYID2010) INTO v_partyMAX FROM PARTY2010;
  INSERT
  INTO PARTY2010
    (
      PARTYID2010
    )
    VALUES
    (
      sys_guid()
    )
  RETURNING PARTYID2010
  INTO o_PARTYID2010;
END;

And I have another SP that could be the next one

create or replace PROCEDURE INUP_USER(
    v_F1DOMAINAPPLICATIONID VARCHAR2,
    v_F1PARTYID20104009 VARCHAR2, 
    v_F1USERTYPEID40284009 VARCHAR2,
    v_HINT4009 VARCHAR2,
    v_IMAGEPROFILE4009 VARCHAR2,
    v_PASSWORD4009 VARCHAR2,
    v_PASSWORDEXPIRATIONDATE4009 DATE,
    v_PASSWORDSALT4009 VARCHAR2,
    v_RECOVERYPASSWORDMODE VARCHAR2,
    v_STATUS4009 VARCHAR2,
    v_USERACCOUNT4009 VARCHAR2,
    v_USERDESCRIPTION4009 VARCHAR2,
    v_USERID    VARCHAR2 

)
AS 
v_UserCount NUMBER := 0;
vl_UserID varchar(100);

    IF(v_F1PARTYID20104009 is null)
    // aqui llamaría al SP anterior y quisiera guardarme el out del sp 
    anterior

The truth investigated by google but I found nothing to guide me, any help is very helpful

oracle plsql

asked by Bruno Sosa Fast Tag 22.11.2017 в 15:17

source

1 answer

Search in mysqli with a field separated by comma Delete bookmarks and update them, google maps V3

score 1 · Accepted Answer

Making the call is very simple. What may not be so obvious is how to assign the result to v_F1PARTYID20104009 , which seems to be your intention.

If that is the case, the answer is that you can not. To the input parameters, as it is v_F1PARTYID20104009 , you can not change the value. So the following is not legal:

CREATE OR REPLACE PROCEDURE INUP_USER(
    -- ...
    v_F1PARTYID20104009 VARCHAR2, 
    -- ...
)
AS 
BEGIN
    IF v_F1PARTYID20104009 is null THEN
        INUP_PARTY(v_F1PARTYID20104009); -- no se puede así.
    END IF;
END;

What you should do is copy v_F1PARTYID20104009 to a local variable. Then you can use this local variable to make the call to SP INUP_PARTY :

CREATE OR REPLACE PROCEDURE INUP_USER(
    -- ...
    v_F1PARTYID20104009 VARCHAR2, 
    -- ...
)
AS
    w_F1PARTYID20104009 VARCHAR2(100); -- variable local
BEGIN
    w_F1PARTYID20104009 := v_F1PARTYID20104009; -- hacer copia

    IF w_F1PARTYID20104009 is null THEN
        INUP_PARTY(w_F1PARTYID20104009); -- ahora sí se puede.
    END IF;

    -- sigue usando la variable local a partir de aquí.
END;

Another option would have been to change v_F1PARTYID20104009 to be a IN OUT parameter, but I'm not sure this would make sense in your case.

Separate note: Your SP INUP_PARTY I see it very strange. I do not understand how it works. Since sys_guid() returns a BINARY , I do not see how the allocation SELECT MAX(PARTYID2010) INTO v_partyMAX FROM PARTY2010 can work since v_partyMAX is not the same type. In fact I do not understand what that assignment is for. I only mention it in passing, since that is not the question.