i made this loop to commit the same item but depend on the count of suppliers to set the same item every time to different supplier . but the problem this code commit only the first sypplier but not any othere one ..
code : { key - commit - trigger }
declare
var_record_count number ;
VAR_p_request number;
begin
var_record_count:= get_block_property('control',CURRENT_RECORD);
[code]....
I have a master detail form based on two tables and i want to save the record automatically when the user exits the last field.how i can do it.which trigger is appropriate and what is the code.
View 8 Replies View RelatedI'm getting some problem in my project. I've taken my form fields in tabular way. I also have the ID with auto-increment system there. I want that when user moves towards the 2nd row for inserting new record then the 1st record would be committed.
I'm getting my bank-id from database and making +1 increment and made that bank-id text box into display box.
When running my form then it is increasing one like this :
database record
BKID-00001
and with one increment it is showing this in my form display field of 1st row in tabular form.
BKID-00002
but when I moves towards the 2nd row it doesn't commits the form and gives the BANK-ID AS "BKID-00002" again like the above row.
I have a form reading record information from a flat file and inserting data into a number of varied tables. At the end of file (last line read) the form issues a commit to make all inserts/updates permanent.
There is a record that disappears in particular and this everytime I re-run the test. I mean than in Debug mode, I find it in the right table everytime I query that table until the moment the commit is issued in the code - which is definitely the opposite of what should happen. I understand that for some reason a rollback happens for that record (others are ok after the commit) but my point is that if for some constraint reasons, the record did not qualify, an error should have popped up right from the Insert that added that record, right? Then How comes I find it in the table up until the moment of the commit ?? ...is the deferrable constraint property a clue for digging further?
i m using oracle 10g 10.2.0.2 version.i create a form and using check box on this form.when i click this check box then loop is using behind it.and current cursor is going to last record
i want if i click 4 record then cursor is still showing on 4 record mean i click which record after using loopmy current cursor is showing on that particular record
how it is possible
I'm running 11.2.0I am looking at tuning a sql statement, and the question was brought up as to the max inserts per transactions in 11g, and if it exceeds 1000.I haven't found a solid answer yet, but I thought that 10g was higher than 1000.
My first thought was to implement a commit loop on every 1000 rows, as that is how things were handled in the past.But I found an article that talks about redo logs and performance and how it's a horrible practice to do the commit loop.
What I haven't found is what is the better methodology in doing this?My scenario could encounter inserts as much as 20,000 at a time.
My oracle version is oracle 9i
I need to commit after every 2000 records.Currently am using the below statement without using the loop.how to do this?
do i need to use rownum?
BEGIN
UPDATE
(SELECT A.SKU,M.TO_SKU,A.TO_STORE FROM
RT_TEMP_IN_CARTON A,
CD_SKU_CONV M
WHERE
[Code].....
I have a cursor which I am opening and then looping through. Within this loop I am comparing attributes within this cursor with attributes from another loop that this one is within.
you will see in the IF statements (there are several distinct IF statements within the loop) that there is a check which assesses if the attributes are not equal.
If they are NOT, the value of v_mismatch is set to 1
What I need to do instead of setting this to 1, is to go to the next record in the loop. How can I achieve this?
OPEN c_distMatrix;
LOOP
FETCH c_distMatrix INTO r_dist;
EXIT
WHEN c_distMatrix%NOTFOUND;
--compare each field and update the counter
[code].....
in my loop with cursor in a procedure body i am displaying some field values to create a report and after every record i am displaying horizontal line(-------) but i don t want this line to appear after the last record displayed like below,
gfsfsf gsgfsfds gsdgfdg
------------------------------------
edyet gdgtdgt gtdfdfdgd
------------------------------------
dfds hedhgg idudhdh
how can i achieve this within pls/sql procedure body.
I have created a sql loader control file to load a delimeter file in a table.
The file gets loaded into the table properly, however the commit is happening for every 64 records.
My aim is to rise the commit count by 1000 records.
I am using a record type to print some column in a same line.
Eg: I want to create index on some composite key columns. But i dont know how many columns are there. So want to use a loop which will count the number of column and then create the index like:
CREATE INDEX PRODUCT.XIF1AGMNT_PROD ON PRODUCT.AGMNT_PROD(LOAN_ID,LOAN_PROD_STRT_DT) TABLESPACE PRODUCT_INDEX;
I have a table of 3 columns:
SQL> show user
USER is "ANDREY"
SQL>
SQL>
SQL>
SQL> --create the table:
[code]...
I insert rows into it:
SQL> --fill it with data:
SQL>
SQL> insert into a(key1 , key2) values (1 , 1);
1 row created.
SQL> insert into a(key1 , key2) values (1 , 5);
[code]...
i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.
i've found out that i could do it by an SQL statement:
update a
set key3 = 'inactive'
where key2 = (
select max(key2)
from a a2 where a2.key1=a.key1
);
however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:
SQL> create or replace procedure proc1
2 IS
3
4
5 var1 a.key1%type;
[code]...
unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:
SQL> exec proc1;
PL/SQL procedure successfully completed.
SQL> select * from a;
KEY1 KEY2 KEY3
---------- ---------- ----------
1 1 active
1 5 incative
2 24 active
2 21 active
ORA-01034: ORACLE not available
How to insert null record (for some columns) in table using loop.
sample data of x_tab
order_id order_name
231 xxx
123
345
111 vvvv
I am calling a child form from a parent form.It works perfectly if the parent form is adding records and while entering records when i press the button to call the child form, the whole things work perfectly according to plan.
The problem begins when i run execute query command in the parent form and then call child form then it does not "commit_form". So this is my problem that child form does not work perfectly when parent form is being called in execute_query procedure.
My working:
1) I read in the documentation that When parent form status is query_only then child will also have the same mode regardless of the parameter given in call_form.So i checked the :SYSTEM.FORM_STATUS of both the parents and child form,it shows "CHANGED" hence this point is covered. (dont know how come the parent form is in changed status but at least it is doing my work)
2) I further read and found that Commit_form procedure make the :SYSTEM.FORM_STATUS as QUERY. Here i am facing problem as in child form when i make changes and press commit form. Then before commit_form and after commit_form the :SYSTEM.FORM_STATUS results in "CHANGED".You can see this in the following code which i have written in save button.
message(:SYSTEM.CURRENT_FORM || ' a ' ||:SYSTEM.FORM_STATUS); pause;
commit_form;
message(:SYSTEM.CURRENT_FORM || ' b ' ||:SYSTEM.FORM_STATUS); pause;
IF Form_Success THEN
Commit;
IF :System.Form_Status <> 'QUERY' THEN
Message('Error prevented Commit');
RAISE Form_Trigger_Failure;
END IF;
else
message('FAIL');
END IF;
exit_form;
then at last exit_form module shows that" i have unsaved data in the form" save Yes-No-Cancel?
After committing form i want to clear few item and retain few item and make the form ready for next record to be inserted along with retained item.
View 4 Replies View RelatedI have a problem with key-commit trigger. I have written some validations and computations criteria on the block level (lines level). Actually there are a number of loops involved in it.
Problem is that the computations are performed twice. (may be the validations would also be performed twice, which couldn't be felt ). Since i read somewhere that key-commit is fired on different events, which i infered to be firing only just before database commit.
Is it possible to clear the form after a successful commit? In order to avoid the user from double posting of record by making double commit.
View 17 Replies View Relatedhow we can sum data with out commit command in forms
View 3 Replies View RelatedMy procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
Restrict field after insertion before commit. i mean when user inputs the data in one field and moves to next field.when ever he want to return back on the previous field it can't be edited.before commit;
View 2 Replies View RelatedI am just making a audit table as well. i have learnt the basics from here URL.....
My problem is that after inserting into audit table if i issue commit command then the table + unsaved data present on the form is also saved.What i want is that i issue a command which save only inserted record in audit table, and should NOT save data present on the data entry form. (which will be saved later by other method/button).
I have a four tab canvas. The first 3 tabs belong to one data block. The last tab has two data blocks.
How can I automatically commit changes if the user clicks on any tab and not necessarily in tab order?
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.
View 4 Replies View RelatedHow Can We Stop to insert Data if Master Block have the record and detail block have no record but commit command applied i want that commit command can not be work if detail block have no record
View 7 Replies View RelatedI have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...
View 4 Replies View RelatedI am having template table as
template_name varchar2(100) -- primaykey
ex_col varchar2(100)
tab_colvarchar2(100)
Now on the form 6i i've two columns with following data
Column AColumn2 B
-------------------------------------
route a_route
time a_time
id a_id
and a text field where the name of the template will be entered.
e.g.
text filed - testing
In template table i want to insert above values, so template table will look like this
template_name ex_col tab_col
-----------------------------------------------------
testing route a_route
time a_time
id a_id
since tempate_name is primary key, i how should i construct for loop for insert.
there this is my insert loop but the problem is there is row added more than the normal count ..
DECLARE
var_record_count NUMBER;
var_p_request NUMBER;
[Code]....
here i m giving the code
DECLARE
TYPE SUBNO_TABLE_TYPE IS TABLE OF
TRANS_DEICMAIN1.SUBNO%TYPE
INDEX BY BINARY_INTEGER;
SUBNO_TABLE SUBNO_TABLE_TYPE;
K NUMBER := 1;
S NUMBER := 1;
[code]...
HERE CHECK1 IS A CHECKBOX.WHEN I AM USING THIS CODE ONLY ONE RECORD IS SAVED AT A TIME INSTEAD OF SAVING ALL RECORDS.
CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;
can i use forall instead of 'for loop ..end loop'