SQL & PL/SQL :: Query On V$session - Status Column

Dec 2, 2011

If a session runs a query, the status will be inactive after completing the database call. I know this concept. But I just want to know what will be session status, if it running a procedure contains many select queries, DML quries and loop? Whether it will be active until procedure completes or it will be switching active and inactive?

View 3 Replies


ADVERTISEMENT

Server Administration :: What Does SNIPED Status In V$session Mean

Nov 22, 2010

what does 'SNIPED' status in v$session mean? It is killed/ clear from instance automatically or need to kill manually for releasing the resource.

View 3 Replies View Related

SQL & PL/SQL :: Query To Get Overall Status For Clients

Sep 11, 2010

I have a clients table called CLIENTS. One of the fields in this table is called 'status'

I need to set this status field, depending on if the transactions for this client have passed.

The transactions are in a table called TXNS and has fields
client_id, txn_id, txn_status

txn_status can be 'success' or 'failed'

I need to somehow set the overall 'status' field on CLIENTS to success or fail depending on if any transactions failed or not

So for every client I want to list the status, any query that can possible list the overall status for the client?

select client_id, MAX(status)
from txns
group by client_id

but the max(status) bit needs to get the overall status part

View 10 Replies View Related

Application Express :: How To Hide Column Link URL On Status Bar

May 8, 2013

Apex 4.1

I have IR wit one column link target to URL, and it uses javascript:my_function(#COLUMN_VALUE#)as URL, that is, calls some function and passes the value of that column to the function; everything works fine; but when I place a cursor over the link, bottom line of IE9 browser (is it called a 'status bar'?) clearly shows:

'javascript:my_function(<actual value>)' Is there any way to prevent it from showing function name and parameter value? I tried onmouseover="window.status='';"but it does not work

View 3 Replies View Related

SQL & PL/SQL :: Select Query To Get Top Two Rows Based On Status Date

Oct 29, 2012

I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.

SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI

View 10 Replies View Related

PL/SQL :: Query To Fetch Parent Records Only If No Child With Particular Status?

Sep 16, 2012

I need to fech parent records only when no child record with status 'N' exists. There are only two possible values for status column of child table 'Y' / 'N'.

Below are table structures and insert statements for data.

CREATE TABLE MASTER
(
COL1  NUMBER,

[Code]....

COMMIT;Query I framed is below

select * from master where exists (select null from child where child.col2 = master.col1
group by child.col2 having count(distinct col3) =1 )

Output in above case would be 3 as for 1 there's one record with status as 'N' and for 2 there's no child record. I am on 10g.

View 2 Replies View Related

Server Administration :: Check Status Of Listener And Read Only From DB Query?

Jun 13, 2010

I need to display the parameter and status of DB for listener and Read Only.

I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.

View 8 Replies View Related

Forms :: Block Status When User Presses Enter Query Key

Feb 28, 2012

Is it possible to get the status of form or block every time? I mean if user presses Enter query key, it should display some status and if user presses execute query key then it should reflect some status. If user update any then it should reflect.

View 7 Replies View Related

Performance Tuning :: How To Find Percentage Completion Of Query In Running Status

May 2, 2012

How can we check completion status for running sql query. i.e. how much % completed

SQL> begin
2 delete from gsmcrmdw.wc_loy_txn_f_aa
3 where integration_id in
4 ( select integration_id
5 from support_olap.recover_wc_loy_txn_f_953to955
6 );
7 commit;
8 end;
9 /

View 35 Replies View Related

Performance Tuning :: SID Column In V$SESSION?

Dec 12, 2010

Even though the users are not logged in why the V$SESSOIN shows the SID and why many times?

View 2 Replies View Related

PL/SQL :: Alter Session Query On Linux?

Nov 1, 2013

I was trying to execute below query inside a sql script which is called from a shell script on linux environment.  EXECUTE IMMEDIATE 'alter session set events ''10176 trace name context forever'' ';   

This thing works at our test environment.  But giving error "SP2-0670: Internal number conversion failed" when tried on live(production) environment. What could be the reason? I found another way of writing such queries on net as follows, will it be useful? EXECUTE IMMEDIATE q'|alter session set events '10176 trace name context forever'|

View 1 Replies View Related

Application Express :: Sql Query To Fetch Session ID

Feb 18, 2013

I am to trying to fetch session id of a previously submitted process of a search button......so that i can display the search results in a different page.....so is there any sql query or pl/sql procedure to fetch the session id.

View 1 Replies View Related

SQL & PL/SQL :: ORA-01001 - User Refresh SSRS Session And Again Run Query

Jan 11, 2012

We have a package procedure called from SSRS , which returns a REFCURSOR to the SSRS.Sometimes it throws the error. The user Refresh the SSRS session and again run the query , the error does not happen

Following is the error number

ORA-01001: invalid cursor

We have not used any static cursor , so this is not happening because we have opened a cursor and not closed it.The Cursor used is REF CURSOR and the output parameter is declared as

---------------
p_ref_cursor OUT SYS_REFCURSOR
----------

The Procedure returns the Select query in the REF CURSOR.

View 7 Replies View Related

Server Administration :: Query To Find Hostname For Oracle Session

May 20, 2010

How I can find the hostname for an oracle session id?

View 4 Replies View Related

Application Express :: Kill Current Session When Another Session With Same User Starts

Nov 19, 2012

I have an urgent requirement to kill an existing session if a new session starts for the same user. I have been reading lot of blogs and posts on the above topic, but could clearly tell me how to do it.

I thought of putting a process in 101 page when login button is pressed to catch this and kill the old session.

View 7 Replies View Related

Application Express :: (X) Process Taking 60% CPU In Two Session (30% Each Session)

Oct 23, 2012

We are using Apex 4.0/Oracle 10gR2/ Hp-Ux, We noticed that there is a process Running in Two Sessions that seems running since 3 Hours using 60% CPU,** 30% given below sql it executes in two Sessions!

declare function x return boolean is begin -- if instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY')>0 then
declare
l_position number := 0;
begin
loop
l_position := instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) ;
exit when instr(upper(:F4000_P4651_PLUG_SOURCE),'ORDER BY ',l_position + 1) = 0 ;

[code]....

View 4 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Application Express :: Column Link Where Column Number Changes With Query

Feb 11, 2013

We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report

<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012

[Code]....

The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.

Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)

ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.

So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.

View 0 Replies View Related

Grant Select On V$session And Gv$session Directly?

Aug 28, 2013

Instead of giving permission directly to v$session and gv$session to a developer, Is there any other way to do the same?

View 3 Replies View Related

SQL & PL/SQL :: Query Row To Column

Mar 4, 2013

query for below requiremnet :-

I have table A having column name Varchar2(10), Seq Number ,Address varchar2(20)

Data in table as
name SeqAddress
A1bangalore
A2karnataka
A3India
B1Mumbai
B2Maharastra
B3India

I need to write query to get below Output

Abangalore,karnataka,India
BMumbai,Maharastra,India

I can not use any inbulit function of oracle like "SYS_CONNECT_BY_PATH", "LIST_AGGR" or any other function , even can not use any user defined function.Need to write only SQL to get this result.

how can we get above result

View 6 Replies View Related

Query One / Several Column(s) To Populate Another?

Jun 16, 2009

I need to query a table to read the value, specifically a date, in one column and characters in another, i.e. and ID number, and populate a new column with new data. For example:

If Column A = 1/1/2009 and Column B = 0123 in table 'Persons' I need a query statement to populate Column Z with a 'Yes'

If Column A = 2/1/2009 and Column B = 9876 in table 'Persons' I need a query statement to also populate Column Z with a 'Yes'

View 3 Replies View Related

SQL & PL/SQL :: Query For Table Has 90 Column?

Jul 22, 2010

I have one table which has 90 columns all has varchar2 datatype except one Column[primary key (Number)]. In this Table we have 1000 records, I want to fetch those records from Table which has value in all 90 columns means there is no null value in any column.

I know simple method Like this :-

