Delete all rows from a table - Data Service Integrator

Hello,
is there a best practice how to delete all rows from a table in ALDSP 3.2?
Let's have a table TESTTABLE and create the physical service with the wizard. Then I can create a logical service with a procedure like this:
declare procedure tns:deleteAll(){
t1:deleteTESTTABLE(t1:TESTTABLE());
}
But this is quite slow because for every row a single delete statement is executed. It also seems impossible to create a physical service based on a SQL statement like "delete from TESTTABLE".
I could create a stored procedure but this feels a bit too complex for such a simple operation.
Thanks for any help,
gwi
Edited by: user3972018 on Dec 11, 2008 8:30 AM
fixed typo 

You are correct on all counts. Open a case with Customer Support, ask for an enhancement request for the ability to create a physical xqse procedure from a sql-statement that does insert/update/delete. (it's a long boring story as to why this is feature is not in the product). In your case, describe your use-case, time-frame, urgency etc, and provide the link to your post on this forum.
Please also refer to CR322358 in your case.
Edited by: mikereiche on Dec 11, 2008 9:33 AM

Related

Oracle XQuery performance issue in XMLType column

Dear All,
As for oracle I'm using oracle 11g to measure the performance.
I'm using data from XMark project which is a >100MB data of XML for bencmarking purposes.
I make a table that contains an XMLType column and upload the data into that column, after doing that I try to do a query like this:
select xmlquery(
'for $i in /site/people/person
where $i/id = "person0"
return $i/name'
passing BookXMLContent Returning Content)
from Book;
The purpose of this query is to retrieve the name of a person that have id = 'person0'
My questions are:
1. Did I do something wrong with my query?
2. Is there any setting on the database that I should done prior to doing the query to done significantly better result?
3. Is there any other approach that are much better than I currently used?
Regards,
Anthony Steven
Edited by: mdrake on Nov 4, 2009 6:01 AM 
Anthony
First, please read the licencing terms for Oracle ( And I suspect DB2, MSFT) . You are not allowed to publish externally (in any form, including forum posts :) ) the results of any benchmarking activities. I have edited your post accordingly. I hope this research is not part of a thesis or similar work that would intend making public as you and your institution would be in violation of your licence agreeement were you to do so.
Now back to your question, how can you improve performance for XMark
#1. Can you show us the create table statement you used, so we can see how you created your XMLType column BOOKXMLCONTENT.
#2. Did you create any indexes
#3. Did you look at the explain plan output.
-Mark
Edited by: mdrake on Nov 4, 2009 6:06 AM

Fetching limited number of rows in a pl sql procedure

I have a web interface in which i need to display data from the database.
For displaying this data, i am using a ADF table, the data is returned by a procedure i created in the database.
The problem is that i don't want to fetch all the data from the table in one single go, as the number of rows returned can be very large.
So i have a scenario in which i need to fetch the first ten rows(1-10), then the next ten (11-20) and so on.
How can implement such a functionality in a procedure. I am returning data from the procedure as a out parameter of ref cursor type.
I am using Oracle Database 11g R2.
I don't want to use rownum for doing this.
Thanks in advance
Edited by: user12217802 on May 12, 2010 10:43 AM
Edited by: user12217802 on May 12, 2010 10:47 AM 
how about FIRST_ROWS hint in your SQL query? That will get first X rows as fast as possible
http://tahitiviews.blogspot.com/2008/11/mystery-of-firstrows-hint.html
pipeline functions can return partial result even before the complete PL/SQL completes
http://www.akadia.com/services/ora_pipe_functions.html 
Excellent details, discussion, techniques and examples on PAGINATION can be foun here.
http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html
vr
Sudhakar B. 
user12217802 wrote:
I have a web interface in which i need to display data from the database.
For displaying this data, i am using a ADF table, the data is returned by a procedure i created in the database.
The problem is that i don't want to fetch all the data from the table in one single go, as the number of rows returned can be very large.
So i have a scenario in which i need to fetch the first ten rows(1-10), then the next ten (11-20) and so on.
How can implement such a functionality in a procedure. I am returning data from the procedure as a out parameter of ref cursor type. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064
Would outline some methods.
I am using Oracle Database 11g R2.
I don't want to use rownum for doing this.Why don't you want to leverage the functionality of the product you are paying decent sums of money for?
Thanks in advance
Edited by: user12217802 on May 12, 2010 10:43 AM
Edited by: user12217802 on May 12, 2010 10:47 AM

hi

