- 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] Little efficient \r\n
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)
We can use Trigger Post_Query, which is triggered on each block line. Within this trigger, there is a query: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.select prod_dsc into :BLOCO_ITENS_PED.PROD_DSC from produto where prod_cod=:BLOCO_ITENS_PED.PROD_COD;
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.2. We created the block based on this view.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
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).
[Tip] Block based on VIEW
A very useful thing in Forms is to be able to base the blocks in views. Let's go to a brief example:
-
- 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.
When I carry a new line to insert a new record, do not ...
can anyone give help?
Thanks, guys.
- dr_gori
- 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
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.
Strange ... I'vê used it several times and I never came across this problem.
-
- 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 ...
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 ...
-
- 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.
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?
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?
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
-
- 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!!!
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.
I, in particular, prefer to use the post_query and always restricting the fetch of 20 lines.
-
- 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!!!
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!
This post was not what Thomas suggested, but what I advise to do in the use of views in Forms.
Disconnect there!
-
- 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 !!!!
- 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 !!!!
- dr_gori
- 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
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
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.I would like to know if this saw establish a lock in the table where it is set to be changed?
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.
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.Is there any sense to create a view that uses one table?
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?Always and is always better to use forms referenced in views?
: -O
-
- 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.
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.
- dr_gori
- 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
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).
-
- 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 !!!
know = I learned (RSS) ..
Thank you again for the explanations.
Thanks !!!
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:
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.
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:
create or replace
trigger TG_VWFRTE00_INSERT
instead of insert on VWFRTE00
I hope I have helped.
- Porva
- 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
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?
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?
CREATE OR REPLACE VIEW XXAR_EVENTO_ANALITICA_V AS
SELECT xea.rowid xea_rowid
, xea.evento_analitica_id
, xea.evento...
-
- Information
-
Who is online
Users browsing this forum: No registered users and 10 guests