Sysdate - Insert Severity Level Of Call After Certain Amount Of Time
Dec 2, 2011
I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time, e.g. after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created.
A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level
View 1 Replies
ADVERTISEMENT
Apr 11, 2013
I have a stored procedure that stores a record containing a date field.
The syntax is
insert into audit_log values (lv_sequence, sysdate, REC_TYPE, p_pln_id, OPER_UPDATE, log_message);
This works except that the date stored in the record has a time of 00:00. This does not work well for an audit log.
How can I store a date that includes a time?
An interesting fact is...I used this same command in SQL Developer to store a record and the date stored did have a time???
View 7 Replies
View Related
Sep 20, 2013
I have to concatenate a date(not sysdate) with the system time and store it in a column having datatype as DATE... So I tried like this....
SELECT TO_CHAR (C_DATE, 'DD-MON-YYYY') || ' ' || TO_CHAR (SYSDATE, 'HH:MI:SS PM') FROM DUAL;
But while inserting the output of the above query in my table it throws error like this... ORA-01830: date format picture ends before converting entire input string Is there any possibility to achieve this.
View 6 Replies
View Related
Feb 6, 2013
I need to go on day start and end time for each day. Any function that will go on start and end time according to sysdate for that day.
View 7 Replies
View Related
Oct 4, 2012
I am using Oracle 11g .when i query
select sysdate from dualit returns the current UTC date and time as output.
But when i query
select DBTIMEZONE from dual it returns -07:00 which indicates PDT.
i am wondering is this correct behaviour, i am expecting DBTIMEZONE to give +00:00 since sysdate returns as UTC date time.'
View 5 Replies
View Related
Feb 17, 2011
I have table 'A' with column 'ID','NAME','IN_DATE','PHONE','EMAIL'
Now I have to create a trigger such that on every insert in the table 'A' the value of column 'IN_DATE' changes to sysdate.I m not good in PL/SQL
View 4 Replies
View Related
May 1, 2013
I have a requirement where i have data spread in rows which i have to pivot kind off operation to find the time taken using a Log table.
below is the existing data is present.
col0 col1 col2 col3 col4
30003695/1/2013 8:41Batch Startedproc_unalloc1345
30003705/1/2013 8:41blah proc_unalloc1345
30003715/1/2013 8:46blah proc_unalloc1345
30003725/1/2013 8:46blah proc_unalloc1345
30003735/1/2013 8:56blah proc_unalloc1345
30003745/1/2013 8:57Unalloc Endedproc_unalloc1345
[code].....
Below is what I am trying to find from the above data.
col1Unalloc Alloc Metric
13450.16 1.07 1.3
the result is Hours.Minutes.
I am trying to minus the two dates and find the time taken for a particular section( eg, unalloc ).
I tried using three union queries .
Select block,timestamp From (
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Batch Started' And col3 = 'proc_unalloc' And
Parms Like '1345'
Union
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Unalloc Ended' And col3 = 'proc_unalloc' And
[code].....
I will minus the start and end time for unalloc to get time taken for un alloc. Then i will minus end time of unalloc and end time for alloc to get time taken for alloc... like wise for metric.
View 6 Replies
View Related
Feb 27, 2012
when i tried to insert the details from oracle froms..the data inserts twice to the DB..
my table structure:
create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);
here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg
[Code]....
form insertion code..
Begin
insert into app_sri(null,'robo','Big',100);
commit
End;
the data inserted...but twice
what is the reason behind the double insertion?
View 8 Replies
View Related
Feb 8, 2012
what is the difference between after or before insert in row level trigger and statement level trigger.
View 3 Replies
View Related
Dec 17, 2012
I have 4 tables , Certain users need to (Select ,insert,delete) certain rows only based on nApplication id
All the tables have Application Id , Based on this application id user will (Select ,insert,delete)
I tried row level security DBMS_RLS.add_policy but it hangs every time when i query the data or when i try to drop the policy. Is there is any method to resolve this issue.
If only Row level security will solve this issue , give me some example
View 18 Replies
View Related
Aug 10, 2012
I've plan to use "CPU Time Per User Call" metrics.
The thresholds are:
Warning: 8000
Critical: 10000
But this alarm raise every minute.
I think it's too low.But which is the correct value to identify performance problems?
View 4 Replies
View Related
Jul 24, 2010
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
View 3 Replies
View Related
Feb 4, 2012
how to insert data in two tables in same time
table1 --master table
table2 --transaction table
View 13 Replies
View Related
Apr 29, 2013
i have emp table of 5 columns empno,ename,dept,sal,edit_timing(edit timing datatype is timestamp).and my form contains empno,ename,dept,sal. is if i insert a record into forms it automatically insert the date and timing into edit_timing columns.
View 2 Replies
View Related
Jan 4, 2011
How can i insert 50 million records at a time
View 1 Replies
View Related
May 14, 2010
I have a data like,
1) manual_temp_master
auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010
2)manual_temp_detl
auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010
[code]....
I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:
sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010
View 8 Replies
View Related
Nov 17, 2010
what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.
View 1 Replies
View Related
Jan 20, 2011
We are inserting data using JDBC (Java program) in Oracle 11gR2 DB and Timesten to oracle (using AWT cache group) . In reality insertion in oracle is faster than Timesten Cache DB.
Timesten version - TimesTen Release 11.2.1.7.0 (64 bit Linux/x86_64)
1. It is Client/Server Model.
2. Cpu has 4 core and we are using 3 core for insert the Data.
3. Perm and Temp size is big enough compare to Data Size
4. auto commit=0
5. durable commit=0
6. PassThrough=1
7. LogBufParallelism=3
8. LogPurge=1
9. LockWait = 0.1
View 3 Replies
View Related
Jan 10, 2011
I create databalock - LC_REQ_PO_ITM_DTLS...In design time Insert and update allowed for that block - YES. While i execute query, i write procedure like this
GO_BLOCK('LC_REQ_PO_ITM_DTLS');
EXECUTE_QUERY;
SET_ITEM_PROPERTY('pb_item_cd', enabled, property_false);
SET_BLOCK_PROPERTY('LC_REQ_PO_ITM_DTLS',INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY('LC_REQ_PO_ITM_DTLS',UPDATE_ALLOWED, PROPERTY_FALSE);
but when i press execute button , then data comes properly on that bloack..but i click on that block, error comes like this - 'You can not create record here '.and data disappearing..
View 3 Replies
View Related
Aug 10, 2005
I have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.
View 32 Replies
View Related
Jun 8, 2010
I want to insert multiple record in diff. table by using single query...
how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..
But i want to do this task by using single query....how can i do it?
View 4 Replies
View Related
Oct 3, 2011
I am getting error at compilation time. I want to insert images into table. The code is
declare
f_lob bfile;
b_lob blob;
begin
INSERT INTO imag VALUES(empty_blob())
return img INTO b_lob;
f_lob := bfilename( 'PIC', 'DESERT.JPG' );
dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
dbms_lob.loadfromfile( b_lob, f_lob, dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
end;
errors are
[code]
View 1 Replies
View Related
Feb 4, 2013
I am working on oracle 11g...I have one normal insert proc
CREATE OR REPLACE PROCEDURE test2
AS
BEGIN
INSERT INTO first_table
(citiversion, financialcollectionid,
dataitemid, dataitemvalue,
[code]....
I am processing 1 lakh rows.tell me the reason why bulk collect is taking more time. ? According to my knowledge it should take less time. do i need to check any parameter?
View 5 Replies
View Related
Jan 4, 2013
how can i write a trigger to insert the system clock time in the oracle form?
View 15 Replies
View Related
Oct 22, 2012
Can you take an incremental backup level 1 or level 0 without archivelogs?
syntax would bebackup as compressed backupset cummulative level 1 database.
The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.
View 20 Replies
View Related
Apr 11, 2013
between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.
which will execute first on update DML event ?
View 2 Replies
View Related
Aug 4, 2009
I am using below code to spell number in words
create or replace
function spell_number( p_number in number )
return varchar2
-- original by Tom Kyte
-- modified to include decimal places
[Code]....
The number amount is : 9899.25
I am getting the output is:
NINE THOUSAND EIGHT HUNDRED NINETY-NINE POINT TWO FIVE
But i want output should be
NINE THOUSAND EIGHT HUNDRED NINETY-NINE AND Twenty five FILS ONLY
where i need to modify the code.
Files is equal to cent as per DOLLOR currency
View 6 Replies
View Related
Jul 28, 2012
I need to put amount '0' if the row exist but has no amount in my "where " conditions. the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is : "result" tab in excel atached file.i need this result: "result 2" tab in excel atached file.
View 8 Replies
View Related
Aug 8, 2013
I need to get payment amount. For example i have one payment for 3 invoices.I need to show in my report for this payment 3 invoices:
invoice_number, invoice_amount, payment_amount.
Total payment is $10,000.
Invoice A: $5,000
Invoice B: $5,000
Invoice C: $5,000
So,
for Invoice A payment is 3,000$,
for Invoice B payment is 2,000$,
for Invoice C payment is 5,000$.
This is that i need to get.In action payment amounts are after withholding tax and i need to get amount before.
View 5 Replies
View Related
Nov 29, 2012
i want to get balanced amount based on max(suid ).
my query is
create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type,
v_area_suid in i_purchase_h.pih_area_suid%type,
v_invoice_dt in i_purchase_h.pih_invoice_date%type,
v_pih_suid in i_purchase_h.pih_suid%type,
v_type in i_purchase_h.pih_type%type) is
[code]......
my output is
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00055.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.00030.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
my output should come
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00075.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.000105.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
View 3 Replies
View Related
