Query Record

[anju123]

hi friends,
i have some questions:
1. I have created a screen for capturing client detail information but I am having a problem when two users query for same company detail. It blocks the other. I know the reason why it blocks but i am not getting how to display message (like some one is already querying on it) when one user is already querying on the same record.
Suppose there is a company called ‘XYZ corp.’ and I am querying on it or making some changes and at the same time some other user wants to look at the same record, so how can I inform other user that someone is already looking or updating this record.
2. I want a list of value that starts from first of January to sysdate (current date). Can anyone tell me how can I do this?

Thanks in advance,
Anju

 

[lewisp]

Is this application in Oracle Forms? You could re-write the ON-LOCK trigger to explicitly lock the row(s) you are working on using FOR UPDATE with NOWAIT, then trap the -54 error.

You could build a list from a cursor using ALL_OBJECTS, such as

[tt]SELECT To_Date('01-JAN-' || To_Char(SYSDATE,'YYYY'),'DD-MON-YYYY') + (rownum - 1)
FROM ALL_OBJECTS
WHERE (rownum - 1) <= SYSDATE - To_Date('01-JAN-' || To_Char(SYSDATE,'YYYY'),'DD-MON-YYYY');[/tt]

Make sure theres at least 366 objects in ALL_OBJECTS.

 

[anju123]

Thanks, but this query takes long time to execute. is there any way to make it faster?
I haven't tried your first suggestion regarding on-lock trigger yet but thanks a lot for the solution.

Anju