Oracle

• Get substring in a value: SUBSTR('ABCDEFG', 3, 4)

  • 3 -> start_index

  • 4 -> length

• Time Difference Between Two Consecutive Rows: 

select

    lag(READ_DATE) over (order by READ_DATE) as read_start_date,

    READ_DATE as read_finish_date,

    round((READ_DATE - lag(READ_DATE) over (order by READ_DATE))*24*60*60, 0)  as timestamp_diff_secs

from READ_CSV_FILES_LIST

order by READ_DATE desc;    

• Limit rows: where rownum <= 10

• How to create temporary table to make the query much easier to read: 

  • WiTH TEMP_TABLE AS ( Select * from some_table)

  • SELECT * FROM TEMP_TABLE;

• Get only month, year number etc. from a date:  EXTRACT (MONTH FROM myDate)

• Learn Oracle version from SQL query: SELECT * FROM v$version;

• Now in Oracle: SYSDATE

• Now minus 1 minute: where LOG_TIME > sysdate - (INTERVAL '1' HOUR)

• Split string according a character and take the 4th occurence: regexp_substr('ali.veli.deli.kulaklari.kupeli','[^.]+', 1, 4)

• How to extract group from regular expression in Oracle: regexp_substr('alivelikirdokuzelli', 'ali(.+)kirk', 1, 1, NULL, 1)   (the number as last parameter denotes which group to take)

• Epoch to DateTime (for TR):

  • TO_DATE('19700101','yyyymmdd') + (epochColumn/24/60/60/1000) + 3/24

  • TO_TIMESTAMP('1970-01-01 03:00:00.0', 'YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(CHANNEL_VIEW_ID/1000, 'SECOND')

• Filtering with LIKE in Oracle SQL Developer: LIKE '%asd%'

• BLOB to string conversion: utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD))

• Format decimal places: REPLACE(REPLACE(TO_CHAR(price), '99999999.99'), ' ', ''), '.', ',')

• Format date as sting: TO_CHAR(myDateColumn,  'YYYYMMDDHH24MISS')

• How to get all table names in a schema: SELECT DISTINCT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'AHMET' ORDER BY 1;

• How to find Oracle Service Name: select * from global_name;

• How to find all tables with particular column name: select owner as schema_name, table_name, column_name from all_tab_columns where UPPER(column_name) LIKE '%FOO%' order by 1, 2, 3;

• String Concatenation: SELECT column1 || column2 FROM XTABLE

• Oracle SQL Developer'da bir tabloyu CTRL+A ile seçtikten sonra CTRL+C yapıp da excel'e yapıştırınca başlıklar da gelmez. Gelsin isterseniz CTRL+SHIFT+C

• Bir grup içinde, bir sütuna göre sıralayıp istediğin satırı alabilmek için: Row_number() OVER (partition BY customerID ORDER BY createDate DESC) AS myRowNum

• Oracle SQL Developer ile şifre resetleme için:

  • https://drive.google.com/open?id=1DFO9C85b99bwBUZMCk6PhnwHbBmdb6patvxcP7Co6N4

  • NOT: Şifreyi değiştirince (reset edince yani) kayıtlı connection'ı edit edip şifreyi değişmeye gerek yok, kendi yapıyor bunu.

  • NOT: Şifreniz expire olmuş ise yeni şifre belirleme işlemini Oracle SQL Developer ile yapabilirsiniz (DB Admin'in vs bunu yapması şart değil)

• Procedural language name used by Oracle is PL-SQL

• String to date:  TO_DATE('2017-12-22,15:46:33', 'YYYY-MM-DD,HH24:MI:SS')

• Geçici olarak Date Time vs. format değiştirme:

  • alter SESSION set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS' 

  • alter SESSION set NLS_TIMESTAMP_FORMAT = 'DD-MM-YYYY HH24:MI:SS.FF3'

• Convert columns into rows with LISTAGG: SELECT STUDENT_NAME, LISTAGG(GRADE,',') WITHIN GROUP(ORDER BY GRADE DESC) AS GRADES FROM STUDENT_GRADE GROUP BY STUDENT_NAME

• PC kapanırsa Oracle SQL Developer'dan sorgu kurtarma: Ne yazıkki en son açık pencereler ve içindeki sorguları geri getiremezsin ancak F8 ile SQL History penceresini getirebilirsin ki bu da işine yarar.

• Oracle'da bir kolon için sadece numeric karakter içerenleri fitreleme: where regexp_like( columnName , '^[[:digit:]]*$' )

• Simple PL/SQL foreach loop in Oracle Sql Developer: 

  • SET SERVEROUTPUT ON

  • BEGIN

  •   FOR employees IN (SELECT NAME FROM EMPLOYEE)

  •   LOOP

  •     dbms_output.put_line( employees.name );

  •   END LOOP;

  • END;

• If you are experiencing that stupid redraw issue in SQL Developer like below:

• • • Solution: Open windows RUN menu by pressing WindowsKey+r. Enter %AppData%. Go to this folder: \Roaming\SQL Developer\system???\o.sqldeveloper\product-preferences.xml file. Make sure it is like this:

<hash n="DisplayOptions">

      <value n="rightMarginColumnAtWrap" v="false"/>

   </hash>

   <hash n="environment-options">

      <value n="lafClass" v="com.sun.java.swing.plaf.windows.WindowsLookAndFeel"/>

      <value n="verifyICachedFileData" v="true"/>

   </hash>