[Oracle] How to reset sequence value

How-To

Example

    PROCEDURE SP_RESET_SEQUENCE AS
        seq_exists VARCHAR2(1) := 'T';
    BEGIN
        SELECT CASE WHEN COUNT(*) > 0 THEN 'T' ELSE 'F' END
            INTO seq_exists
        FROM all_sequences
        WHERE sequence_name = 'SEQ_TEST' AND sequence_owner = 'TEST' ;

        IF seq_exists = 'T'
            THEN execute immediate 'DROP SEQUENCE TEST.SEQ_TEST';
        END IF;

        execute immediate 'create sequence TEST.SEQ_TEST start with 1 increment by 1 MAXVALUE 9999 cycle';

    END SP_RESET_SEQUENCE;

[DataGrip] [Database] How to print DBMSUOUTPUT message into console

Enable DBMSUOUTPUT in the Output Panel:

[Oracle] [DataGrip] How to connect to Oracle as SYSDBA

Problem

When I try to connect to Oracle using sys, I got the following error:

How-To

Using sys as sysdba as username is working fine.

[Oracle] ORA-06592 - CASE not found while executing CASE statement

Problem

When I try to execute the following function, I got ORA-06592 - CASE not found while executing CASE statement error:

    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;

How-To

The error result from missing else clause, the function should be modified as bellows:

    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
            ELSE o_result := '';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;

[SchemaSpy] How to customize css in SchemaSpy Document ?

Requirement
If I would like to customize css file in SchemaSpy, how to edit and assign new css file when I re-generate schema document?


How-To
1. Generate schema document firstly and find out the schemaSpy.css file.

2. Edit schemaSpy.css, ex. update background color in odd tr

tr.odd td.detail {
  background-color: #eaeaea;
}

3. Place schemaSpy.css file into specific directory, then assign it into -css parameter:

java -jar "C:\schemaspy\schemaSpy_5.0.0.jar" ^
     -dp "C:\schemaspy\ojdbc6.jar" ^
     -t orathin ^
     -db TEST_DB ^
     -s ap_tax ^
     -host localhost ^
     -port 1521 ^
     -u system ^
     -p password ^
     -schemas "ODS,TS" ^
     -charset UTF-8 ^
     -o "C:\schemaspy\schema_doc" ^
     -norows -hq -css "C:\schemaspy\schemaSpy.css" ^

[Oracle] Retrieving JSON in Oracle

Problem
Assume I would like to select a CLOB column with JSON value, it looks like:

{
 "test_no":"9999",
 "year":"0108",
 "registerDate":"20191031"
}

How to select test_no value (i.e. "9999") in Oracle?


How-To
Using JSON_VALUE to fulfill this requirement:

SELECT JSON_VALUE(JSON_COLUMN, '$.test_no' RETURNING VARCHAR2) 
FROM test_table

Reference
https://www.red-gate.com/simple-talk/sql/oracle/json-for-absolute-beginners-part-4-retrieving-json-in-oracle/

[Sybase] java.lang.ClassCastException: java.sql.Timestamp cannot be cast to com.sybase.jdbc4.jdbc.DateObject

Problem
In my spring boot project, it will occur the following ClassCastException irregularly:

java.lang.ClassCastException: java.sql.Timestamp cannot be cast to com.sybase.jdbc4.jdbc.DateObject
 at com.sybase.jdbc4.tds.TdsParam.checkDateRange(TdsParam.java:1707)
 at com.sybase.jdbc4.tds.TdsParam.prepareForSend(TdsParam.java:997)
 at com.sybase.jdbc4.jdbc.ParamManager.checkParams(ParamManager.java:1192)
 at com.sybase.jdbc4.tds.Tds.language(Tds.java:1022)
 at com.sybase.jdbc4.jdbc.SybStatement.sendQuery(SybStatement.java:2557)
 at com.sybase.jdbc4.jdbc.SybPreparedStatement.sendQuery(SybPreparedStatement.java:3191)
 at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:2829)
 at com.sybase.jdbc4.jdbc.SybPreparedStatement.execute(SybPreparedStatement.java:1566)
 at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
 at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.execute(ResultSetReturnImpl.java:128)
 at org.hibernate.id.IdentityGenerator$InsertSelectDelegate.executeAndExtract(IdentityGenerator.java:87)
 at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
 at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2933)
 at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3524)
 at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
 at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:637)
 at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
 at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
 at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
 at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:318)
 at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:275)
 at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:182)
 at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:113)
 at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:67)
 at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:189)
 at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:132)
 at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:58)
 at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:783)
 at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:768)
 at sun.reflect.GeneratedMethodAccessor253.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:305)
 at com.sun.proxy.$Proxy162.persist(Unknown Source)

How-To
When I remove DYNAMIC_PREPARE=false parameter from my datasource url, ClassCastException does not happen so far.
But the root cause is still unknown.

[SQL Server] IF EXISTS update else insert

Scenario
If I would like to implement a SQL statement to fulfill the following requirement, how to do it?

How-To
You can utilize if exists..else to do, here has an example:

  IF EXISTS (select * from TEST where ID = :id)
    UPDATE TEST 
    SET USER_ID = :userId, 
 USER_NAME = :userName, 
 LAST_UPDATE_TIME = getdate() 
    WHERE ID = :id
  ELSE
    INSERT INTO TEST (ID, 
                      USER_ID, 
            USER_NAME, 
        CREATE_TIME, 
        LAST_UPDATE_TIME)
               VALUES (:id, 
         :userId, 
         :userName, 
         getdate(), 
         getdate())
  ;

