PL/SQL :: SELECT - How To Wait Until Result Appears

Mar 28, 2013

SELECT * from TABLE_1 where a=b;

Is it possible to make the SELECT (PL/SQL is OK ) wait/block in case there are no rows found until another session inserts rows that match the condition?

Something like this

cursor c is select * from TABLE_1 where a=b order by column_a for update skip locked;
row c%rowtype;
begin
open c;

[Code].....

Or the INSERTing session should send some kind of notification (semaphore?) to the reading session?

View 29 Replies


ADVERTISEMENT

Forms :: Wait Event During Execution Simple Select Statement

Jul 19, 2013

Yesterday i got wait event when executed simple select from table.This select was like:

SELECT emp_number from employer where subs_id = 111

I got one row, select is very fast.In our Core Bank System we have package with function which returns such information. I tested this select on test DB, and nothing wrong. But when I executed such select and package on Production DB, DB Admin saw that 88 sessions waits when my session release the resource. But what can happen, it was simple select? I used PL/SQL developer to get information from table:

1) SELECT emp_number from employer where subs_id = 111 then
2) Package with this function

Another users used Oracle Forms screen to execute package. How simple select statement could stop all DB?

BANNER
1Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
2PL/SQL Release 10.2.0.5.0 - Production
3CORE 10.2.0.5.0 Production
4TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
5NLSRTL Version 10.2.0.5.0 - Production
[code]...

Forgot to say that after succeful execution on Prod DB I disconnected, and in EM my session was INACTIVE.

View 3 Replies View Related

PL/SQL :: Wait Event Starts With SQL*Net Message From Client - Time Wait 178577 Units

Dec 11, 2012

I am trying to look at wait events for a long running query in TOAD.I start the query on one instance of TOAD and open the Session Browser on another instance.But I am surprised to find that in "TOtal Waits" on the RHS-> SQL*Net message from client is the longest time taking and is already -> 178577 units whereas I have just started the query.

Whereas in the Current Waits it shows DB File Scattered Read currectly as some seconds.

View 5 Replies View Related

SQL & PL/SQL :: Obtain Result Set When Doing Select

May 13, 2010

when in sqlplus I execute:

select * from mytable;

in sqlplus I get the returned rows of this table. But this is not the case when I do in plsql the following:

create or replace procedure myproc is

a varchar2(50);
begin
a := 'select * from mytable';
execute immediate a;
end;

there is nothing I can see. Is there a way to catch this result set and see it on the screen? Something that is not creating new structures like cursors, etc?

View 16 Replies View Related

How To Export Database Out Of Select Result

Nov 18, 2008

I got this select that gets me a couple thousand rows, or over a million, and i can't get how to export it into a new database.

View 2 Replies View Related

SQL & PL/SQL :: Check Datatype Of Result From Select Query?

Jul 7, 2011

I have this query

SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484

is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?

View 13 Replies View Related

PL/SQL :: Unexpected Result With Select Max (column) In Anonymous Block

Nov 27, 2012

The following query gives me 28,800 as sum(sal)

SELECT SUM(salary)
FROM employees
WHERE department_id =60
O/P is :  28800But when i use the above query in anonymous block it gives me 684400
DECLARE
v_sum_sal NUMBER;

[code]....

The above output statements gives me 684400 as output.. But the expected is 28800

View 6 Replies View Related

SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause

May 4, 2010

The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.

Before adding the columns 4,5,6,the result set count was 11.

SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....

View 8 Replies View Related

Client Tools :: Different Charactersets Show Line-break In Select Result

Mar 22, 2010

i have a little curiosity in result of a select statement.

One database has following characterset settings:

NLS_CHARACTERSET WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16

and the following select statement results in multiple lines:

SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
BUP
OPEN

Another database has following characterset settings:

NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

and the same select shows only a 2 line result:

SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN

Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?

Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.

View 3 Replies View Related

AWR Report Shows Wait Time Is High But OS Shows Wait Time As Normal

Oct 2, 2012

We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.

Service Time : 15.36%
Wait Time : 84.64%

This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.

As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.

View 17 Replies View Related

SQL & PL/SQL :: Determine Number Of Times A Value Appears

Nov 11, 2012

