Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Please help with concatenate a string in the QUERY_DATA_SOURCE

antobaySep 1 2021

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. 
This post has been answered by Hub Tijhuis on Sep 1 2021
Jump to Answer
Comments
Post Details
Added on Sep 1 2021
10 comments
2,667 views