I've inherited a front end written by a third-party. That front end interacts with Oracle through procedures written by a different third-party. The stored procedure in question requires 2 minutes and 36 seconds to return search results when it is manually executed. I can't see the procedure and that team has suggested that I increase...
I am new to MySQL coming from Oracle. I have a requirement to create a table with the primary key in one column but with the following format. X-A letter stating the country of origin e.g. S for Spain, Z for Zimbabwe e.tc (we have five countries of origins only) YYYYMMDD-Date in that format 9999-4 digit office code. 9999999-7 right p...
I have a join in a oracle query which looks like: FROM eiv.table1 eiv.table2 b WHERE a.state_cd = b.state_code(+) what does the (+) towards the end mean? With this query I have noticed that sometimes I am getting an empty space when records do not match in tables. Is this a left outer join or ri...
I am reading a csv file in to a datatable in vb.net and making a few checks and appending an extra column. I then want to perform a bulk insert using microsofts Oracle.DataAccess (no choice in this) to an Oracle database. what would be the best way to perform this as there is no bulkImport like in SQLserver. thanks ...
How do you delete rows from a table, where a column contains a substring, but the type of that column is 'Long'. (Yes, I know I shouldn't use Long, but I'm maintaining someone else's mess). My first attempt was: delete from longtable where search_long(rowid) like '%hello%'; (following on from this answer) This returns: S...
Help! I've used impdp and had a typo - now I've got a user name starting with colon (:) - e.g :my_schema. How can I drop this user? I've tried everything I could think of to escape it, but nothing helps. Edit: To clarify - I know how to drop a user. I'm having difficulty overcoming the special character issue. ...
I am trying to implement a sequence in an Oracle database to act as a surrogate key creator for a table. For performance reasons, I want this sequence to be cached. I have read that there are potential pitfalls when using cached sequences since rollbacks and instance failures will result in missed values. This got me to thinking. Let...
I have an application that uses AJAX liberally. I have several places where a single database column is being updated for the record the user is actively editing. So far I've been creating separate stored procedures for each AJAX action... so I've got UPDATE_NAME, UPDATE_ADDRESS, UPDATE_PHONE stored procedures. I was just wondering if...
Does anyone use the PL/SQL Web Toolkit at all? We use it for internal reporting where I work. However, does anyone have any experiences of it for producing client-facing websites? General advantages/disadvantages compared to other web languages, such as JSP, PHP etc ...
Given the following Oracle (10g) package definition: create or replace PACKAGE "foo" AS bar VARCHAR2(32000) := NULL; END; what is the scope of bar? Does each session get its own foo.bar, or is foo.bar global across sessions? Can you quote me chapter and verse from a reference document? ...
Hi I am new to Oracle and I am working on something that needs to parse a SOAP request and save the address to DB Tables. I am using the XML parser in Oracle (XMLType) with XPath but am struggling since I can't figure out the way to parse the SOAP request because it has multiple namespaces. Could anyone give me an example? Thanks in ...
I am getting an oracle error ORA-01001: invalid cursor in the production where a number of transactions are processed in bulk. However the same code works fine in development. I need to know when can one have ORA-01001: invalid cursor in an update query. I did some googling and found that there are two possibilities of getting this erro...
When I attempt to do something like SELECT Max(ObjectId) FROM Objects; I see that in the explain-plan that this is performed by doing a sort. Now, sorting (which I guess would require something in the complexity of O(nlogn)) must be much costlier than just scanning each row and remembering the max value (which could be done in O(n)). ...
Hello, I've got the problem, that I use a property in the persistence.xml which forces Hibernate to look only for tables in the given schema. <property name="hibernate.default_schema" value="FOO"/> Because we are using now 4 different schemas the actual solution is to generate 4 war files with a modified persistence.xml. That not v...
I have an existing database with tables which each have 4 partitions (there are tables using both RANGE and HASH partitioning). I need to import it into another database with a pre-created schema where the same tables will have 8 partitions. How do I do this? Does this "just work" if I do a table-level import? ...
We have a lot of SP's and many of them take forever to execute. These SP's are pretty long and its a pain to go through the SP to find out which query is taking long. Whats the best way to find out which query is taking the most time? so that we can just concentrate on that query rather than spending time in research. Currently I am ...
my $dblinks = ''; $dblinks = $dbh->selectcol_arrayref("select db_link from db_links where ticket=\'LOW\'"); my $success = 0; for my $dblink (@$dblinks) { $success = eval { my ($ret) = $dbh->selectrow_array("select 1 from " . $dbh->quote_identifier($dblink, 'SYSIBM', "SYSDUMMY1") ); $ret; }; if ($success) { &Loggi...
I have two PL/SQL Stored procedure each handling its own Transaction (Begin/Commit and Rollback in case of error). From .Net code I Call these two SP as shown below. using (TransactionScope ts = new TransactionScope()) { CallSP1(); CallSP2(). ts.SetComplete(); } If my Call to SP2 fails will i...
Using .Net and Oracle 11g - I've been returning dataTables from a procedure inside of a package by opening a Cursor. IE - 'OPEN TABLEREF FOR SOMESQL; Where TableRef is an 'OUT' Param. It's been working great. What I'm struggling to do is have that first Proc call another Proc and let that second Proc open the cursor. Inside Proc1 (w...
So let's say I have a compound index on a table that indexes 4 fields. create index idx_comp_index on mytable ( fielda, fieldb, fieldc, fieldd ); If I query that table using all four of those fields in my where clause plus an additional field or two, will the index still be used? select * from mytable where fielda = 'i' and fieldb ...