Link to home
Start Free TrialLog in
Avatar of NiceMan331
NiceMan331

asked on

How you can edit records of select statement

I have a question ,
why we cannot  edit any record of a selected query , either in Toad or in oracle form ?
and any way to to direct edit through oracle form , or should be use "update command only" ?
Avatar of Sean Stuber
Sean Stuber

you need to select the rowid in order to make the grids editable in toad
alternately you can use the edit syntax
simply replace "select * from"  with the word "edit"
it will only work for single table queries though.


for oracle forms, whether it's editable or not is something built into the form.
you would have to talk to the form creator about how and why any particular form is editable or not
If user A  creates a table  X, user B can't see nor modify that table.
If user A (or a DBA)  "grant select on table X to B" or "grant select to public",  user B can see the table and its records, (select * from A.X)
If user A (or a DBA) "grant update to B" or "grant update to public" user B can update it.
insert or delete grants are needed to add  a record or remove it.
In addition a block in ORACLE Forms can be defined as select only, but if the problem also exists in Toad then there is problaby a lack of database priveliges.
Avatar of NiceMan331

ASKER

Flow01, I'm the not the dba of the database , but I'm the one who created the table and selecting from same oracle user , I can directly delete or edit the table in toad when I select the table data from schema browser , but when I select from table where ????? , I cannot edit it from the resulted query ,,,,,             Sdstuba , what would be the syntax ? : is : edit * from mytable where ?????
And regarding the form , the firm data source is select from query , is there any property should be reviewed to make the block editable ?
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok now i can update in Toad
but still in forms not
the property setting of the block is :
insert allowed , update allowed , delete allowed = yes
locking mode = delayed
1)
You did use the forms generator or did you build your block and  the associated triggers your self.
2) What happens : are you able to modify an item (what are the item properties)  and can't you commit the change , do you get a message ?
You did use the forms generator or did you build your block and  the associated triggers your self.
i used block wizard to create the block , then i changed the datasource of the block to make it select from clause , instead of table , cause i want to use seach inside the block
and create a trigger to do like this
set_block_property('alpha_tran',QUERY_DATA_SOURCE_NAME,sql_string);

Open in new window

based on a value of non_database items

2) What happens : are you able to modify an item (what are the item properties)  and can't you commit the change , do you get a message ?
when locking mode = automatic , i benn not able to change , then when i changed it to delayed , i can type in the field , press f10 to save , no error message , but no save done
a)  you can als filter the data by either define a where clause of the block or change that where clause on execution time (set_block_property('DEFAULT_WHERE')  , in later versions there is also a '..WHERE .. ONCE' option

b)
And what is in the select : if you expanded it to a join the select might not be key-preserved any more)
Can you mimic the update in the oracle database ?
update (<your select statement>) set <your_update_column>=<your_update_column>;

c) debug the update and commit processing
add the trigger ON_UPDATE to the block
BEGIN
   MESSAGE('START ON UPDATE',ACKNOWLEDGE);
   UPDATE_RECORD;
   IF FORM_SUCCESS THEN
      MESSAGE('UPDATE SUCCESS',ACKNOWLEDGE);
  ELSE
      MESSAGE('UPDATE NOT SUCCESS',ACKNOWLEDGE);
  END IF;
exception when form_trigger_failure then
   message('UPD fail:' || error_text || message_text,acknowledge);
 when others then
   message('UPD others:' || error_text || message_text || sqlerrm,acknowledge);
END;        
     
add the trigger ON_COMMIT to the block
BEGIN
   MESSAGE('START ON COMMIT',ACKNOWLEDGE);
   COMMIT;
   IF FORM_SUCCESS THEN
      MESSAGE('COMMIT SUCCESS',ACKNOWLEDGE);
  ELSE
      MESSAGE('COMMIT NOT SUCCESS',ACKNOWLEDGE);
  END IF;
exception when form_trigger_failure then
   message('COMMIT fail:' || error_text || message_text,acknowledge);
 when others then
   message('COMMIT others:' || error_text || message_text || sqlerrm,acknowledge);
END;
You mean that the problem may be in setting the block source to a from clause ?
Because sometimes my block for displaying data and it based on summary for some fields selected from one or more table , so i found the only way is to use select from clause
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
debug the update and commit processing
when i tried to change , it sent me this messages after f10
start on commit
no change to save

