Sometimes we face issue in getting correct date format in XLS output generated using RTF template.
We can retain format selected in Data Template query and use same in XLS output using below syntax
Double Click on RTF field value
It will open "Text Form Field Options" window , click on "Add Help Text" button
Use below to force literal format
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?ACCOUNTING_DATE?></fo:bidi-override>
Beware of parameterized queries. Parameters are sequential and the number changes when you add any additional parameter for new requirement, subsequently you will need to change the later used bind variables accordingly.
Moreover it does not matter what number you use after colon (:) , it will simple consider it next parameter.
In PL/SQL query does not recognize bind variable number used, instead it will accept based on position
e.g
set serveroutput on size 10000;
declare
l_x number := 1;
l_y number := 2;
l_z number := 3;
l_op1 varchar2(15) :='No';
l_op2 varchar2(15) :='No';
l_op3 varchar2(15) :='No';
begin
execute immediate '
select sysdate from dual
where 1 =:1
and 2 =:3
and 3 = :2'
into l_op1
using l_x , l_y, l_z;
dbms_output.put_line (' Output 1 : '||l_op1);
execute immediate '
select sysdate from dual
where 1 =:1
and 2 =:35
and 3 = :2'
into l_op2
using l_x , l_y, l_z;
dbms_output.put_line (' Output 2 : '||l_op2);
execute immediate '
select sysdate from dual
where 1 =:1
and 2 =:3
and 3 = :2'
into l_op3
using l_x , l_z,l_y;
dbms_output.put_line (' Output 3 : '||l_op3);
exception when no_data_found then
dbms_output.put_line (' ERROR : '||SQLERRM);
end;
Results
Output 1 : 22-SEP-20
Output 2 : 22-SEP-20
ERROR : ORA-01403: no data found
PL/SQL procedure successfully completed.
Last select in the block fails to identify l_y is at position 3 .
If you use simple sql query , this will work but not in PL/SQL
select sysdate from dual
where 1 =:1
and 2 =:3
and 3 = :2;
Input values
:1 = 1
:2 = 3
:3 = 2
Results
22-SEP-20
