Feb 03 2017

Round Up A Number By Protecting 2 Decimal in Oracle

Category: SQLFatih Acar @ 12:04

I will show how to round up a number by protecting 2 decimal. When I use round or ceil function, I can not have correct result. I want to round a number upwards with calculate 2 decimal.

I want result like below examples.

1.2445 > 1.25
1.21 > 1.21
1.521 > 1.53
1.1 > 1.1

You can use trunc function with add 0.009 to number.

Examples

select trunc(1.224 + 0.009,2) from dual;
1.23
select trunct(1.16 + 0.009,2) from dual;
1.16
select trunct(1.521 + 0.009,2) from dual;
1.53

5,245 total views, no views today

Tags: Oracle Administration, Oracle SQL Query, SQL


Dec 08 2016

Update Another Column On The Same Table With Trigger While Updating Table in Oracle

Category: Errors and Solutions,Procedure,SQLFatih Acar @ 11:01

If you want to do update another column when execute an update on table, you can use before update trigger. If you use after update trigger, you can get some errors. if you use pragma autonomous_transaction parameter as declare in trigger you can get errors like that are “ORA-00060: deadlock detected while waiting for resource”, “ORA-06512: at “trigger_name” line 7″,”ORA-04088:error during execution of trigger “trigger_name”” if you don’t use, you can get errors like that are “ORA-04091: table tablename is mutating, trigger/function may not see it”,”ORA-06512: at trigger_name”, line 6″,”ORA-04088: error during execution of trigger trigger_name”

You can resolve this problem with using before update trigger. You can update another column of updating the same table.

Trigger Example

My example is about that If status column of table1 change from 0 to 1, I will write another columns that are name, surname and studentno at the same table table1.

create table table1 (id number, status number, name varchar2(100), surname varchar2(100), studentno varchar2(100),address varchar2(500),city varchar2(100));

create or replace trigger trg_trigger_name_upt
before update of status on table1
referencing OLD as old NEW as new
for each row
v_studentno varchar2(100);
begin

IF :old.status = 0 AND :new.status = 1 and UPDATING THEN

:new.name := ‘Fatih’;
:new.surname := ‘Acar’;
v_studentno := seq_studentno.nextval;
:new.studentno := v_studentno;

END IF;

end;
/

19,111 total views, 20 views today

Tags: Oracle Administration, Oracle Error Solutions, Oracle SQL Query


Oct 17 2016

Obtain Surname, Last Name and Mid Name From Full Name in Oracle

Category: Procedure,SQLFatih Acar @ 13:43

If you have a column with full name data and you want to split this data, you can use below function to split operation.

Split Name ve Surname

Table : test
Data1 : Emin Fatih Acar
Data2 : Fatih Acar

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘,Instr(fullname,’ ‘,-1))-1) name,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname
from test;

Result:

Data1
name : Emin Fatih
surname : Acar

Data2
name : Fatih
surname : Acar

Split Name,Midname and Surname

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘)-1) name,
substr(fullname,instr(fullname,’ ‘)+1,Instr(fullname,’ ‘,-1,1)- instr(fullname,’ ‘)-1) midname,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname,
from test;

Result:

Data1
name : Emin
midname : Fatih
surname : Acar

Data2

name : Fatih
midname : null
surname : Acar

6,716 total views, no views today

Tags: Database Administration, Oracle Administration, Oracle SQL Query


May 11 2016

Add Time Zone Offset To Timestamp in Oracle

Category: SQLFatih Acar @ 11:23

If you have to add or remove offset to timezone, you can use below sql syntax. I will use for Istanbul timezone. Database stamp timezone as UTC and not add Istanbul timezone offset when insert operation. If you have to show on reports as Istanbul time zone, you can use below SQL.

SQL> select to_char( FROM_TZ ( CAST ( datecolumn as timestamp), ‘UTC’) at time zone ‘Europe/Istanbul’), ‘dd.mm.yyyy hh24:mi:ss’) from tbl_test;

9,051 total views, no views today

Tags: Oracle SQL Query


Nov 15 2015

Auto Increment Primary Key With Trigger in Oracle 11g

Category: Administration,Procedure,SQLFatih Acar @ 14:50

You can provide auto increment primary with trigger by appling below operations.

Create Table

SQL> CREATE TABLE TESTUSER.TBL_TEST
(id number primary key,
name varchar2(100));

Create Sequence

SQL> CREATE SEQUENCE TESTUSER.SEQ_TEST_ID
START WITH 1
MAXVALUE 999999999
MINVALUE 1
CYCLE
NOCACHE
NOORDER;

Create Trigger For Auto Increment

SQL> CREATE OR REPLACE TRIGGER TESTUSER.TRG_AUTO_INC_TEST_ID
before insert on TESTUSER.TBL_TEST
for each row
begin
if inserting then
if :NEW.”ID” is null then
select TESTUSER.SEQ_TEST_ID.nextval into :NEW.ID from dual;
end if;
end if;
end;
/

Oracle 12c release has primary key auto increment feature with identity type column. You can use like below.
Continue reading “Auto Increment Primary Key With Trigger in Oracle 11g”

12,393 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle SQL Query