You are right, block based on FROM clause is "read-only".
so this is the problem
If you want to update and show summary fields from other tables use the base table for the block and add non_database items for the summary fields . Populate the non-database items in the post_query trigger : like
select sum(x) , sum(y) into :non_database_item1,  :non_database_item2
from table_a
where  table_a.fk_to_base_table = :base_table_id
So the conclusion that I can't update a block if it is based on select from clause ?
Yes, the block based on FROM clause cannot be updated.
If you change your block to be based on a view, you may be able to get what you want in the form.  That allows you to make *ALL* items query-able (if you want to) *AND* you have a few options available then to support updates in the form:
1. instead-of trigger in the database on the view
2. on-update trigger in the block
3. default Forms update mechanism, if you change the "DML Data Source" to a table (assuming that the records in the block are each based on a single row from a single table, with some related or calculated columns added).  You may also have to write a custom "on-lock" trigger for this block, if you use this option.
flow01
If you want to update and show summary fields from other tables use the base table for the block and add non_database items for the summary fields . Populate the non-database items in the post_query trigger : like
select sum(x) , sum(y) into :non_database_item1,  :non_database_item2
from table_a
where  table_a.fk_to_base_table = :base_table_id
what if i want the summary in tabular block , such i want to display summary sales per branch , each branch in one row
If you add a non_database item to a block there will be as many items as rows in the block .
An a post_query trigger fires for each row so you can get different summary results on each row.

If you want multiple summary rows with 1 row that can be update you should have made a master-detail from with an updatable master-block and a query-only detailblock (that block can be based on a view or a select )
If you add a non_database item to a block there will be as many items as rows in the block .
good , but if i don't know how many rows the query will result ? for example
select branch,sum(sales) from mytable
where branch_group = mycreteria
here the result will be vary from query to query
That sounds like  a control block with a non_database_ item :control.branch_group
and a  master_block showing x rows based on the table "braches" and  filtered by defining a where clause in the block definition "where brach_group = :control.brach_group
the master_block has 1 non_database item sum_sales that is populated in the post-query trigger
begin
select sum(sales) into :sum_sales where branch = :brach;
end;

(you can do the same without the control block by making the block queryable and enter the name of the branch_group in enter-query mode.)
sorry , i confused between this sentense :
master_block showing x rows based on the table "braches"
and this
the master_block has 1 non_database item sum_sales

before you post your last one , i thought you advices me to create a non_database_block based on unknown number of rows  then it created row by row depend on the actual number of branches as per the query result

but now , i didn't get the adea


you can do the same without the control block by making the block queryable .

queryable  means what ?
1.  You started a question based on a solution approach.  That did not work and I'm now aiming on a different approach.  And - based on what a gathered from your info - I made some guesses . Since they raise question marks that where probably not the right guesses. Lets try a new start:
what table should be updateble,  on what  criteria should the data be filtered and what additional information would you want to see (summaries etc). If you can , make a visual example.  And if it does not violate your corporates rules share the origal select you had in mind.

2. If you make a block and some items queryable than you can enter search criteria in that items when the block is in query-mode. If you then enter 'B%' in the item 'branch_group'   you will only get braches (stil under the assumption the block is based on the 'branches' table)  that belong branch_groups that start with a B.
You can also define an Lov on that item based on the available branches. Drawback is that once the block is queried you don't see the filter criteria anymore.
ok , i understand
give me a chance to prepare the requested info in step # 1
sorry for the delay
i been joined to the quarterly tasks in our company , we just finished
let me describe the issue
table : alpha_tran is the updateble table , consist basiclly of :
doc_date date
cost_center number
acc_no number
debit number
credit number
and some other details field
look at picture : tran
it is now based on table alpha_tran
i fill creteria which you may find it in the footer of the form , then click search
it wil show the filtered record based on : default_where
and here i can update the fileds now
now look at picture " summary
this tab consist of 4 summary blocks , all of them based on same table : alpha_tran but with different summarized type

let see on the top of its left side , block "TB_SUM_CNTR"  labeled as : shop_summary
this is grouping (debit and credit) based on cost_center
its data source based on this clause from
select cost_center,
            SUM (balance) Balance,
            SUM (sal) Sales,
  sum(all_exp) all_exp
from (
select cost_center,acc_no , (debit - credit)  balance,
(case when acc_no = 15 then credit-debit else 0 end) sal,
(case when acc_no = 15 then 0 else debit-credit end) all_exp,
from alpha_tran
)
group by cost_center

Open in new window

and below of this block you can see non_database items to filter the result
when i filled those items , then "run" command will call this procedure


PROCEDURE upl_tb_by_centr IS
sql_string varchar2(8000);
BEGIN
     
go_block('TB_SUM_CNTR');
clear_block;
sql_string := '(
select cost_center,
            SUM (balance) Balance,
            SUM (sal) Sales,
  sum(all_exp) all_exp
from (
select cost_center,acc_no , (debit - credit)  balance,
(case when acc_no = 15 then credit-debit else 0 end) sal,
(case when acc_no = 15 then 0 else debit-credit end) all_exp,
from alpha_tran
where
and (a.cost_center between ''' ||:BASIC_BLOCK.cntr_frm|| ''' and ''' ||:BASIC_BLOCK.cntr_to|| ''')

)
group by cost_center
) ' ;
     
