I am trying to develop a form consisting of a key block and a single data block. The problem is that the driving table is not the table that needs to be updated.
user wants the following layout:student Advisor
ID# name degree major Concentration ID# name
The driving table (TABLE A) will supply the first 5 fields. The advisor ID comes from (TABLE B).
The user needs to update the adviser ID# field associated with the student ID# field. The form is to be tabular listing all students. I've seen some info on using procedures to insert, delete, update, query and lock tables, but i'm just not sure if that's what is needed.
i have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.
how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
I have a table named employee with 10 records. Empid is the primary key and empsal contains salary of the employees. I created backup of this table and name of backup table is empbackup.
Now empsal was updated to empsal*10 in employee table and 10 more records were also inserted in employee table. Now I want to restore the empsal column for 10 employees from empbackup(as initially there were only 10 employees in employee table when empbackup was created) table how can I do that in PL/SQL or Oracle?
I want to update nested table record based on index suppose i want to update 3rd number of hobby for name2 employee
i have written the below query
SCOTT@orcl_11gR2>update Table(select hobby from emp 2 where empno= 2) e 3 set value(e)='new_value' where to_char(value(e)) = (select 4 to_char(tab1_element) 5 from (select rownum rn,
[Code]...
2 rows updated.
but the above query is updating 2 records but my requirement is it should update only third record
How can we update nested table based on index number
ITEMNUM STORELOC lastyear currentyear AM1324 AM1 need sum(quantity) here need sum(quantity) AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
check my query and correct it basically I want to insert/update data from one user to other therefore I write this coding at my form button, when user press button first time its insert data successfully but if user press button again then it should update because data have been inserted in first step.
Actually it is detail table so it can have more then one record against any master. My query fails in updation, it inserts a new record instead of update.
I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..
[URL].......
how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?
assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?
create materialized view my_view refresh fast as select * from t1;
I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values: GLOBETROTTER: agent has visited five or more different locations in the course of his missions ROVER: agent has visited between one and four locations SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are agents table: agent_id, location_id targets table: target_id, location_id missions_agents table: agent_id, mission_id missions_targets: target_id, mission_id missions table: mission_id, location_id
My code is
DECLARE status AGENTS.TRAVEL_STATUS%TYPE; contact AGENTS.CONTACTS%TYPE; CURSOR loc_cur IS SELECT a.AGENT_ID id,
I've to do a update activity for data correction. There are about 1 crore records involved in this. I want to keep a log of the activity at least Sales No. so that I can validate my data correction. Main cursor is involved for this. The problem is if I insert each record for sales number in the loop then process will take about double time. So i decided to create a nested table and insert after each session finishes.
I'm talking about session here is. The table I'm updation is partition table. So I can have many sessions and each session updating different table partition. This way there is no lock, better performance. Server has 35 processors so I can at least have 25 sessiions.
I could have used varray for this. But I want to learn nested table also. Problem here is not to take different way to complete this activity but to learn why I'm facing this issue.
CREATE OR REPLACE TYPE OBJ_TXN_LOG AS OBJECT ( SALES_NO VARCHAR2(24) ,sALES_SEQ NUMBER(4) ) ;
for Inserting records created below cursor, records are inserted and I can see on my screen (form) also.
DECLARE CURSOR InvestIS select * from Tempinvest; BEGIN OPEN Invest; GO_BLOCK('ReturnReport'); last_record; [code]......
2nd step:-
Updating records I wan to update some columns in this table so I created a 2nd cursor to update records but this cursor is not working accordingly. my requirement is: before Update first of all Find inv_co_code & inv_fnd_code when find then update column Redunits
2nd Cursor
DECLARE ddate date; refdate date; co_code number; co_name varchar2(50); fnd_code number; fnd_name varchar2(50); units number; amount number; stat varchar2(1); CURSOR bnr IS
select Ddate,refdate,co_code,co_name,fnd_code,fnd_name,units,amount,stat from ( select inv_date DDate,Vdate RefDate,inv_co_code Co_code,inv_co_name Co_name,inv_fnd_code Fnd_code,inv_fnd_name Fnd_name, inv_nofu Units,inv_amount Amount,Status Stat from ( ------------- Bonus ------------------- select inv_date,vdate,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_nofu,Inv_amount,inv_uprice,'B' Status from investment where code is null and inv_date <= :dd1 ---------------- Redemption -------------- Union All select red_date,null,red_co_code,red_co_name,red_fnd_code,red_fnd_name,red_nofu,red_amount,red_uprice,'R' Status from redemption where red_date <= :dd1 )) where co_code = 13 and fnd_code = 1 order by co_code,fnd_code,ddate ; ------------------------------------------ vddateddate%type; vrefdate refdate%type; vco_codeco_code%type; vco_nameco_name%type; [code].......
I have this requirement in Oracle FORMS ver 6i where I populate some records in a table in a datablock and display them on the screen. A facility needs to be provided to the end users to select the records randomly by just entering the count of the records in a non-d/b text field. The number entered in the count field will decide how many records are to be selected randomly.
I tried using the query -
UPDATE <table a> SET <col 1> = <value 1> WHERE rowid IN (SELECT rowid FROM (SELECT * FROM <table a> ORDER BY DBMS_RANDOM.VALUE) WHERE <col 2> = <value 2>) /
which I found on the link [URL] but while compiling, the Form gives a compilation error for the ORDER by clause - i'm sure there's no syntax error in the way I have written it...
1.Insert all data from global table to base table. 2.Update all data (that means retrieved all data from base table to global table and update this data). Question: How to Insert and Update from Global temporary table ??
I would like to UPDATE the columns p1 and p2 of my table student (studentid:pk,name,p1,p2,...) for a given studentid.and I have a when-button-pressed trigger with this
UPDATE student SET student.p1=:validation.proj1, student.p2=:validation.proj2 where UPPER(student.studentid)=UPPER(:validation.studentid);commit_form;
when I run my form with a correct studentid, I got this error: FRM-40508: ORACLE error: UNABLE to INSERT record
but it is cworking correctly in sqlplus; and I have all priveligies.
I have two table and trying to update tableA with data from tableB but gives an error. My TableA has columns - colA,colB,colC,colX and table B has columns - colA,colB,colX. Sample data looks like this:
this is the query I wrote update tableA a set (a.colX)=(select (b.colX) from tableB b where a.colA=b.colA and a.colB=b.colB);
When i run it gives this error:single row subquery returns more than one row.
tableB has only 1 value for colX for each colA,colB record but tableA has mutiple colA and colB repeated but for all of thsoe in table A i want to update the tableB.colX value for matching colA and colB.
I am using oracle 8i (8.1.7) with forms 5 and reports 3 on a windows 2000 platform. Now I want to migrate from 8i to 10 g. Will the applications in form 5 and reports 3 work or should I have to upgrade the forms and reports as well.
I have a text file called ReturnedFile.txt. This is a comma separated text file that contains records for two fields.... Envelope and Date Returned.
At the same time, I have a table in Oracle called Manifest. This table contains the following fields:
Envelope
DateSentOut DateReturned
I need to write something that imports the ReturnedFile.txt into a temporary Oracle table named UploadTemp, and then compares the data in the Envelope field from UploadTemp with the Envelope field in Manifest. If it's a match, then the DateReturned field in Manifest needs updated with the DateReturned field in UploadTemp.
I've done this with SQL Server no problem, but I've been trying for two days to make this work with Oracle and I can't figure it out. I've been trying to use SQL*Loader, but I can't even get it to run properly on my machine.
I did create a Control file, saved as RetFile.ctl. Below is the contents of the CTL file:
LOAD DATA INFILE 'C:OracleTestReturnedFile.txt'
APPEND INTO TABLE UploadTemp FIELDS TERMINATED BY "'" ( ENVELOPE, DATERETURNED )
If I could get SQL*Loader running, below is the code I came up with to import the text file and then to do the compare to the Manifest table and update as appropriate:
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end BEFORE INSERT OR UPDATE OF issued ON shares_amount FOR EACH ROW DECLARE BEGIN INSERT INTO shares_amount(date_end) VALUES(SYSDATE); END set_date_end; /
I have the search screen in my form so after searching if i select the row by using the button it will navigate the first tab page that is "gas" screen here if i tried to change the value like update and save the form it is not allowing me to update the value raising the error message "oracle unable to insert the record". if i see the "display error" in menu it is having the select statement with error "unique key violation error ora-00001".
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
when i press when button pressed trigger, i want first the form will delete all the previous data and then populate the data from the table, that's why i used clear_block first, but this clear_code is not working here. my coding is given below
go_block('show'); clear_block(NO_VALIDATE); declare cursor c1 is select * from qtr_demand order by 1; begin [code]..........
Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.
But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.
why it's failing to pull records if i try to query data in first case only.
