Creating a stored procedure with IN and OUT Paramters

Question

This is my first time creating a stored proc that has both IN and OUT parameters, and i am pretty lost. What i am trying to do is have an API pass in 3 parameters (ID_TX, FORM_NAME and DATA_DATE) into my stored proc and have my stored procedure do a simple insert and then pass out a single value (SUBMISSION_ID). However, when I try to compile the code, i keep getting the errors. The errors are as follows:

 Error(8,1): PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior The symbol "begin" was substituted for "INSERT" to continue. 

Error(10,1): PLS-00103: Encountered the symbol "RETURNING" when expecting one of the following:     . ( ) , * % & = - + < / > at in is mod remainder not rem =>    <an exponent (**)> <> or != or ~= >= <= <> and or like like2    like4 likec between || multiset member submultiset 

Error(11,1): PLS-00103: Encountered the symbol "END"

Is there some syntax basics/knowledge that I am missing? Thanks in advance!

create or replace procedure API_SUBMISSION(rID_TX IN VARCHAR, rFORM_NAME IN VARCHAR, rDATA_DATE IN VARCHAR, v_submission_id OUT NUMBER)
IS BEGIN
DECLARE
v_submission_id number;
insert into submission (SUBMISSION_ID, RESPONDENT_ID, SUBMISSION_DT, SUBMISSION_TYPE_ID, SUBMISSION_NAME_TX, SUBMISSION_SEQ_NB, CREATE_DT, CREATE_USER_ID, MODIFY_DT, MODIFY_USER_ID, EFFECTIVE_DT, INACTIVE_DT)
            VALUES (null, get__respondent_id(rID_TX, rFORM_NAME, trunc(sysdate), sysdate, rDATA_DATE || 'TEST ' || rFORM_NAME, 1, sysdate, 1, null, null, null, null)
returning submission_id into v_submission_id;
END API_SUBMISSION;

sorry, i realized i left them out, added them JUST as you asked :) — John Wick, Apr 09 '18 at 15:05
im dumb. When i looked at some examples online, it had 'returning' , sorry about that — John Wick, Apr 09 '18 at 15:09
You are correct and returning is correct one. This is duplicate on this SO: https://stackoverflow.com/questions/5558979/inserting-into-oracle-and-retrieving-the-generated-sequence-id — jose_bacoy, Apr 09 '18 at 15:16
ah, maybe i need to learn how to search better. I've tried looking but failed to find :( how did you search for that so quickly/easily (especially with it being a question 7 years back??)? — John Wick, Apr 09 '18 at 17:21

Boneist · Accepted Answer · 2018-04-11T14:43:56.797

There are several issues with your procedure:

You have declared an out parameter to hold the returned submission_id, so there is no need to re-declare it.
You don't need the declare keyword inside a procedure/function unless a) it's an anonymous block or b) you're nesting PL/SQL blocks. You're doing neither; you can simply take advantage of the implicit declaration section between the IS/AS and BEGIN keywords. Not that you need to in this case.
You're missing a closing bracket from your call to get__respondent_id - I assume that it's got two parameters?

That means you could rewrite your procedure to be:

CREATE OR REPLACE PROCEDURE api_submission(rid_tx          IN VARCHAR2,
                             rform_name      IN VARCHAR2,
                             rdata_date      IN VARCHAR2,
                             v_submission_id OUT NUMBER) IS
BEGIN
  INSERT INTO submission
    (submission_id,
     respondent_id,
     submission_dt,
     submission_type_id,
     submission_name_tx,
     submission_seq_nb,
     create_dt,
     create_user_id,
     modify_dt,
     modify_user_id,
     effective_dt,
     inactive_dt)
  VALUES
    (NULL,
     get__respondent_id(reia_id_tx, rform_name),
     trunc(SYSDATE),
     SYSDATE,
     rdata_date || 'TEST ' || rform_name,
     1,
     SYSDATE,
     1,
     NULL,
     NULL,
     NULL,
     NULL)
  RETURNING submission_id INTO v_submission_id;

END api_submission;
/

no sir, the parameters for the respondent id function are (id_tx, form_name and trunc(sysdate)) — John Wick, Apr 09 '18 at 15:18
"sir" is a bit presumptive, no? In regards to your statement - you have twelve columns specified in the insert into column list, but (assuming three parameters for the get__respondent_id function call), only 11 values passed in. You need to make sure you are passing in the correct values. I'll leave that for your to fix, since you know your table and data better than I do. — Boneist, Apr 09 '18 at 15:22

Creating a stored procedure with IN and OUT Paramters

1 Answers1