Please help, sorry I don't intend to double post. I posted this originally in Forms group but I feel it is more of an SQL / PL/SQL question.
I want to concatenate a string in the QUERY_DATA_SOURCE in Oracle form (PL/SQL block) but couldn't get it to work. Am able to do this in Oracle SQL developer but the forms throws an error (ORA-01403) no data found. This is because I got the concatenation wrong. I will be grateful for your help. Thanks
Forms [32 Bit] Version 11.1.2.0.0 (Production)
Oracle Toolkit Version 11.1.2.0.0 (Production)
PL/SQL Version 11.1.0.7.0 (Production)
Oracle Procedure Builder V11.1.2.0.0 - Production
My objective is to concatenate 'EUR' to col_b.
Before = INVOICE_123.pdf
Expected result = EURINVOICE_123.pdf
This works in SQL
create table table_a
( col_a number,
col_b varchar2(20));
Insert into table_a (COL_A,COL_B) values (123,'INVOICE_123.pdf');
Insert into table_a (COL_A,COL_B) values (124,'INVOICE_124.pdf');
select decode(col_a,123, 'EUR'||col_b, col_b)col_b from table_a
COL_B
EURINVOICE_123.pdf
INVOICE_124.pdf
IN Oracle Forms QUERY_DATA_SOURCE
v_sql_query := '('||
'select
decode(col_a,123, ''EUR''||col_b, col_b) col_b
from table_a
)';
This did not work. I have tried to put more double quotes but still gives error (ORA-01403) no data found.