oracle compitable create table

[zohab]

Hi ,

 

I have create table statement compitable with Postgresql and I want to make it oracle compitable

 

It gives error with oracle DB.

 

CREATE TABLE HT_SALARY
(
  HT_SALARY_ID  CHARACTER VARYING(32)		NOT NULL,
  AD_CLIENT_ID  CHARACTER VARYING(32)		NOT NULL,
  AD_ORG_ID     CHARACTER VARYING(32)		NOT NULL,
  ISACTIVE      CHARACTER(1)                   NOT NULL        DEFAULT 'Y',
  CREATED       TIMESTAMP WITHOUT TIME ZONE 	NOT NULL 	DEFAULT now(),
  CREATEDBY     CHARACTER VARYING(32)		NOT NULL,
  UPDATED       TIMESTAMP WITHOUT TIME ZONE 	NOT NULL 	DEFAULT now(),
  UPDATEDBY     CHARACTER VARYING(32)         	NOT NULL,
  C_BPARTNER_ID CHARACTER VARYING(32)         	NOT NULL,
  AMOUNT	 NUMERIC 	      	        NOT NULL,
  C_CURRENCY_ID VARCHAR(32)         	        NOT NULL,
  VALIDFROM     TIMESTAMP WITHOUT TIME ZONE    NOT NULL,
    CONSTRAINT HT_SALARY_ISACTIVE_CHECK CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
    CONSTRAINT HT_SALARY_KEY PRIMARY KEY (HT_SALARY_ID), 
    CONSTRAINT HT_SALARY_AD_ORG FOREIGN KEY (AD_ORG_ID)
     REFERENCES AD_ORG (AD_ORG_ID), 
    CONSTRAINT HT_SALARY_AD_CLIENT FOREIGN KEY (AD_CLIENT_ID)
     REFERENCES AD_CLIENT (AD_CLIENT_ID),
    CONSTRAINT HT_SALARY_C_BPARTNER FOREIGN KEY (C_BPARTNER_ID)
     REFERENCES C_BPARTNER (C_BPARTNER_ID), 
    CONSTRAINT HT_SALARY_C_CURRENCY FOREIGN KEY (C_CURRENCY_ID)
     REFERENCES C_CURRENCY (C_CURRENCY_ID)
);

[Adam]

"CHARACTER VARYING(32)" is not a valid Oracle data type; you'd need to use varchar2(32) - assuming the 32 in yours means bytes. now() is also not a valid function, you'd need to use sysdate - without brackets - however that's probably not in the correct date format. You can combine it with to_char to set the format:

 

to_char(sysdate, 'YYYY-MM-DD')

 

I belive this would also fail:

 

CONSTRAINT HT_SALARY_ISACTIVE_CHECK CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))

 

You'd need to use the standard SQL IN operator, supported by PL/SQL.. Although I'm not sure the syntax will be anything like the same in Oracle.

 

There may be other issues but see how that changes things.