sql() Function and Oracle ORA-00911 Invalid Character

dataservices job design
1

I wrote the following SQL to create a primary key on a table in an Oracle 11g database. It executes OK in TOAD:

ALTER TABLE DM_YEAR ADD CONSTRAINT DM_YEAR_PK PRIMARY KEY (YEAR_ID);

However, in Data Services when I put it in an sql function in a script in a workflow:

sql('TARGET_DS','ALTER TABLE DM_YEAR ADD CONSTRAINT DM_YEAR_PK PRIMARY KEY (YEAR_ID);') ;

It raises a database error ORA-00911… What is wrong with it please as I cannot figure out what the invalid character is - it is the same bit of sql code that works in TOAD?!? What is Data Services doing to my working sql and sending to the database?!?


18521	1	DBS-070301	03/11/2011 09:00:21	|Session JOB_PHASE1|Work flow WF_CTRL_PRELOAD_DB_WORK|Work flow WF_PRELOAD_DB_WORK
18521	1	DBS-070301	03/11/2011 09:00:21	Oracle <DEV> error message for operation <OCIStmtExecute>: <ORA-00911: invalid character
18521	1	DBS-070301	03/11/2011 09:00:21	>.
18521	1	RUN-050304	03/11/2011 09:00:21	|Session JOB_PHASE1|Work flow WF_CTRL_PRELOAD_DB_WORK|Work flow WF_PRELOAD_DB_WORK
18521	1	RUN-050304	03/11/2011 09:00:21	Function call <sql ( TARGET_DS, ALTER TABLE DM_YEAR ADD CONSTRAINT DM_YEAR_PK PRIMARY KEY
18521	1	RUN-050304	03/11/2011 09:00:21	(YEAR_ID); ) > failed, due to error <70301>: <Oracle <DEV> error message for operation <OCIStmtExecute>: <ORA-00911:
18521	1	RUN-050304	03/11/2011 09:00:21	invalid character
18521	1	RUN-050304	03/11/2011 09:00:21	>.>.
18521	1	RUN-053008	03/11/2011 09:00:21	|Session JOB_PHASE1|Work flow WF_CTRL_PRELOAD_DB_WORK|Work flow WF_PRELOAD_DB_WORK
18521	1	RUN-053008	03/11/2011 09:00:21	INFO: The above error occurs in the context <|Session JOB_PHASE1|Work flow WF_CTRL_PRELOAD_DB_WORK|Work flow
18521	1	RUN-053008	03/11/2011 09:00:21	WF_PRELOAD_DB_WORK|sql(...) Function Body|>.
18521	1	DBS-070301	03/11/2011 09:00:21	|Session JOB_PHASE1|Work flow WF_CTRL_PRELOAD_DB_WORK|Work flow WF_PRELOAD_DB_WORK
18521	1	DBS-070301	03/11/2011 09:00:21	Oracle <DEV> error message for operation <OCIStmtExecute>: <ORA-00911: invalid character
18521	1	DBS-070301	03/11/2011 09:00:21	>.

Thanks!

Darth Services :uk: (BOB member since 2007-11-20)

2

I thought it would work as well, but obviously not. My guess is, the last semicolon is the problem. It is not part of a SQL statement, it is a formatting char of SQLPlus.

So try without:

sql('TARGET_DS','ALTER TABLE DM_YEAR ADD CONSTRAINT DM_YEAR_PK PRIMARY KEY (YEAR_ID)') ;

Werner Daehn :de: (BOB member since 2004-12-17)

3

Thanks Werner, removing the ‘;’ resolved it! :+1:

Darth Services :uk: (BOB member since 2007-11-20)

4

The semi-colon would be used if you had submitted PL/SQL script through the SQL() function. In SQLPlus you would use the slash / to indicate that the PL/SQL block is complete and should be executed. But in the SQL() function you would leave off the the / and include all the necessary semi-colons. Here is an example of a PL/SQL block used to create a table on the fly:

$LV_SQL_Stmt =                 'BEGIN ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '   BEGIN ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '      EXECUTE IMMEDIATE \'DROP TABLE WORK_INV_ACCT_KEYS\'; ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '   EXCEPTION ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '   WHEN OTHERS THEN ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '      NULL; ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '   END; ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '   EXECUTE IMMEDIATE \'CREATE TABLE WORK_INV_ACCT_KEYS ';
$LV_SQL_Stmt = $LV_SQL_Stmt ||                        '(ID NUMBER(10) NOT NULL) ';
$LV_SQL_Stmt = $LV_SQL_Stmt ||                        'CACHE NOCOMPRESS PCTFREE 0 NOLOGGING NOPARALLEL\'; ';
$LV_SQL_Stmt = $LV_SQL_Stmt || '   EXECUTE IMMEDIATE \'COMMENT ON TABLE WORK_INV_ACCT_KEYS IS \'\'Table used during the SURF stage processing of INV_ACCT This table can be removed if the ETL job is not running.\'\'\';';
$LV_SQL_Stmt = $LV_SQL_Stmt || 'END;';

eganjp :us: (BOB member since 2007-09-12)

5

Sure, because then the ; is part of the SQL prepared statement.

:+1:

Werner Daehn :de: (BOB member since 2004-12-17)