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. ^_^

Install Form Builder on Vista

i use form builder version Oracle Developer Suite 10g (9.0.4) on XP, then when i install it on Vista, it came with 2 error message, ORA-00604 and ORA-01009.

ORA-00604: error occurred at recursive SQL level string
Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).
Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

ORA-01009: missing mandatory parameter
Cause: A host language program call did not pass all required parameters.
Action: Check the syntax for the call and enter all required parameters.

This error occured because Vista only compatible with form version 10.1.2.3 & upcoming version 11.1.1.

Then with no other option, i download the new version of Oracle Developer Suite 10g (10.1.2.0.2), it comes with two files ds_windows_x86_101202_disk1.zip (626,122,752 bytes) and ds_windows_x86_101202_disk2.zip (236,880,881 bytes).

When first time i install it, i got problem again. It show this message

“Install has encountered an error while attempting to verify your virtual memory settings. Please verify that the sum of the initial sizes of the paging files is at least 256 MB”

To handle it, click Start | Control Panel | System and Maintenance,

click Tab System, View amount of RAM and processor speed

“uncheck automatically manage paging file size for all drives”

Set Initial size to = 2048 and Max = 4096, then restart your computer.

Generate Insert Statement Script to Extract Data from Oracle Table

Here, you can use the following code to generate insert statement to extract the existing data from an oracle table. The generated script can be run at a later time to re-create your data. The code handles only date, char, varchar2, and numeric data types. I got this code from googling, original version by Ashish Kumar, but i have modify user_tables to ALL_TABLES, user_tab_columns to ALL_COLUMNS, because i want to get insert script from another user schema, not only from my user schema.