Create table& Create index under User-Defined DDL

[sarjushah]

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

[chulett]

Try getting rid of the last semi-colon.

[sarjushah]

[quote="chulett"]Try getting rid of the last semi-colon.[/quote]

I tried that. But getting the same error TEMP_TABLE_OCI: ORA-00911: invalid character

[chulett]

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.

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

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.

[ray.wurlod]

Don't you need a double semi-colon between multiple commands?

[chulett]

I don't believe so, from what I recall of the documentation either are valid with the difference that I noted above.

[Sainath.Srinivasan]

What if you remove the CREATE INDEX command ?

[Sainath.Srinivasan]

What if you remove the CREATE INDEX command ?