oracle - Problems with binding variables in SQL from shell script

Question

I need to use bind variables in an SQL query executed from a shell script. Having lots of troubles with it, I started to simplify and ended up with the following;

variable ret_val_1 clob
exec select to_char(t.Fecha,'yyyymmddhh24mi')  into :ret_val_1  from MyTable t where t.Column1 = 'A' and rownum <=1
spool &1
select :ret_val_1 from dual;
spool off;
set verify on;
exit;

This works fine.

Now, if I try exactly the same code, but using Column2 which is a NUMBER (Column1 is a CHAR), I get the following error:

exec select to_char(t.Fecha,'yyyymmddhh24mi')  into :ret_val_1  from MyTable t where t.Column2 = 4 and rownum <=1

SP2-1504: Cannot print uninitialized LOB variable "RET_VAL_1"

When I execute directly:

select to_char(t.Fecha,'yyyymmddhh24mi')  
from MyTable t 
where t.Column2 = 4 and rownum <=1*

it works perfectly fine!

Any clue?

In the real query, which is much more complex, I have to join both clauses (among others) and use two bind variables as I have to spool them into two different txt files.

Thxs a lot

question from:https://stackoverflow.com/questions/65936847/problems-with-binding-variables-in-sql-from-shell-script

Answer 1

Waitting for answers