Different Results When Run Through All Records Compared To Running A Single Record?

Dec 18, 2008

I am using toad to output the details of the database. (I need these details in a format which i can then import into excel) However, When I run sporadic individual records through my query, the results come back correct and as expected. But when I run the full data set through my query a lot of the records have different values than what they do when run individually!

Ive spent all day on this script and its really a hack of various other cursors and scripts brought together. Its driving me mad as my code doesn't seem to be wrong (since the results are correct when a single record is run) yet there must be something wrong as my full data set does not return correctly!

View 2 Replies


ADVERTISEMENT

Data Guard :: Running Observer On Different OS As Compared To Primary And Secondary DBs

Jan 28, 2013

Primary RAC and Standby RAC databases are using version 10.2.0.4. They are configured & controlled via Data Guard Broker. Currently, the observer runs from one of the Standby nodes. All are on Solaris x86 64bit OS version 10.

Now, I have to move Observer to 3rd DC and I am looking If I could use another OS for observer instead of same as DB OS version. Why? Our new infrastructure is on RHEL Linux, therefore, I would like to deploy observer once and then wait for existing databases to be upgraded and moved to RHEL. Also, i understand that database and observer software should be of same version e.g. 10g, 11g.

My question is what if i install & configure observer on RHEL ? The end result would be:

1. Primary DB: Solaris 10, Oracle Software binaries version 10.2.0.4
2. Secondary/Standby DB: Solaris 10, Oracle Software binaries version 10.2.0.4
3. Observer: RHEL 5.8, Oracle Software binaries version 10.2.0.4

Is it accepted mix configuration say e.g. to run for six months? Also, it would be grateful to know if there any license implications when running observer on a different node running no databases at all.

View 2 Replies View Related

Forms :: Fetch Single Record From Multiple Records Based On Condition

Jan 27, 2012

I have made a travel booking system which comprises of 3 forms

1)Travel Booking form
2)Reservation Form
3)Cancellation Form

Under one booking number i can add multiple users in which they can have there multiple travels.

Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.

What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.

For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.

View 9 Replies View Related

SQL & PL/SQL :: Group Results By Date With Running Totals?

Apr 30, 2012

My version of the Database:

BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

I have a staff table with the columns staff_id and completion_date. The completion_date shows the date a staff member completed questionnaire. If the staff member did not complete the questionaire, then the completion_date column will be NULL.

Table Definition:

-- Create table
create table staff
(
staff_id number not null,
completion_date date
);

See attached text file (staff.sql) for Insert Statements.

The result set needs to have the following columns: ReportDate: the Sunday of each week. Completed: The number of staff who have completed the questionnaire by the ReportDate. NotCompleted: The number of staff who did not complete the questionnaire by the ReportDate. Total: The SUM of Completed and NotCompleted columns.

As the number of Completed goes up, the number of NotCompleted goes down. Eventually Completed will equal Total and NotCompleted goes to zero.

The result set would look similar as follows and used to generate a bar graph chart:

ReportDateCompletedNotCompletedTotal
2012-Apr-01814651473
2012-Apr-082214511473
2012-Apr-1514413291473
2012-Apr-2242010531473
2012-Apr-295788951473

View 8 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name  

Null Type  ------ ---- ------------ ID_P        NUMBER       NAME_P      VARCHAR2(12) DATE_P      TIMESTAMP(6) Records---------------------1 loosi     22-AUG-13 01.27.48.000000000 PM1 nammi  26-AUG-13 01.28.10.000000000 PM2 kk        22-AUG-13 01.28.26.000000000 PM2 thej      26-AUG-13 01.28.42.000000000 PM 

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej  26-AUG-13 01.28.42.000000000 PM

View 4 Replies View Related

Getting Count Of Records / Returning Zero For Null Results?

Jul 23, 2009

I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.

This is the pre-existing query:

SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')

The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.

View 14 Replies View Related

Oracle 11g - Record Query And Time To Fetch Results For A Specific User ID

Jul 22, 2013

I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?

View 7 Replies View Related

Running Two Instances On Single Machine

Apr 25, 2013

i am using oel 5.5 with oracle 10g release 2 installed in it . I am unable to run two different instances on same machine how can do that? I changed the ORACLE_SID then i startup one instance is starting but when i change the sid again and tried to startup it shows me that oracle is already started shut it down first.

Is there any way to startup two different instances?

View 5 Replies View Related

SQL & PL/SQL :: Show Same Record Twice With A Single Query?

