Home » Developer & Programmer » Forms » HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE
HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE [message #253722] Tue, 24 July 2007 13:15 Go to next message
jtc103
Messages: 19
Registered: May 2007
Junior Member
Hi All,

I populate my details block and my totals block dynamically using the following code (in blue):

SET_BLOCK_PROPERTY('blk_details', QUERY_DATA_SOURCE_NAME,
'(SELECT ENTNUM, ESTNUM, ROUND(' || v_col(1) || ') AS det_col1, ROUND('

|| v_col(2) || ') AS det_col2, ROUND('

|| v_col(3) || ') AS det_col3, ROUND('

|| v_col(4) || ') AS det_col4, ROUND('

|| v_col(5) || ') AS det_col5, ROUND('

|| v_col(6) || ') AS det_col6, ROUND('

|| v_col(7) || ') AS det_col7, ROUND('

|| v_col(Cool || ') AS det_col8, ROUND('

|| v_col(9) || ') AS det_col9, ROUND('

|| v_col(10) || ') AS det_col10, ROUND('

|| v_col(11) || ') AS det_col11, ROUND('

|| v_col(12) || ') AS det_col12, ROUND('

|| v_col(13) || ') AS det_col13, ROUND('

|| v_col(14) || ') AS det_col14, ROUND('

|| v_col(15) || ') AS det_col15, ROUND('

|| v_col(16) || ') AS det_col16, ROUND('

|| v_col(17) || ') AS det_col17, ROUND('

|| v_col(18) || ') AS det_col18, ROUND('

|| v_col(19) || ') AS det_col19, ROUND('

|| v_col(20) || ') AS det_col20, ROUND('

|| v_col(21) || ') AS det_col21, ROUND('

|| v_col(22) || ') AS det_col22, ROUND('

|| v_col(23) || ') AS det_col23, ROUND('

|| v_col(24) || ') AS det_col24, ROUND('

|| v_col(25) || ') AS det_col25, ROUND('

|| v_col(26) || ') AS det_col26, ROUND('

|| v_col(27) || ') AS det_col27, ROUND('

|| v_col(28) || ') AS det_col28, ROUND('

|| v_col(29) || ') AS det_col29




FROM ' || v_current_view || '
WHERE (source = '''||:blk_selection.lst_source||''')

AND (naicsest = '''||:blk_selection.lst_naics||''')

AND (geoest = '''||:blk_selection.lst_geoest||''')

AND (substrat = '''||:blk_selection.lst_substrata||''')

AND (empnemp = '''||:blk_selection.lst_empnemp||'''))


');
/*ORDER BY ' || v_str_orderby || ' */


SET_BLOCK_PROPERTY('blk_details', ORDER_BY, v_str_orderby);

GO_BLOCK('blk_details');
EXECUTE_QUERY;

SET_BLOCK_PROPERTY('blk_totals', QUERY_DATA_SOURCE_NAME,
'(SELECT COUNT(entnum) AS tot_entnum, COUNT(estnum) AS tot_estnum,

SUM(ROUND(' || v_col(1) || ')) AS tot_col1, SUM(ROUND('

|| v_col(2) || ')) AS tot_col2, SUM(ROUND('

|| v_col(3) || ')) AS tot_col3, SUM(ROUND('

|| v_col(4) || ')) AS tot_col4, SUM(ROUND('

|| v_col(5) || ')) AS tot_col5, SUM(ROUND('

|| v_col(6) || ')) AS tot_col6, SUM(ROUND('

|| v_col(7) || ')) AS tot_col7, SUM(ROUND('

|| v_col(Cool || ')) AS tot_col8, SUM(ROUND('

|| v_col(9) || ')) AS tot_col9, SUM(ROUND('

|| v_col(10) || ')) AS tot_col10, SUM(ROUND('

|| v_col(11) || ')) AS tot_col11, SUM(ROUND('

|| v_col(12) || ')) AS tot_col12, SUM(ROUND('

|| v_col(13) || ')) AS tot_col13, SUM(ROUND('

|| v_col(14) || ')) AS tot_col14, SUM(ROUND('

|| v_col(15) || ')) AS tot_col15, SUM(ROUND('

|| v_col(16) || ')) AS tot_col16, SUM(ROUND('

|| v_col(17) || ')) AS tot_col17, SUM(ROUND('

|| v_col(18) || ')) AS tot_col18, SUM(ROUND('

|| v_col(19) || ')) AS tot_col19, SUM(ROUND('

|| v_col(20) || ')) AS tot_col20, SUM(ROUND('

|| v_col(21) || ')) AS tot_col21, SUM(ROUND('

|| v_col(22) || ')) AS tot_col22, SUM(ROUND('

|| v_col(23) || ')) AS tot_col23, SUM(ROUND('

|| v_col(24) || ')) AS tot_col24, SUM(ROUND('

|| v_col(25) || ')) AS tot_col25, SUM(ROUND('

|| v_col(26) || ')) AS tot_col26, SUM(ROUND('

|| v_col(27) || ')) AS tot_col27, SUM(ROUND('

|| v_col(28) || ')) AS tot_col28, SUM(ROUND('

|| v_col(29) || ')) AS tot_col29

FROM ' || v_current_view || '
WHERE (source = '''||:blk_selection.lst_source||''')

AND (naicsest = '''||:blk_selection.lst_naics||''')

AND (geoest = '''||:blk_selection.lst_geoest||''')

AND (substrat = '''||:blk_selection.lst_substrata||''')

AND (empnemp = '''||:blk_selection.lst_empnemp||'''))');

GO_BLOCK('blk_totals');
EXECUTE_QUERY;


Now my question is, what would be the best way for me to keep the generated data so that I can keep accessing them within the form without having to re-run the same code? What I want to do is be able to save the generated data into an external file, and I was reading about this and I believe I can use the DDE package, but I'm not sure how to keep the data and manipulate it once it has been displayed? Any insights on this would be greatly appreciated as I'm very new at using forms developer.
Re: HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE [message #253750 is a reply to message #253722] Tue, 24 July 2007 14:46 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you, perhaps, store date into a global temporary table (if your database version supports it)?
Re: HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE [message #254005 is a reply to message #253750] Wed, 25 July 2007 08:17 Go to previous messageGo to next message
jtc103
Messages: 19
Registered: May 2007
Junior Member
would the save to a global temporary table be part of my SET_BLOCK_PROPERTY code? thanks
Re: HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE [message #254050 is a reply to message #254005] Wed, 25 July 2007 11:34 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ummm ... I wouldn't say so. What I meant was to create a global temporary table (GTT) (once, not every time you use the form) and fill it using your "blue" code - not as a part of the SET_BLOCK_PROPERTY, but a trigger (or procedure). Block's source would then be a GTT and all the "ordinary" commands (enter query, execute query, updates, deleted, ...) would work normally.

I'm sorry if it doesn't do what you need, but - this concept came on my mind while reading your post. Perhaps it is not applicable to the problem at all. If so, sorry for wasting your time.
Re: HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE [message #255172 is a reply to message #253722] Mon, 30 July 2007 23:30 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please read the sticky concerning formatting. Making the code 'blue' was useless!! Putting it inside 'code' '/code' tags would have made it more readable, especially if you had formatted it!!

create or replace package tst_pkg is
  function tstname (name in varchar2)
    return varchar2;

  pragma restrict_references (tstname, wnds, wnps);
end tst_pkg;
/
create or replace package body tst_pkg is
  type varchr_arr is table of varchar2 (5)
    index by binary_integer;

--  NOTE :
--  Cannot use the <pls table>.<property> syntax (eg. tst_tab.last) at all
--  in the file as this causes the pragmas to fail.  Array sizes are now
--  initialised in package initialisation.
--  Could not use composite data type for the same reason. Converted each
--  composite record to 2 scalar varchar2(4) arrays.
  tst_tab_end   pls_integer;
  tst_tab       varchr_arr;

  function tstname (name in varchar2)
    return varchar2 is
    name_copy   varchar2 (50);
  begin
    name_copy  := name;
--
-- remove non alpha chars by translating them to blanks and removing blanks
--
    name_copy  := translate (name, '''-._~@#$%^&*()+=|/', '                  ');
    if length (name_copy) = 0 then
      return ('0');
    end if;
    name_copy  := replace (name_copy, ' ', '');
    if length (name_copy) = 0 then
      return ('0');
    end if;
    return (name_copy);
  end tstname;
--***********************************************************************
--      PACKAGE INITIALISATION
--**********************************************************************
begin
--  initialize tst_tab
--
  tst_tab (1)  := ('A');
  tst_tab (2)  := ('E');
  tst_tab (3)  := ('I');
  tst_tab (4)  := ('O');
  tst_tab (5)  := ('U');
  tst_tab_end  := 5;
end tst_pkg;

This technique will permit you to use the contents of a 'temporary' table quickly and easily. If you populate the table via some function call then each person will have their own copy. The table is populated the first time that you call a routine in the package.

David
Re: HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE [message #255392 is a reply to message #255172] Tue, 31 July 2007 11:59 Go to previous messageGo to next message
jtc103
Messages: 19
Registered: May 2007
Junior Member
Thanks David. And sorry about not using the code tags.

So, once I've populated the table from my first function call, do I just keep reusing tst_tab(1) whenever I need to, to access the data? Is it working like a global table?

Do excuse my questions if it sounds ignorant, as I'm not an experienced forms and pl/sql user.

Thanks!
Re: HOW TO SAVE DATA GENERATED SO I CAN KEEP RE-USING THE SAME DATA TO BE ABLE TO SAVE IT TO FILE [message #256054 is a reply to message #255392] Thu, 02 August 2007 20:41 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
The data exists in the database. You can't access it DIRECTLY from the form but you can write 'get', 'put', and 'process' routines in the package which you then call from the form. I suggest that you read the "PL/SQL User's Guide and Reference" manual and get a better understanding of packages. Remember, any PL/SQL that you can put into a Forms package can be stored in the database. Some people, me included, believe that the database is the proper place for ALL PL/SQL code. The only commands that can ONLY be stored in Forms packages are the Forms commands themselves, for example, 'up', 'down', 'get_item_property', 'set_item_property', etc.

David
Previous Topic: Call Report from Forms - sometimes process "hang up"
Next Topic: How to reverse a string / array (merged)
Goto Forum:
  


Current Time: Wed Dec 07 08:42:11 CST 2016

Total time taken to generate the page: 0.10983 seconds