how could you do dml operations on a complex view !!!
Using INSTEAD OF database triggers.
Of course, updateable join view can work without INSTEAD OF triggers.
I would use Instead-Of- Triggers on the view. Then you can use the default behaviour in your module and you can do update/delete on the view outside forms, e.g. when you need to change data by scripts.
I'm also with the INSTEAD-OF-trigger. You might need to add a ON-LOCK-trigger and do manual locking.
This sounds like a perfect use of a From Clause Query based block. This would enable you to programatically change the data source based on which IN, OUT, ALL parameter is selected.
You could use a Key-ListVal trigger to display your LOV and then act on the value selected in the LOV to set the QUERY_DATA_SOURCE_NAME property of your block. You would then use the On-Insert, On-Update and On-Delete triggers to handle the DML.
Another option would be to create a database package and then use a Procedure based data block. Your database package will have you insert, update and delete procedures as well as the locking procedure. THere are numerous examples on the internet of Procedure Based blocks.
We are getting this error when we are including the following code in KEY-listval trigger
Error is - Cannot set the blocks query data source
Action being performed - selecting one value among IN, OUT and ALL
Block 'BLK_ERRORS' properties are
database data block Yes
Enforce Primary Key No
Query Allowed Yes
Query Data Source Type From clause query
Query data source name "View"
Query Data Source Columns Null
Insert allowed, Update allowed Yes
Key Mode Automatic
Code in KEY-listval trigger
l_show_lov := show_lov('LOV_FEED'); --This LOV contains values called IN, OUT AND ALL --
--This LOC will populate the :block.text_feed
Please let us know how to fox this or any URL pointers which could be helpful. Found many links but they were not giving desired results
1 person found this helpful
One of the keys to using a From Clause Query based data block is that you must have a corresponding Block.ITEM defined for each column returned by your Data Source query. This can be a little tricky if query modifications in your code changes the number of columns returned. If your code does change the number of columns, then you will want ensure your query always returns the same number of columns, but if certain columns are not needed, just alias them as NULL (EG: SELECT NULL AS your_column_name).
Another key to remember with From Clause Query based blocks is to remember that the query is parsed by the Database and not in your Form. Therefore, you can't reference Form Items directly in the query, but rather you have to reference the "Value" of the Form Item. You must also include the Opening and Closing parenthesis in the SQL String. With this in mind, your query:
Will need to be rewritten as:
lv_seql := ' ( SELECT columns FROM view WHERE '||:BLOCK.text_feed ||' ) ';
This change assumes, of course, that your :BLOCK.TEXT_FEED column includes propertly formated WHERE clause. Also, notice that I added Opening and Closing parenthesis to the SQL String. Remember, this is a FROM CLAUSE Query so the underlying query that is executed on the database looks like this:
SELECT <List of Columns> FROM <( Your Sql Statement Here )>
Forms does not include the "(" ")" by default so your code has to ensure it includes the opening and closing parenthesis.
Hope this helps.
We are able to retrieve the data based on the selection of IN,OUT or ALL after following the above steps.
In our case, there are 10 columns which will be displayed in the form. If user wants to select a specific row, he will query it with a value in a column and ctrl+F11 should give the corresponding row. How can we achieve this?.
...In our case, there are 10 columns which will be displayed in the form. If user wants to select a specific row, he will query it with a value in a column and ctrl+F11 should give the corresponding row. How can we achieve this?...
To achieve this, you have to over-ride the default Pre-Query or Execute-Query triggers so you can include the user's "Query Value" in your From Clause Query (FCQ) sql statement. Since you reference using the CTRL+F11 to execute the query, I will assume that your user also uses the F11 key to put the form into Query Mode. Based on this assumption, I recommend using the Pre-Query trigger to modify your FCQ SQL statement. Your code woud look something like the following. Keep in mind that you have to account for all 10 rows the user can potentially query from in your code.
/* Sample Pre-Query trigger */ /* Code is not tested. This code also assumes that all queried items are */ /* character fields. */ DECLARE v_fcq_sql VARCHAR2(2000); BEGIN v_fcq_sql := < Your Main Query here...> -- Now, lets check to see which columns the user has entered a search criteria in... IF ( :YOUR_BLOCK.ITEM1 IS NOT NULL ) THEN v_fcq_sql := v_fcq_sql || ' AND column1 = '' ' || :YOUR_BLOCK.ITEM1 ||' '' '; END IF; IF ( :YOUR_BLOCK.ITEM2 IS NOT NULL ) THEN v_fcq_sql := v_fcq_sql || ' AND column2 = '' ' || :YOUR_BLOCK.ITEM2 ||' '' '; END IF; -- Do this same construct for Items 3 - 10 IF ( :YOUR_BLOCK.ITEM10 IS NOT NULL ) THEN v_fcq_sql := v_fcq_sql || ' AND column10 = '' ' || :YOUR_BLOCK.ITEM10 ||' '' '; END IF; -- Lasly, add the closing Paranethisis to the SQL Statement... v_fcq_sql := v_fcq_sql || ')'; -- Finally, Assign the FCQ Sql to the Query Data Source propert of your Block. Set_Block_Property('YOUR_BLOCK',QUERY_DATE_SOURCE_NAME, v_fcq_sql); END;
As I mentioned above in the code sample, this is untested - it simply demonstrates the how to use Default Query functionality on a From Clause Query block.
1. We have used WHEN-LIST-CHANGED trigger to populate details based on the selection of LIST ITEM 'X' (IN,OUT,ALL) and is working fine.
when we used KEY-LISTVAL trigger ,it was not working properly. So we have used WHEN-LIST-CHANGED trigger.
2. Regarding PRE-QUERY trigger, we have included the code mentioned by you in Pre-query trigger at block level and we are able to query the records based on value in any column. But in this case, WHEN-LIST-CHANGED trigger is not getting fired. i.e., for any value X selected, it is displaying ALL records only.
How can we fix this?
I was not aware you were using the When-List-Changed trigger to populate your details block. This is why it is important to explain what you are doing when you post a question. Remove the code from your Pre-Query and just put everything in your When-List-Changed trigger. After modifying the Query Data Source property simply call the Execute_Query(); built-in.
When we put every thing in When-List-Changed trigger, during query mode we observed that when-list-changed trigger is not firing and hence the functionality is not working.
Please let us know if we missed anything.
OK. Take a look at the properties of your When-List-Changed trigger, specifically the Fire in Enter-Query Mode property. If this is "Yes" then the trigger should fire. If it is "No" then it won't.