Sep 24, 2012

I have a scenario for which I need to show same record twice.

i.e

SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul

Is this possible with a single query.

View 7 Replies View Related

SQL & PL/SQL :: ORA 01427 - Single Row Sub Query Returns More Than One Record?

Oct 10, 2013

I am not able to update a table cloumn using below query and getting ORA 01427: single row subquery returns more than one record error.

update fuctions f set id = (Select g.fn_id from access g where g.level = 'A' )
where f.u_id = "ABC";

I understand that problem is there with inner select query.

View 3 Replies View Related

Single To Multiple Records

Sep 18, 2013

I have a table as col_1 number,col_2 varchar2(10)

col_1 col_2
1 abcdefghijkl

what i want is i've to split

col_1 col_2
1 abcdefghij
1 kl

View 4 Replies View Related

PL/SQL :: Multiple Records In Single Row?

Jul 8, 2013

I want to insert multiple records in a single row. Example:  I have a below query  

select '"'||c1||'","'||c2||'","'||c3||'"'from (select 'ABC' as C1,'ZYX' as C2,'TEST' AS c3from dual unionselect 'A1' as C1,'a2' as C2,'A3' AS c3from dual)

And I want to insert the above 2 records in table T1 as a single row as below: 

"A1","a2","A3""ABC","ZYX","TEST" Column1"A1","a2","A3""ABC","ZYX","TEST"

View 6 Replies View Related

CPU Utilization Is More In 11g As Compared To 10g?

Sep 17, 2013

 Recently we have upgraded to 11g(11.2.0.3) from 10.2.0.2 on AIX 5.1 enviornment. After upgrade, it has been noticed that CPU utilization is more while executing a program. 

CPU is crossing 85%  in 11g, in 10g it is 60% during the program execution. I have gone through the SQLs which are getting executed, elapsed time for all SQLs are less than in 10g. i.e. elaspsed for all SQLs in 11 is very less.  in AWR report, DB CPU stood in the top. 

View 10 Replies View Related

Forms :: Delete Detail Block Single Record

Apr 22, 2010

My form consist two datablock cust_mstr,cust_dtls, each customer code consists two or three contact details records, when i delete the single detail record instead of deleting single record it deletes all the details records from cust_detail table.

Here by i attaching my form for your reference pls find the code in delete button

View 12 Replies View Related

Forms :: Multi Values For One Single Record (student_id)

Sep 1, 2010

I am using forms 10g...

I have 2 blocks,in one i insert student_id and when i click on the button i should get all his courses in the other block...

BUT.. when the student has more than 1 course I get this:

when button pressed trigger raised unhandled exception ORA-01422

Here is the button

begin

select TEST_STUDENT_INFO.stud_name,TEST_STUD_CRSE_DETAIL.crse_no,
TEST_COURSES.crse_name,TEST_STUD_CRSE_DETAIL.crse_type,TEST_STUD_CRSE_DETAIL.crse_type_desc,
TEST_COURSES.crse_time
INTO :stud_name,:crse_no1,:crse_name,:crse_type1,:crse_type_desc1,:crse_time
from TEST_STUDENT_INFO,TEST_COURSES,TEST_STUD_CRSE_DETAIL
where (TEST_STUD_CRSE_DETAIL.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_STUDENT_INFO.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_COURSES.CRSE_NO = TEST_STUD_CRSE_DETAIL.CRSE_NO)
and (TEST_STUD_CRSE_DETAIL.CRSE_TYPE = TEST_COURSES.CRSE_TYPE);

end;

but if the student has ONLY ONE course it WORKS FINE!

View 4 Replies View Related

Forms :: Hide A Single Row Field Of Multi Record Item?

Nov 3, 2010

Consider below is a multi record block rows, i want to hide "23". Is it possible using Set_Item_Instance_Property or any other built-in is there in oracle forms to hide a single row field in a multi record block.

11 12 13
21 22 23
31 32 33
. . .

View 3 Replies View Related

Forms :: How To Disable Single Record In Multi Non-database Block

Mar 28, 2011

The main qn is in the subject line.

I have used the following code