column_name1 IS NOT NULL AND Column_name2 IS NOT NULL.

Like this we can write IS NOT NULL condition for all column.Is there any other way to write this Query because it makes Query very longer and it is very tedious job to write this Condition for all Columns.

View 3 Replies View Related

SQL & PL/SQL :: Query Two Table After One Column ID?

May 25, 2010

I have the following issue i have two table

PRODUCT (id, product_name)
1, prod1
1, prod11
2, prod2
3, prod3

OSS(id, oss_name)
1, oss1
2, oss2

what i want to return is the product_name and the oss_name for each id.

1, prod1, oss1
1, prod11,
2, prod2, oss2
3, prod3,

View 13 Replies View Related

SQL & PL/SQL :: How To Write Column To Row In A Query

Nov 17, 2005

How to write column to row...in a SQL query?

For example..

SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40

The above output to be written in a single row like given below.

7369 20 10 40

1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other

2) It should be in SQL query only..Not in procedures or functions.

View 9 Replies View Related

SQL & PL/SQL :: Dynamic Column Value Query?

Nov 3, 2010

I have a Strange requirement from client data is loaded from excel to Oracle Table- TST_TBL (with Header in Excel)

CREATE TABLE TST_TBL
(
JOB_DETAIL_ID NUMBER,
SHEET_NAME VARCHAR2(100 BYTE),
COL1 VARCHAR2(400 BYTE),
COL2 VARCHAR2(400 BYTE),
COL3 VARCHAR2(400 BYTE),
COL4 VARCHAR2(400 BYTE),

[Code]...

After the Data is loaded, we see data look like the above.

(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column.
(2) Null Column indicate us that there is a break in the column.
(3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column.
(4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.

If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?

Insert into TST_TBL
(JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3,
COL4)
Values
(100, 'Wire_1', 'Gen1',
'Gen2', 'Gen3', 'Gen4');
Insert into TST_TBL

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Alter Column By Using The Query

May 20, 2010

i have a database in which some tables. Now i want to alter column by using this query.

ALTER TABLE SALE_INVO_DETAIL_COPY
MODIFY ("QTY" number(5,2))

this column properties is QTY NUMBER(5)

now i want to convert it into QTY NUMBER (5,2)

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Timestamp From Only One Date Column?

Apr 10, 2013

I want to write a query to get the time stamp from only one date column,

I tried using a group by clause but getting error "not a group by exp."

Below is the query

SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP

[code]....

In the above i am looking for a diference on ProdRequestId,

Output

ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03

View 24 Replies View Related

SQL & PL/SQL :: Query To Transpose Column Names To Row

Mar 9, 2011

Query : select * from scott.dept where deptno=10;

Result
DEPTNO |DNAME |LOCATION
-------|----------|----------
10 |ACCOUNTING|NEW YORK

But i want the above resultset as below,

COL1 COL2
----------------------
DEPTNO | 10
DNAME | ACCOUNTING
LOCATION | NEW YORK

View 4 Replies View Related

SQL & PL/SQL :: Multiple Column In Pivot Query?

Dec 17, 2011

I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.

Herewith I'm displaying my try on single column pivot query.

SQL> select * from
2 (select deptno,job,sal
3 --,comm
4 from emp)
5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER'))
6 order by 1;

[code]....

I've tried this one also, but it didn't seems to be working.

SQL> select * from
2 (select deptno,job,sal,comm
3 from emp)
4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER'))
5 order by 1;

[code]....

I want result like below.

DEPTNO PAYMENT 'CLERK'_PAYMENT 'SALESMAN'_PAYMENT 'MANAGER'_PAYMENT
---------- ------- --------------- ------------------ -----------------
10 SAL1300 2450
20 SAL1900 2975
30 SAL 950 5600 2850
30 COMM 2200

is it possible to have multiple column pivot query.

View 2 Replies View Related

SQL & PL/SQL :: Query To Find Empty Column?

Aug 3, 2010

I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.

I need a query that should return the column which is empty but its next and previous column is not empty.

View 13 Replies View Related







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