ORACLE Open Command

[JSWARBRI]

I'm trying to use the Open Command to perform a delete on an oracle table as truncate privileges have not been given for the DB. But I cannot get it to work.

The statement I am entering is:

'DELETE FROM #GDSSSchemaOrcl#.T1_NDS_OFF_LINKS'

and these are the errors I'm getting:

OCI_T1_NDS_OFF_LINKS,1: Oracle call failed: sqlcode = -900
Message: ORA-00900: invalid SQL statement

OCI_T1_NDS_OFF_LINKS,1: ExecuteImmediate failed for:
'DELETE FROM DBADSSDEV.T1_NDS_OFF_LINKS'

OCI_T1_NDS_OFF_LINKS,1: Failure during execution of operator logic

OCI_T1_NDS_OFF_LINKS,1: Fatal Error: Invalid open command : 'DELETE FROM DBADSSDEV.T1_NDS_OFF_LINKS'

Can anyone help with this??

Cheers

[Jay]

Check if u have given all the connection parameters to the database....user/pwd/remote server (database name)....

Jay

[JSWARBRI]

Hi Jay,

Yes, all the parameters are defined. It is a job that was working, until the DBA took away all truncate privileges

Thanks

[ArndW]

J - that means that the command works if you remove the Oracle Open 'DELETE FROM #GDSSSchemaOrcl#.T1_NDS_OFF_LINKS'?

What if you replaced the parameter in the open command with the Oracle schema name directly ? (perhaps that part of the command doesn't parse parameters correctly)

[JSWARBRI]

Yes, its a working job, until I put the DELETE statement in.

It is parsed correctly, as one of the error messages above mentions the parameter:

OCI_T1_NDS_OFF_LINKS,1: ExecuteImmediate failed for:
'DELETE FROM DBADSSDEV.T1_NDS_OFF_LINKS'

[dsxdev]

hi JSWARBRI,

Try to execute the command
'DELETE FROM DBADSSDEV .T1_NDS_OFF_LINKS'

as it is and see if it executes.
If it is fine then ok.

[JSWARBRI]

Hi dsxdev,

I tried this in SQL Plus and it works fine. However, it still does not work in the DataStage job.

Thanks.

[mjgmc]

Hello,

I need to do exactly the same, and I get exactly the same error. Did anyone find the answer to this?

Thanks,

mjgmc

[prabu]

Hi dsxdev,

I tried this in SQL Plus and it works fine. However, it still does not work in the DataStage job.

Thanks.

are you sure to have executed it with the single quotes as you have posted initally. try to give some statement like

Code: Select all

select * from dual 

in the open command and please let me know if it works.

[prabu]

I'm trying to use the Open Command to perform a delete on an oracle table as truncate privileges have not been given for the DB. But I cannot get it to work.

Cheers

truncate and delete are having a HUGE differene depending on the database (atleast in Oracle, yes). if its a staging table, the DBA shouldn't mess around with it. i have never seen a fact table truncated before for loading unless you don't want to maintain history :D . IMHO, Speak with your DBA to understand why he has revoked the truncate permission instead of sneaking the delete code . logically delete and truncate does the same thing, that's cleaning up. hope to see your DBAs view on this strage restriction.

[kriskkumar]

Oracle call failed; sqlcode = -900; message: ORA-00900: invalid SQL statement.

Tried to use all of hte following stmts in the open command of oracle enterprise stage (DS 7.5.1) and get the above error, did anyone get a resolution on this ?

'DELETE FROM TABLE1'
'DELETE FROM TABLE1;'
"DELETE FROM TABLE1"
"DELETE FROM TABLE1;"
DELETE FROM TABLE1


I am using the Load and append options.


Thank you,

[ray.wurlod]

Is it your table? That is, are you the creator? Otherwise fully qualify the table name.

Code: Select all

DELETE FROM #Schema#.TABLE WHERE 1 = 1;

(It may be that DELETE without WHERE has been barred, so include a WHERE condition that is always true to delete all rows.) The trailing semi-colon may not be needed.

[jreddy]

I am having the same problem as well.. i have a oracle enterprise stage reading source data from a table.. but before i read the table, i want to perform an update on the records.. so i give the update statement in the OPEN command option..
as 'update table_name set cur_ind='Y' where <condition>'

i even tried just a simple sql as suggested
'delete from table_name where 1=2'

both these throw the same error as others have mentioned in this topic

Oracle call failed; sqlcode = -900; message: ORA-00900: invalid SQL statement
ExecuteImmediate failed for:
'delete from stg_ps_xlat where 1=2;'.
Failure during execution of operator logic.
Invalid open command : 'delete from stg_ps_xlat where 1=2;'.

Could someone advise if they were able to use open command successfully - perhaps i have the syntax wrong??

[ray.wurlod]

Have you tried without the semi-colon?

Have you tried with the table name qualified with its owner name?

[jreddy]

Yes Ray,

I actually did not put the semi-colon.. but in the director log, in the error messages i see that it was added by default when it was evaluated.. i tried job parameters and i also tried qualifying it with the schema/user name, inspite of being the owner for that table...

thanks