Fill zero in front of number

SQL Server:

select replicate('0', (10-len('123')))+'123'

Oracle:

SELECT LPad('123',10,'0') FROM dual

DB2:

values char(repeat('0',10-length('123'))||'123',10)

Statement.executeBatch() always returns an array of value -2

The elements in the array returned by the method executeBatch may be one of the following:

1. A number greater than or equal to zero -- indicates that the command was processed successfully and is an update count giving the number of rows in the database that were affected by the command's execution
2. A value of -2 -- indicates that the command was processed successfully but that the number of rows affected is unknown
   If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following:
3. A value of -3 -- indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails

reference : executeBatch

Change IP of listener

listener.ora

# listener.ora Network Configuration File: C:\oracle\product\10.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.1.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      )
    )
  )

Oracle SQL Developer avoid substitution

SET DEFINE OFF;
SELECT 'D&G' FROM DUAL;

OR

SELECT 'D&'||'G' FROM DUAL;

Hierarchical Query

Start with connect by prior 階層式查詢用法

SELECT
  s.role_id,
  s.role_name,
  s.role_base_on,
  b.role_name role_base_on_name
FROM m_usr_role s
LEFT JOIN m_usr_role b ON b.role_id = s.role_base_on

ROLE_ID                              ROLE_NAME                                          ROLE_BASE_ON                         ROLE_BASE_ON_NAME                                  
------------------------------------ -------------------------------------------------- ------------------------------------ -------------------------------------------------- 
system.administrator                 系統管理員                                          common.logon.user                    登入帳號                                            
85e9953c-00fd-4a0c-979c-7a41bd3f085a 初學者                                             231570cd-832f-40e3-b198-fb2336245926 訪客                                               
868a17dd-7d21-48a6-b2d5-f9a80313414a 初級專家                                            85e9953c-00fd-4a0c-979c-7a41bd3f085a 初學者                                             
3e9a45b1-82f5-49df-8be6-d67df54760c9 中級專家                                            868a17dd-7d21-48a6-b2d5-f9a80313414a 初級專家                                            
bd6eb9b1-05a3-4059-bc99-90258a7e8d86 高級專家                                            3e9a45b1-82f5-49df-8be6-d67df54760c9 中級專家                                            
886cd029-db55-48ca-8835-d9c2e02fccad 初級顧問                                            bd6eb9b1-05a3-4059-bc99-90258a7e8d86 高級專家                                            
0dec42e1-7de3-4a67-be15-97fbea759771 中級顧問                                            886cd029-db55-48ca-8835-d9c2e02fccad 初級顧問                                            
f5648453-32a5-42c3-8ec1-3b31d0d812fd 高級顧問                                            0dec42e1-7de3-4a67-be15-97fbea759771 中級顧問                                            
231570cd-832f-40e3-b198-fb2336245926 訪客                                                                                                                                       
common.logon.user                    登入帳號                                                                                                                                    

10 個資料列已選取

SELECT
  role_id,
  role_name,
  role_base_on
FROM m_usr_role
START WITH role_name = '高級顧問'
CONNECT BY PRIOR role_base_on = role_id

ROLE_ID                              ROLE_NAME                                          ROLE_BASE_ON                         
------------------------------------ -------------------------------------------------- ------------------------------------ 
f5648453-32a5-42c3-8ec1-3b31d0d812fd 高級顧問                                            0dec42e1-7de3-4a67-be15-97fbea759771 
0dec42e1-7de3-4a67-be15-97fbea759771 中級顧問                                            886cd029-db55-48ca-8835-d9c2e02fccad 
886cd029-db55-48ca-8835-d9c2e02fccad 初級顧問                                            bd6eb9b1-05a3-4059-bc99-90258a7e8d86 
bd6eb9b1-05a3-4059-bc99-90258a7e8d86 高級專家                                            3e9a45b1-82f5-49df-8be6-d67df54760c9 
3e9a45b1-82f5-49df-8be6-d67df54760c9 中級專家                                            868a17dd-7d21-48a6-b2d5-f9a80313414a 
868a17dd-7d21-48a6-b2d5-f9a80313414a 初級專家                                            85e9953c-00fd-4a0c-979c-7a41bd3f085a 
85e9953c-00fd-4a0c-979c-7a41bd3f085a 初學者                                             231570cd-832f-40e3-b198-fb2336245926 
231570cd-832f-40e3-b198-fb2336245926 訪客                                                                                    
8 個資料列已選取

reference : Start with connect by prior 階層式查詢用法

RETURNING : return value from query

SET SERVEROUTPUT ON;
DECLARE 
    v_column VARCHAR2(100);
BEGIN
    DBMS_OUTPUT.ENABLE;
    INSERT INTO table1(column1) VALUES ('TEST VALUE') RETURNING column1 INTO v_column;
    DBMS_OUTPUT.PUT_LINE(v_column);
END;

EXECUTE IMMEDIATE : execute string query

SET SERVEROUTPUT ON;

/* execute query */
DECLARE
BEGIN
    EXECUTE IMMEDIATE 'SELECT CURRENT_DATE FROM DUAL';
