Jump to content

oracle compitable create table


zohab

Recommended Posts

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)
);

Link to comment
Share on other sites

  • 4 weeks later...

"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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.