I've used the following code to delete records in an Oracle table from a fex. Trying on a different table and it doesn't seem to work:

gives me this message:
(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 0/DELETE

SQL SQLORA
DELETE FROM WORK_ORDER_DETAIL
WHERE STATUS = 'C' ;
END

I still have 52 records with STATUS = 'C'

Using 5.3.3 webfocus on Unix.

Tried this code against my database it works

SQL SQLMSS SET SERVER TEST
SQL SQLMSS
DELETE FROM TESTEMP WHERE ENO=1;
END
-EXIT

Jim,

Are you sure who have delete privilage on this table?

Thanks

Hi Jim,

Has this issue been resolved? Have you got a chance to try Kamesh's suggestion?

I did some search on online documents and came across an idea, in addtion to authorization/privilage as newtofocus suggested. Just wondering, could there be problems with the adapter that connects the Oracle database and the WebFOCUS server? If you run the SQL query from the database side (not within a fex), will you be able to delete the records?

As always, you can contact Information Builders' Customer Support Services and open a case for assistance at any time. The phone number is 1-800-736-6130, or you may access the online system InfoResponse.

Hope this helps.

Cheers,

Kerry

I can't seem to delete all the rows in the Oracle table.

-*Call the Oracle Stored Procedure
SET SQLENGINE=SQLORA
-RUN

SQL SET SERVER efactory_dev_dev045
-RUN

SQL SQLORA
delete AdjustmentsRpt_Answer;
END

-EXIT

I tried the suggestion below and it still did not delete them.

SQL SQLORA
delete from TableName
where field1=1;
END

Since my 1st field is a date, I tried sysdate or 1. Did not work. I even tried "where 1=1"

Thanks,
Dan

Keep in mind that the IBI server is in fact the user on that database in Oracle.
Look/ask your Oracle DBA what that user (server_ibi) is allowed to do on that specific table. In my company the IBI application only has read rights.
If you run the specific action the dba can set a trace on the database to see what is happening.

Does SQLORA issue a COMMIT at the end by default?

If it was a rights issue you would expect an error message back.

NO, you have to issues the commit.

Jimm that is what you are missing is the COMMIT

a 0 delete means there is no record found to delete.

You would recieve a permissions error if that was the issue. Look in your ODBC setup and see what user IBI's software is using and you can talk to your DBA about what's happening.

If you don't know how to log on to your oracle database using a tool like sqlplus or iSqlplus, then you'll probably want to get with your DBA and see if he can run your Delete statement from a machine that has the Oracle Client software installed (that's if you can't shell into a Linux server or something, depending on your server setup).

Whatever is returned in a sqlPlus session is your actual error, but more than likely it's a simple "no row found for delete". Also, to delete all records from a table using SQL, the answer is simpler than you think... try a "delete from schema.table;" and remember, this is a DML action so you will have to commit; the transaction when you finish if your database does not have autocommit turned on. (it will not have autocommit more than likely). The commit; would have to be done in the same ODBC connection in order for the changes to be preserved. Also, IIRC you may be able to set the commit level of each transaction in your ODBC setup, but I have not tried this method and it is a bad practice in my opinion.

another note.... you'll probably want to start specifying the schema in which your table exists.

If your DBA set up a synonym for a schema that has a WORK_ORDER_DETAIL table, it may not be the same as the one you're querying to find WHERE STATUS = 'C' records.

Also, try a TRIM(STATUS) = 'C'

Thanks for all your suggestions. There's nothing wrong with the access priv. The COMMIT did it for us.

Dan