ORACLE Open Command
Moderators: chulett, rschirm, roy
ORACLE Open Command
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
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
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)
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)
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
are you sure to have executed it with the single quotes as you have posted initally. try to give some statement likeJSWARBRI wrote:Hi dsxdev,
I tried this in SQL Plus and it works fine. However, it still does not work in the DataStage job.
Thanks.
Code: Select all
select * from dual
Re: ORACLE Open Command
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.JSWARBRI wrote: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
-
- Participant
- Posts: 4
- Joined: Tue Jun 05, 2007 7:54 am
Oracle call failed; sqlcode = -900; message: ORA-00900: inva
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,
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,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Is it your table? That is, are you the creator? Otherwise fully qualify the table name.
(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.
Code: Select all
DELETE FROM #Schema#.TABLE WHERE 1 = 1;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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??
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??
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: