RE: SELECT statement...
Title: RE: SELECT statement... Hi, Count(*) will never return NO_DATA_FOUND..so NULL value being compared is out of question Viral. -Original Message- From: Arul kumar [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Subject: Re: SELECT statement... Hi Rich, I doubt any NULL value being checked in the WHERE CLAUSE... as u will be knowing a NULL is not equal to another NULL in oracle !! Just try applying NVL on such possible columns Hope this may help u. regards, Arul. Richard Huntley wrote: > I'm encountering something very strange...has anyone ever encountered a > simple select > statement that found no data such as: > > SELECT COUNT(*) > FROM student_grps s, grps g > where s.stuid = :new_stuid > and g.gid = s.gid; > > This code is inside a before insert row level trigger and the select works > fine > at an SQL prompt but encounters a NO_DATA_FOUND exception when the trigger > fires. > Any ideas at all?? How can count(*) encounter this?? > > TIA, > Rich > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Richard Huntley > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arul kumar INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle OLE-DB
Title: Oracle OLE-DB Hi All Can anyone help me on this..Pls reply ASAP The requirement is as follows: -- The front-end is reading some data from external files (flat files like csv or maybe xml). -- The middle tier application (C++ component) is processing this information i.e. identifying the data and mapping the information to the respective table/column format -- The middle tier application is storing this mapped information in some kind of buffer/array -- The OLE-DB (or anything similar) set of functions/procedures should provide means to interface with this buffer and also the oracle database. I wanted to know if there is any layer (like OLE-DB or something similar) that can read this and provide functions that can insert/update data to the oracle database. Please let me know anyone's view on this. Let anybody forward me the ideas if any. Regards, Viral Amin
Problem with Rollback segment getting locked
Title: Problem with Rollback segment getting locked Hi All, I have about 12 Rollback segmennts in my database & 25 concurrent users logged in. I am continuously facing problems of oracle just keep locking couple of Rollback segments. other people have to wait till Oracle releses the locks on this rollback segments. My Query is..Why Oracle is just using the few rollback segments from the avilable 12 & then others have to wait till the locks on the rollback segments are released. Pls help..This is URGENT!! Regards, Viral Amin
Oracle Discoverer Report Query
Title: Oracle Discoverer Report Query Hi All, I need to create a report in Oracle Discoverer. I would like to know if it is possible to create a report with the following logic. If yes, then how do I go about creating a report. I have a table TBLREPORT with the following structure from which I have to pick up records. Table: TBLREPORT dttime DATE interval VARCHAR2(10) (The field will have values BASE, HOURLY) rdvalue number (aggregate to be applied in business area is AVG) Data in table: Dttime interval rdvalue 20-AUG-2001 9:00 am HOURLY 10 20-AUG-2001 10:00 am HOURLY 20 21-AUG-2001 9:00 am HOURLY 20 21-AUG-2001 10:00 am HOURLY 30 22-AUG-2001 9:00 am HOURLY 10 22-AUG-2001 10:00 am HOURLY 20 23-AUG-2001 9:00 am BASE 10 23-AUG-2001 9:15 am BASE 20 23-AUG-2001 9:30 am BASE 30 23-AUG-2001 9:45 am BASE 40 23-AUG-2001 9:00 am HOURLY 25 (the base data for 23-AUG-2001 from 9:00 am to 9:45 am is rolled up to arrive at HOURLY data e.g. 10+20+30+40 = 100/4 = 25) 23-AUG-2001 10:00 am BASE 50 23-AUG-2001 10:15 am BASE 60 23-AUG-2001 10:30 am BASE 70 23-AUG-2001 10:45 am BASE 80 23-AUG-2001 10:00 am HOURLY 65 (the base data for 23-AUG-2001 from 10:00 am to 10:45 am is rolled up to arrive at HOURLY data e.g. 50+60+70+80 = 260/4 = 65) 24-AUG-2001 9:00 am BASE 10 24-AUG-2001 9:15 am BASE 20 24-AUG-2001 9:30 am BASE 30 24-AUG-2001 9:45 am BASE 40 24-AUG-2001 9:00 am HOURLY 25 (the base data for 24-AUG-2001 from 9:00 am to 9:45 am is rolled up to arrive at HOURLY data e.g. 10+20+30+40 = 100/4 = 25) 24-AUG-2001 10:00 am BASE 50 24-AUG-2001 10:15 am BASE 60 24-AUG-2001 10:30 am BASE 70 24-AUG-2001 10:45 am BASE 80 24-AUG-2001 10:00 am HOURLY 65 (the base data for 24-AUG-2001 from 10:00 am to 10:45 am is rolled up to arrive at HOURLY data e.g. 50+60+70+80 = 260/4 = 65) From this table I have to pick-up data for week = 20-AUG-2001 to 24-AUG-2001. (the week start date is entered by the user in the report) I have to first check if BASE data exists for the week. If no base data exists then check for HOURLY data. The following data exists: No BASE data will be available for 20-AUG-201, 21-AUG-2001 or 22-AUG-2001, since it would have been deleted. HOURLY data is available for these days. BASE data would be available for 23-AUG-2001 and 24-AUG-2001. HOURLY is available for these days also. 20-AUG-2001 21-AUG-2001 22-AUG-2001 23-AUG-2001 24-AUG-2001 --- --- --- --- --- No BASE No BASE No BASE BASE BASE HOURLY HOURLY HOURLY HOURLY HOURLY The input to the report is: Week start date = 20-aug-2001 Hour range = 9:00 am to 10:00 am Now in order to produce a weekly report, 1) The HOURLY data for 20-AUG-201, 21-AUG-2001 and 22-AUG-2001 should be viewed for the hour ranges the user enters. 2) The BASE data for 23-AUG-2001 and 24-AUG-2001 should be rolled-up to arrive at HOURLY data only for the hour ranges entered by the user, using the logic specified for rollup as given in the data. The HOURLY data present for 23-AUG-2001 and 24-AUG-2001 should not be considered but the BASE data should be considered which should be rolled-up to arrive at HOURLY data. 3) Then the HOURLY data from 20-AUG-201, 21-AUG-2001, 22-AUG-2001, Base data of 23-AUG-2001 and 24-AUG-2001 rolled-up to HOURLY should be used to generate the weekly report, taking into consideration the hour ranges entered by the user. Any help will be greatly appreciated. Thanks in advance Viral.
Stored Procedure Performance Problem --- Please Help
COMMIT;
END IF;
-
-- Store the value of current serverid
-
v_prev_srv := server_rec.idserver;
-
-- Constructs the column and value pair list for all counters flagged 'Y' in the TBLCOUNTER table
-
FOR cur_trans_rec IN cur_trans_main(server_rec.idserver, server_rec.dttime) LOOP
BEGIN
v_strrptcolname := get_col_name(cur_trans_rec.idcounter);
IF v_strrptcolname <> 'XXX' THEN
v_collist := v_collist || ',' || v_strrptcolname ;
v_valuelist := v_valuelist || ',' || cur_trans_rec.numreadingvalue;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-
-- Update the TBLCOUNTER_READING.YSNTRANSFORMFLAG to 'Y' for the transformed record.
-
UPDATE tblcounter_reading
SET ysntransformflag = 'N'
WHERE CURRENT OF cur_trans_main;
END LOOP;
-
-- Build the Insert statement and store it in a variable with coulm and value pair list created above
-
v_sql_stmt := 'INSERT
INTO tblreportcounter_reading(IDSERVER, DTREADINGTIME, STRINTERVAL' || v_collist || ')
VALUES ('|| server_rec.idserver ||','
|| '''' || to_date(server_rec.dttime,'DD-MON- HH24:MI')|| '''' ||','
|| '''' || 'BASE' || ''''
|| v_valuelist || ')';
BEGIN
-
-- Execute the insert statement prepared
-
EXECUTE IMMEDIATE v_sql_stmt ;
EXCEPTION
WHEN OTHERS THEN
-
-- On error rollback data, log the database error in SFERROR table and exit process.
-
ROLLBACK;
logerror_prc(SQLERRM, 'Transformation' );
EXIT;
END;
-
-- Re-Initialize variables
-
v_collist := Null;
v_valuelist := Null;
v_sql_stmt := Null;
END LOOP;
END transform_prc;
Regards
Viral Amin
