Home » Developer & Programmer » Forms » oracle 9i with form 6i
| oracle 9i with form 6i [message #87850] |
Wed, 16 February 2005 23:54  |
ala
Messages: 17
Registered: May 2004
|
Junior Member |
|
|
Hi All,
I faced this proplem in my form.I was working on oracle 8i , forms 6i. I'm going to implement oracle 9i with form 6i.
I created a form built on a procedre, the proplem is:
I have a function called into a backage built on a nother database. the package called by a synonym .
-----this is the code:
query-procedure -----trigger.
declare
in_data get_data.emplisttable_type;
begin
g_data1.getemp(emp_no, in_data);
plsql_table.populate_block(in_data,'EMPNO');
end;
when I run this procedure , It return ORA-6504.
so , I need help.
regards
ala
|
|
|
|
| oracle 9i with form 6i [message #237396 is a reply to message #87850] |
Mon, 14 May 2007 06:20  |
naeeym
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
Hello,
I was looking for info on plsql_table.populate_block. I found this message. I was wondering if you could help me.
I want to know if I could populate a block with a Record Group.
I depend heavily on Record Groups, as it provides a method to customize query results.
Lets say, I have a complex query, where many columns based on many different queries.
Lets say, I wanted to generate weekly sales report on each employee, 1st week, 2nd week, 3rd week and so on..with previous credit history, current given credit, previous due collection, current due collection and total due.
Upto my knowledge and experience, the result can not be get in a single query, or may be, but the query would get so complex, that it would be difficult to handle and inefficient.
What I do is create a record group instead, query different queries as well and get different record group, go through all the records, and get a final record group.
I could have used Global Temporary Table, insert/update on it and so on...but I didn't wanted the extra SQL <==> PLSQL switch, network round trip...
I can put the record group to a report, and all is ok.
My question is, how do I put the record group data in a forms data block??
I don't want to create_record; next_record for each record on the data block. I could use something like a populate_block. But I could not find any info on it, where did it came from, is it a form specific option, or Oracle supplied package??
If I used the Global Temporary Tables, I could have just point the block to it and execute_query, but then that is an extra execute statement, extra network load.
I have already prepared the Record Group, all I want is to point it to the data block and send it to the report.
I was wondering if is there any better way to do these things?? I was thinking if I could use a store procedure that could return such record group, but I think Oracle store procedure does not support Record Group, hence how can it supply multiple records of data??
Still, with store procedure, I would have to call it twice, One for the data block, and another for the Report. I was wondering if theres a way to share data between form and report without calling it twice, as these are complex and resource hungry and time consuming process??
Any help or pointer to the right direction is highly appreciated.
Naeeym.
|
|
|
|
|
|
| Re: oracle 9i with form 6i [message #237611 is a reply to message #237550] |
Tue, 15 May 2007 03:50 |
naeeym
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
Hello,
Thank you David for your reply.
"As you don't want to use manual populate method (and I agree with you on that very strongly) then I suggest that you work on creating a 'view' that will give you your data or use a package to retrieve the information and base the block on it."
I don't know how do I base my data block on package. Creating a view won't solve my problem, as I can't get the result in a single query.
But my initial problem still remains, even If I base my block on a Package (I don't know how though), I would also have to send the data to a printer / Report.
I want a way call the Procedure / Package / Function or whatever Just Once and only once. And I want it to show in a grid, and then (if the user requests) send it to the printer.
If I just tag the procedure to the data block, how do I send it to a Report??
""plsql_table.populate_block" is an internal Oracle feature about which they do not share any information. From memory, Oracle Designer uses it in the table APIs that it generates."
Is there any way to learn more about this plsql_table.populate_block?? or anything like this??
Thanks again. Looking forward to your reply.
Naeeym.
|
|
|
|
|
|
| Re: oracle 9i with form 6i [message #238702 is a reply to message #237875] |
Sat, 19 May 2007 07:28 |
naeeym
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
Hello,
Thanks again.
I was googling for plsql_table.populate_block, without much success.
"Please define 'report'! You can populate a table with the information and then get Oracle Reportwriter to retrieve and print it, or you can write the data to a flat file or a spreadsheet."
I know I can populate a table with the information, and then get the Oracle Reportwriter to retrieve and print it, But I don't want to do that. You see, I have lots of data, and some complex queries, I don't want to run the complex queries twice just for some Oracle inadequate, incomplete options. I didn't ask Oracle to have different product, so different session, and different queries, ...
First of all, I don't want to populate any table thats for sure. May be I could use a SQL Object that I could extend and return from a Stored Function / Procedure. But not TABLE. I don't know if SQL Objects hit the rollback segment and/or data blocks for any reason, if it does, SQL Object is cancelled. I don't know, clarify me if you please whether SQL Objects do hit the data / rollback segment.
Now what I do I retreive data as the form of Record Groups, then I go through the record groups, binary search other record groups, and fill in the blanks.
You see, I do a lot of work just to avoid JOIN conditions with (+) options, and I do have lot of them.
Finaly, when I get a finished Record Group, I send it to the ReportWriter via data_parameter, But it does not execute the query as the data was given via data_parameter. Still the ReportWriter consumes another expensive sessions, as many of my clients have 5 Users Oracle License, Oracle forms+Oracle Reports = 2 sessions, Only two user can run the program. My client complains they bought 5 user license, but effectively they can run only 2(two), another User can run only the form, I suggest them to run only the entry forms on the 3rd user.
You can say, executing query on a populated table is easy, yes, it is, but the network round trips are a mess. And when you have lots of data, the simplest execute_query (unnecessary) is not wanted.
I am doing most of my work this way, Populating Record Groups and sending it to the ReportWriter.
Recently I was developing a form where the User requested to view the data in the form in tabular format, and if necessary they will Print it.
I was in problem, well not a problem but uneasy situation, as I personally don't want to execute the query twice, (one for the form tabular format, and another for the Report).
If I populated a table with the data, I can easily populate the block with that table, and then call the report with that table.
But my problem is, say I have 1000 data, for 1000 data I have to populate (with some complex queries which in turn calls for other tables with lots of data, say I have a invoice table where it have 1 Million data, and I am generating the summary for 1000 sales officer, with credit history, due collection, achievement percentage...blah blah blah.), the 1000 data.
Then with that table I execute on the form, it gets the 1000 data,
then with the table I call the reportWriter, which also gets the 1000 data.
With record Group, I only populate the record group, and send it to the report. I only don't know how to use it as a query data source on the form block.
Thanks in advance.
Naeeym.
|
|
|
|
|
|
| Re: oracle 9i with form 6i [message #239300 is a reply to message #238977] |
Tue, 22 May 2007 00:20 |
naeeym
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
Hello,
Thanks again.
Running the report in batch, I don't know, I don't quite follow. We have users running queries all the time, I don't what condition, and what crieteria they would choose to query, and the amount of data processed, and also the result is sometime huge.
We have 13 branches, 1 new branch is going to be open next month, each branch contributing 50,000 to 250,000 data per month, also with other varius information,
We have got a big Data ware house, where we save the data per branch per month, where we have got last 2 years data (2005 and 2006), and also the data for current year.
We were planning to include previous data that was in different format prior to Oracle, to migrate and also enter into the Data warehouse.
I am developing various Forms for the User so that they could choose a Branch, Start Date, End Date, and run various queries, like MR wise sales report, SR wise return, closing forms, Product wise sale for that time, Party Statements,
I also loop through the Monthly data, to have Multiple months data in a single statement.
We also have CONSOLIDATED Reports, Product wise ALL Depots, All Product SALES, Returned Products, Sales Value etc.
We also have YEARLY CONSOLIDATE Reports, where the amount of data it touches is HUGE.
We were planning on developing some more PRODUCT WISE YEARLY COMPARISON REPORTS, Say upto APR 2005, APR 2006, and APR 2007, the product wise sale is this and that, and the value is this and that, and the Sales Trend is somewhat what the result would be.
I am not sure if these type of queries can be done or should be done in Batch. Say the MD wants to know the sales figure of some particular depot, I would have to maintain another database just to remember in what table I saved that particluar query result.
Also its not very feasible. May be I was on leave, and the MD wanted that report that time, He might be annoyed and ask for my head (huh huh).
What I tried to do is put all the queries in a compiled form, and put it in the MD's laptop, so that he could choose the branch or all depots (consolidate), and input the date, and get whatever the report he might want. Default date is the current date, so normally all he have to do is click some button.
And I believe this is the way to go, if Oracle can not support this, excuse Oracle.
But upto now, Oracle is supporting us quite well, what the problem is on our end. Some of the queries we run have multiple grouping, ordering, sub-queries blah blah blah and lots of data. I don't know how it does handle all this, but it does. I don't believe Oracle can do EVERYTHING, like sucking your dick, but its quite a mouthfull.
I believe saving all the information I need to query and
"(including, if necessary the 'rowid' of the records to be rpinted) " is not OK with me, If I go for this way, I am afraid, all this rowids and the temporary information would outrun the Original data, and in no time it will grow so fast and so big, I might have to look for a New Oracle, new database, may be Oracle Power 100.
and I am not sure
"a trigger on the table to invoke the report"
An user clicks a button to view some report, and he looks for a trigger?? a gun fire?? hah hah
"and the output comes out on that user's predefined printer (which is a also defined in the database for each user)."
And that particular user that wanted the print has 3 printers, 1 plotter, he puts the paper in the DOT printer, and the laser printer starts beeping??
Trigger is EVIL, its so bad you may not believe, something is happening as a by products of some other thing is EVIL. Try not to use Trigger on anything, on the database or a GUN, never. Its only my theory.
If you sit tight and wait for the user to come to you, and ask for a report, batch procesing might be an IDEA (good or bad, I don't know), but if you have lots of data, and a lot of users looking for info on the database in lots of different ways, going for PRE-COMPUTED static data is NOT the way to go, well may be a couple of special cases, the the query is definite and known, but these are special cases.
Sorry, I was on the mood for writing this time.
But my original question remains open. How do I share data (without another TABLE) between form and report without calling it twice, even with TABLE, you are calling (executing) it twice.
Thanks again.
Naeeym.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Dec 06 15:17:54 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
