I crearted this script which runs fine from SQL Navigator.But When I use it under User Defined DDL, I am getting a runtime error. The error is TEMP_TABLE_OCI: ORA-00911: invalid character
How can I make it work ?
here is rthe script I am using
CREATE TABLE SCT003_CASE_BNFT_TEMP (
GRP_CASE_ID DECIMAL(38) NOT NULL,
POL_NUM VARCHAR(7),
PRTPCNT_NUM DECIMAL(7),
COMPASS_UPDTS_CMPLT_FLG CHAR(1),
PLN_TYP VARCHAR(3),
PLN_SEQ_NUM DECIMAL(3),
GRP_CVRG_ID DECIMAL(38),
C_SC_BNFT_TYPE VARCHAR(4) NOT NULL,
SHRT_NM VARCHAR(200) NOT NULL,
CASE_KEY DECIMAL(15) NOT NULL,
CONT_NO VARCHAR(15) NOT NULL,
BNTP_KEY DECIMAL(15) NOT NULL,
EFF_DT DATE
, PRIMARY KEY (GRP_CASE_ID, POL_NUM, PRTPCNT_NUM, PLN_TYP, PLN_SEQ_NUM, GRP_CVRG_ID, CASE_KEY, BNTP_KEY) )
;
Create Index SCT0031_Index_1 On SCT003_CASE_BNFT_TEMP ( Case_Key asc )
;
Create table& Create index under User-Defined DDL
Moderators: chulett, rschirm, roy
Those "Invalid Character" errors usually come from two things: either job parameters that don't get resolved which cause it to complain about the # or using a trailing semi-colon in SQL. The latter can work just fine in other tools but not in DataStage, hence the suggestion.
However, they are meaningful in the Before and After SQL tabs where ";;" means something different from ";" when it comes to treating them as one transaction or separate transactions. That being said, I still think it is complaining about the semi-colons. Try putting the semi-colon at the end of the line it belongs with rather than on a separate line, see if that helps.
If that still doesn't work, as a test remove the "create index" DML and make sure just the "create table" by itself works as expected. Leave off the semi-colon for that test.
However, they are meaningful in the Before and After SQL tabs where ";;" means something different from ";" when it comes to treating them as one transaction or separate transactions. That being said, I still think it is complaining about the semi-colons. Try putting the semi-colon at the end of the line it belongs with rather than on a separate line, see if that helps.
Code: Select all
CREATE TABLE SCT003_CASE_BNFT_TEMP (
GRP_CASE_ID DECIMAL(38) NOT NULL,
POL_NUM VARCHAR(7),
PRTPCNT_NUM DECIMAL(7),
COMPASS_UPDTS_CMPLT_FLG CHAR(1),
PLN_TYP VARCHAR(3),
PLN_SEQ_NUM DECIMAL(3),
GRP_CVRG_ID DECIMAL(38),
C_SC_BNFT_TYPE VARCHAR(4) NOT NULL,
SHRT_NM VARCHAR(200) NOT NULL,
CASE_KEY DECIMAL(15) NOT NULL,
CONT_NO VARCHAR(15) NOT NULL,
BNTP_KEY DECIMAL(15) NOT NULL,
EFF_DT DATE
, PRIMARY KEY (GRP_CASE_ID, POL_NUM, PRTPCNT_NUM, PLN_TYP, PLN_SEQ_NUM, GRP_CVRG_ID, CASE_KEY, BNTP_KEY) );
Create Index SCT0031_Index_1 On SCT003_CASE_BNFT_TEMP ( Case_Key asc );
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom