| DBMS_SQL: DEFINE_ARRAY and COLUMN_VALUE problem in FORMS 6i [message #148505] |
Thu, 24 November 2005 13:19  |
POCEWAR
Messages: 3
Registered: November 2005
|
Junior Member |
|
|
Hi gurus. The following example code works OK when executed on database (the goal is to use DEFINE_ARRAY to fetch multiple rows in a single SELECT statement):
declare
c number;
d number;
n_tab dbms_sql.Number_Table;
indx number := 1;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'select number_value from my_table order by 1', 1);
dbms_sql.define_array(c, 1, n_tab, 10, indx);
d := dbms_sql.execute(c);
loop
d := dbms_sql.fetch_rows(c);
dbms_sql.column_value(c, 1, n_tab);
for i in n_tab.first .. n_tab.last loop
dbms_output.put_line(TO_CHAR(n_tab(i)));
end Loop;
exit when d != 10;
end loop;
dbms_sql.close_cursor(c);
exception when others then
if dbms_sql.is_open(c) then
dbms_sql.close_cursor(c);
end if;
raise;
end;
n_tab is filled correctly by the COLUMN_VALUE statement.
But, when executed within Forms 6i it doesn't work (dbms_output.put_line was changed within FORMS by a MESSAGE built-in). The problem seems to be COLUMN_VALUE doesn't copy the rows from DBMS_SQL buffers to the specified destination table n_tab (as it does when the block is executed directly on the database).
After dbms_sql.column_value(c, 1, n_tab); statement, n_tab.first and n_tab.last are NULL. Therefore, the table is not being updated.
Do you know the reason of these different behaviors? What could I do to get the same behavior in Forms, if possible?
Thanks a lot in advance
|
|
|
|
|
|
| Re: DBMS_SQL: DEFINE_ARRAY and COLUMN_VALUE problem in FORMS 6i [message #150594 is a reply to message #150017] |
Thu, 08 December 2005 10:52  |
POCEWAR
Messages: 3
Registered: November 2005
|
Junior Member |
|
|
Thanks David for your reply. No, it's ok as it is: the idea is to keep reading blocks of n records (in my example n = 10) until the system reads less than that (in that case it means that the last block of records was read). In the example provided the "processing" of the records is done by the FOR statement (which prints the records that have been read). As a note, the same code works fine when I run it directly in SQL*PLUS or TOAD (in Oracle Forms I just substituted the dbms_output.put_line statement by a message one). For instance, in TOAD n_tab(1) exists and I can print it, while in Oracle Forms I get an error (because n_tab(1) doesn't exists).
I think the real problem is that COLUMN_VALUE (using an array instead of a scalar variable) appears to be a feature designed to run just on server side, not on client's side (PL/SQL provided in Oracle Forms -client side- is a limited version of database PL/SQL -server side-).
Thanks
|
|
|
|
|
|
| Re: DBMS_SQL: DEFINE_ARRAY and COLUMN_VALUE problem in FORMS 6i [message #150790 is a reply to message #150671] |
Fri, 09 December 2005 14:06 |
POCEWAR
Messages: 3
Registered: November 2005
|
Junior Member |
|
|
Thank you David for your reply. A base table block cannot be used in my case. Well, it's a long story... but I'll try to make it short (the little example I posted was merely illustrative, but not representative).
We programmed a module some time ago (a report tool) where we can store different queries, to fit our end users needs. Therefore, those queries are just text stored in a table... of course, the queries table is a master one having other child tables (query's parameters, query's roles, query's fields, query's child queries, etc). Thus, the end user can choose an existing query, enter its parameters and then the query is executed at runtime (using DBMS_SQL) and the result set is exported automatically to Excel (let's say this tool is our tiny, homemade Discoverer ). Since the query construction and execution is performed in a database package, the solution works fine.
But this time I wanted to develop a much simpler module with more capabilities, faster and, very important, without modifying the existing database package nor creating a new one (let's say I have strong reasons for this). Therefore, the code for the new application must be in the form. But you already know the results of trying to use DEFINE_ARRAY and COLUMN_VALUE within Forms.
Finally I desisted from using DBMS_SQL in this case, and tried record groups instead (creating and populating groups dynamically). Of course it would have been faster to use DBMS_SQL with arrays (reading n-record blocks per read) but at least the solution with record groups was faster that using DBMS_SQL, reading row by row.
Thanks
[Updated on: Fri, 09 December 2005 14:09] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