I am trying to determine the number of times a value appears and display the count. However the value can only be counted once per 'trip' even though it may appear several times per trip.

for e.g.

Quote:trip table
-------
trip_id start_date end_date duration
445 01-jan-12 03-jan-12 3

Quote:pickup table
--------
pickup_id trip_id company
1 445 randomname
2 445 randomname
3 445 google.inc
4 878 randomname

with the above data the expected value would be two because the trip id appears twice so it was just the one trip - given a count of one. I am not sure how create a query to check this.

View 2 Replies View Related

Forms :: Error Appears In Builder 11g And Weblogic Server

Jan 5, 2011

i'm facing a problem when i run form om forms 11g

that error appears :

frm-10142 HTTP listener not running on weblogic on port 80

and when i ran it on weblogic server after compiling it on internet explorer a blank window appear and written DONE

View 1 Replies View Related

Oracle 10g IMP - After Tables Are Loaded Procedure Appears To Hang?

Oct 14, 2011

I have an Oracle IMP that seems to complete, but there are two things that I don't understand:

1) The tables seem to load successfully in about 4 hours. After the tables are loaded, the IMP procedure appears to hang for10+ hours. I can query the tables at this time, so I'm not sure what it's doing. Is it maybe applying constraints?Is it normal for applying constraints to take 3-4X as long as importing the data?

2) I see a few of these messages in the alert log during the import.Thread 1 cannot allocate new log, sequence 2754 I don't see errors in the IMP log so are these messages informational or warnings, or will there be something wrong with my imported data?

View 1 Replies View Related

Forms :: How To Handle Position / When Do You Want To Save Changes / Dialogue Box Appears

Jan 22, 2013

I am working on Oracle apps. I have developed a custom screen. On the screen, if the user press 'F4' on final step, it will ask the dialogue..Do You want to save changes?

Yes No Cancel.

Even the user press the "Yes" , I want to check my own validation and commit when my validation get perfect.

View 1 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

SQL & PL/SQL :: Transfer Data - Arabic Language Column Appears As Junk

Feb 8, 2012

When I transfer the data from Oracle to MS SQL I have one column in a table which is of Arabic Language.The data is fine in Oracle but when I transfer it to MS SQL Server I see that the text in MS SQL server appears as junk.

View 1 Replies View Related

Forms :: How To Hide Oracle In-built Message Which Appears At Bottom Of Window

Mar 18, 2011

How to hide oracle in-built message which appears at the bottom of the window(just above the task-bar) to the left side.

View 2 Replies View Related

Application Express :: Enkitec Navbar Vertical List Appears Momentarily?

Jul 24, 2013

Apex 4.2.0.00.27Firefox 22.0, IE 8 Just started using the Enkitec Navbar, and we have noticed that when a page is rendered a vertical list always appears momentarily before being replaced with the Navbar list. It happens on all applications we have installed it on, and every time a page is refreshed. 

View 13 Replies View Related

Cursor Pin S Wait On X

Apr 17, 2013

In our environment we have db link to fetch data from other database. Whenever we try to fetch data using the dblink we receive the wait event "cursor: pin S wait on X" and we do not get any result. The db link works fine. what could be the issue.

DB Version : 11.1.0.7 OS: AIX

View 1 Replies View Related

Cursor - Pin S Wait On X

Nov 14, 2006

i am using the Oracle 10 g 10.2 with the windows 2003 3 sessions from a single User shows the currenncy on the top activity graph of the enterprice manager they are not getting so much resources of system....when i go in the detail of the session i get

cursor: pin S wait on X

i am unable to kill these sessions how could i get rid of these sessions

View 2 Replies View Related

Forms :: Canvas / Wait Property In 6i?

Nov 28, 2011

I am using Forms 6i in Oracle Applications, In the button click event i used set_canvas_proeprty() twice. First time it executes these two statements, second time it does not. If i use any message() statement before these two statements, it executes.

Do we have wait() statement in Forms ?in apps we have app_item_property.set_property(), can we use same command to set canvas property also.

View 1 Replies View Related

SQL*Net More Data From Client Wait Event?

Oct 8, 2013

DB 11.2.0.2AIX 6 

I am getting following two top wait events from AWR report

1)SQL*Net more data from client
2)log file sync 

