My requirment is something like below.
My Oracle DB server time is in UTC. which lags by 9:30 mins to IST.how to get tHe IST time (Asia/Kolkotta) time for that UTC timezone? I can not hard code +9:30 to UTC as this difference varies as per Daylight savings every 6 months.
how to get java nanotime equivalent in oracle
View 2 Replies View RelatedI've stucked with a query. I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
(
(PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID1 <> 13
union
[code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code
""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......
I need to get the output as ú (Lowercase U-acute) using oracle sql.
SQL> select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('<x>')) from dual;
where <x> represents the hexadecimal equivalent for the character 'ú' ,
what is the equivalent of Top n Percent in Oracle sql 11g. Here is my requirement:
I have to find stores contributing top 20% of sales: Store Sales PercetageABC200(200/380)*100=52%XYZ100(100/380)*100=26%PQR50(50/380)*100=13%dddd20(20/380)*100=5%rrrr10(10/380)*100=2%
In the above example I have to get only store ABC as this store alone is contributing more than Top 20%If I change the requirement to Top 70% I have to get store's ABC and XYZ.
I am upgrading from 11.2.0.1 to 11.2.0.3 (64 bit) on Windows (64 bit) I planned to install Oracle 11.2.0.3 on new home and i am not sure wheteher i have to install: Oracle Gateway and Oracle Examples on the 11.2.0.3 new home. I am not using any non - oracle so i may not need Oracle Gateway. I am not sure about examples media . Does it have only examples to learn (or)will it have any optional components which we may require (like companion CD in 10g)? What about the ODBC and com components will it be installed with the database media itself? After DB upgradation is it possible to install oracle gateway/examples on the same home?
View 2 Replies View RelatedWhat would be sybase str(approx_numeric [, length [, decimal] ]) equivalent in oracle.
Sybase call :
select str(10000.5555, 6, 3) as Test.
Tset
-------
10001
Is it possible to send again the function of equivalent of DATEADD in sql for plsql??
View 3 Replies View RelatedIs there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?
View 10 Replies View Relatedcase when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age
the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode
"php 5.3.x + oracle 11g R2 XE 11.2.x.x"...I'm quite new to oracle (2-4 months) and php(2 weeks).I have one query with results that needs to be reused in several parts of my website. I can't seem to find the equivalent of the mysql_data_seek in Oracle. I wanted to reset the cursor/pointer of oci_ fetch ($result) so that I can scroll the result again.So far this are what I have come up:
A. On first fetch put the the results in a php array and call the array later on.
B. Do the query again.
C. Keep on looking for a mysql_data_seek equivalent and fail.
I'm leaning towards option 'A' but I just wanted to consult the experts.
What is the stream's "tag" equivalent in Golden gate ?My tables are already in GG replication, but i want to do few insert in to source which i dont want to replicate to target.
View 1 Replies View RelatedI see month aging buckets in the Oracle Application I am using (Keystone time and billing). I need to do a query in directly in Oracle (Toad front end to Keystone database) using month aging buckets . I use the following in Access that matches the results in Oracle, but I need to work directly in Toad because I want to avoid having to bring over all the dates when I want to summarize by buckets.:
2-4 Months: Sum(IIf(DateDiff("m",[invoice date],forms!Reporting_Standard!txtlastdayofmonth) In (2,3),[Outstanding Gross],0))
I know about getting the difference between two dates and that works fine for day aging buckets, but I need months, which can deal with months that are different sizes.
I want to know that is there any way to know the execution time of a sql query.
View 1 Replies View RelatedCreate table time_in (
Emp_id varchar2(6),
at_date date,
time_in varchar2(6),
status varchar2(2)
)
[Code]....
-------------- required restul----------
emp_id at_date time_in at_date time_out
------ ------- ------- -------- --------
00009 16/03/2013 09:47 17/03/2013 09:45
00009 17/03/2013 09:48 17/03/2013 10:43
00009 18/03/2013 09:57
00009 19/03/2013 09:38 19/03/2013 05:22
00009 20/03/2013 09:48 20/03/2013 05:24
status 01 = time in
status 02 = time out
I'm trying to figure out, based on total scheduled shift time and scheduled breaks what the effective schedule time is by hour for a particular employee.
So I have a shift query that gives me this (using a cross-day shift here because they do happen-not sure if that will impact things):
Shift ID EmployeeID ShiftStart ShiftEnd
123 99 04/10/2013 21:00:00 04/11/2013 06:00:00
And I have a activity query that gives me
Shift ID EmployeeID ActivityStart ActivityEnd Type
123 99 04/10/2013 23:00:00 04/10/2013 23:15:00 Break
123 99 04/11/2013 02:00:00 04/11/2013 03:00:00 Lunch
123 99 04/11/2013 04:00:00 04/11/2013 04:15:00 Break
And I need a query that give me this:
Shift ID EmployeeID ShiftStart Hour Net_Scheduled_Time(min)
123 99 04/10/2013 21:00:00 04/10/2013 21:00:00 60
123 99 04/10/2013 21:00:00 04/10/2013 22:00:00 60
123 99 04/10/2013 21:00:00 04/10/2013 23:00:00 45
123 99 04/10/2013 21:00:00 04/11/2013 00:00:00 60
123 99 04/10/2013 21:00:00 04/11/2013 01:00:00 60
[Code]...
Is there a relatively easy way to get there in one query, no temp tables?
Is there a save exceptions clause or an equivalent for an Insert as select* statement ?
How do I trap the errors in the below statement -
INSERT INTO copy_emp
SELECT * FROM emp;
Is it an all or nothing scenario ?
How to find time log for query or any procedure like start time and end time and total time.
So that I can tune that queries properly.
Also how can we find estimated query running time.?
I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.
View 2 Replies View RelatedI have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.
View 6 Replies View RelatedI need to query a table to get the time difference based on different dates.
SAMPLE DATA:
JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12
in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as
01:23:12-00:56:00 and the difference should be in minutes.
for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.
I was given this query: SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
My coworker also said this:
Using this statement as a starting point:
SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
but I need
for
select -5 from dual;
to return
America/NewYork or EST
Is there a way to know how much time will a query take to execute without running it, just like using the autotrace (traceonly) and explain plan utility.
View 16 Replies View RelatedI 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.
I am facing a very strange issue with one of our Oracle query. The query is usually completes in a minute or two. Even the execution plan of the query is good and it works perfect most of the times, as expected. The query fetches about 1000-2000 records each day.
But on a given day, the query takes about 30-40 mins to execute completely. Upon checking the load on DB server, there are no other processes running which can impact the run time of this query. Moreover, the record counts fetched are almost same as compared to other days. There is no pattern observed as that this phenomenon occurs. it all happens once in a while.
Configuration is Oracle 10g with RAC environment on LINUX
My oracle database version is 11.2.0.3.0 where i am having one schema in that schema i am having 3 same tables with same structure same data but with different name.
but problem is in first table when i perform select query it takes 5 sec, in another table it is taking 0 sec and in third table it is taking 10 sec.
I have designed a form in which there are two datablocks one contains the major details and the other contains details saved under that record. And it is working properly. If you are not getting then take an example of department and employee datablocks, first contains the info of department and second contains the employee info working in that department.
now what i want to do is to make the life of user easier, as the records are displaying properly so if user want to know that this record is present in the details of that header so while scrolling all records can he search by putting some info in the detail block to get that result while remaining on the same datablock?
Need to check the elapse time for particular query ran from last 24 hours , it was ran multiple times and need to know for each execution what is elapse time .
which view we can use ?
I have 10 users in my database, I need to find the time at which a query is fired by any of the users in the database. provide the query for this? I think i have to use V$sql,v$sqlarea or v$sql.
View 1 Replies View Related