i create a view(outdoor_log) as and joined it with base table(Sup_master)and create a button named as "ADD" for displaying records in base table block(outdoor_logdetail).
I want that when i select particular record from base table(sup_master) so it displayed relevent records from view(outdoor_log) and when we click on ADD button it take records from 4 tables ..for this i write code on ADD button
insert into outdoor_logdetail (select pr_prog_master.prno,pr_prog_detail.sno,
but when we click on "ADD" it insert duplicat rows into "OUTDOOR_LOGDETAIL" block
and in the "OUTDOOR_LOGDETAIL" block some items are control item for them i write an another query with inline view and code this query in POST _QUERY with "OUTDOOR_LOGDETAIL" block.
now when we click on "ADD" button it creates duplication and result is soo much slow.
plz help me and solve my problem..
waiting ur reply
Perhaps your way is the right way, but I'd do that without "Add" buttons, using ordinary master-detail relationship.
If I understood you well, master block's source would be view you created ("outdoor_log"). Detail block can not be a table - it could be a view if you created one. Why didn't you do that? This view ("outdoor_log_detail") could use select statement written in WHEN-BUTTON-PRESSED trigger, the one in INSERT statement (select ... from pr_prog_master, pr_prog_detail, sup_master where ...).
Even better idea would be a detail block whose source is a FROM clause query - you'd again use the same SELECT statement, and this option would allow you to create relationship between master and detail block - just include desired master field into the WHERE clause of the detail block. This solution is OK if detail block is used ONLY to display values - you can't use this option to insert, update or delete records.
Fancy solution might be use of a stored procedure which would have, as an IN parameter, a key field from the master block. Returning value should be an index-by (PL/SQL) table variable. It is useful when displaying information which requires complex SQL queries resulting in poor performance (you said your solution is "soo much slow").
Maybe all I wrote is nonsense, but - to me - using a button to insert values into the table and then fetch them into the detail block - looks like a bad design.
Google found several possible examples, but all of them require non-free registration. If someone knows a free solution, share it with us. Or, are you ready to pay for it?
In a few words:
- write a stored procedure. As you need a PL/SQL table which also needs to be declared, you could do that in a package. First declare a record type which will represent data you want to display, then another type which is a table of previously declared record type, and, finally, PL/SQL table.
- stored procedure needs IN parameter(s) which will enable you to restrict query written in the procedure and IN OUT parameter of a PL/SQL table type. Data fetch could be achieved through a cursor loop if there are several records to be retrieved.
- in Oracle Forms, create a block whose source is the previously written stored procedure. Data block wizard will suggest available columns and procedure arguments. Set values for arguments you need and create master-detail relationship.
Forms will create several triggers which are not to be edited.
- you'll need an ON-LOCK trigger on detail block; if records are to be displayed only, its code is a simple "null;" statement.