I am trying to search a varchar2 column in a table for matching strings using the value in another column. The column being searched allows free form text and allows words and numbers of different lengths. I want to find a string that is not part of a larger string of text and numbers. Example: 1234a should match "Invoice #1234a" but no...
I am trying to run a procedure on ORACLE with the thin jdbc client and c3p0. here's the code that's supposed to run: Map<String, Object> paramMap = new HashMap<String, Object>(); paramMap.put("I_NODE_ID", nodeId); paramMap.put("I_PARENT_ID", parentId); paramMap.put("I_STRUCTURE_ID", structureId); ...
Hello, I am trying to copied records from one table to another as fast as possible. Currently I have a simple cursor loop similiar to this: FOR rec IN source_cursor LOOP INSERT INTO destination (a, b) VALUES (rec.a, rec.b) END LOOP; I want to speed it up to be super fast so am trying some BULK operations (a BULK FETCH, then a FOR...
I've been spending more and more time writing DB Wrappers for Oracle access. This seems to be quite generic procedure, and I was wondering is there already are code generators that generate access routes to Oracle PL/SQL Stored Procedures in C++? I'm looking for a configurable generation tool that would be capable of managing connection...
Question: In which four clauses can a subquery be used? (Choose four.) A. in the INTO clause of an INSERT statement B. in the FROM clause of a SELECT statement C. in the GROUP BY clause of a SELECT statement D. in the WHERE clause of a SELECT statement E. in the SET clause of an UPDATE statement F. in the VALUES clause of an ...
Hello, I am planning to execute Oracle PL\SQL blocks via JDBC (can't test it yet, question of few days). Is there anything I should know? Does everything work as it used to with plain SQL? I mean: ResultSet rs = st.executeQuery("DECLARE BEGIN NULL; END;"); Or will I need some custom classes? I'd like to keep it as much simple as pos...
how to generate report and save it to file using pl/sql oracle? ...
Hi, Where can i find good tutorial for SQL,PL/SQL and also interview questions. ...
I need this to be done in Oracle SQL (10gR2). But I guess, I would rather put it plainly, any good, efficient algorithm is fine. Given a line (or sentence, containing one or many words, English), how will you find the last word of the sentence? Here is what I have tried in SQL. But, I would like to see an efficient way of doing this. ...
I want to achieve this .. update Table_A c set c.Column1 = (select d.column1 - b.column2 from Table_B d, Table_A b where b.primary_key = d.primary_key) But for outer query there is no primary key clause i have added.. How do i achieve it ...
I have an Oracle table which contains data like 'Shiv------Shukla' (consider '-' as space). Now I need to write a program which leaves just one space and removes all other spaces. Here is the program which I've made but it is not giving me expected result. DECLARE MAX_LIMIT VARCHAR2(50):=NULL; REQ VARCHAR2(20):=NULL; CUR...
Hello, I would like to find the distinct CLOB values that can assume the column called CLOB_COLUMN (of type CLOB) contained in the table called COPIA. I have selected a PROCEDURAL WAY to solve this problem, but I would prefer to give a simple SELECT as the following: SELECT DISTINCT CLOB_COLUMN FROM TABLE avoiding the error "ORA-00932: ...
Hi All, I have to generate some million update from some table, to update themselves. I had just recently learned about parallel(tablename,threads) which really improved the performance in PLSQL developer when I had run something like this: select /* + parallel(table1,100) parallel(table2,100) */ 'update table1 set id = 1 where ...
I have the following SQL statement: SELECT CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" , ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID, LEVEL -1 "LEVEL" FROM ANIMALS START WITH ANIMAL_ID IN( '2360000002558' ) CONNECT BY ((PRIOR SIRE_ANIMAL_ID = ANIMAL_ID and LEVEL < 5) OR (PRIOR DAM_ANIMAL_ID = ANIMAL_ID AND LEVEL < ...
NOTICE: Appericiate all the answers, thanks but we already have a sequence.. and can't use the UNIQUE contraints because some items need to have duplicates.. I need to handle this using PLSQL somehow, so based on some criteria (using an if statement) i need to ensure there is no duplicates for that.. And just to confirm, these identifier...
Hiya, Here I go again with some PLSql.. I want to know, if there's anyway I can use the following function like a select without having to turn it into a function or procedure (so I can see the code from the script where it is contained). The code would be like: DECLARE outpt VARCHAR2(1000) := ''; flow_rI VARCHAR2(50); ...
If the SELECT INTO statement doesn't return at least one row, ORA-01403 is thrown. For every other DBMS I know this is normal on a SELECT. Only Oracle treats a SELECT INTO like this. CREATE OR REPLACE PROCEDURE no_data_proc IS dummy dual.dummy%TYPE; BEGIN BEGIN SELECT dummy INTO dummy FROM dual WHERE d...
hi, my current java code is deployed to the DB and the plsql code calls it and uses it. I need to get the Java code out of the database and still be able to use it. The options I had in mind are: Web services Java Stored Procedures Calling OS command using pl/sql RMI What is your recommendation? please add cons and pros. thanks, L...
I am facing a problem. I have one query Select * from tabA where (a) in (a,b,c) OR b in (a,b,c) I want to facing performance issue due to this query as I need to remove the or condition , so I tried with the following query: Select * from tabA where (a,b) in (a,b,c) but this query seems not to work, please help. I dont want to...
I want to write an Oracle PL/SQL stored procedure that takes as a parameter a list of pairs of some other type, say varchar2(32). Is this possible? What's the best way to do it? ...