Hey.. Does anyone know how to turn this string: "Smith, John R" Into this string: "jsmith" ? I need to lowercase everything with lower() Find where the comma is and track it's integer location value Get the first character after that comma and put it in front of the string Then get the entire last name and stick it after the first init...
Is there an existing PL/SQL method which takes a string and returns the same string but with backslashes preceding any regexp chars? ...
I have a HashMap that I am serializing and deserializing to an Oracle db, in a BLOB data type field. I want to perform a query, using this field. Example, the application will make a new HashMap, and have some key-value pairs. I want to query the db to see if a HashMap with this data already exists in the db. I do not know how to do thi...
There's lots of information about how cached execution plans in SQL Server eliminate much if not most of the performance advantage of stored procedures over dynamic sql. However, I have not found much about whether the same is true for Oracle databases. Does anyone have information or pointers to information about dynamic sql handling in...
I have analyzed a hibernate generated oracle database and discovered that a delete of a row from a single table will spawn the firing of 1200+ triggers in order to delete the related rows in child tables. The triggers are all auto-generated the same - an automatic delete of a child row without checking for existence first. As it is impos...
Suppose an Oracle instance has to be recovered after a disaster. Do sequences get reset to the initial state, or the last saved state, or are cached values preserved? Thank you very much. :-) ...
I need to all of timestamps in a column the same amount. I want to write an sql statement like: update sometable set timecol = timecol + <4 months>; ...
Say I have these table in an Oracle database Person: PersonId <- PK ID (int, from sequence) PersonFirstNameID <-- int PersonLastNameID <-- int PersonSecurityID <-- int PersonDetails PersonFirstNameID -CompositeKey PersonLastNameID -CompositeKey PersonSecurityID -CompositeKey PersonDetailKey PersonDetailValue PersonDetailRisk Now I wan...
Trying to make a generic PL/SQL procedure to export data in specific XML format, e.g. Excel XML. Let's say the procedure accepts a string with the SELECT query to EXECUTE IMMEDIATE. This requires access to data types of each column of the resulting rowset, which -- seeing as the procedure is to be generic -- is only known after the que...
I'm trying to dequeue from a queue on another server. I can't use propagation to get it to the server that needs the data. If I put a dblink in the name of the queue in the dequeue options I get an error. Error at line 9 ORA-25200: invalid value prim_queues.prim_trade_q@prim_dbln, QUEUE_NAME should be [SCHEMA.]NAME ORA-06512: at "SYS....
I recently started working on a large complex application, and I've just been assigned a bug due to this error: ORA-04091: table SCMA.TBL1 is mutating, trigger/function may not see it ORA-06512: at "SCMA.TRG_T1_TBL1_COL1", line 4 ORA-04088: error during execution of trigger 'SCMA.TRG_T1_TBL1_COL1' The trigger in question looks like ...
I have plsql procedure which accepts certain parameters e.g. v_name, v_country, v_type. I wish to have a cursor with a select statement like this: select column from table1 t1, table2 t2 where t1.name = v_name and t1.country = v_country and t1.id = t2.id and t2.type = v_type If certain parameters are empty can I only add the relevant...
We have a problem with the Euro character when saving and retrieving it from a Oracle 10g using the Oracle 10.2.0.3 JDBC driver. The problem only occurs during a JUnit test running under Linux. The Euro characters returned from database after saving are total screwed up. Oracle has been configured to use character set "WE8MSWIN1252". Cou...
Here's the situation: we have an Oracle database we need to connect to to pull some data. Since getting access to said Oracle database is a real pain (mainly a bureaucratic obstacle more than anything else), we're just planning on linking it to our SQL Server and using the link to access data as we need it. For one of our applicati...
I'm working in two different Oracle schemas on two different instances of Oracle. I've defined several types and type collections to transfer data between these schemas. The problem I'm running into is that even though the type have exactly the same definitions (same scripts used to create both sets in the schemas) Oracle sees them as ...
I have a legacy VB6 application which I've inherited. The issue I'm facing is that I'm getting the infamous ORA-12638: Credential retrieval failed error message whenever it tries to connect to one of our Oracle databases. I can connect fine from SQLPlus and Toad, however. I've Googled around and everyone seems to say that modifying th...
Hello, I have a large-ish Oracle table containing rows representing units of work, with columns for start time and end time in addition to other meta-data. I need to generate usage graphs from this data, given some arbitrary filtering criteria and a reporting time period. E.g., show me a graph of all of Alice's jobs for the 24-hour pe...
I am reading file from ResultSet and it's required to save file into Oracle Database. ... ResultSet rs = ... java.sql.Blob myfile = rs.getBlob("field") java.io.OutputStream os = ((oracle.sql.BLOB) myfile).getBinaryOutputStream(); I get get this error message java.lang.ClassCastException Any one have solution to this? Thanks! ...
Some of Oracle's analytic functions allow for a windowing clause to specify a subset of the current partition, using keywords like "unbounded preceding/following", "current row", or "value_expr preceding/following" where value_expr is a physical or logical offset from the current row or value (depending on whether you have specified ROW ...
I've created a package that contains a stored procedure that I plan to invoke from a separate application. The stored procedure will return a sorted list of all the views and tables in the schema. To do that, it performs a simple select on the DBA_TABLES and DBA_VIEWS synonyms, as shown below: CREATE OR REPLACE PACKAGE BODY TITAN_ENTITY...