Home » SQL & PL/SQL » SQL & PL/SQL » Finding the time diffrence (on rhel5)
Finding the time diffrence [message #393112] Fri, 20 March 2009 06:08 Go to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Hi my intension is to find the time taken unfortunately my query is generating an error
create or replace pROCEDURE SP_LOADER
IS
O_ID 		VARCHAR2(10);
O_STATUS	VARCHAR2(02);
stime		  timestamp;
etime               timestamp;
end_time           timestamp;
BEGIN

stime :=systimestamp;

INSERT INTO Quote(Id,Name,AccountId,ActualShipDate,ContactId,DueDate,EndDate,ItemProductId,QuoteNumber,RequestedShipDate,Revision,SalesRep,ServiceLineTotal,ServiceTotal,BilltoContactId,BilltoAddressId,ShipToContactId,ShipToAddressId,StartDate,Status,StatusOrder,StatusType,Comments)
(SELECT Id,Name,AccountId,ActualShipDate,ContactId,DueDate,EndDate,ItemProductId,QuoteNumber,RequestedShipDate,Revision,SalesRep,ServiceLineTotal,ServiceTotal,BilltoContactId,BilltoAddressId,ShipToContactId,ShipToAddressId,StartDate,Status,StatusOrder,StatusType,Comments from Quote_load);


INSERT INTO QuoteItem (Id,LineNumber,PartNumber,ProductId,Quantity,QuantityRequested,UnitofMeasure)
(SELECT Id,LineNumber,PartNumber,ProductId,Quantity,QuantityRequested,UnitofMeasure from QuoteItem_load);

sELECT ID,STATUS INTO  O_ID,O_STATUS FROM qUOTE ;

INSERT INTO wbia_jdbc_eventstore (event_id,xid,object_key, object_name,object_function, event_priority, event_status,event_comment) values
(seq_wbia_jdbc.nextval,O_ID,'N.pkey','MI_SalesOrderBG','Create', 1,1,case O_STATUS
											 when '1' then 'CREATE'
											 when '2' then 'UPDATE'
											 when '3' then 'DELETE'
											 else 'INVALID' end);

etime :=systimestamp;

SELECT SUBSTR(stime,1,30) "Time1",

       SUBSTR(etime,1,30) "Time2",

       SUBSTR((etime-stime),1,30) "Time2 - Time1"  into end_time

 FROM dual;



commit;
end;


Here i known that i messed up with my substraction(select statement) please correct me
Re: Finding the time diffrence [message #393114 is a reply to message #393112] Fri, 20 March 2009 06:15 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

>>Hi my intension is to find the time taken unfortunately my query is generating an error

What error message ?? Please exact error message from your sesssion.

Post your DDL (Table) Script.

Babu
Re: Finding the time diffrence [message #393115 is a reply to message #393114] Fri, 20 March 2009 06:17 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Here are my errors
30/1 	PL/SQL: SQL Statement ignored
36/2 	PL/SQL: ORA-00947: not enough values 
Re: Finding the time diffrence [message #393117 is a reply to message #393115] Fri, 20 March 2009 06:23 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

ORA-00947: not enough values 
Cause: This error occurs when a SQL statement requires two sets of values equal in number, 
but the second set contains fewer items than the first set. 
This can occur in a WHERE or HAVING clause in which a nested SELECT returns too few columns as in:

WHERE (A,B) IN (SELECT C FROM ...) 

Another common cause of this error is an INSERT statement in which the VALUES or SELECT 
clause does not contain enough values needed for the INSERT, as in


INSERT INTO EMP(EMPNO,ENAME) VALUES('JONES') 
 
Action: Check the number of items in each set and change the SQL statement to make them equal.  


Babu

[Updated on: Fri, 20 March 2009 06:25]

Report message to a moderator

Re: Finding the time difference [message #393126 is a reply to message #393117] Fri, 20 March 2009 06:53 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
changed the program still getting errors
create or replace pROCEDURE SP_LOADER
IS
O_ID 		VARCHAR2(10);
O_STATUS	VARCHAR2(02);
stime		  timestamp;
etime               timestamp;
end_time           timestamp;
BEGIN

stime :=systimestamp;

INSERT INTO Quote(Id,Name,AccountId,ActualShipDate,ContactId,DueDate,EndDate,ItemProductId,QuoteNumber,RequestedShipDate,Revision,SalesRep,ServiceLineTotal,ServiceTotal,BilltoContactId,BilltoAddressId,ShipToContactId,ShipToAddressId,StartDate,Status,StatusOrder,StatusType,Comments)
(SELECT Id,Name,AccountId,ActualShipDate,ContactId,DueDate,EndDate,ItemProductId,QuoteNumber,RequestedShipDate,Revision,SalesRep,ServiceLineTotal,ServiceTotal,BilltoContactId,BilltoAddressId,ShipToContactId,ShipToAddressId,StartDate,Status,StatusOrder,StatusType,Comments from Quote_load);


INSERT INTO QuoteItem (Id,LineNumber,PartNumber,ProductId,Quantity,QuantityRequested,UnitofMeasure)
(SELECT Id,LineNumber,PartNumber,ProductId,Quantity,QuantityRequested,UnitofMeasure from QuoteItem_load);

sELECT ID,STATUS INTO  O_ID,O_STATUS FROM qUOTE ;

INSERT INTO wbia_jdbc_eventstore (event_id,xid,object_key, object_name,object_function, event_priority, event_status,event_comment) values
(seq_wbia_jdbc.nextval,O_ID,'N.pkey','MI_SalesOrderBG','Create', 1,1,case O_STATUS
											 when '1' then 'CREATE'
											 when '2' then 'UPDATE'
											 when '3' then 'DELETE'
											 else 'INVALID' end);

etime :=systimestamp;
select etime-stime into end_time  from dual;
commit;
end;


========================error==========================================
29/1 PL/SQL: SQL Statement ignored
29/13 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got IN TERVAL DAY TO SECOND
==============================================================
please help me with calculating the diffrence
Re: Finding the time difference [message #393130 is a reply to message #393126] Fri, 20 March 2009 07:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hint:

If you subtract "Friday" from "Saturday", you do NOT get "Monday", you get "One Day"
Re: Finding the time diffrence [message #393132 is a reply to message #393112] Fri, 20 March 2009 07:53 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
I think that timestamp-timestamp might return a number - so you might try an explicit conversion TO_YMINTERVAL.
Please, check that and tell if that works.
I know that DATE-DATE results in a non-integer number which is in fact the number of seconds between them but expressed in days. Example:
SELECT (SYSDATE-TRUNC(SYSDATE))*24*60*60 FROM DUAL
The query should return the number of seconds in a day that have already passed since its beginning.
Re: Finding the time difference [message #393135 is a reply to message #393126] Fri, 20 March 2009 08:09 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#i1002765

Regards

Raj
Previous Topic: Multiple records in one row
Next Topic: Invalid cursor while inserting rows
Goto Forum:
  


Current Time: Sat Dec 10 12:48:09 CST 2016

Total time taken to generate the page: 0.04447 seconds