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.
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.
how many number of times the sql's had been executed/run in an oracle session. Executions in V$sql, is it the right place to look at? Will it give the number of times the sql has been executed in a particular session?
Get the following error from Oracle ORA-24333: zero iteration count Cause: An iteration count of zero was specified for the statementAction: Specify the number of times this statement must be executed on whether is this a Oracle bug or if not what should be set to avoid this failure.
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?
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?
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.
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.
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.
I have a table that holds the definition of schedules, a schedule defines when a document should be submitted to a specific party. The schedule definition contains a start date, and end date, a recurrence type (is this submitted one time, or on a recurring schedule) and the frequency at which the document should be submitted. The second table provides a history of the submissions, this stores when it was due and when it was received. At the beginning of the month we pre-populate the submittal table with a list of records that will be due for the month. For instance, on September 1 we look through all of the schedules and determine which ones would have a record due at some point in 9/12 and then create a record in the submittal table.
I am having issues getting the calculated list of submittal records to work properly.
The DDL and DML will be in a follow-up post
Here is the query that I am currently using and fails to work properly.
with schedules as ( -- generate a list of valid permit schedules select s.schedule_id,s.submittal_frequency_months,s.recurrence_type, s.first_due_date,s.requires_approval, round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) recurrence_number
[Code]....
-- create a list of all potential due dates for these schedules
select submittal_id_seq.nextval,schedule_Id,8,requires_approval, case when recurrence_type='One Time' then first_due_date when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
[Code]....
-- exclude those that already have a submittal record ;
Basically I found all possible records in the schedule table that could have a record due in September, then generate a result for all possible instances and then look at only those whose calculated due date is 09/01/2012. I've determined that the root problem I have right now is this line:
(select level iteration from dual connect by level <= (select max(schedules.recurrence_number) from schedules)) d
Schedule ID 469907 has a start date of 05/15/1992 and a frequency of every 2 months. I calculate what I call the recurrence number, which is the number of times the schedule has happened since its start date to now. I use that to do an add_months calculation from the start date and then eventually compare these calculted start dates with my target month (09/12). In this one records case the calculated recurrence number is 122. So when I generate the connect by level is does 122 records for every schedule, so I end up with duplicate records in the submittal table for many of the schedules. This current query could probably work if I could figure out a way to make the level be schedule ID specific, but I've failed at that thus far.
Aside from the fact that this is returning the wrong results, I am thinking there must be a better more efficient method to determine which records are due for a given month. I was thinking there is probably some cool way to use the model clause here, but I haven't got a grasp on that one yet.
If you run the following insert statement you'll see that it inserts over 2400 records:
insert into submittal (submittal_id,schedule_id,submittal_status_type_id,requires_approval,due_date,created_by,created_date,modified_by,modified_Date) with schedules as ( -- generate a list of valid permit schedules select s.schedule_id,s.submittal_frequency_months,s.recurrence_type, s.first_due_date,s.requires_approval,
[Code]....
-- only submittals whose last due date has not passed, null last date included
and trunc(s.first_due_date,'mm') <= to_date('09/01/2012','mm/dd/yyyy') -- only valid start dates --and round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) >0 )
-- create a list of all potential due dates for these schedules
select submittal_id_seq.nextval,schedule_Id,8,requires_approval, case when recurrence_type='One Time' then first_due_date when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
[Code]...
You can see the problem after words:
select schedule_id,count(0) from submittal where trunc(due_date,'mm')=to_date('09/01/2012','mm/dd/yyyy') and submittal_status_type_id=8 having count(0) >1 group by schedule_id;Tony
I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProdMy problem is, There is data for IN time and OUT time data type is DATE. I need to calculate OT hour from this two field and store into a third one column. But i'm confuse what should be the data type of the third column. Is it number or else ?
I know difference of two date column is number. So, first i need to know that data type i should use ?
Secondly, how i get the sum of the all stored time ? for example data are
I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.
The data table is: ITEM_INVENTORY The master table is: MASTER_SOURCE_SYSTEM
UPDATE ITEM_INVENTORY I SET I.SOURCE_SYSTEM_ID = (SELECT NVL(M.SRC_SYS_ID,-100) FROM MASTER_SOURCE_SYSTEM M WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE ) WHERE ORG_CODE = 'TNXC' AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
The situation here is that:
1. There are about 15000 rows that match ORG_CODE = 'TNXC'. 2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.
My question is: Do both the inner select statements execute 15000 times?
The statement executes within a second and updates 15000 rows. How is this made possible?
We are investigating performance of SQL executions on a database server and we suspect I/O on the server is an issue
For example one particular statement accesses one row during execution (index access) and still takes 2.4 seconds out of which it does I/O for 1.9 seconds
which of the following sections in the AWR will give us the correct information about the I/O, it is slow or not?
1) Load Profile Logical reads per second Physical reads per second
2) Top 5 Timed Foreground Events waits / time(s) for events like "db file sequential/scattered read" average wait(ms) for events like "db file sequential/scattered read"
3) Foreground Wait Events db file sequential read db file scattered read
4) Wait Event Histogram %of waits <1ms <2ms Disk file operations I/O db file sequential read db file scattered read
5) Wait Event Histogram Detail (64 msec to 2 sec) Wait Event Histogram Detail (4 sec to 2 min)
6) IOStat by Function summary Buffer Cache Readsreads per sec
The data in the table was imported from a csv file and there is a relationship between the rows. Each combination of col1, col2 and col3 describes a full route of a journey. The row with an entry in col6 describes the full route and the other rows describes each leg in the route.
For example, for R1, the route is AA to BB via CC. Another example for R4 the route is FF to SS via XX, PP, and OO.
What i would like to do is missing a route. For example the route for R3 is DD to EE via FF. There is an entry for DD to FF but is missing an entry for FF to EE.
The results should return the following rows which are incomplete
Here is what i have come up with but it doesnt quite returned the correct result.
select * from tableA a Where not exists( select 1 from tableA b where instr(col6,col4,1)>0 and instr(col6,col1,1)>0) And a.col1=b.col1 And a.col2=b.col2 And a.col3=b.col3 )
The db is 11.2.0.3 on a linux machine.I would like to know the "fetch size" of an application, but I was not able to find any related meteris in v$statname.
The application configruation is invisible to me.Do I need to do some calculations based on statistic metrics from v$statname?
If so, what meteris should be considered for the assumption for "fetch size" ?
The following is from manual, but the application configuration is invisible to me.
[URL]
Setting the Fetch Size
The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:
For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.
But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".
I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.
New Zealand operates Daylight Savings, so twice a year it changes its clocks.
When New Zealand is on standard time it is UTC+12.
When New Zealand is on daylight savings time it is UTC+13.
Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.
I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.
I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.
Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?
I'm trying to determine if/when a possible Hierarchical circular reference will occur in my data
Sample Hierarchical structure that I have
Emp -> Supv A BA CB DC EC
[Code]....
Finally, to my question. It seems that I can detect the problem After it happens but do I need a trigger on the update statement to detect if/when a possible circular reference will occur?? or can I run a sql statement prior to update to detect possible circular reference?
Is it possible to create a .sql script that, when executed, will determine which OS (i.e. Windows or Linux) Oracle is being hosted on? At the moment, all our scripts are written for Windows and I believe that for Linux the slashes must point the other way in order for the script to run.
Or, would the easiest thing be to create two copies of the script - one for Windows and one for Linux? :)
I have code that creates a sequence and a select statement that generates the next number in a sequence. It starts the sequence at 5 and every subsequent number is the previous number minus 3. The minimum value it can go to is 0. All I want is for my select statement to execute three times in a row. Is there any way I can do that?
create sequence MY_FIRST_SEQ increment by -3 maxvalue 5 start with 5 minvalue 0 nocycle;
1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname 2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration 3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1 4. Generate the last seven days batch run times 5. Generate a report into .csv format and email out 6. I have access to sqlplus and plsql developer
I need to open an explicit cursor for making a total: after I have to use the same information of that explicit cursor for dividing a column of the cursor by that total. It is not enough to open close, reopen and reclose because I just obtain one register at the same time and it is the same register two times consecutively.
I don't want to use auxiliary structures cause there are 18000 columns for 10200 rows.
FOR i IN 300..300 --18000 LOOP y:=ymax-ysize*(i+0.5);