GO_BLOCK('CHILD_BLK');
FIRST_RECORD;
LOOP
IF NVL(:CHILD_BLK.SELECT_FLAG,'N')='Y' THEN
V_REC_NO := GET_BLOCK_PROPERTY('CHILD_BLK',CURRENT_RECORD);
SET_ITEM_INSTANCE_PROPERTY('CHILD_BLK.ASSET_DESC',
V_REC_NO,

[code]....

View 5 Replies View Related

Forms :: Insert / Update And Delete Single Record At A Time?

Aug 10, 2005

I have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.

View 32 Replies View Related

SQL & PL/SQL :: Creating New Instance Compared To New Schema?

May 17, 2011

I have SQL Server database I would like to migrate into Oracle. The database supports a large application. It is around 10GB. I requested a new instance but was advised I would have to pay for that but if I asked for a new Schema it could go in our current company instance. I am fine with that since it wont cost more money if I just add a new schema to our Company Oracle instance. Just curious what is the advantage of getting a new instance compared to creating a new schema for 10 GB of data?I assume the advantage of creating a new instance is our Schema (in new instance) and work will have its own space/house and can grow in size without any issues?

View 3 Replies View Related

Fetch Records In Single Select Query And Display

Mar 15, 2011

I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.

Name id dept_no
Ram 101 10
20
30
Ani 201 10
20

View 1 Replies View Related

How To Get Start And End Date From Single Column With Iterative Records

Jul 25, 2013

The problem I am facing analyzing a certain table s trying to get a proper start and end date for a specific field such as TICK_COL, because there are so many other fields are being updated in this table - all using MTC_DATE - this is causing iterations of TICK_COL.

So first step was to just use lead to get the end date for all iterations so I could picture how this might look with a start and end date

CODE A (see below)
FROM_DATE TO_DATE
SKU TICK_COL MTC_DATE LEAD (MTC_DATE)
21524804 RIBG 10101 20080615
21524804 RIBG 20080615 20080625
21524804 RIBG 20080625 20080628
21524804 RIBG 20080628 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20080930
[Code] .......

My first bright idea? I tried using Rank as well, hoping to rank each of this tick_color changes as 1, which works for the exception of when tick_col changes to RIWH or RIGR again.

The ranking function doesn't see the 2nd change to RIWH as entirely unique and assigns it a 2 and 2nd change to RIGR a 3. If I could rank each of those as 1 I could query these results as an in-line view where rank = 1 and do lead to get the start and end date, finished,

CODE B (see below)
21524804 RIBS 20130725 20130725 8
21524804 RIBS 20130327 20130725 7
21524804 RIBS 20130317 20130327 6
21524804 RIBS 20130312 20130317 5
21524804 RIBS 20120813 20130312 4
21524804 RIBS 20100916 20120813 3
21524804 RIBS 20100518 20100916 2
21524804 RIBS 20091120 20100518 1
[Code] ........

I am Expecting to see this below:

21524804 RIBG 10101 20080920
21524804 RIWH 20080920 20080923
21524804 RIGR 20080923 20081031
21524804 RIWH 20081031 20090311
21524804 RIGR 20090311 20091120
21524804 RIBS 20091120 20130725

The code I used to generate the first table was which obviously, does not get me as far as I�d like.

CODE A

SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].......

CODE B

SELECT sku_nk,
ticket_type_color,
TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')) mtc_date,
CASE
WHEN LEAD (TO_NUMBER (TO_CHAR (mtc_date, 'YYYYMMDD')), 1, 0)
[code].........

View 3 Replies View Related

SQL & PL/SQL :: Distinct And Individual Records In Single Or Minimum Pass(es)

Feb 21, 2012

I have following data

select rowid,object_name,object_type from do;

ROWID OBJECT_NAME OBJECT_TYPE
------------------ ------------------------------ ------------------
AAA/wuAAHAAAW73AAA CON$ TABLE
AAA/wuAAHAAAW73AAB I_COL2 INDEX
AAA/wuAAHAAAW73AAC I_USER# INDEX
AAA/wuAAHAAAW73AAD C_TS# CLUSTER
AAA/wuAAHAAAW73AAE I_OBJ# INDEX
AAA/wuAAHAAAW73AAF I_CON2 INDEX

6 rows selected.

I want it in the following manner

select rowid,object_name,object_type from do;

ROWID OBJECT_NAME OBJECT_TYPEGROUP
------------------ ------------------------------ ---------------------------
AAA/wuAAHAAAW73AAA CON$ TABLE2
AAA/wuAAHAAAW73AAB I_COL2 INDEX1
AAA/wuAAHAAAW73AAC I_USER# INDEX1
AAA/wuAAHAAAW73AAD C_TS# CLUSTER1
AAA/wuAAHAAAW73AAE I_OBJ# INDEX1
AAA/wuAAHAAAW73AAF I_CON2 INDEX1

