SQL & PL/SQL :: Select First Unlock Record In Table For Update
Jun 6, 2012
I'm looking for a solution to select the first row that is not currently locked in a table and insert a record to another table that reference that first row. this is my scenario:
create table ticket
(
id number(10) not null,,
ticket_type number(1) not null,,
is_sold number(1) not null,
CONSTRAINT ticket_pk PRIMARY KEY (id)
);
cust_id cust_name ticket_id
------------ -------------------- ----------
1 John 10000004
2 Sara 10000005
my goal is finding the first free ticket ( not sold ) in the ticket table and insert buyer information of that ticket in customer_ticket table. at last I will mark that ticket as a sold one in ticket table with update.
Problem is that the first transaction locks the the first row in ticket table and the second transaction running the same query goes to wait untill the first transaction commit or rollback. However when first transaction finish successfully, second transaction select duplicate id from ticket table that was selected by the first transaction!
I tried to solve problem with "skip locked" and "nowait" options with select for update, but they didn't work.
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 want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0
I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)
Basically, the procedure receives a Table Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.
I have difficulties to merge these data. Below is what I tried
CREATE or REPLACE PACKAGE BIZ_xxx_MERGE IS TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER; PROCEDURE MERGE_xxx_ACTIVITE_SP ( MyLP IN xxx_ACTIVITE_Type ); END BIZ_xxx_MERGE;
CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS PROCEDURE MERGE_xxx_ACTIVITE_SP ( MyLP IN xxx_ACTIVITE_Type ) AS BEGIN MERGE INTO MyTbl [code].........
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
i update MFI Loader from 1.8 to 2.0.1 When i open this what i get System.Data.OracleClient requires Oracle client software version 8.1.7 or greater [color ="# FF0000"][/color]
After successfully installing the 12c, how can I connect to pdb to use the scott or hr schemas?By default when I connect as sys or system from sqlplus it is connecting to orcl not to pdborcl.also how can we make scott connection in sql developer.
I want to update record when i press the button "update" but i don't have idea ,if i will create a new forms ,or new block or the update is made in the same form.
Based on ACCOUNT_NUMBER column we have to check the GUID data in test table.
for example ACCOUNT_NUMBER =11 as duplicate it has 2 values then only, we need to check the corresponding GUID if any occurence ("9f680174-cb87-4f71-887a-92" and "9f680174-cb87-4f71-887a-91"), we should select,if not leave it.
the following case is successfully done with mssql databases.
Case:
Table UserGroup Columns id, name, handshake
When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.
What have I done so far:
- created an after update trigger (mutual error) Caused by trying a delete action in the update action, not possible.
- created a view in combination of instead of update trigger.
This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.
Code so far:
create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE) is begin if p_handshake = 'd' then delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId; commit; end if; end;
create or replace view USERGROUP_V as select * from USER_GROUP
create or replace trigger USER_GROUP_T1 instead of update on USERGROUP_V for each row declare PRAGMA AUTONOMOUS_TRANSACTION; begin Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE); end;
I got this error: frm-41051: You can not update this record
While i searched about this error on net,
I did everything..my block property is yes for INSERT_ALLOWED and UPDATE_ALLOWED.
By programatically I didn't set property false for that block.i check each and every item property for that block...its also yes for INSERT_ALLOWED and update_allowed..
I have modified an existing form which is from 4 tables. I altered one of the table to suit my requirement and have compiled the form . It is compiled with out any errors. But when i enter any data it shows an error "frm-40509 --Unable to update record" and does not gets saved.
I am unable to select only record for 07Nov06. if i use between i get records e.g. :07 November ,2006-0941
Select TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME, TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME from SESSION_LOG where SESSION_LOG.SESSION_START_TIME between '06Nov06' AND '07Nov06';
I have a UNION query having 3 parts, 1st gets date, 2nd data and 3rd displays the formatted data count :WHERE clause of 2nd and 3rd queries are same.
Problem is that I an getting different record counts when I select the overall count of records given by the whole UNION query and when I run to count the records given by each query individually.First count. Here I am selecting the overall count of records given by the query :
CREATE TABLE Emp_addrs ( EMP_ID NUMBER(15) NOT NULL, ADDRESS_ID NUMBER(15) NOT NULL, SITE_USE_ID NUMBER(15) NOT NULL, SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL, SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)
Insert Script code :
insert into Emp_addrs values ( '1207' , '1846', '2342','A'); insert into Emp_addrs values ( '1207' , '1846', '2343','I'); insert into Emp_addrs values ( '61618' , '165200', '261449','A');
[Code]...
A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'. Now Site_use_status can have either = 'I' or 'A' value.
For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').
Just to clear my requirements, out of the above records I want the following records:
I have a sql query where I need to select only records with an 18 month gap between max(date) and previous date( no dates between max(date)and 18 month gap date), when I run the below query it should only select supid 130, not 120 (even though 120 does contain an 18 month gap date it also has a date that is less then the 18 month gap( '25-NOV-2012','DD-MON-YYYY'). how would get the query to look back 18 months for the next date and evaluate the month_between.
. example:
create table supply(supID number(8), supply varchar2(20), supdate Date,supamount number(13,2)); insert into supply values(100,'Tapes',to_date('01-AUG-2013','DD-MON-YYYY'),50.00); insert into supply values(100,'TV',to_date('01-APR-2013','DD-MON-YYYY'),250.00); insert into supply values(100,'Discs',to_date('25-DEC-2012','DD-MON-YYYY'),25.00); insert into supply values(120,'Tablets',to_date('25-AUG-2013','DD-MON-YYYY'),15.00);
[Code]....
and p.supid in(select s.supid from supply s where months_between
I don't want the users to be able to update a record where the date reported field has a date.I have used the set_block_property(blk_id, update_ allowed, property_false). But it still prompts me if I do change something and I try to exit the form.
How can I stop the "Do you want to save changes you have made" box from showing?
I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.
Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:
1. select from projects_history with projectID = 1 2. if no records, then go back to projects and get projectID = 1
3. select from task_history with taskID = 1 4. if no records, then go back to tasks with taskID = 1
5. select from client_history with clientID = 1 6. if no records, then go back to client with clientID = 1
where client.taskID = task.taskId and task.projectID = project.projectID.
Is there any way that I can check what are the elements present in a pl sql record type by querying in table?
For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?
i want to update record that is fetched based on join condition on form
1. made a block manually :::: EMPSAL 2. Query DATA SOURCE NAME :::: EMP a, Sal b 3. Where Clause :::: a.empid = b.empid 4. DML DATA Target Type :::: Table 5. DML DATA Target Name :::: EMP a, Sal b 6. All Columns are marked a.empid, a.empname, b.sal, b.date etc
As all of you know, In the Apex, when you create a form with report in the page, you are able to insert and edit data. But when you edit the data, the data will be modified in the same row. In other word, you loose the old data.
What I need to do is: I have revised_num field and production_date field. I want to create a form with report and insert and edit data as is in the apex and insert 0 to the revised num until production date is null. But when production date is not null, then from that point, I want to insert data to another row and modify revised num to 1. and I want the revised num be incremented by 1 each time the user modifies the data after the production date is not null.I don't know where I should start.
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?
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
I am trying to run an Oracle report with a query that has an embeded sql. this sql is returning more than 1 row, and the report is failing.
I need to pick the latest record entered that this sql return.
I tried rownnum and it works but only i can get the rown num I specify, not the latest record. I try to order, but I am getting an error back.
select w.emp_no, (select t.timestamp from tob.work_unit t where t.work_date = to_date('20130312', 'YYYYMMDD') and rownum = 1 order by t.timestamp desc) ,w. spare_type from work.work_unit w where w.work_date = to_date('20130312', 'YYYYMMDD')
I am getting missing right parenthesis at the order by keyword My report is much complex than this, but I am tring to see if I can get the row that I want.
