Oracle query in stored procedure

Question

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

4946110112060005_200116_74064350165099586691985
536018
05

when it's executed i get this result

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

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

that is not the same query - AND MIT IN(''||mit||'') . First off, change the procedure sql to use bind parameters. — OldProgrammer
– OldProgrammer, Commented Aug 12, 2020 at 22:29
thanks man, i declared the variables, set them (:= with the in parameter) and pass them to the query and it worked, ok so, in parameters in a query are a no no. — Héctor Pérez Silva
– Héctor Pérez Silva, Commented Aug 12, 2020 at 22:35

Sayan Malakshinov · Accepted Answer · 2020-08-13 00:59:00Z

1

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.

answered Aug 13, 2020 at 0:59

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Héctor Pérez Silva Over a year ago

thanks mate, the problem is solved, i used plsql variables and it worked, but i will give you accepted answer (this is the only answer and i dont know how to close the topic xD)

Collectives™ on Stack Overflow

Oracle query in stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related