[Tip] Block based on VIEW

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
Post Reply
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:

A very useful thing in Forms is to be able to base the blocks in views. Let's go to a brief example:
  • Let's say a table contains the items of a request. Obviously, only the product code will be in this table and not the product description. How to Show the description? [/ List]

    Select all

    Estrutura das tabelas:
     
    ITENS_PEDIDO
     
      IT_COD    NUMBER(5),
     
      PROD_COD  NUMBER(5),
     
      QUANT     NUMBER(10,2)
     
    
     
    PRODUTO
     
      PROD_COD  NUMBER(5),
     
      PROD_DSC  VARCHAR2(60)  
    Little efficient \r\n

    We can use Trigger Post_Query, which is triggered on each block line. Within this trigger, there is a query:

    Select all

    select prod_dsc
     
    into :BLOCO_ITENS_PED.PROD_DSC
     
    from produto
     
    where prod_cod=:BLOCO_ITENS_PED.PROD_COD;
    This SELECT will run on each block line, seeking the description of the product. We have to leave the property of item database item no.

    This is very efficient! Imagine if the block is to get 500 lines? The customer will make 500 description queries !!! Result: Bank performance drop, useless traffic on the net, etc.

    Very efficient \r\n

    Let's base our block in a view! How do we do it?

    1. We create a view_itens_pedido with the product description.

    Select all

    create or replace view VIEW_ITENS_PEDIDO as
     
    select
     
      a.*,
     
      b.prod_dsc
     
    from 
     
      itens_pedido A,
     
      produto      B
     
    where a.prod_cod=b.prod_cod
    2. We created the block based on this view.
    Now let's make this view "alterable"
    3. In the block property DML Data Target Name , we placed the name of the table where the change will be made. (In case, items itself_pedido)
    4. Let's set now what the key field field will be. We click on item it_cod, and we go to the primary key property. We put yes in it.
    5. Now let's tell the forms that the PROD_DSC field is not part of the table! We change the Query Only property for YES!

    and ready !!!
    Forms now knows that the PROD_DSC field should not be recorded when the table is modified. He knows that this field does not belong directly to items_pedido!

    This way you can also that the user use the description to filter things in Forms. (COM F7 and F8).
sandkiller
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Mon, 12 May 2008 1:44 pm
Location: Poços de Caldas

This works fine just that I can not insert data into the table. I can only change data that already exist.
When I carry a new line to insert a new record, do not ...
can anyone give help?


Thanks, guys.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Did you confirm if the allowed insert of the block ta yes?
Strange ... I'vê used it several times and I never came across this problem.
rodfbar
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 241
Joined: Tue, 09 Oct 2007 11:15 am
Location: Batatais - SP

Anything also send the message he returns when you try to insert a record
sandkiller
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Mon, 12 May 2008 1:44 pm
Location: Poços de Caldas

Insert Allowed is YES and I can change the data in a good ...

But there is no error message because when I press a blank field, the cursor blinks but when I write something disappears soon and It is in white again ...

:(
sandkiller
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Mon, 12 May 2008 1:44 pm
Location: Poços de Caldas

And I already discovered that if you do not do the Data Block I can add data but after running Query I can not.
User avatar
rog3rk
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 37
Joined: Fri, 18 Jul 2008 8:06 am
Location: Sapiranga RS

One doubt ...

I created the block with a view. More whenever you try to enter something in the fields of this block the following message appears:

FRM-40602: You can not insert or update data in the view.

I know you can not insert or modify these fields, but you wanted to create a way for this message to not appear.

Does anyone know how to do?
User avatar
rog3rk
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 37
Joined: Fri, 18 Jul 2008 8:06 am
Location: Sapiranga RS

I found a way to do what I wanted. I created an on-error trigger at the VIEW block level I did not put anything like code (only one null;). I do not know if it's the most correct way to do this, but it works .. hehe
RodrigoValentim
Moderador
Moderador
Posts: 367
Joined: Tue, 25 Mar 2008 3:41 pm
Location: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Well, I advise the use of views only for consultation cases like Thomas suggested, the reason is that to work with views it is necessary a certain care in the development of Forms. One of them is, if the view has a group function, killed the possibility of any data change .. the reason is that it will not know which record is being changed, deleted ... yesterday I had an example of this. A screen that was query and it was necessary to insert a field that will undergo constant changes ... When trying to take advantage of the screen, it was not possible and it will be necessary to make a new screen almost from zero.

I, in particular, prefer to use the post_query and always restricting the fetch of 20 lines.
RodrigoValentim
Moderador
Moderador
Posts: 367
Joined: Tue, 25 Mar 2008 3:41 pm
Location: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Just a correction ... as I respond to many posts, I messed up.

This post was not what Thomas suggested, but what I advise to do in the use of views in Forms.

Disconnect there! :)
paulaholti
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 43
Joined: Fri, 29 Jan 2010 4:02 pm
Location: sp
iniciante oracle pl/sql forms reports

