Help Center/ GaussDB(DWS)/ More Documents/ Tool Guide (Kuala Lumpur Region)/ DSC: SQL Syntax Migration Tool/ Oracle Syntax Migration/ Schema Objects/ Database Keywords
Updated on 2023-03-17 GMT+08:00
View PDF
Share

Database Keywords

DSC supports GaussDB(DWS) keywords, such as NAME, LIMIT, OWNER, KEY, and CAST. These keywords must be enclosed in double quotation marks.

Gauss Keywords (NAME, VERSION, LABEL, POSITION)

The keywords NAME, VERSION, LABEL, and POSITION are changed to ASKeyword.

Input – NAME, VERSION, LABEL, POSITION

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SELECT id, NAME,label,description
        FROM (SELECT a.id             id,
                     b.NAME           NAME,
                     b.description    description,
                     b.default_label  label,
                     ROWNUM           ROW_ID
                FROM CTP_ITEM A
                LEFT OUTER JOIN CTP_ITEM_NLS B ON A.ID = B.ID
                                              AND B.LOCALE = i_language
               ORDER BY a.id ASC)
       WHERE ROW_ID >= to_number(begNum)
         AND ROW_ID < to_number(begNum) + to_number(fetchNum);
 
SELECT DISTINCT REPLACE(VERSION,' ','') ID, VERSION TEXT
        FROM (SELECT T1.SOFTASSETS_NAME, T2.VERSION
                FROM SPMS_SOFT_ASSETS T1, SPMS_SYSSOFT_ASSETS T2
               WHERE T1.SOFTASSETS_ID = T2.SOFTASSETS_ID)
          WHERE SOFTASSETS_NAME = I_SOFT_NAME;
 
SELECT COUNTRY, AMOUNT
          FROM (SELECT '' COUNTRY || '' AMOUNT, '1' POSITION
                  FROM DUAL )
         ORDER BY POSITION;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
SELECT id,NAME,label,description FROM (
 SELECT a.id id,b.NAME AS NAME,
 b.description description
 ,b.default_label AS label,
 ROW_NUMBER( ) OVER( ) ROW_ID
 FROM CTP_ITEM A LEFT OUTER JOIN 
 CTP_ITEM_NLS B
 ON A.ID = B.ID AND 
 B.LOCALE = i_language
 ORDER BY a.id ASC) WHERE
 ROW_ID >= to_number( begNum )
 AND 
 ROW_ID < to_number( begNum ) + to_number( fetchNum )
;
 
SELECT
  DISTINCT REPLACE( VERSION ,' ' ,'' ) ID
     ,VERSION AS TEXT
     FROM
        (
         SELECT
         T1.SOFTASSETS_NAME
         ,T2.VERSION
         FROM
        SPMS_SOFT_ASSETS T1
       ,SPMS_SYSSOFT_ASSETS T2
        WHERE
        T1.SOFTASSETS_ID = T2.SOFTASSETS_ID
       )
      WHERE SOFTASSETS_NAME = I_SOFT_NAME ;
   
 
   
SELECT COUNTRY ,AMOUNT
FROM ( SELECT '' COUNTRY || '' AMOUNT
        ,'1' AS POSITION
          FROM
         DUAL
       )
     ORDER BY
     POSITION
;

TEXT & YEAR

Input – TEXT, YEAR

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
SELECT 
  NAME, 
  VALUE, 
  DESCRIPTION TEXT, 
  JOINED YEAR, 
  LIMIT 
FROM 
  EMPLOYEE;
  
SELECT 
  NAME, 
  TEXT, 
  YEAR, 
  VALUE, 
  DESCRIPTION, 
  LIMIT 
FROM 
  EMPLOYEE_DETAILS;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
SELECT 
  "NAME", 
  VALUE, 
  DESCRIPTION AS TEXT, 
  JOINED AS YEAR, 
  "LIMIT" 
FROM 
  EMPLOYEE;
 
SELECT 
  "NAME", 
  "TEXT", 
  "YEAR", 
  VALUE, 
  DESCRIPTION, 
  "LIMIT" 
