Am trying to do update a clob column using a connection object that is retrieved using Apache DBCP connection pooling. Earlier, I've implemented connection pooling using this and it was working fine i.e am able to update CLOB. I switched to DBCP because I was getting java.sql.SQLException: ORA-01000: maximum open cursors exceeded. I've ...
If I know the correct format of fields, should I create check constraints for all of those fields, or will this affect the performance of inserts/updates too much? Would it be a good idea to use regular expressions for complex rules, or should I only use simple constraints like case and length? The fields are already being validated at ...
I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains dbms_output.put_line statements at various points. The output from these print statements appear in the log files, but only once the script has completed. Is there any way to ensure that the output appears in the log file a...
I recently discovered a difference between Oracle adds months to a given date (using ADD_MONTHS function) and the way Java adds months to a Calendar object. For instance in oracle: select add_months('2009-02-28', +1) from dual; produced the result: "09-03-31" And the query: select add_months('2009-02-28', -1) from dual; Produces...
I have an application that holds data referencing 300,000 customers. When a user did a search the result was often bigger than our MaxRequestlength would allow, we have dealt with this in two ways: We have increased our MaxRequestLength to 102400 (KB) and required the user to supply two letters of the first Name and two letters of the la...
In our test bed, a number of test suites will be run in a row (unattended), producing reports for later consumption. I want to include in those reports queries which are candidates for further investigation, along with the data that justifies their inclusion in that list. We should be able to associate any query identified this way wit...
I have an Oracle stored procedure that returns a BLOB in an output parameter: PROCEDURE GET_IMAGE_DATA(i_image_type IN NUMBER, o_image_data OUT BLOB) IS BEGIN SELECT IMAGE_DATA INTO o_image_data FROM IMAGES WHERE IMAGE_TYPE = i_image_type; END GET_IMAGE_DATA; I want to use JDBC Spring to read this...
We are looking to retire old data in out Oracle warehouse. To give a very simplified overview, a proposal was suggested to develop a process using PL SQL Stored Procedures that, given source/dest table, etc., parameters, use the Oracle ALL_TAB_COLUMNS view to construct a target table that mirrors the source table. If the dest table ex...
I'm talking about oracle. I have a few materialized views, and they're updated from time to time (it is done with a scheduled task). It is easy to know last refresh date - just query USER_MVIEW_REFRESH_TIMES. And is there any way to know if some views are being updated in the current moment? ...
Hi, am new to oracle. is there a bulitin keyword does the same job of SQL server APPLY?? ...
I'm using Toad 8.6 and when I try to add new records using the Schema Browser, Data tab, my 'insert record' is grayed out. so, I installed the 9.6 version and the same problem persists. see the grayed out buttons in red in the picture. but i'm able to add records through the SQL insert statement at the SQL Editor of Toad. Weird ! is...
The application is on .Net 3.5 and Oracle Standard Edition 11G database where a table have over 2 million records. The record size is not huge. The records have a Status column of type Char(1). Depending upon the status a process picks records to process and updates the status after completion. A record goes via multiple processes in ser...
Hi, Oracle 10g on Suse 9.1 I'm looking after an Oracle DB. I have good SQL skills but I'm no DBA, (a diffence in job roles I have difficulty explaining to my non-IT savvy bosses!) I know enough to get Oracle up and running and put in place it's 'automatic' options for backup and optimisation then I leave it well alone. Everything else ...
How to parse a string of date in an arbitrary specified Oracle date format in C#? So... the oracle format string is a bit different from the C# datetime format string, so I can't use that format-string as an argument for the parse. I am using Devart/CoreLab but their OracleDate.Parse seems to be really strange and not working for me. H...
I'm used to the comfort of working with MSSQL and the Enterprise Manager and Query Analyzer. Now working with Oracle 9.2 and having to suffer using SQL Plus for making queries, I'm looking for some better alternatives. Are there any free or low-cost tools for this purpose? ...
I'm trying to load data from oracle to sql server (Sorry for not writing this before) I have a table(actually a view which has data from different tables) with 1 million records atleast. I designed my package in such a way that i have functions for business logics and call them in select query directly. Ex: X1(id varchar2) x2(id varch...
Friends, I am currently investigating a performance problem with our application. The problem is that an operation that should be completed almost instantly is taking several minutes. In case it is of importance the application architecture is Oracle Forms, Oracle Application Server and Oracle Database. All of which are 10.x The cus...
I have 2 tables in the following format: employee (employeeID, EmployeeName, DepartmentID) departments (DepartmentID, DeptName) How many employees there are who work in each of the departments that have more employees than the average number of employees in a department. im looking to the results in the following format: Dept Name...
In my ruby script, I set to connect to oracle db via: conn = OCI8.new(username, password, database) It works well on my own desktop (Ubuntu), however, when I deploy it onto a lab machine (Ubuntu VM), it has oracle connection error. The machine has all oracle drivers needed because I can connect to it by commandline sqlplus. Also I did e...
Hi, Im using sun.jdbc.odbc.JdbcOdbcDriver to connect to an oracle database.I know I would be probably be better off using the thin driver but I want the app to work without specifying the db server name and port no.My connection string is like jdbc:odbc:DSN. The queries that I execute in my application may return millons of rows.All th...