Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: Post Query Trigger

    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).
    target is:


    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

    declare
    rec number;
    begin

    commit_form;
    clear_message;
    insert into outdoor_logdetail (select pr_prog_master.prno,pr_prog_detail.sno,
    sup_master.pdate,pr_prog_detail.remarks,sup_master .emp_code
    from pr_prog_master,pr_prog_detail,sup_master
    where pr_prog_detail.prno=pr_prog_master.prno
    and pr_prog_master.supplier_id=sup_master.sup_code
    and rtrim(pr_prog_master.prno)=rtrim(utdoor_log.prno)
    and pr_prog_detail.sno=outdoor_log.sno
    and sup_master.emp_code=utdoor_logdetail.emp_code
    );
    commit;
    rec :=:system.cursor_record;
    go_block('outdoor_logdetail');
    execute_query;
    go_block('outdoor_log');
    go_record(rec);
    end;
    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  3. #3
    Join Date
    Oct 2005
    Posts
    2

    Pst Query Trigger

    u r talking about Stored procedure in ur reply ..can u give me an example of the whole process should i implement to solve my problem???

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

    I guess that would be all ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •