SQL & PL/SQL :: Display Time Format In Oracle By Create View

Nov 16, 2011

I am not able to find any information regarding inserting data with timestamp format without putting the date.

This is what i want to do:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';

I create the column data type as timestamp with time zone. This is value i have inserted:

Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);

And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:

Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;

But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.

BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:

Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;

Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?

View 3 Replies


ADVERTISEMENT

PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time

Feb 6, 2013

Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.

View 2 Replies View Related

Replication :: Oracle 10g - Create Materialized View

Jun 16, 2008

We are using Oracle 10g rel 2. The replication is setup on 1 server which is in City A, and the snapshot server is in City B.

City A .
Create materialized view log on table-a
with primary key
including new values.

City B database.
Create user test_rep identified by test
grant connect, resource, create any materialized view , table, view , procedure to test_rep.

Create materialized view city-A_db_MV
refresh fast
select * from cityA.Tablea@city-Adb

When i select from city-A_db_MV, it showed the complete table-A of city-A database.

Now if we make any changes to City-A table at the Master site, will it be propagated automatically to the MV site.

I guess we need to create jobs to push / or refresh fast .. isnt it. But exactly how to do it is a question.

Secondly if we make a replication group at Master site at city-A db, how do we refresh that Group and how to monitor whether it is refreshing on time or not? do we need to see the jobs every now and then.

but still a lot of questions unanswered, even though i had read the documents earlier.

1-The MView was created without identifying that after what interval it will be fast refreshed.
2- How to Manually refresh it. Does it support On Commit, I think it is not.
3- Where should be we make a group and then add the table to that group and refresh that group.

Should this group belongs to the Master Site or to MV site?

View 1 Replies View Related

SQL & PL/SQL :: Using Create View And Joining Multiple Tables In Oracle

Apr 8, 2012

I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.

Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.

CREATE TABLE DIVISION
(div_code NUMBER(5),
emp_num NUMBER(5),
div_name VARCHAR(25),
CONSTRAINT pk_division primary key (div_code));
[code].......

SELECT - this SELECT statement is supposed to generate the output of the CREATE VIEW CODE above. SEE Crows Foot diagram attached.

View 9 Replies View Related

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

Oct 8, 2010

is it possible to create primary key on view and use this view for creating foreign key .

View 3 Replies View Related

SQL & PL/SQL :: Display Data In Grouping Format

Oct 13, 2010

I have a query on displaying data as per my requirement. I have created a table called sales it has four columns

create table sales(country,state,district,sales);
and am inserting some same data

insert into sales('india','TN','Chennai',100);
insert into sales('india','TN','KPURAM',120);
insert into sales('india','TN','Bangalore',35);
insert into sales('india','ANDR','Guinder',100);
insert into sales('india','ANDR','Nellai',76);
insert into sales('london','city-a','xstreet',89);
insert into sales('london','city-a','binroad',100);

select * from sales;

country state district sales
india TN chennai 100
india TN KPURAM 120
india TN Bangalore 35
india ANDR Guinder 100
india ANDR Nellai 76
london city-a xstreet 89
london city-a binroad 100

the data is displayed in this format. How i am trying to display data.

View 5 Replies View Related

SQL & PL/SQL :: Update Display Name Based On Format?

May 3, 2013

I have a table which has columns like First_Name,Last_Name and Display_Name. Now the every entry in the table have first name and last name populated .

I would like to populate the display name based on the format Display Name = First Name+ " " + Last Name.

For eg. If the first name John and last name is Doe , then the display name should be John Doe.write such a queries which dynamically picks up all the rows and populate the display name.

View 12 Replies View Related

SQL & PL/SQL :: To Display Data In Range Format

Apr 25, 2012

I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.

create table ot_shop_Rec ( item varchar2(12), it_name varchar2(20),rev number, qty number)
drop table ot_shop_rec

insert into ot_shop_rec values ( '1018001-1001', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1002', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1001', 'GRID',0,10);
insert into ot_Shop_rec values ('1018001-1003','COL',0,10);

I WANT THE OUTPUT IN REPORT LIKE

ITEM RANGE DESC QTY REV

1018001-1001 - 1018001-1003 COL 30 0
1018001-1001 GRID 10 0

View 5 Replies View Related

Wrong Number Format Display Using DBLink

Jan 7, 2013

I have an issue trying to execute some queries using a dblink. When i run any query with numeric fields only display 4 digit and int the source database the fields have 5 digit. The dblink work between ans MSSQL database to an Oracle Database

Example:
MSSQL
select cardnumber from card
cardnumber
19121
19122

Oracle (with dblink)
select cardnumber from card@dblink1
cardnumber
1912
1912

View 1 Replies View Related

PL/SQL :: Date Format - Display Fraction Of Seconds

Oct 8, 2012

select to_char(SYSDATE,'MMDDYYYYHHMISS') from dual;

Ouput:
10082012010338

It is giving me correct output, but i want to display even the Fraction of seconds, how can i?

I have tried this..but not working..

select to_char(SYSDATE,'MMDDYYYYHHMISSFF') from dual;

Output: Error.

View 4 Replies View Related

SQL & PL/SQL :: Display Data In Required Format By Select Query?

Dec 12, 2012

i like to display a Table data like the below format,

Output:

EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:

where condition:
where year = 2007

Table Structure:

create table HR_PAYSLIP
(
EMP_NO VARCHAR2(6) not null,
YEAR NUMBER(4) not null,
MONTH NUMBER(2) not null,
BASIC_PAY NUMBER(9,2),
)

Insert Command;

INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);

Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month

So how to write a Query for that?

View 9 Replies View Related

Reports & Discoverer :: Display Data Vertically (landscape Format)?

Apr 19, 2013

I need the data in this below format.

I added the image...

View 2 Replies View Related

Reports & Discoverer :: Change Format Of Figure Display On Bar Graph?

Aug 18, 2009

how i can change format of figure display on bar graph(data labels) in oracle 10g graphical reports .i want to change format fom 99.99 to 9999 .

View 1 Replies View Related

SQL & PL/SQL :: UTC Date Time Should Be In Format

Mar 1, 2010

iam having a table A where column ABC is a varchar field with varchar2(50).

currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18

now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.

View 9 Replies View Related

SQL & PL/SQL :: Average Of Time In HH:MM:SS Format

Jun 17, 2010

how to calculate the average of the time in th e HH:MM:SS format stored database table.. column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as timestamp(6).

MY COL
------
1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00

[Code]...

View 29 Replies View Related

PL/SQL :: Date And Time Format

Dec 6, 2012

converting text values that I have to date and time format (without the millisecs and AM/PM values).

The following is an example:

04-DEC-12 07.47.58.000 AM

or would i need to change this in excel before importing this type of data.

View 6 Replies View Related

SQL & PL/SQL :: Date Time Format

Jan 31, 2013

I am running following statement

select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5

and It gives me the error

ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"

View 5 Replies View Related

SQL & PL/SQL :: To Store The Date And Time In GMT Format

May 13, 2010

Our data centers resides in different zones and we would like to store the date and time information in GMT format and it should aware of daylight savings.

Which data type is preferred ? Date or timestamp with timezone or timestamp with local time zone. Do we need to configure any parameter on the database?

View 1 Replies View Related

SQL & PL/SQL :: Date And Time Format Statement

Jan 11, 2012

I would like to do a select to get the current date&time in this format:

2011-10-04T04:20:35.2313103+01:00

do you know how can I do that?

I want to convert this sql statement in this format.

View 12 Replies View Related

Data In Date Format For In Time

Oct 26, 2006

i have data in date format for in time actually it should be 9:30 am and out time at 2:30 pm

SELECT MIN(IN_TIME) AS IN_TIME,MAX(OUT_TIME) AS OUT_TIME FROM EMP_ATND_DETAIL AS ET, EMP_ATND_INFO AS EAI
WHERE ET.EMP_ATND_INFO_ID=EAI.EMP_ATND_INFO_ID AND ATND_DATE = '2006-10-25' AND EMP_ID =24

+---------------------+---------------------+
| IN_TIME | OUT_TIME |
+---------------------+---------------------+
| 2006-10-20 09:30:00 | 2006-10-13 01:30:00 |
| 2006-10-24 02:30:00 | 2006-10-13 06:30:00 |
| 2006-10-24 09:30:00 | 2006-10-13 01:30:00 |
+---------------------+---------------------+

i have should get 9:30 am because that is lessser then 2:30 pm

do i have