set_block_property('TB_SUM_CNTR',QUERY_DATA_SOURCE_NAME,sql_string);


execute_query;

exception 
	when form_trigger_failure then
   message('fail:' || error_text || message_text,acknowledge);

 when others then
   message('others:' || error_text || message_text || sqlerrm,acknowledge);
END;

Open in new window


then the run displayes sum amount per branch
now in the middle of the form there is another block labeled as "center_summary"
based on same table but group (debit and credit) by acc_no for one cost_center of the selected record from  previous block using "when_mouse_click" trigger

PROCEDURE UPL_TB_BY_acct IS

sql_string varchar2(8000);
BEGIN
     
go_block('TB_CNTR_ACTS');
clear_block;

sql_string := '(
SELECT cost_center,
            acc_no,
            balance,
  exp_per(acc_no,balance ,SUM (sal) OVER (PARTITION BY cost_center)) of_sales,
       
               exp_per(acc_no,balance ,SUM (all_EXP) OVER (PARTITION BY cost_center)) of_exp
       FROM (
       select cost_center,acc_no,
            SUM (balance) Balance,
            SUM (sal) Sal,
  sum(all_exp) all_exp
from (
select cost_center,acc_no , (debit - credit)  balance,
(case when acc_no = 15 then credit-debit else 0 end) sal,
(case when acc_no = 15 then 0 else debit-credit end) all_exp,
from alpha_tran
where
cost_center = ''' ||:TB_SUM_CNTR.COST_CENTER||  '''  group by acc_no

)) ' ;

set_block_property('TB_CNTR_ACTS',QUERY_DATA_SOURCE_NAME,sql_string);

execute_query;

exception 
	when form_trigger_failure then
   message('fail:' || error_text || message_text,acknowledge);
 when others then
   message('others:' || error_text || message_text || sqlerrm,acknowledge);

END;

Open in new window


and then when i clicked on any record ( = any acc_no ) , it will also filtered the 3rd bklock based on other specific details
sorry for long details , but hope it is usefull
Tran.jpg
Summary.png
If you create views using the queries in your procedures, you can then change your summary blocks to be base-table blocks.  That way, the queries in those blocks use default Forms functionality, so the number of records will be handled automatically and you don't need to write procedures or try to use non-database fields.  Those options make your Form *MUCH* more complex than necessary, and *MUCH* more difficult to develop.
in this long discussion
https://www.experts-exchange.com/questions/28712691/Procedure-to-create-view.html
we arrive to a conclusion that the best way is to make the form based on from clause
, cause the view can't accept parameter
It is true that Oracle views do not accept parameters, but if the views include the column that you need to pass parameters for in your query, then you can use views and make your forms *MUCH* simpler.
If you remember your solution in similar problem here.                           :                     https://www.experts-exchange.com/questions/28712691/Procedure-to-create-view.html.        You suggest GTT table
Sorry , this is the link.                                    https://www.experts-exchange.com/questions/28689764/Oracle-Forms-Dynamic-Data-Source.html
That is another option that can work for problems that a view cannot handle.  I would try a view first, then if that doesn't work or is not possible for some reason, try a GTT (plus a procedure to populate it).  Either of those options can make a form or report *MUCH* easier to build, and possibly perform much faster.
The view exactly fail to have it here , I have only 2 options : GTT , or the form clause , , let see the opinion of flow also as he is the one who been following this topic
You said "and here i can update the fileds now"  , so that part is solved.
you describe the summary queries but you did not tell (or i missed)
if the form compiles (if not what message),
runs (if not what message)
and returns the right results. (if not what does it return)
then you having a working solution now?

For future work or less coding:

Looking at your story as i see it you could define the
create or replace view TB_SUM_CNTR_VIEW
select cost_center,
            SUM (balance) Balance,
            SUM (sal) Sales,
  sum(all_exp) all_exp
from (
select cost_center,acc_no , (debit - credit)  balance,
(case when acc_no = 15 then credit-debit else 0 end) sal,
(case when acc_no = 15 then 0 else debit-credit end) all_exp,
from alpha_tran
)
group by cost_center

and
DEFINE THE  TB_SUM_CNTR BLOCK BASED ON THE VIEW TB_SUM_CNTR_VIEW
WITH A WHERE
cost_center between :BASIC_BLOCK.cntr_frm AND :BASIC_BLOCK.cntr_to
and get the same results

PROCEDURE UPL_TB_BY_acct IS -- needs less code
BEGIN
go_block('TB_CNTR_ACTS');
-- clear_block;
execute_query;
exception
      when form_trigger_failure then
   message('fail:' || error_text || message_text,acknowledge);
 when others then
   message('others:' || error_text || message_text || sqlerrm,acknowledge);
