dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: works with TOAD, works with sql*plus, but NOT FROM APPLICATION!!!!!

  1. #1
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25

    works with TOAD, works with sql*plus, but NOT FROM APPLICATION!!!!!

    Hello guys,

    Hope everything goes fine. I need some help with this issue.

    I call an stored procedure from TOAD, it works fine. I call the same stored from SQL*Plus, it also works fine, but when i call this stored from an application, using ADO, it brings me and ORA-00900 invalid SQL statement.

    This stored procedure only makes and small QUERY on a view somewhere else, like this :

    CREATE OR REPLACE PROCEDURE Sp_Int_BuscaDatosArbolFPro
    (sTipoDoc in VARCHAR2, sNroDoc in VARCHAR2, out_cursor out pk_cursor.CURSOR_TYPE)
    AS
    BEGIN
    open out_cursor for
    select *
    from leer_por_rut_asegurado
    where tipo_documento = sTipoDoc
    and rut_asegurado = sNroDoc;
    END;

    leer_por_rut_asegurado is a view. I execute this stored from TOAD and it works fine, also from SQL*Plus with no problem, the problem arises when i call this stored procedure from and app via ADO. I cannot see what goes wrong. Anyway i tried this if it might help :

    select * from leer_por_rut_asegurado where tipo_documento = ' 1' and rut_asegurado = '4456789';

    shows no error messages but no data. If i do this :

    select * from leer_por_rut_asegurado where tipo_documento = 1 and rut_asegurado = 4456789;

    shows no error messages with 1 row returned. Is it possible that this could be the cause? the columns datatypes?

    thanks in advance to everyone.
    Regards,

    DGC

  2. #2
    Join Date
    Nov 2005
    Location
    Indianapolis
    Posts
    24

    How are you calling the procedure?

    How is the procedure being called from the app? Can you show us the code? It might be an error in how the procedure is being called, like using the execute Sqlplus command.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hi

    if tipo_documento is varchar2 then you should use quote it with quotes

    ' 1' is not the same as '1'

    Nevertheless since you are using PL/SQL and you have passed varchar2 parameters PL/SQL with quote them automatically. I suspect is the way you invoke the procedure in your code. How are you calling the procedure?

  4. #4
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by wdfloyd
    How is the procedure being called from the app? Can you show us the code? It might be an error in how the procedure is being called, like using the execute Sqlplus command.
    here is some visual basic code :

    'una vez validado, buscamos por tipo y nro doc.
    ssql = stg.sEsquema & "Sp_Int_BuscaDatosArbolFPro(" & ArmaSqlTexto(tnull(sAux, " ")) & ", " & ArmaSqlTexto(ogContacto.nrodoc) & ")"

    'Abro el query
    Set rs = New clsDatos
    rs.Abrir ssql

    'no se cargan los seguros, del recordset vino nulo
    If rs.Cantidad = 0 Then
    Set rs = Nothing
    Exit Function
    End If

    the ssql final looks like this :

    CMR_ARG_DESA_TEST.Sp_Int_BuscaDatosArbolFPro('1', '17650468')

    To make me even more upset, this ssql statement works fine from my developmente site, but raises the ORA-00900 from my client developtment site.

    Thank very much for your time.

    regards,

    DGC

  5. #5
    Join Date
    Mar 2006
    Posts
    74
    erm, you need to review the way you execute stored procedures in ADO.. find a microsoft tutorial on it or something, because that's just not right at all.. you wont get any results that way.. its more like you have to do this:

    dim cmd as ADODB.Command
    cmd = "package.storedprocedure(:input1, :input2, utput)"
    cmd.CommandType = ADODB.StoredProcedure

    cmd.Parameters.Append( cmd.CreateParameter( "input1", varchar, 200, adInput))
    cmd.Parameters.Append( cmd.CreateParameter( "input2", varchar, 200, adInput))
    cmd.Parameters.Append( cmd.CreateParameter( "output", some_cursor_type, 200, adOutput))



    but please read a tutorial.. stored procs are called a totally different way!

  6. #6
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by cjard
    erm, you need to review the way you execute stored procedures in ADO.. find a microsoft tutorial on it or something, because that's just not right at all.. you wont get any results that way.. its more like you have to do this:

    dim cmd as ADODB.Command
    cmd = "package.storedprocedure(:input1, :input2, utput)"
    cmd.CommandType = ADODB.StoredProcedure

    cmd.Parameters.Append( cmd.CreateParameter( "input1", varchar, 200, adInput))
    cmd.Parameters.Append( cmd.CreateParameter( "input2", varchar, 200, adInput))
    cmd.Parameters.Append( cmd.CreateParameter( "output", some_cursor_type, 200, adOutput))



    but please read a tutorial.. stored procs are called a totally different way!
    Thank you. You are absolutely right. I is done that way, i just because i was in a rush i didnīt clarify that the object RS in visual does that for me.

    for example :

    i send this string :"storedprocedure(data_x, data_y)"

    my object internally makes the : "storedprocedure(:input1, :input2, utput)"

    I send all the stored procedures calls the same way. They all works but not this one.

    One thing i must clarify anyway. My development site is using a view to bring the data i need, but this view show local tables, and the store brings the cursor as REF cursor.

    Instead, my client development site is using the view that look for data on a remote server, via dblink. This is a difference i forgot to tell. Might it be the cause?

    Thank you to everyone for the time reading this post.

    regards,

    dgc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width