SELECT name, emp_id,
min(to_char(IN_TIME, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(OUT_TIME, 'DD/MM/YY HH24:MI')) out_time,
FROM EMP_ATND_DETAIL

View 3 Replies View Related

Know User Account Locked Time In HH:MM:SS Format?

May 30, 2011

How we know a user account locked time in HH:MM:SS format ?

View 1 Replies View Related

SQL & PL/SQL :: How To Get Data From A Table In Date And Time Format

Jul 24, 2013

how to get data from a Table in Date and Time format? Curently I'm using To_TIMESTAMP for a date range.

Here is the code Snippet -

Select *
FROM TABLE_Name
WHERE COUNTRY= 'IN'
AND CALC_DATETIME between TO_TIMESTAMP('2013/06/05', 'YYYY/MM/DD HH:MI:SS') AND TO_DATE ('2013/07/08', 'YYYY/MM/DD HH:MI:SS')

View 39 Replies View Related

Time Format - Way To Change My Select To Creation

Jul 18, 2012

when I select I have to using AND creation_dt>='17-JUL-12' is there a way to change my select to creation_dt='17-JUL-12' ?
on meantime my local format is 18-JUL-12(18-JUL-12) is thee anything I can do?

View 5 Replies View Related

SQL & PL/SQL :: How To View Rows In Table Format Using Select Query

May 28, 2012

I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo

SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),

[code]...

But the case is I need to display the value in table format Horizontally as below

EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING

[code]...

View 14 Replies View Related

How To Display Time In Minutes

Oct 15, 2012

I have two columns which I need to add together then devide by 60 in order to display that time in minutes. the problem I am facing is that at times I get numbers above 60 and my client doesn't want to see numbers above 60.

i.e (col1 + col 2)/60 = 34.87

what I need to do is make sure that when the number reaches 60 it moves on to 35.

View 2 Replies View Related

Display Date And Time

Sep 28, 2011

I had a database at microsoft access but i had move it to the oracle database.

When i check the datatype at oracle, date column is at DATE datatype. but it only display date. like in oracle. it display:

 oracle_date.PNG ( 74.61K )
Number of downloads: 4

But when i link it to microsft access. like i will control the oracle from access. the result is at below:

 access_date.PNG ( 136.11K )
Number of downloads: 3

So, my problem is how to display the date and time fully in oracle?

View 1 Replies View Related

Sql To Display Output In Horizontal View?

Sep 8, 2012

I need to display the sql query output in horizontal manner. Below I have given the script.

create table TestTable (id number, name varchar2(10))
insert into TestTable values (1, 'John')
insert into TestTable values (2, 'Mckensy')
insert into TestTable values (3, 'Valneech')
insert into TestTable values (4, 'Zeebra')

[code]....

But for my requirement, I need to display in horizontal manner.

ID   1       2       3           4
Name John    Mckensy Valneech    Zeebra

View 3 Replies View Related

PL/SQL :: Only Display A Column Value One Time For A Record / Not A Static Value

Jan 7, 2013

I am trying to only display a column value one time for a record, not a static value. The value changes and there can be many values. I do not think grouping will work since the Date value is dynamic. I am using Oracle for Toad 10.5. There are 4 records with this test data.

select Date, Person, Language, Country
from TableATableA
Date            Person  Language   Country
01/25/2013       James   English
12/20/2012       James   English
US

[code]....

View 3 Replies View Related

Application Express :: Where To Add HTML Format For Phone No So It Dials On Touch In List View

Sep 12, 2013

In Apex 4.2 Jquery mobile list view how can you apply html format for the phone# field to make it dial with a touch.  For example, I see how to do a list view of the 'DEMO_CUSTOMER' sample data, but I cannot figure out how to format the phone # fields so that they will dial on touch.  I know the HTML tags to use and I know how to use it in a standard report, but I can't figure out list view. 

View 3 Replies View Related

Forms :: Display Country Name And Region At Time Of Navigation

Feb 10, 2011

I have developed customer entry form, its working fine. I have 2 display items on form. One is country name & other is region, these are fetched based on country id LOV.

Now I want to display country name & region at time of navigation. I have written following code on WHEN_NEW_RECORD_INSTANCE of block.

******
DECLARE
CCNTRY_ID VARCHAR2(2);
CCNTRY_NAM VARCHAR2(25);
CREGION VARCHAR2(11);
begin
select cntry_nam,region into ccntry_nam,cregion from cntry where cntry_id=ccntry_id;

exception
when no_data_found then
message('NO DATA FOUND');
end;
**********

I always get message not data found on navigation. Problem is only with the display item other, values of database columns are being shown.

View 3 Replies View Related







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