Call a stored procedure, into another stored procedure

Question

I have a procedure, that insert a line into one of my table.

After the INSERT in the procedure, I want to find all the lines into another table, and then, call the insert procedure of the second table.

So I have all the first procedure that works fine

P_INSERT_TABLE1

INSERT INTO TABLE1

...
COMMIT;

FOR record_po IN (SELECT C3, ...
                    FROM T_TABLE2
                    WHERE id = v_id)
LOOP
      P_INSERT_TABLE2(record_po.C3, ...);
END LOOP;

All "in parameters" for P_INSERT_TABLE2 are VARCHAR2, so I make a "to_char" for each column are not varchar2 :

P_INSERT_TABLE2(pi_id,
                      record_po.C3,
                      record_po.C4,
                      record_po.C5,
                      record_po.C6,
                      record_po.C7,
                      to_char(record_po.C8, 'DD/MM/YYYY');

Here, pi_id, is one of the in parameters of P_INSERT_TABLE1, in VARCHAR2.

So now, I have this error message :

Erreur(357,1): PLS-00306: number or args types wrong in the call of P_INSERT_TABLE2

I don't understand, why P_INSERT_TABLE2 don't accept parameters, while there are all the good types in the good order?

If I call the procedure like "call P_INSERT_TABLE2(...)" I have an error like :

Erreur(357,9): PLS-00103: Symbol "P_INSERT_TABLE2" instead one of this symbols :     := . ( @ % ; immediate Symbole ":=" 

create or replace
PROCEDURE P_INSERT_TABLE2 (
  pi_id          IN VARCHAR2
  ,pi_C3         IN VARCHAR2
  ,pi_C4         IN VARCHAR2
  ,pi_C5         IN VARCHAR2
  ,pi_C6         IN VARCHAR2
  ,pi_C7         IN VARCHAR2
  ,pi_C8         IN VARCHAR2
  ,pmessage      OUT NOCOPY VARCHAR2  
) 

Thanks for helping.

Answer 1

The declaration of P_INSERT_TABLE2 is invalid. You can't have 5 input parameters all named pi_C4. Since you're not getting a compilation error creating that procedure, I'll guess that this was a bug that was introduced posting the question here rather than something that is actually in the code.

According to the declaration of P_INSERT_TABLE2, the procedure takes 7 input parameters and one output parameter. In the code you posted, you appear to be passing in 7 input parameters but you are not passing in a variable for the output parameter. It appears that you need something like

P_INSERT_TABLE2(pi_id,
                record_po.C3,
                record_po.C4,
                record_po.C5,
                record_po.C6,
                record_po.C7,
                to_char(record_po.C8, 'DD/MM/YYYY'),
                <<some local variable for the output parameter>> );

Beyond the syntax errors, I am extremely dubious when I see someone taking a perfectly good DATE, casting it to a string, and then passing that to a procedure. That implies either that P_INSERT_TABLE2 is going to turn around and convert the string back to a date, which means that you're doing extra work and have introduced additional points where the conversions can fail, or that you are going to write the string representation of a date to a table. Neither of these implications are good.

I am also highly dubious of any procedure that has an OUT parameter named pMessage. That tends to imply that you're not using exceptions properly and that you're passing an error message back rather than throwing an exception if your code encounters an error. That virtually always leads to much more brittle code that is much more difficult to debug than when you use proper exceptions.