Sorry to climb the topic, but I have doubts in relation to View.

- I wonder if this saw establish a lock in the table where it is set to be altered?.

- In the case of INSERT, do you have some way to populate item PROD_DSC automatically taking advantage of the view or do I have to do a select in When Validate Item?

Is there any sense to create a view that uses one table?

- Always and always better use referenced forms in views?

Thanks !!!!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

I would like to know if this saw establish a lock in the table where it is set to be changed?
Yes, because in reality the view is only used by the forms to seek the data. After you have already populated the block, it uses the "DML Data Target Name" property and applies everything normally (locks, etc.) in this table.
In the case of INSERT, do you have some way to populate item prod_dsc automatically taking advantage of the view or do I have to do a select in when validate item?

Well, the purpose of this is to bring information from other tables without having to be doing post_query and seeking line per line ... to insert descriptions, if it is in the same table, no problem. With this you will not be able to insert DSC into another table. (nor does it make sense), then you use another registration of this other table.
Is there any sense to create a view that uses one table?
There is Yes, if your goal is to restrict tables information. Example: You have a table with employees and one of the fields is the salary. Then you do not want to release this field for anyone to see. So you create a view without the salary, and gives grant to anyone who wants from this view.
Always and is always better to use forms referenced in views?
In my opinion, yes ... first because avoids post-query. Second because you can filter through the Description field. (If it is the Post-Query method, it does not, because the field is not consulting). Third because it is easier to program! Just set the fields there and do not need to make any code. If you have this Feature in Forms, why not use?

: -O
paulaholti
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 43
Joined: Fri, 29 Jan 2010 4:02 pm
Location: sp
iniciante oracle pl/sql forms reports

Well informed, thank you Dr. Gori.

knows, I do not know Oracle Application, but knows that any of it is done with views and all triggers control (Lock, Delete, Insert, Update) is also done "manually". What else I found "complicated" is that it seems that no table has constraints (fk).

Must be well labored to fiddle with Application.

Thanks !!!

* I will use View whenever you can.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Yes, Oracle Applications can not have constraint because it is all configurable by the user. (concept of flex-field).
paulaholti
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 43
Joined: Fri, 29 Jan 2010 4:02 pm
Location: sp
iniciante oracle pl/sql forms reports

Correcting:

know = I learned (RSS) ..

Thank you again for the explanations.

Thanks !!!
User avatar
NightSpy
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 49
Joined: Tue, 09 Sep 2008 4:18 pm
Location: SP

I hope you will add the content of the topic.

When there is a need for your view to insert, delete or change lines of more than one table .. In case a view with 2 or more related tables, we have the possibility to create triggers for view with the Famous (or not so famous) "Instead of"

Example of trigger header in the view to insert in more than one table:

Select all

create or replace 
trigger TG_VWFRTE00_INSERT 
instead of insert on VWFRTE00
Within this trigger you do all the treatment for insertion into amis from a table..power it replaces the "insert" behavior of the view

I hope I have helped.
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Galera, returning the topic here, I have a question

I made a form based on View, and it's working right, insert, update, delete and such ..

99] but in the documentation here of the project they say to bring the row_id that would be the identifier of the base table of the view, until I did my view bringing the row_id, but I removed this field from the forms, for not knowing what to do with it, But even then it is working, someone knows if there really is the need of row_id and how should I treat you in forms, what do I have to do with it?

Select all

 
CREATE OR REPLACE VIEW XXAR_EVENTO_ANALITICA_V AS 
SELECT xea.rowid xea_rowid 
      , xea.evento_analitica_id 
      , xea.evento... 
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot], Majestic-12 [Bot] and 18 guests