END;
 
the TB_SUM_CNTR block is not a master detail block because it does not depend on a row  item of the block with alpha_tran

For TB_CNTR_ACTS you can use the same view method an it is a master-detailblock with
TB_SUM_CNTR as master. If you would use the forms wizard to create that relation you would not need a where clause because in that case code  will be generated that creates the relationship (based on COST_CENTER item in both views) . By memory that works by making the COST_CENTER of the TB_CNTR_ACTS a querable item and in a pre-query trigger assign the value of TB_SUM_CNTR.cost_center to that item:  the results are then filtered by that value.
Procedures you now have made to synchronize TB_CNTR_ACTS and TB_SUM_CNTR would have been made by oracle forms builder.
you describe the summary queries but you did not tell (or i missed)
if the form compiles (if not what message),
runs (if not what message)
and returns the right results. (if not what does it return)
then you having a working solution now?
yes , it is working with me perfectly and giving correct data
actually my basic question answered already , but why i continue asking  ? because you & henka advised me to base the block on a table directly , not on from clause
, i mentioned that some times i have to display summary data in the block  , i wrote 2 topics before in the above links , we arrived to the fact that using the GTT is one option , and the other is to use "from clause" as a base of the block and i really needed your opinion about what is the best perfect solution when i have to use criteria in such case
now look please about what you told me lastly to create this view
create or replace view TB_SUM_CNTR_VIEW
select cost_center,
            SUM (balance) Balance,
            SUM (sal) Sales,
  sum(all_exp) all_exp
from (
select cost_center,acc_no , (debit - credit)  balance,
(case when acc_no = 15 then credit-debit else 0 end) sal,
(case when acc_no = 15 then 0 else debit-credit end) all_exp,
from alpha_tran
)
group by cost_center

Open in new window

in this case ok , but what is i need to add before the last statement in that sql :
from alpha_tran where tran_date between :parameter1 and :parameter2

Open in new window

which would be 2 fields non_database items of the block to query it
here the date is not a part of the view to query it , it is just a part of the table alpha_tran which your advised view will be based on it , how to solve this issue , i mean to query the view based on selected range of date which is not part of it ?
As I said earlier in this question, Oracle views do not accept parameters.  Often, the way around that limitation is to include the column in the view that your parameters need to match.  But if you don't want to include the tran_date column in the view (since then you would have a separate record in the view for each date) your best option in this case is to use a different technique, like a GTT that you populate via a procedure, then base your form block on the GTT.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok , if I successes to create the package with parameters or the pipelined function , what will the data source of the block then ?
With the package solution you can use a view and set the arguments in the pre_query trigger.
With a pipelined function you have to put the arguments in the 'from clause query' .
select ... from table(tablefunction(:arg1, ....)),
but for the easy where you mentioned ,
just add the where the   'from clause query' .
Sorry , I didn't understand  the last part of your last comment
The words started at :     But for the easy about you mentioned .....etc
I would use the package option .
But in considering to use a pipeline function in the from clause or an written sql : The where-clause you wanted to use is easy.  It is so easy that building a table-function is  unnessary extra work.   Then just use the  'from clause query'  just like you did.  
I mentioned the function as another option to pass arguments to sql.
i built this pipeline function

CREATE OR REPLACE function EDSS.str_type_func(p_where_clause in varchar2) return str_type_tab pipelined
is
    v_rec str_type := str_type(null,null,null,null,null);
    v_cur sys_refcursor;

begin
 

open v_cur for 
' select STR_NO , sum(gross) gross, sum(net) net, sum(discount) disc, count(dob) d_count from (
select to_number(a.STR_NO) str_no,b.dob ,nvl(sum(b.PRICE),0) Gross,nvl(sum(b.DISCPRIC),0) Net,(nvl(sum(b.PRICE),0) - nvl(sum(b.DISCPRIC),0)) Discount 
from 
cr_store a
left join cr_gnditem b on a.str_no = B.UNIT
'|| p_where_clause ||'
group by a.str_no,b.dob
order by sum(b.PRICE) desc 
)
group by str_no ' ;

         
  
    loop
        fetch v_cur into v_rec.str_no, v_rec.gross, v_rec.net,v_rec.disc,v_rec.d_count;
        exit when v_cur%NOTFOUND;
        pipe row (v_rec);
        end loop;

    close v_cur;
end;
/

Open in new window


now :
in database , i run it like this
select * from table(str_type_func(' where dob = ''30-sep-15'''));

Open in new window

and success ,
2- in the form , i make from clause same sql
select * from table(str_type_func(' where dob = ''30-sep-15'''))
then when query the block , the mouse cursor works but no records displayed
shift f1 , no error
i'm working with form 6i , may be not supported ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
so , i have to try the other option : the packages