Does it hints towards network latency and hardware configuration?what should i do for first wait event?

View 11 Replies View Related

Concurrency Wait On Insert Statement?

Feb 19, 2013

I am running Oracle RAC 2 nodes 11g R2 on AIX 7.1

I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.

All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?

View 3 Replies View Related

Client Tools :: Wait Tab In Toad

Mar 30, 2011

I open the session browser and wait tab . here is the image

what does the wait_time and second_in_wait indicate , some times I saw wait_time more that 15 seconds what does this mean ?

View 1 Replies View Related

PL/SQL Timer Wait Event Analysis

Jun 22, 2013

One of our Job running long than usual time. I checked the wtait event for which its waiting. its PL/SQL Timer wait. But i noticed the total waits is 179 and timed out is also 179 for PL/SQL Timer. I checked that job that particular procedure calls dbms_lock.sleep in it. I want to know why this wait event is getting timed out ?

View 1 Replies View Related

Performance Tuning :: Wait / Sleep Command In SQL?

Mar 15, 2012

How to use wait/Sleep command in SQL. I have to write a script that takes about 20 min to execute.

View 1 Replies View Related

Performance Tuning :: Buffer Busy Wait?

Dec 20, 2011

I noticed oracle background process ora_fbda_padwsdpr is suffering from buffer busy wait. When i further finding the object, it was on SYS_FBA_FA tables.

what is this is causing BUFFER BUSY WAIT. Also to add we have disabled flashback database.

View 4 Replies View Related

SQL & PL/SQL :: Nowait Requested But Had To Wait To Lock Dictionary

Aug 5, 2010

I get the Error like this frequently

ERROR at line 1:
ORA-20001: ORA-04022: nowait requested, but had to wait to lock dictionary
object
ORA-06512: at "MDWDBA.MDW_PKG", line 917
ORA-04022: nowait requested, but had to wait to lock dictionary object
ORA-06512: at line 1

why this occurs? and how to Resolve it?

View 2 Replies View Related

Cursor Pin S Wait For X In 11g Enterprise Edition Release 11.2.0.2.0

Sep 4, 2013

Ours is Oracle 11.2.0.2.0 Db 4 instances RAC on Unix AIX OS.Since long we are facing problem that CPU utilization reached 100% and reboot is required alteast once or twice a month.On seeing the Events Logs we find that the Event "CURSOR PIN S WAIT FOR X" is consuming a lot of waits.

On analyzing i came to know that we are firing same query from Application 15 to 20 lack times for which a lot of Mutex keeps spining for getting Shared Mode and consumes a large amount of CPU.

View 3 Replies View Related

Performance Tuning :: Digging Out Wait Events

Oct 15, 2013

I am using 11.2.0.3.0 version of oracle. As told by the DBAs that two days back the system realised high wait_class i.e 'Application' wait, and its a lot high than regular value as per our system and we need to dig it down, to avoid any future issue. now using below query , i found that the high wait time due to wait class 'Application' is actually belongs to particular event 'SQL*Net break/reset to client', and the sample time was '9 AM' morning.

select time,
round(max(case when event = 'SQL*Net break/reset to client' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end) ,2) SQL_break_reset_client,
round(max(case when event = 'Wait for Table Lock' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) Wait_for_Table_Lock ,
round(max(case when event = 'enq: KO - fast object checkpoint' then
[code]....

Now i want to track it down further to the 'session/sql query/application' level resulting into such high value of wait time. so i queried, dba_hist_ active_ session_history, for the same sample duration (8.30 to 9 am) having event 'SQL*Net break/reset to client', and got two sessions(123,154) and their serial# , but there i got one sql_id(3ahgrey10ogh i.e a 'SELECT' query) specific to one session(123) but for other(125) no sqlid, and also SUM (wait_time + time_waited) is showing '0'.

Then i just removed the sampletime filter from the below query and observed that the same 'same session(123)+session serial#' was activated since 4 days back and was experiencing same waitevent 'SQL*Net break/reset to client', (it was having normal wait event 'db file sequential read' for sometime then after it went for this event ).

SELECT user_id,
PROGRAM,
machine,
session_id,
SESSION_SERIAL#,
sample_time-7/24,
session_state,
[code]....

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved