Generate Last Seven Days Batch Run Times

Jan 8, 2013

I would the query to do the following:

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

select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...

View 3 Replies


ADVERTISEMENT

Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days

Sep 2, 2011

I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).

View 7 Replies View Related

SQL & PL/SQL :: How To Convert Days Into Weeks And Days (if Any) Oracle 9i

Apr 5, 2011

want to write Procedure to return weeks and days from given set of days.

let suppose we have 72 days to return weeks and days then return should be 7 weeks and 2 days.Can i use date function Or ?

View 5 Replies View Related

PL/SQL :: How To Sum Times Value

Mar 18, 2013

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

1.30 -- One hour and thirty min.
2.45
3.50

View 20 Replies View Related

SQL & PL/SQL :: How Many Times Do The Inner Execute

Oct 2, 2010

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?

View 10 Replies View Related

SQL & PL/SQL :: How To Get Minutes Between Two Times

Oct 19, 2012

i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00

so in this it should return 360 minutes.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Rows N Times In A Result Set

Mar 9, 2010

How to get rows N times in a result set?

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".

View 2 Replies View Related

SQL & PL/SQL :: Dealing With Times Recorded In UTC

Oct 8, 2011

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 ?

View 6 Replies View Related

How To Make A Command Loop Three Times

Jun 28, 2013

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;

select MY_FIRST_SEQ.NEXTVAL from dual;

View 5 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

SQL & PL/SQL :: Populate Column Two Times With Different Criteria?

Mar 22, 2011

I am trying to query O/P from two tables.Hence, populate same column(Level_Name) two time on report based on its Level

Table =Level_Mst

Codeno Level_Name Parent_level
1 IT Dir 0
2 HR Dir 0
3 Assets Section 1
4 Payroll Section 2

Table=Users

User_id Name Top_level Bottom_Level
1 John 1 3
2 Smith 2 4

Desired O/P

Name Top_lvl Bottom_lvl Top_lvl_Name Bottom_Lvl_Name
John 1 3 IT Dir Assets_Section

Right now im getting name from level_mst either for top_level or for bottom_level .I want to show both names in one row.

View 6 Replies View Related

SQL & PL/SQL :: How To Fetch Two Times Same Explicit Cursor

Nov 25, 2012

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);

[Code]......

View 6 Replies View Related

SQL & PL/SQL :: Run Query Multiple Times With Time Gap

May 25, 2013

I need to run the below query and display the result every one hour.

Below code giving result only once.

I think my method is wrong.

SQL> declare
2 counter number :=0;
3 item varchar(300);
4 BEGIN
5 FOR item IN (

[Code]...

PL/SQL procedure successfully completed.

View 12 Replies View Related

Replication :: Job Failing Several Times In TOAD

Jan 25, 2011

In TOAD,I see one of my jobs is failing several times. However, I could refresh it manually.I found the following error. What is the cause of error and the way to rectify this.

ORA-23404: refresh group "ORA-23404: refresh group "schema_name"."job_name" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
"."" does not exist.

View 4 Replies View Related

PL/SQL :: Dates And Times In A Subquery With A Group By

Dec 20, 2012

I have some data that I need to group to the Month, Day, Year, Hour and minute in a subquery. Then I need to summarize it to the Month and year in the parent query.If I try to group by the field itself, it is not taking it down to hour and minutes - just the day, so I am losing records.if I do a TO_char (visitdate, 'DD-MON-YY HH:MI AM') in the subquery, then the main query no longer sees it as a date, so cannot do my TO_CHAR(VISITDATE,'MON-YYYY') in the parent. I could parse out the pieces using string manipulation, but that seems rather silly.Is there a way to keep as a date in my sub query and then convert to a string?

it looks a little like this, with some other fields that I have to max, sum ...

visit     provider     person     visitdate
1     2     1     12/20/2012 10:30
2     2     2     12/20/2012 10:30
3     2     5     12/20/2012 11:30
4     3     3     12/21/2012 11:30
5     3     4     12/21/2012 11:30

I need to boil this down to

provider     visitdate
2     12/20/2012 10:30
3     12/21/2012 11:30
2     12/20/2012 11:30

Then I use that in a subquery where I use just the month and year TO_CHAR(VISITDATE,'MON-YYYY') AS APPT_MO_YR right now if I do a group by visitdate on the subquery it returns

provider     visitdate
2     12/20/2012
3     12/21/2012

even if I do a group by to_date(visitdate, 'DD-MON-YY HH:MI AM') it is still returning :

provider     visitdate
2     12/20/2012
3     12/21/2012

View 4 Replies View Related

How To Copy Set Of Rows N Times And Append Them With Different Value For A Column

May 27, 2011

Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.

View 8 Replies View Related

Performance Tuning :: Number Of Times Sql Had Been Executed / Run?

Oct 25, 2011

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?

View 3 Replies View Related

PL/SQL :: Sample Function Returns Many Times Of Expected Value

Nov 30, 2012

The SAMPLE clause in the select statement works well in most cases, but we found in some instances the result is way off - between 200% to 700% discrepancy has been observed.

For example, we have thee tables with the following results:

Table1: 495,365,317 rows (20 cols, unique primary key present), SAMPLE ( 0.002018712182064212 ) returns 41,499 (about four times off - we expected about 10,000)
Table2: 3,350,864,539 rows ( 5 cols, unique primary key present), SAMPLE ( 0.00029843044634040336 ) returns 9,835 (this is good as it is close to 10,000)
Table3: 6,974,724,543 rows ( 5 cols, no unique primary key present), SAMPLE ( 0.00014337483779250091 ) returns 58,789 (about six times off - we expected about 10,000)

The tables got billions of rows, and that is why we want to do sampling. The sample percentage rate is computed to return about 10,000 rows in all three tables.On Table3, we ran the sampling three times in one occasion, and we got "58,570", "24,575" and "24,561"

I expected +/- 20% of variance, but 200% to 700% seems to be way too much.Once again, I stress that it does work well in most cases (another 3.4 billion table and numerous smaller tables we tested were well within +/- 5 percent of the target).I noted the presence of a primary key above because I read an article saying that the SAMPLE function relies on the existence of a primary key (which does not quite explain the examples above).Is this kind of spread something we should expect or is it a bug? Is the sampling rate too small for such large tables?

View 2 Replies View Related

Forms :: FRM-40209 - Field Must Be Of Form XX-XX-XXX Coming Several Times

Oct 3, 2012

I have a form which has a Number field. I set it's Format Mask property to 9,99,99,99,99,999 so that I can format it comma separated and to restrict the user to enter decimal values. If the user enter any format other than this for example any decimal values, it is showing the message FRM-40209:Field must be of the form 9,99,99,99,99,999 which is fine. But when the user enters a decimal value and try to save the form it pop up this message several times.

I tried to catch this error in ON-ERROR trigger and display a message. But this is also pop up several times. I tried to raise form_trigger_failure also. This is also not working. I want this error message to come only once.

View 2 Replies View Related

Performance Tuning :: ASH Report - SQL Has Been Executed For 1567 Times?

Sep 12, 2012

In ASH report

there is a section that goes like this

SQL ID Planhashed Sampled # of Executions % ActivityEvent% Event Top Row Source
fdy93qpr1227 1567 7.58direct path read 3.65TABLE ACCESS - FULL

does it suggest that this SQL has been executed for 1567 times is this correct .

View 7 Replies View Related

Backup & Recovery :: How Many Times To Recover (TSPITR) A Tablespace

Feb 7, 2011

How many times i can do recover (TSPITR) a tablespace ? While trying to recover tspitr tablespace on 2nd time i got error message ORA-01178 file 13 created before last create controlfile,but first time it was success.

View 1 Replies View Related

An Iteration Count Of Zero Specified For Statement Action - Specify Number Of Times?

Aug 2, 2013

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.

View 2 Replies View Related

SQL & PL/SQL :: Carriage Return Line Feed Multiple Times In Same CLOB

Jan 31, 2012

I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:

insert into table(Data) values (MyConc)

(MyConc is a string put togheter by another application)

Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?

My CLOB-data should look something like this:

1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;

And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]

where [X]=the character I need for CRLF =)

View 11 Replies View Related

Performance In Batch Run

Sep 15, 2012

We are having a major issues with the batch run. we are using oracle 11g db. We run the scripts to populate the tables and then call scripts to run the extractions. The issue here each time we run the sql it takes so much inconsistent time.We have created index and run the db stats then run the extractions.The sql sometimes takes 10 minutes or sometimes takes hours to run? This is major show stopper of the project.

View 3 Replies View Related

Windows :: PL/SQL With Batch File

Aug 17, 2012

I am executing multiple PL/SQL files(.sql) with single batch file. The batch file sql.bat has got 3 sub sql sub-tasks to complete once its run. The sql.bat is show below

@Echo off

CD C:Report
echo Loadin tables from text file Report.txt
sqlplus security/password <c:Reportloader_security.sql
echo Creating Security table
sqlplus security/password <c:Reportcreating_security_final.sql
echo Inserting text file Security table
sqlplus security/password <c:Reportinsert_security_final.sql

PAUSE

The sql.bat runs perfectly if I double click on the sql.bat file separately. But if I call the sql.bat from a different batch file 'Final.bat' it throws the below error.

Error
-----------
Executing SQL commands and loading file into SQL tables
Loadin tables from text file Report.txt
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[code].....

The Final.bat file calls other bat files too. It is as show below.

CD C:ReportSecurity
echo Merging all Files
CALL merge.bat

CD C:ReportSecurity
echo Deleting old files
CALL del.bat

CD C:ReportSecurity
echo Executing SQL commands and loading file into SQL tables
CALL sql.bat

View 11 Replies View Related

SQL & PL/SQL :: Batch ID From Child Status

Mar 27, 2013

Im looking for a query which returns the batch for which all the child should either be in 'A_STATUS','B_STATUS' or 'C_STATUS'. In this query im expecting a query which returns batch 2,3 and 4.

create table batch (batchid number);
insert into batch values(1);
insert into batch values(2);
insert into batch values(3);
insert into batch values(4);

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: How To Run A Batch File In Oracle

Aug 18, 2009

I have a .bat file in my client system,which will open a web page after executing(after double clicking on it).I want to execute the same batch file from my pl/sql block.So,after executing my pl/sql block that .bat file should execute,and it should open the same web page.

View 12 Replies View Related

Updating Millions Of Rows In A Batch

Nov 9, 2011

I have a table which has plenty of rows. In production, I would estimate it to be from 30 millions to 300 millions. I need to update on column (flag) in all the rows (created before certain date).Now saying just:

UPDATE MyTable SET flag = 3 WHERE created < to_date('2010-10-08 23:59:59', 'YY-MM-DD HH24:MI:SS');
COMMIT;

Does not seem like a good idea - the commit-buffer would become too big.I will write a PL/SQL script for this. The question is, whether I should:

a) Update each row separately, and commit after every 10000 rows. ( WHERE RowId = [rowId] )
b) Update 10000 rows with set of dates ( WHERE rowId > [some_row_id] AND RowId < [some_row_id_2]

In the latter example the some_row_ids would naturally be fetched. The rowIds come from sequence. So which one would be more effective?I am not too familiar with PL/SQL or Oracle for that matter.

View 2 Replies View Related

Tuning SQLs In A Batch Process

Mar 18, 2013

I'm having an issue with stale optimizer statistics for some SQLs that are run in a batch process. The problem is that the process runs many times during the day - sometimes 20 to 30 times. And each time, the tables are updated, i.e. rows are inserted or deleted, etc.

So eventually the optimizer statistics for those tables become stale and the performance of the SQLs start to slow down (a lot). How best to gather the optimizer stats on the tables so they don't become stale when the batch process runs each time? The problem is that I also can't add/modify the code in the batch process because it is delivered by the vendor as is.

View 1 Replies View Related

SQLPlus Script To Run In Batch File

Nov 1, 2010

I am trying to create a batch file which will be executed with windows scheduled task. This batch file will have sqlplus script running Oracle query. I can run this query from the command prompt, no problem,

This is the sqlplus scipt to run query:
C:\Documents and Settings\testuser>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 19:54:25 2010
Copyright © 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: TEST1
Enter password:
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
TEST1@DB> spool testuserfile
here goes my query
TEST1@DB> spool off
TEST1@DB> exit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
C:\Documents and Settings\testuser>

How do I put this in a batch file. I was able to figure how to logon, but I get stuck at the spool command. My batch file starts like this:

cd C:\Documents and Settings\testuser
sqlplus TEST1/Password

View 1 Replies View Related







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