0

I am getting with the call of store procedure ORA-00900 error. My code is:

Calling:

execute pkg_titularitalia.insertar(1,12,'123','123', 123, null);

Store Procedure:

 PROCEDURE Insertar(pIdUsuarioSeg           IN number,
                     pIdTitular             IN OUT number,
                     pPartitaIVA            IN OUT varchar,
                     pCodigoIS              IN OUT varchar,
                     pIdUsuarioModificacion IN OUT number,
                     pFechamodificacion     IN OUT date) AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('aa');
  END;

If I test in console I see the result in DBMS console but when I am executing in code is giving me the error ORA-00900 invalid statment

what is it wrong?

I changed the call for 

declare 
  a number := 5643;
  b varchar := '123';
  c varchar := '';
  d number := 5643;
  e date;
begin
  -- Call the procedure
  execute insertar(1, a, b, c, d, e);
end;

as everybody suggested, is giving me yet ORA-06550

====================================================================================

SOLUTION (I GOT IT)

declare 
      a number := 5643;
      b varchar(13) := '123';
      c varchar(39) := '';
      d number := 5643;
      e date;
    begin
      -- Call the procedure
      execute insertar(1, a, b, c, d, e);
    end;

006550 was giving me for the length of the varchar in the declare section of begin/end block

EstevaoLuis
  • 2,422
  • 7
  • 33
  • 40
david
  • 39
  • 2
  • 10
  • sorry I copied directly I know that – david Dec 03 '13 at 09:25
  • thx. I set begin end block as Frank Schmitt said in the answer but I am getting ORA-06550. – david Dec 03 '13 at 09:29
  • You also will hit `oar-00363` error because you have most actual parameters of your procedure declared in `in out` mode and trying to pass in literals. Literals cannot be used as an assignment target, only variables. – Nick Krasnov Dec 03 '13 at 09:31
  • what are the line and column numbers in the error message now? – Tom Thomas Dec 03 '13 at 09:33
  • I edit the answer withh all of you told me, the store procedure I didnt changed but I change the way to call but it is giving me ORA-06550 – david Dec 03 '13 at 09:41
  • 1. When declaring a variable(**not a formal parameter of a stored procedure**) of `varchar2` (**note** not `varchar`. `Varchar2`) data type you **have to** specify a maximum size. 2. About how to execute your stored procedure. Take a close look at @Frank Schmitt [answer](http://stackoverflow.com/a/20347258/997660). – Nick Krasnov Dec 03 '13 at 10:02
  • I know Nicholas, it was only an example, I know it is better use varchar2 (grrr sometimes confuse me because I am writing code for MySql, Sql Server and Oracle), but anyway, it is an example. Thanks anway, I wrote the solution in the question – david Dec 03 '13 at 10:10

1 Answers1

0

execute is a SQL/Plus command. To run your procedure from another application, just wrap the call inside a PL/SQL block:

begin
  pkg_titularitalia.insertar(1,12,'123','123', 123, null);
end;
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • thankyou it is my big provblem I use sql server and oracle and sometimes it is confusing. thx, it is that – david Dec 03 '13 at 09:26
  • Frank I edited again the question I set what you mentioned but ist is giving ORA-6550 – david Dec 03 '13 at 09:42
  • ok frank, I got the solution, when I declared the parameters I had to set the length of the varchar, I will reedit topo with the right solution – david Dec 03 '13 at 10:03