2 Replies Latest reply: Jun 5, 2013 2:51 AM by ThAn RSS

    problem store procedure

    1011493
      I'm doing a sp that returns me the value of a variable, then an update, but does not work when I add the output parameter

      i dont know what's the error im new in oracle

      the code its:
      create or replace
      PROCEDURE PA_CONSECUTIVO (TipoConsecutivo in VARCHAR,AUXI OUT int)

      AS

      BEGIN

      SELECT consecutivo + 1 into AUXI
      FROM tbl_consecutivo
      WHERE UPPER(ltrim(rtrim(Tipo_Consecutivo))) = UPPER(ltrim(rtrim(TipoConsecutivo)));

      UPDATE tbl_Consecutivo SET consecutivo = AUXI
      WHERE upper(ltrim(rtrim(Tipo_Consecutivo))) = upper(ltrim(rtrim(TipoConsecutivo))) ;


      END PA_CONSECUTIVO;


      and the table its

      TIPO_CONSECUTIVO     VARCHAR2(20 BYTE)          
      CONSECUTIVO     NUMBER(38,0)     
      FECHAULTIMO     DATE     


      I appreciate the help.
        • 1. Re: problem store procedure
          rp0428
          Welcome to the forum!

          Unfortunately you have posted in the wrong forum.

          This forum, as the title says, is for 'SQL Developer (Not for general SQL/PL/SQL questions).
          >
          I'm doing a sp that returns me the value of a variable, then an update, but does not work when I add the output parameter
          >
          And it won't work - you added an 'output' parameter. But then this code tries to use it as if it were an 'input' parameter
          UPDATE tbl_Consecutivo SET consecutivo = AUXI 
          You can only assign to output parameters; you cannot read them.

          1. Define a variable
          2. Select into the variable
          3. UPDATE using the variable
          4. Assign the variable to the output parameter

          This is the WRONG forum. Please mark this question ANSWERED.

          If you need any help other than the above repost the question in the SQL and PL/SQL forum.
          PL/SQL
          • 2. Re: problem store procedure
            ThAn
            Hi,

            another approach would be to define AUXI as IN OUT Parameter, so that you can use
            it as an assignment target as well.

            Regards