CODEWITHSUNDEEP
oracle-databasestored-procedures
Héctor Pérez Silva
Héctor Pérez Silva

Reputation: 470

Oracle query in stored procedure

i'm working with stored procedures, i have a query that should bring one specific id

 SELECT * into SID_INCOMING FROM 
                    (
                     SELECT SID
                     FROM TBL_INCOMING  
                     WHERE XKEY = ''||nro_tarjetatmp||'_'||fecha_hr_trasacfinal||'_'||datos_referencia_adquirentetmp||''
                     AND CODIGO_AUTORIZACION IN(''||codigo_autorizacion||'')
                     AND MIT IN(''||mit||'')
                     ORDER BY SID ASC
                   ) WHERE ROWNUM <= 1;

the variables values are in order

when it's executed i get this result

enter image description here

but when i execute the same query with the same parameters i get this result

enter image description here

and this is the one i sould get with the procedure, so why is this happening? it seems to me, that the sp is not considering the second and third parameter

any help is welcome, thanks

Upvotes: 0

Views: 154

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8665

You need to use aliases to avoid your problem with AND MIT IN(''||mit||''): this predicate compares column MIT with itself. SO just add aliases:

 SELECT * into SID_INCOMING FROM 
                    (
                     SELECT SID
                     FROM TBL_INCOMING  ti
                     WHERE ti.XKEY = ''||your_proc_name.nro_tarjetatmp||'_'||your_proc_name.fecha_hr_trasacfinal||'_'||your_proc_name.datos_referencia_adquirentetmp||''
                     AND ti.CODIGO_AUTORIZACION IN(''||your_proc_name.codigo_autorizacion||'')
                     AND ti.MIT IN(''||your_proc_name.mit||'')
                     ORDER BY ti.SID ASC
                   ) WHERE ROWNUM <= 1;

Also why do you add ''||? It doesn't add anything into your variables, but forces impicit type conversion in cases when they are not varchar2/char data types.

Could you show the results of "describe TBL_INCOMING" please? Since looks like CODIGO_AUTORIZACION is a number data type and probably MIT is number too.

Upvotes: 1

Related Questions