FROM 
  EMPLOYEE_DETAILS;

NAME and LIMIT

Input: GaussDB(DWS) keywords NAME and LIMIT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
CREATE TABLE NAME
      ( NAME VARCHAR2(50) NOT NULL
      , VALUE VARCHAR2(255)
      , DESCRIPTION VARCHAR2(4000)
      , LIMIT NUMBER(9)
      )
  /*TABLESPACE users*/
  pctfree 10   initrans 1   maxtrans 
  255  
     storage ( initial 256K next 256K 
     minextents 1 maxextents 
     unlimited ); 
   
 SELECT NAME, VALUE, DESCRIPTION, LIMIT
   FROM NAME;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
CREATE TABLE "NAME"
         ( "NAME" VARCHAR2 (50) NOT NULL
         , VALUE VARCHAR2 (255)
         , DESCRIPTION VARCHAR2 (4000)
         , "LIMIT" NUMBER (9)
         ) 
      /*TABLESPACE users*/ 
      pctfree 10 initrans 1 maxtrans 255 
      storage ( initial 256 K NEXT 256 K minextents 1 
      maxextents unlimited );

 SELECT "NAME", VALUE, DESCRIPTION, "LIMIT"
   FROM "NAME";

OWNER

Bulk Operations

Input: Use SELECT to query the GaussDB(DWS) keyword OWNER

1
2
3
4
 
SELECT
          owner
     FROM
          Test_Col;

Output

1
2
3
4
 
SELECT
          "OWNER"
     FROM
          Test_Col;

Input: Use DELETE to query the GaussDB(DWS) keyword OWNER

1
2
3
 
DELETE FROM emp14
     WHERE
          ename = 'Owner';

Input

1
2
3
 
DELETE FROM emp14
     WHERE
          ename = 'Owner'

KEY

Blogic Operations

Input: GaussDB(DWS) keyword KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 parallel_enable IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp18 RW ( RW.empno ,RW.ename ) SELECT
               res ,RWN.ename KEY
          FROM
               emp16 RWN ;
               COMMIT ;
          RETURN res ;
END ;
/

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp18 ( empno ,ename ) SELECT
               res ,RWN.ename "KEY"
          FROM
               emp16 RWN ;
               /* COMMIT; */
          null ;
          RETURN res ;
END ;

Range, Account and Language

When Gauss keywords are used as aliases for any column in the SELECT list without defining the AS keyword, the AS keyword to define the aliases.

Input

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
CREATE
     OR REPLACE /*FORCE*/
     VIEW SAD.FND_TERRITORIES_TL_V (
          TERRITORY_CODE
          ,TERRITORY_SHORT_NAME
          ,LANGUAGE
          ,Account
          ,Range
          ,LAST_UPDATED_BY
          ,LAST_UPDATE_DATE
          ,LAST_UPDATE_LOGIN
          ,DESCRIPTION
          ,SOURCE_LANG
          ,ISO_NUMERIC_CODE
     ) AS SELECT
               t.TERRITORY_CODE
               ,t.TERRITORY_SHORT_NAME
               ,t.LANGUAGE
               ,t.Account
               ,t.Range
               ,t.LAST_UPDATED_BY
               ,t.LAST_UPDATE_DATE
               ,t.LAST_UPDATE_LOGIN
               ,t.DESCRIPTION
               ,t.SOURCE_LANG
               ,t.ISO_NUMERIC_CODE
          FROM
               fnd_territories_tl t
     UNION
     ALL SELECT
               'SS' TERRITORY_CODE
               ,'Normal Country' TERRITORY_SHORT_NAME
               ,NULL LANGUAGE
               ,NULL Account
               ,NULL Range
               ,NULL LAST_UPDATED_BY
               ,NULL LAST_UPDATE_DATE
               ,NULL LAST_UPDATE_LOGIN
               ,NULL DESCRIPTION
               ,NULL SOURCE_LANG
               ,NULL ISO_NUMERIC_CODE
          FROM
               DUAL ;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
CREATE
     OR REPLACE /*FORCE*/
     VIEW SAD.FND_TERRITORIES_TL_V (
          TERRITORY_CODE
          ,TERRITORY_SHORT_NAME
          ,LANGUAGE
          ,CREATED_BY
          ,CREATION_DATE
          ,LAST_UPDATED_BY
          ,LAST_UPDATE_DATE
          ,LAST_UPDATE_LOGIN
          ,DESCRIPTION
          ,SOURCE_LANG
          ,ISO_NUMERIC_CODE
     ) AS SELECT
               t.TERRITORY_CODE
               ,t.TERRITORY_SHORT_NAME
               ,t.LANGUAGE
               ,t.CREATED_BY
               ,t.CREATION_DATE
               ,t.LAST_UPDATED_BY
               ,t.LAST_UPDATE_DATE
               ,t.LAST_UPDATE_LOGIN
               ,t.DESCRIPTION
               ,t.SOURCE_LANG
               ,t.ISO_NUMERIC_CODE
          FROM
               fnd_territories_tl t
     UNION
     ALL SELECT
               'SS' TERRITORY_CODE
               ,'Normal Country' TERRITORY_SHORT_NAME
               ,NULL AS LANGUAGE
               ,NULL CREATED_BY
               ,NULL CREATION_DATE
               ,NULL LAST_UPDATED_BY
               ,NULL LAST_UPDATE_DATE
               ,NULL LAST_UPDATE_LOGIN
               ,NULL DESCRIPTION
               ,NULL SOURCE_LANG
               ,NULL ISO_NUMERIC_CODE
          FROM
               DUAL ;

Primary Key and Unique Key

If primary and unique keys are declared on table creation, only the primary key needs to consider for migration.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
create table SD_WO.WO_DU_TRIGGER_REVENUE_T
(
  TRIGGER_REVENUE_ID NUMBER not null,
  PROJECT_NUMBER     VARCHAR2(40),
  DU_ID              NUMBER,
  STANDARD_MS_CODE   VARCHAR2(100),
  TRIGGER_STATUS     NUMBER,
  TRIGGER_MSG        VARCHAR2(4000),
  BATCH_NUMBER       NUMBER,
  PROCESS_STATUS     NUMBER,
  ENABLE_FLAG        CHAR(1) default 'Y',
  CREATED_BY         NUMBER,
  CREATION_DATE      DATE,
  LAST_UPDATE_BY     NUMBER,
  LAST_UPDATE_DATE   DATE
)
;  

alter table SD_WO.WO_DU_TRIGGER_REVENUE_T
  add constraint WO_DU_TRIGGER_REVENUE_PK primary key (TRIGGER_REVENUE_ID);
alter table SD_WO.WO_DU_TRIGGER_REVENUE_T
  add constraint WO_DU_TRIGGER_REVENUE_N1 unique (DU_ID, STANDARD_MS_CODE);

Output

CREATE
     TABLE
          SD_WO.WO_DU_TRIGGER_REVENUE_T (
               TRIGGER_REVENUE_ID NUMBER NOT NULL
               ,PROJECT_NUMBER VARCHAR2 (40)
               ,DU_ID NUMBER
               ,STANDARD_MS_CODE VARCHAR2 (100)
               ,TRIGGER_STATUS NUMBER
               ,TRIGGER_MSG VARCHAR2 (4000)
               ,BATCH_NUMBER NUMBER
               ,PROCESS_STATUS NUMBER
               ,ENABLE_FLAG CHAR( 1 ) DEFAULT 'Y'
               ,CREATED_BY NUMBER
               ,CREATION_DATE DATE
               ,LAST_UPDATE_BY NUMBER
               ,LAST_UPDATE_DATE DATE
               ,CONSTRAINT WO_DU_TRIGGER_REVENUE_PK PRIMARY KEY (TRIGGER_REVENUE_ID)
          ) ;

PROMPT

PROMPT should be converted to \ECHO supported by GAUSS.

Oracle Syntax

Syntax after Migration

 
prompt
prompt Creating table product
prompt ===============================
prompt
create table product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50)
);
 
\echo
\echo Creating table product
\echo ===============================
\echo
CREATE TABLE product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50)
);
Parent topic: Schema Objects