END;

/* execute query & set value */
DECLARE
    v_time DATE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    EXECUTE IMMEDIATE 'SELECT CURRENT_DATE FROM DUAL' INTO v_time;
    DBMS_OUTPUT.PUT_LINE(v_time);
END;

/* execute query with parameter */
DECLARE
    v_emp_id VARCHAR2(20);
    v_name VARCHAR2(20);
BEGIN
    v_name := 'Bruce';
    v_emp_id := '00987';
    EXECUTE IMMEDIATE 'UPDATE employee SET employee_name = :1 WHERE employee_id = :2'
    USING v_name, v_emp_id;
    COMMIT;
END;

/* execute procedure */
DECLARE
    v_start_index NUMBER := 24;
    v_end_index NUMBER:= 587;
    v_sum NUMBER;
    v_status NUMBER(1,0);
BEGIN
    DBMS_OUTPUT.ENABLE;
    EXECUTE IMMEDIATE 'BEGIN get_lot_amount(:1, :2, :3); END;'
    USING IN v_start_index, IN v_end_index, OUT v_sum, IN OUT v_status;

    IF v_status = 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR');
    END IF;
END;

Java: ResultSet on store procedure

Oracle

CREATE OR REPLACE PROCEDURE get_result (
    start_pattern IN VARCHAR2,
    end_pattern IN VARCHAR2,
    result_set OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN result_set FOR
        SELECT
            sheet_id
        FROM checksheet_with_process
        WHERE
            sheet_id >= start_pattern
            AND sheet_id <= end_pattern
        ORDER BY sheet_id DESC;

END;

-- test 
SET SERVEROUTPUT ON;
DECLARE
result_set SYS_REFCURSOR;
sheet_id VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.ENABLE;
    get_sheet_print_list('20100923','20101023',result_set);
    
    LOOP
        FETCH result_set INTO
            sheet_id
        ;
        EXIT WHEN result_set%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(sheet_id);
    END LOOP;
END;

ProcedureResult.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ProcedureResult {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("connection string");
            stmt = conn.prepareCall("CALL get_result(?,?,?)");
            stmt.setString(1, "20101001");
            stmt.setString(2, "20101031");
            stmt.registerOutParameter(3, oracle.jdbc.driver.OracleTypes.CURSOR);
            stmt.execute();
            rs = (ResultSet) stmt.getObject(3);

            while (rs != null && rs.next()) {
                System.out.println(rs.getString("sheet_id"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e2) {
                    // TODO Auto-generated catch block
                    e2.printStackTrace();
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }

}

Cursor with parameter

SET SERVEROUTPUT ON;
DECLARE 
    id_start VARCHAR2(200);
    CURSOR get_old_id(id_pattern VARCHAR2) IS
        SELECT
        column1
        FROM table1
        WHERE column1 LIKE id_pattern
        ORDER BY column1 ASC
    ;

BEGIN
    DBMS_OUTPUT.ENABLE;
    OPEN get_old_id('2010%');
    FETCH get_old_id INTO id_start;
    DBMS_OUTPUT.PUT_LINE(id_start);
    CLOSE get_old_id;
END;

DBMS_OUTPUT in Oracle SQL Developer

SET SERVEROUTPUT ON FORMAT WRAPED;
BEGIN
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.PUT_LINE('Hello oracle!');
END;

Cumulative sum

SELECT * FROM pg_account;
 
SELECT
    s.pg_id,
    s.pg_name,
    s.pg_entry,
    SUM(NVL(c.pg_entry,0)) cumulative,
    ROUND(s.pg_entry / (SELECT SUM(pg_entry) FROM pg_account)*100, 5) percentage,
    ROUND(SUM(NVL(c.pg_entry,0))/(SELECT SUM(pg_entry) FROM pg_account)*100,5) cumulative_percentage
FROM pg_account s, pg_account c
WHERE s.pg_id > c.pg_id OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry)
GROUP BY s.pg_id, s.pg_name, s.pg_entry
ORDER BY s.pg_id
;
 
SELECT
    s.pg_id,
    s.pg_name,
    s.pg_entry,
    COUNT(c.pg_id) ranking
FROM pg_account s, pg_account c
WHERE s.pg_entry < c.pg_entry OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry)
GROUP BY s.pg_id, s.pg_name, s.pg_entry, s.pg_entry
ORDER BY s.pg_entry DESC
;

PG_ID                  PG_NAME              PG_ENTRY               
---------------------- -------------------- ---------------------- 
1                      Bruce                22                     
2                      James                72                     
3                      Yilin                65                     
4                      Ted                  77                     
5                      Charles              35                     
6                      Sean                 43                     
7                      Paul                 57                     
8                      Ken                  35                     
8 個資料列已選取

PG_ID                  PG_NAME              PG_ENTRY               CUMULATIVE             PERCENTAGE             CUMULATIVE_PERCENTAGE  
---------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
1                      Bruce                22                     22                     5.41872                5.41872                
2                      James                72                     94                     17.73399               23.15271               
3                      Yilin                65                     159                    16.00985               39.16256               
4                      Ted                  77                     236                    18.96552               58.12808               
5                      Charles              35                     271                    8.62069                66.74877               
6                      Sean                 43                     314                    10.59113               77.3399                
7                      Paul                 57                     371                    14.03941               91.37931               
8                      Ken                  35                     406                    8.62069                100                    
8 個資料列已選取

PG_ID                  PG_NAME              PG_ENTRY               RANKING                
---------------------- -------------------- ---------------------- ---------------------- 
4                      Ted                  77                     1                      
2                      James                72                     2                      
3                      Yilin                65                     3                      
7                      Paul                 57                     4                      
6                      Sean                 43                     5                      
5                      Charles              35                     6                      
8                      Ken                  35                     6                      
1                      Bruce                22                     8                      
8 個資料列已選取

Select/update table where trigger against, ORA-04091 error occurs

CREATE OR REPLACE
TRIGGER udpate_sheet_processing 
AFTER INSERT ON sheet_item 
FOR EACH ROW


DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 自治事務 
    v_sheet_id VARCHAR2(50);
    v_processing_entry NUMBER(5);
    CURSOR get_processing_amount IS
        SELECT count(*)+1
        FROM sheet_item
        WHERE sheet_id = v_sheet_id
    ;
    
BEGIN
    v_sheet_id := :NEW.sheet_id;

    OPEN get_processing_amount;
    FETCH get_processing_amount INTO v_processing_entry;
    CLOSE get_processing_amount;

    UPDATE product_checksheet
    SET sheet_processing_entry = v_processing_entry
    WHERE sheet_id = v_sheet_id;
    
    COMMIT;  -- commit for update
END;

OracleDBConsole service start failed @ host IP | name changed.

When host ip, or name changes, an error occurs.

It occurs emctl start service error. To solve this error, step by flowing:

set oracle_sid=mes

mes is the sid of oracle.

emctl start dbconsole

Then, a message "OC4J Configuration issue. C:\oracle\product\10.1.0\Db_1/oc4j/j2ee/OC4J_DBConsole_10.1.3.121_mes not found." is displayed. Copy the fold %ORACLE_HOME%\product\10.1.0\Db_1\oc4j\j2ee\OC4J_DBConsole_cci-r3_mes to %ORACLE_HOME%\product\10.1.0\Db_1\oc4j\j2ee\OC4J_DBConsole_10.1.3.121_mes and try start service again.

emctl start dbconsole

Another message "EM Configuration issue. C:\oracle\product\10.1.0\Db_1/10.1.3.121_mes not found." is returned. Copy fold %ORACLE_HOME%\product\10.1.0\Db_1\cci-r3_mes to %ORACLE_HOME%\product\10.1.0\Db_1\10.1.3.121_mes. and restart service again. Service will start successfully.

Date function : system date

Oracle

SELECT CURRENT_DATE FROM DUAL;
SELECT SYSDATE FROM DUAL;

Oracle Date Functions : http://psoug.org/reference/date_func.html

Oracle SQL Server TransactSQL TSQL Transact PL/SQL

Oracle SQL Server TransactSQL TSQL Transact PL/SQL
Oracle Datatypes

Get all table's name from db

Sql server

SELECT * FROM INFORMATION_SCHEMA.TABLES

Access

SELECT * FROM MSYSOBJECTS

MySQL

SHOW TABLES

Oracle

SELECT * FROM USER_OBJECTS

Paging

Sql server

Assume:

@PageSize is the row counts displayed on page

@CurrentPage is the page number

SELECT TOP @PageSize * FROM Table WHERE ID NOT IN
    (SELECT TOP @PageSize*@CurrentPage ID FROM Table ORDER BY ID DESC)
ORDER BY ID DESC

Oracle

Assume:

@MaxRowNum means the max row to query

@MinRowNum means the min row to query

SELECT * FROM 
    (SELECT rownum, * FROM
        (SELECT * FROM Table WHERE SomeColumn = Something ORDER BY SomeColumns)
    WHERE rownum <= @MaxRowNum)
WHERE rownum >= @MinRowNum

↑better performance on visit first serval pages

SELECT * FROM Table WHERE rowind IN
    (SELECT rid FROM
        (SELECT rownum rno, rowid rid FROM
            (SELECT rowid FROM Table WHERE SomeColumn = Something ORDER BY SomeColumns)
        WHERE rownum <= @MaxRowNum)
    WHERE rno >= @MinRowNum)

↑better performance on higher page numbers

SELECT * FROM 
    (SELECT owner, table_name, row_number() OVER(ORDER BY table_name) rownumber FROM dba_tables)
WHERE rownumber >= @MinRowNum AND rownumber <= @MaxRowNum

Select random row from database

Sql server

SELECT TOP 1 * FROM Table WHERE SomeColumn = something ORDER BY NEWID()

ACCESS

SELECT TOP 1 * FROM Table WHERE SomeColumn = something ORDER BY RND(ColumnName)

MySQL

SELECT * FROM Table ORDER BY RAND() LIMIT 1

Oracle

SELECT * FROM (SELECT * FROM Table ORDER BY dbms_random.value) WHERE rownum = 1;