Using order by in column that contains special Turkish characters like ç,ğ,ö,ş,ü can be problem. All special Turkish characters can be listed at the end of the list. You can resolve this problem with NLSSORT. select * from tablename order by NLSSORT(columnname,’NLS_SORT=TURKISH’)
Category: SQL
Round Up A Number By Protecting 2 Decimal in Oracle
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…
Update Another Column On The Same Table With Trigger While Updating Table in Oracle
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:…
Obtain Surname, Last Name and Mid Name From Full Name in Oracle
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…
Add Time Zone Offset To Timestamp in Oracle
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(…
Auto Increment Primary Key With Trigger in Oracle 11g
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…
Oracle DbLink With Odbc Connection From Oracle to MsSQL Server With FreeTDS
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,…
Random Character Generator in Oracle
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;…
Oracle Data File Occupancy in Oracle
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…
Oracle ASM Disks Occupancy in Oracle
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;