i have two tables students &students_history...when student status becomes completed ..it should be automatically updated in students_history table..... i want know how to resolve ..plz guide me......
and the students table is
SID STATUS
100 JOINED
101 COMPLETED
102 PENDING
103 INPROGRESS
Edited by: 1004106 on May 4, 2013 8:39 AM
Edited by: 1004106 on May 4, 2013 8:45 AM 
1004106 wrote:
i have two tables students &students_history...when student status becomes completed ..it should be automatically updated in students_history table..... i want know how to resolve ..plz guide me......
and the students table is
SID STATUS
100 JOINED
101 COMPLETED
102 PENDING
103 INPROGRESS
Edited by: 1004106 on May 4, 2013 8:39 AM
Edited by: 1004106 on May 4, 2013 8:45 AMSounds like a homework assignment.
Also sounds like you are probably leaning towards using triggers (seems the theme of the morning).
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm
If this isn't homework then please elaborate on your requirements. There aren't many times I would recommend a trigger for a real world solution (they're seldom needed, and messy to work with / around).
Cheers, 
what about dbms_jobs....what is the difference b/w dbms_jobs&trigger? 
1004106 wrote:
what about dbms_jobs....what is the difference b/w dbms_jobs&trigger?DBMS_JOBS runs on a time schedule.
Trigger "fire" when DML occurs. 
Hi,
Unless you're using a very old version of Oracle, don't use dbms_jobs. Use dbms_scheduler instead to run some PL/SQL at a fixed time.
Use a trigger if you need to run something autiomatically as soon as some event (such as updating a row in a certain table) occurs. 
hi,
i have two tables which includes salary column in one table...if i insert a new salary in table1 it should automatically updated in table2 which consist of oldsalary &newsalary....how i should update the values using trigger............... 
Hi,
This Running Oracle has an example.
If you have trouble adapting it to your case, post a complete test script (CREATE TABLE, your best attempt at a CREATE TRIGGER, and some DML statements) that poepl can run to re-create your problem and test their ideas.

Trigger not firing with TOAD but fires whith SQL* Plus

HI,
This post is just to have the opinions of expert about using TOAD.
I have script that updates a table T1 which have a trigger before update T1_TRG defined on it that inert a row in another table T2 .
the peoblem i have is when a run this script with TOAD the trigger T1_TRG does not fires, but when i run the same script with SQL* Plus everything works good,
the trigger does fire with no problem.
Does any one encountered this with TOAD, i would like to have an answer to be reassured.
Thanks in advance.
Edited by: manzana on Apr 8, 2011 9:36 AM
Edited by: manzana on Apr 8, 2011 9:36 AM 
Hi,
Looks like you have some problem with commit.
Try commiting it properly.
click the commit on top of the editor, not the one in the grid.
G. 
May be,
1. Check the session id of the TOAD session where you are about to execute the script
SELECT uid FROM dual;2. Execute the script in TOAD.
3. Check the session id in the window where you are about to check whether TRG fired or not. Probably, you would like to check the table 2.
SELECT * FROM tab2; --Provided uid is same!Edited by: abyss on Apr 8, 2011 12:54 PM 
manzana wrote:
HI,
This post is just to have the opinions of expert about using TOAD.
I have script that updates a table T1 which have a trigger before update T1_TRG defined on it that inert a row in another table T2 .
the peoblem i have is when a run this script with TOAD the trigger T1_TRG does not fires, but when i run the same script with SQL* Plus everything works good,
the trigger does fire with no problem.
Does any one encountered this with TOAD, i would like to have an answer to be reassured.
Thanks in advance.
Edited by: manzana on Apr 8, 2011 9:36 AM
Edited by: manzana on Apr 8, 2011 9:36 AMUnless your trigger does something wildly insane like utilize
select module from v$session;As an "on/off" switch i can assure you that the trigger is firing for both sessions (assuming it wasn't dropped or disabled between your run in SQL Plus and TOAD).
As mentioned before, make sure you issue a COMMIT after you run your process, i can't remember, but i think TOAD utilizes threads (different sessions) in some instances, so it's possible you ran your insert (which fired the trigger) and then you queried to see that change and TOAD used another session, which isn't allowed to see your uncommitted changes in the "original" session. 
Thanks for your responses. 
By the way, you should probably be using an 'after' trigger, not 'before'.
As a general rule, use Before if you are updating columns in the row you are inserting or deleting, otherwise use After.
You should also be aware that Before triggers may actually fire more than once. See Tom Kyte's Expert Database Architecture book for details:
http://books.google.com/books?id=TmPoYfpeJAUC&lpg=PA251&ots=RAP84t_v0A&dq=kyte%20trigger%20architecture%20fire&pg=PA249#v=onepage&q&f=false
starting at the bottom of page 249.
Edited by: mtefft on Apr 8, 2011 6:30 PM

ORA-00942:table or view does not exist

SQL> SELECT * FROM TAB
     TNAME     TABTYPE     CLUSTERID
1     MIPLATFORM     TABLE     
2     MICOMPANY     TABLE     
3     MIWEBSITES     TABLE     
4     MIUSER     TABLE     
5     MIOTPCONFIGURATION     TABLE     
6     MIQUICKLINKS     TABLE     
7     MIUSERWEBSITES     TABLE     
8     MISERVICES     TABLE     
9     MIWEBSITESERVICES     TABLE     
10     MIUSERSERVICES     TABLE     
*11     MIAdminUSER     TABLE*
One of our developer created table 'MIAdminUSER'. it shows in above query.
Now i can't drop it as ii gives error ORA-00942:table or view does not exist.
I am confuse how can someone created like 'MIAdminUSER' and not like 'MIADMINUSER'.
Still I can create table name with 'MIADMINUSER'
Please help me to resolve this.
Thank you
Edited by: Oracle_2410 on Oct 4, 2011 11:44 PM 
Using double quotes around object names in Oracle, preserves the case. Hence, in this case, any further references to this table should be done as "MIAdminUSER". 
Thank you very much :) 
Oracle_2410 wrote:
Thank you very much :)Which is why
1) many shops don't let clueless developers shouldn't have DDL privileges. All such changes have to go through a DBA for vetting and consistency
2) it's bad practice even for a DBA to create object names in mixed case. In oracle the default is all caps, and so life is much easier if you just go with that instead of trying to make oracle behave like .... some other product.
Edited by: EdStevens on Oct 5, 2011 6:50 AM

Categories

Resources