6 rows selected.

Here the GROUP is changing when the data type is changing and thus for same data type the group shall remain the same As of now this is achieved by - first selecting distinct object_type, then it's mod(rownum,<input variable) and this result of 'mod' is doing the grouping which is the retrieved along with rowid of all individual record

Present query is as following and it is not much efficient

SELECT DO.ROWID RWID, RID
FROM DO,
(
SELECT OT,CASE MOD(ROWNUM,:v) WHEN 0 THEN :v ELSE MOD(ROWNUM,:v) end as RID
FROM(
(SELECT DISTINCT OBJECT_TYPE OT

[code]....

I tried using sequence with cycle but it gave different results. Even I tried following but it did not gave satisfactory results

select d.rowid,d.object_type,x.x1 from do d,(select distinct object_type,mod(rownum,:v) x1 from do where
created>'01-jan-2008')x where d.object_type=x.object_type and created>'01-jan-2008';

In short the query needs Distinct with mod(rownum) and individual records in a single pass The mod(rownum) i.e. group shall change when the object_type changes but then shall remain constant through out the particular object_type.

View 9 Replies View Related

SQL & PL/SQL :: Print No Results If More Than 10 Records Else Print The Output

Mar 24, 2011

I have this SQL statement:

WITH data
AS (SELECT user_id,
jc_name,

[Code]....

I wish to do something like

if results > 10 print an error message (and no results)
if results < 10 print the results/output

View 1 Replies View Related

Server Administration :: How To Know Currently Running Databases In Single Server

Jul 30, 2010

I have installed three database in MY Linux server. I have started Instance for all database and started database also .

No I want to check my databases status or currently running database How can I know . Or which Dynamic View or Database dictionary I should use ?

View 2 Replies View Related

Forms :: Query Records Of Detail Block Displayed In Single Row

May 30, 2010

I have a Master block and a Detail Block. They are related using two columns, Document_ID and Page_no. I want to display all the records in master table with corresponding detail records beside them in single line. i.e., as shown below:

MasterItem1 DetailItem1 DetailItem2 DetailItem3

I created the relation between them and executed query. When I execute query in the form, I can find that all the Master Items are displayed in vertical records, but i can only see one record of detail fields. They are displayed as shown below:

mitem1-value1 ditem1value1 ditem1value2 ditem1value3
mitem1-value2
mitem1-value3
mitem1-value4

When the navigate down in master items, then the corresponding detail values are being shown on the same first record.

View 1 Replies View Related

SQL & PL/SQL :: Insert Multiple Records In Different Tables At A Time (Using Single Query)

Jun 8, 2010

I want to insert multiple record in diff. table by using single query...

how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..

But i want to do this task by using single query....how can i do it?

View 4 Replies View Related

SQL & PL/SQL :: Inserting New Record In Between List Of Records

May 2, 2012

I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?

Let's consider an example with 5 records:

Table data:

CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/

[Code]...

So when we check the table, we get:

SELECT * FROM enum

IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7

What I would like to have when I run the select query:

SELECT * from enum

IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7

The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.

View 6 Replies View Related

SQL & PL/SQL :: How To Partition Record Into Multiple Records

Jun 15, 2012

I have Leave Balances In such Format

EMP_COD CategoryJON_DTE LEV_BAL
3900000385101/02/200760
3900002678107/01/200840

Now I want to Separate it into Three and Two Partitions Year Wise.

Like this
3900000385101/02/200720 2012
3900000385101/02/200720 2011
3900000385101/02/200720 2010
3900002678107/01/200820 2012
3900002678107/01/200820 2011

View 7 Replies View Related

Application Express :: Displaying All Records In A Tabular Form On Single Page

Aug 27, 2010

After setting up a data entry page,The logic in my page requires that all the records in my tabular form should be displayed .The tabular form is based on a collection, and the user selects rows through a checkbox.Any reload of the page due to pagination will break the logic, as some calculation and display events occur during page load, based on the previous page.

I need to display about 25 rows.I have changed the report attribute to display 50 rows, but it displays rows only up to the bottom of the screen, i.e. 11 rows.

Unfortunately I only just added some new rows and saw this behaviour else I would not have gone that way, as I expected APEX to display all rows if required.

View 19 Replies View Related

Server Administration :: Total Data Growth Compared With Last Year

Sep 15, 2011

Is there any way to tell the total data growth increase of some tables when compared with last year.

View 4 Replies View Related







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