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

[dgcampos]

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

[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.

[pando]

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?

[dgcampos]

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

[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, :output)"
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!

[dgcampos]

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, :output)"
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, :output)"

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