[Database] [Sybase] arithmetic overflow error

Problem
When I try to sum up a integer column in Sybase, I get arithmetic overflow error, how to fix it?

SELECT SUM(columnname) 
FROM tablename

How-To
Simply cast this column to big integer can solve this problem, the updated query SQL statement is as bellows;

SELECT SUM( cast(columnname as BIGINT)) 
FROM tablename

[Sybase] How to get the difference between two days

Problem
I have a datetime column in Sybase table, if I would like to calculate the number of second between this column and current datetime. How to do it?


How-To
Using datediff to calculate the number of seconds between two date.
The syntax and parameters are:

The SQL statement is as bellows:

SELECT DATA_TIME, datediff(ss, DATA_TIME, getdate()) as DIFF
FROM TEST;

Reference
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X47295.htm

[Database] [Sybase] How to implement trim() in Sybase?

Problem
If I would like to remove unnecessary space from a column value, how to do it in Sybase?

Sybase does not have trim function which provide by Oracle.

Error: Function 'trim' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).

SQLState:  S1000
ErrorCode: 14216

How-To
You can use ltrim and rtrim to fulfill this requirement:

select ltrim(rtrim('   123aa       '))

[SQL Developer] How to configure SQL Developer to connect to Sybase database?

Problem
How to configure SQL Developer to connect to Sybase database?


How-To
1. Download jTDS from https://sourceforge.net/projects/jtds/files/

2. Launch SQL Devloper, Tools => Preferences

3. Choose Database => Third Party JDBC Drivers, and add jTDS jar file

4. Close and re-launch SQL Developer. Then you can see SQLServer and Sybase tab in New Database Connection dialog.

[SQL Developer] How to change language to English in SQL Developer?

Problem
I downloaded SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

As I launch SQL Developer, the default language is Traditional Chinese:

How-To
Close SQL Developer and edit ide.conf which will be found in \sqldeveloper\ide\bin

Add the following configuration at the end of ide.conf and save it.

# Set language to en
AddVMOption -Duser.language=en

Launch SQL Developer again

Reference
[1] https://stackoverflow.com/questions/7768313/how-can-i-change-the-language-to-english-in-oracle-sql-developer

[Database] [Sybase] datetime-related example

Here has some examples:

-- get current timestamp
select getdate();

-- cast datetime string to datetime
select cast('2018-07-13 23:59:59' as datetime);

-- get year from datetime
select year(getdate());

-- get month from datatime
select month(getdate());

-- get day from datetime
select day(getdate());

-- get datetime string with YYYY-MM-DD hh:mm:ss format
select str_replace(CONVERT (VARCHAR, getdate(), 23), 'T', ' ') ;

-- add 3 days
select dateadd(day, 3, getdate());

-- minus 10 days
select dateadd(day, -10, getdate());

-- add 2 hours
select dateadd(hh, 2, getdate());

-- minus 3 hours
select dateadd(hh, -3, getdate()) 

-- get current datetime and set its time to 0:0:0
select cast(substring(convert(varchar, getdate(), 23), 1, 10) + ' 0:0:0' as datetime);

[SchemaSpy] How to generate SQL Server schema document via SchemaSpy

Steps
1. Download SchemaSpy jar from http://schemaspy.org/
2. Downnload and install Graphviz from https://www.graphviz.org/
3. Download JDBC jar file for your target database
4. Execute command as bellows:

java -jar "F:\lib\schemaspy-6.0.0-rc2.jar" -dp "F:\sqljdbc_6.0\sqljdbc42.jar" -t mssql05 -db MyDB -host 192.168.0.1 -port 1433 -u user -p secret -charset UTF-8 -o "F:\Schema" -hq 

Commonly used parameters

• -jar: The location of schemaspy jar file
• -dp: NLooks for drivers here before looking in driverPath in [databaseType].properties. The drivers are usually contained in .jar or .zip files and are typically provided by your database vendor.
• -t: Type of database (e.g. ora, db2, etc.)
• -db: Name of database to connect to
• -host: Databas URL to connect to
• -port: Database port
• -u: Valid database user id with read access.
• -p: Valid password
• -charset: Assign character set
• -o: Directory to write the generated HTML/graphs to
• -hq: Generate either higher-quality diagrams. 

[PostgreSQL] org.postgresql.util.PSQLException: ERROR: operator does not exist: text = bytea

Problem
When I try to execute the following select SQL statement:

1 2 3 4 5

select case when (domain_projects <> :domain_projects) then true else false end as result from project where domain_classifier = true and id = :projectId and domain_projects is not null

I get this error message:

  org.postgresql.util.PSQLException: ERROR: operator does not exist: text = bytea
  Hint: No operator matches the given name and argument type(s). 
  You might need to add explicit type casts.

How-To
The problem result from the first line:

1 2 3 4 5

select case when (domain_projects <> :domain_projects) then true else false end as result from project where domain_classifier = true and id = :projectId and domain_projects is not null

I need to cast the parameter to specific data type as per instructions. Therefore, the SQL statement should be modified as bellows:

1 2 3 4 5 6

select case when (domain_projects <> cast(:domain_projects as text)) then true else false end as result from project where domain_classifier = true and id = :projectId and domain_projects is not null