5 Replies Latest reply: Apr 12, 2010 11:26 AM by 21205 RSS

    how to run stored procedure in sql developer?

    763598
      Is this the correct syntax to run the stored procedure in sql developer?

      the procedure is written to accept one input parameter and return an int value.
      BEGIN
        update_employee_login ('john') 
      END;
        • 1. Re: how to run stored procedure in sql developer?
          Tubby
          user12876168 wrote:
          Is this the correct syntax to run the stored procedure in sql developer?

          the procedure is written to accept one input parameter and return an int value.
          Do you have a procedure or a function? A procedure doesn't return anything by definition, though you could use the parameter as an OUT parameter which would potentially modify the value of the variable you pass to the routine. In which case, you wouldn't want to pass the string 'john' like you are doing.
          declare
             l_return_var    varchar2(100) := 'john';
          BEGIN
             update_employee_login (l_return_var) 
          END;
          Alternatively, if you have a function (you always must return something) then you would need an assignment variable of the type which your function is declared to return ( i guessed number here)
          declare
             l_return_var    number;
          BEGIN
             l_return_var := update_employee_login ('john') 
          END;
          • 2. Re: how to run stored procedure in sql developer?
            21205
            user12876168 wrote:
            the procedure is written to accept one input parameter and return an int value.
            no, what are you going to do with the INT return value? Where are you going to leave it?
            declare
               local_var int;
            begin
               update_employee_login ('john', local_var) ;
            end;
            or if it is a function
            declare
               local_var int;
            begin
               local_var := update_employee_login ('john') ;
            end;
            • 3. Re: how to run stored procedure in sql developer?
              763598
              I get an incorrect sysntax while running this in sql developer.

              The error message is 'Incorrect syntax near int'
              • 4. Re: how to run stored procedure in sql developer?
                251692
                Maybe it the shortest way to run the procedure in sql developer is to locate it in the navigator, that right-click with the mouse and the chose run.

                sql developer will show a little editor, where you can copy the code from or where you can fill in your parameters - you have the choice

                Regards,
                Ingo
                • 5. Re: how to run stored procedure in sql developer?
                  21205
                  alright,... well you caught me I didn't test it...

                  substute INT with NUMBER