Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     sql code to delete records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
sql code to delete records
 Login/Join
 
Silver Member
posted
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.
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report This Post
Master
posted Hide Post
Tried this code against my database it works

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


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Platinum Member
posted Hide Post
Jim,

Are you sure who have delete privilage on this table?

Thanks


WF7.1.4 Prod/Test, MRE, self serve, DM
 
Posts: 176 | Location: Desplaines | Registered: August 05, 2004Report This Post
Expert
posted Hide Post
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. Big Grin

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Platinum Member
posted Hide Post
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


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Virtuoso
posted Hide Post
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
Does SQLORA issue a COMMIT at the end by default?

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



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Master
posted Hide Post
NO, you have to issues the commit.

Jimm that is what you are missing is the COMMIT




Scott

 
Posts: 865 | Registered: May 24, 2004Report This Post
Master
posted Hide Post
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.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Master
posted Hide Post
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'


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Platinum Member
posted Hide Post
Thanks for all your suggestions. There's nothing wrong with the access priv. The COMMIT did it for us.

Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     sql code to delete records

Copyright © 1996-2020 Information Builders