Change Query Data Source Name block dynamically using From clause query

Usually, we have build a block in Oracle Form Builder using these properties as a default, for example,

Database Data Block = YES
Query Allowed = YES
Query Data Source Type = Table
Query Data Source Name = EMP

But for some cases we want that block change its Query Data Source Name to something else, dynamically.

So to support this, we have to change some properties

Database Data Block = YES
Query Allowed = YES
Query Data Source Type = FROM clause query
Query Data Source Name =SELECT EMPNO, TO_CHAR(NAME) NAME FROM EMP

And then the block has a pre-query like this,

declare
sql_string varchar2(2000);
begin
message(‘before start’);
pause;
sql_string:= ‘(select empno, to_char(name) name from emp)’;
set_block_property(‘blockname’,QUERY_DATA_SOURCE_NAME,sql_string);

go_block(‘blockname’);
clear_block;
execute_query;
pause;
end;

Hope this usefull. ^_^