Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
2020/10/03

[Oracle] months_between

›
若我想知道兩個日期間差幾個月，可運用 months_between 此內建 function 來完成，若要做四捨五入，可加上 round function；若要做無條件進位，可加上 ceil function。 範例如下： select months_between (...
2020/10/02

[Oracle] [Stored Procedure] Cursor

›
Assume I have a table which name Product, the data look like: SELECT ORDER_DATE, PRODUCT_ID, QTY FROM PRODUCT WHERE PRODUCT_ID = '10...
2020/10/01

[Oracle] ROW_NUMBER Function

›
Assume I have a table which name Product, the data looks like: SELECT ORDER_DATE, PRODUCT_ID, QTY FROM PRODUCT WHERE ORDER...
2020/09/10

[Oracle] [Function] LEAD and LAG

›
I have a Product table: If I would like to know  PRODUCT_ID = 1000 , and find its previous order date and next order date. We can make use o...
2020/09/09

[Oracle] [Function] LAST_DAY

›
If today is 2020/09/05, you can using the following SQL statement to get the end day of this month: SELECT to_char( last_day (to_date( ...
2020/09/07

[Oracle] How to reset sequence value

›
How-To Example PROCEDURE SP_RESET_SEQUENCE AS seq_exists VARCHAR2( 1 ) : = 'T' ; BEGIN SELECT CASE WH...
2020/09/04

[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.
2020/09/03

[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...
2020/09/01

[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? ...
2020/08/09

[Oracle] CASE Statement example

›
Requirement 以下是用來將幣別轉換為代號的邏輯 case 幣別   when 'NTD' then '01'   when 'USD' then '51'   when '...
2020/08/07

[Oracle] [SQL Developer] How to change language in Oracle SQLDeveloper?

›
Problem How to change language from Transitional Chinese to English in Oracle SQL Developer? How-To 1. Go to   C:\<SQLDeveloper Pa...
1 comment:
2020/08/06

[Oracle] ORA-02287: sequence number not allowed here

›
Problem Assume I create a sequence as bellows: create sequence seq_test start with 1 increment by 1 ; As I try to get t...
2020/08/02

[Oracle] [SchemaSpy] Catalog (-cat) was not provided and unable to deduce catalog, wildcard catalog can be used -cat %

›
Problem When I try to generate schema document via SchemaSpy, I got the following error message: Catalog (-cat) was not provided and una...
2020/07/10

[Oracle] PLS-00215: String length constraints must be in range

›
Problem When I tried to create a stored procedure package as bellows: CREATE OR REPLACE PACKAGE PG_TEST_001 AS v_last2Month ...
2020/07/08

[Oracle] Retrieving JSON in Oracle

›
Problem Assume I would like to select a CLOB column with JSON value, it looks like: { "test_no" : "9999" , "y...
2020/06/08

[Oracle] How to import dump file

›
Problem As I tried to import dump file, I got the following error: C: \U sers \u ser01 \D esktop ...
2020/06/07

[Oracle] ORA-65096: 無效的通用使用者或角色名稱

›
Problem As I tried to create a new user, I got the following error: SQL> CREATE USER test_user IDENTIFIED BY oracle; CREATE USER ...
2020/03/09

[Java] [Spring] [JDBC] [Oracle] How to call stored procedure - example 2

›
Assume I have a stored procedure as bellows: create or replace PACKAGE PACKAGE1 AS PROCEDURE TEST_EMPLOYEE ( I_GENDER ...