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

2,495 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;
/

4,616 total views, 10 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

3,431 total views, 10 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;

2,055 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”

4,942 total views, 5 views today

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


Apr 18 2013

Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS

Category: Administration,Linux & Unix,Procedure,SQLFatih Acar @ 14:08

I will use Oracle 11g R2 database and SQL Server 2008 R2 on this example. If you want to connect from Oracle to SQL Server, you can use freeTDS connection library on Oracle Linux systems. If we use Windows Server for Oracle database, we can use direct Windows ODBC Driver Manager. On the other hand, we need some requirement for odbc connection on Linux System. FreeTDS is a odbc connection library for MsSQL Server and other database systems.

I made test below systems.

Systems

  • Oracle Linux 6.x / 7.x
  • Oracle 11g / 12c
  • Windows Server 2008 R2
  • MsSQL Server 2008 R2

dblink

Steps;

  • Download freetds-stable program
  • Install unixODBC and unixODBC-devel
  • Install freetds
  • Configure freetds.conf
  • Configure odbc.ini
  • Configure init
  • Configure Listener
  • Configure tnsnames
  • Create DbLink

Download freetds-stable

Download Page, Direct Download

Install unixODBC-devel

yum install unixODBC
yum install unixODBC-devel

Install freeTDS

tar zfvx freetds-stable.tgz
cd freetds-0.91
./configure –prefix=/usr/local/freetds –with-tdsver=8.0 –enable-msdblib –enable-dbmfix –with-gnu-ld
make
make install

Continue reading “Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS”

105,161 total views, 10 views today

Tags: FreeTDS ODBC Connection, Linux Administration, Oracle, Oracle Administration, System Administration


Feb 01 2013

Random Character Generator in Oracle

Category: Procedure,SQLFatih Acar @ 18:02

Oracle dbms_random package can generate random characters in character, numeric or alphanumeric formats.

dbms_random.value(Parameter, Value)

  • U – Upper case
  • L – Lower case
  • A – Alphanumeric
  • X – Alphanumeric with upper case alphabets.
  • P – Printable characters only.

Create Random Characters

SQL> select dbms_random.random value from dual;
Value : 531879542

SQL> select dbms_random.value from dual;
Value : 1

SQL> select dbms_random.value(100,1000) valuefrom dual;
Value : 452

SQL> select dbms_random.value(100000000000, 999999999999) value from dual;
Value : 515055428760

SQL> select dbms_random.string(‘U’, 10) str from dual;
Value : KGSQLEKTSX

SQL> select dbms_random.string(‘L’, 10) str from dual;
Value = ksfkwqwerv

SQL> select dbms_random.string(‘A’, 10) str from dual;
Value : RhjE+RojjL

SQL> select dbms_random.string(‘X’, 10) str from dual;
Value : GF:E3B4GRB

SQL> select dbms_random.string(‘P’, 10) str from dual;
Value : *Yw(IK/zsj

Continue reading “Random Character Generator in Oracle”

15,844 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration


Jan 23 2013

Oracle Data File Occupancy in Oracle

Category: Administration,SQLFatih Acar @ 18:31

Size of Oracle Data Files can be max 32 GB. If you are not chech data files sizes, The system can give an error when size of data files arrive max extent. You can solve this problem with adding a new data file in tablespace. But this state is not a good. You have to recognize before has an error. For solution, you can check data files sizes with e mail notification or thirt party alert applications. Below query give to us that rate of max extent of tablespaces.

Query of Tablespaces Max Size Rate

SELECT tablespace_name,
ROUND (SUM (NVL (max_mb, 0))) allocated,
ROUND (SUM (NVL (used_mb, 0))) used,
ROUND(100*ROUND (SUM (NVL (max_free_mb, 0))) / DECODE (ROUND (SUM (NVL (max_mb, 0))), 0, 1, ROUND (SUM (NVL (max_mb, 0))))) blank_percent
FROM ( SELECT df.tablespace_name,
df.file_name file_name,
df.BYTES / (1024 * 1024) allocated_mb,
(df.BYTES – SUM (NVL (dfs.BYTES, 0))) / (1024 * 1024) used_mb,
SUM (NVL (dfs.BYTES, 0)) / (1024 * 1024) free_space_mb,
NVL (
DECODE (autoextensible,
‘NO’, DF.BYTES,
GREATEST (DF.BYTES, maxbytes)),
0)
/ (1024 * 1024)
max_mb,
DECODE (
autoextensible,
‘YES’, (df.maxbytes / (1024 * 1024)
– (df.BYTES – SUM (NVL (dfs.BYTES, 0)))
/ (1024 * 1024)),
SUM (NVL (dfs.BYTES, 0)) / (1024 * 1024))
max_free_mb,
df.autoextensible
FROM dba_data_files df, dba_free_space dfs
WHERE df.file_id = dfs.file_id(+) AND DF.BYTES / (1024 * 1024) > 0.2
GROUP BY df.tablespace_name,
df.file_name,
df.BYTES,
df.maxbytes,
df.autoextensible)
GROUP BY tablespace_name
UNION ALL
SELECT A.tablespace_name tablespace_name, round(D.mb_total) allocated,
round(SUM (A.used_blocks * D.block_size) / 1024 / 1024) used,
ROUND(100* (round(D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024)/ round(D.mb_total))) blank_percent
FROM gv$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM gv$tablespace B, gv$tempfile C
WHERE B.ts#= C.ts# and b.inst_id=c.inst_id
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total
ORDER BY blank_percent;

9,338 total views, 1 views today

Tags: Database Administration, Oracle, Oracle Administration


Jan 18 2013

Oracle ASM Disks Occupancy in Oracle

Category: Administration,SQLFatih Acar @ 17:43

If you want to show Oracle ASM Disks occupancy, you can use dictionary views. You can use below command for result.

ASM Disk Occupancy Query

SQL> select name, 100-trunc(100*free_mb/total_mb) occupancy, trunc((total_mb-free_mb)/1024) used, total_mb/1024 total from v$asm_diskgroup;

12,072 total views, 5 views today

Tags: Database Administration, Oracle, Oracle Administration


Dec 20 2012

Show Data Files Size in Oracle

Category: Administration,SQLFatih Acar @ 17:41

If you want to show tablespace and datafile size, you can use below query. Also, you can add to procedure with schedule job for notification as mail.

SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;

7,585 total views, 5 views today

Tags: Database Administration, Oracle, Oracle Administration


Next Page »