Database Table1 Id Table2Id ... Table2 Id StartTime Duration //in hours Query select * from Table1 join Table2 on Table2Id = Table2.Id where starttime < :starttime and starttime + Duration/24 > :endtime This query is currently taking about 2 seconds to run which is too long. There is an index on the id columns and a fu...
We are trying to do a manual failover of our oracle database servers through enterprise manager and it keeps throwing an ORA-16198 timeout incurred on internal channel during remote archival. How do I go about troubleshooting this problem? ...
I want to issue a query like the following select max(col1), f(:1, col2) from t group by f(:1, col2) where :1 is a bind variable. Using PreparedStatement, if I say connection.prepareStatement ("select max(col1), f(?, col2) from t group by f(?, col2)") I get an error from the DBMS complaining that f(?, col2) is not a GROUP BY exp...
I have read that one should not analyze a temp table, as it screws up the table statistics for others. What about an index? If I put an index on the table for the duration of my program, can other programs using the table be effected by that index? Does an index effect my process, and all other processes using the table? or Does it e...
As in the question, how do I automatically reset an Oracle sequence's value back to 0 every year in Oracle 10g? I'm using the sequence to generate an identifier in the format YYYY<sequence value> and the sequence value has to be reset to 0 every year. YYYY is obtained from java and concatenated with the sequence value from Oracle. Th...
Does anyone know of an open source database proxy where it can serve as an intermediary between a closed-source client application and either a SQL server and/or Oracle database? ...
I have recently been receiving the following error whenever I am asked to supply the host username and password in Oracle DataGuard's Enterprise Manager (EM) tool: RemoteOperationException: Error reading error from command. Any configuration or management that needs to be performed and requires the host credentials is throwing this error...
(Oracle PL/SQL) If I have a simple SQL statement that is throwing an error, ie: DECLARE v_sql_errm varchar2(2048); BEGIN UPDATE my_table SET my_column = do_something(my_column) WHERE my_column IS NOT NULL; EXCEPTION when others then -- How can I obtain the row/value causing the error (unknown)? v_sql...
I've run into an odd problem with a legacy ASP application that uses Oracle 9i as its database. We recently had the DBA increase the size of a VARCHAR2 field from VARCHAR2(2000) to VARCHAR2(4000). The problem is that a NULL byte (ASCII: 0) is inserted into the 2001 character position in the string even if the string inserted is larger ...
I'd like to know if it's possible to execute more than one SQL statement within a single execute() or do() call using DBD::Oracle via Perl DBI. Example: # Multiple SQL statements in a single query, separated by a ";" $sql = 'UPDATE foo SET bar = 123; DELETE FROM foo WHERE baz = 456'; $sth = $dbh->prepare($sql); $sth->execute; # ...or...
First off, I'm a total Oracle noob although I'm very familiar with SQL. I have a single cost column. I need to calculate the total cost, the percentage of the total cost, and then a running sum of the percentages. I'm having trouble with the running sum of percentages because the only way I can think to do this uses nested SUM functions,...
I've seen many similar questions & answers, but they've used other DB-specific tricks, or done it in code, etc. I'm looking for a straight SQL batch file solution (if it exists). I have two tables with a parent/child relationship, call them Runs & Run_Values. Runs has an "auto" generated PK, runID (a sequency & a trigger), and two colu...
Hi all, I have a table that looks something like this: ID | STATUS | TYPE ---------------------------------------- x123 | A | High School x122 | I | High School x124 | F | High School x125 | A | College x126 | I | College x127 ...
I want to find the foreign keys of a table but there may be more than one user / schema with a table with the same name. How can I find the one that the currently logged user is seeing? Is there a function that gives its owner? What if there are public synonyms? ...
I have this server with the following system: Windows 2003 R2 Ent Edition SP 2 Oracle 1.0.2.0.4.0 Databases: 5 The databases have their archivelogs located in flash recovery area as shown by the NAME column of v$recovery_file_dest and also by invoking "show parameter db_recovery_file_dest" from sqlplus. I'd like to monitor the space u...
I need to handle some somewhat compliclated parameter validation requirements. We are using SQL reporting 2005 against an Oracle 8.1 DB. The user must not select a date range longer than a year Either FooParm must be supplied or Codes must be supplied and the difference between CodeStart and CodeEnd must be less than 1001 In a pseudo...
I've been talking with someone about the possibility of a iPhone development contract gig. All I really know at this point is that there is a company that wants to make an iPhone app that will hit their internal database. I'm not sure what the database type is( Oracle, MySQL, etc...). I've wanted to know if the database type was Oracle...
I'm trying to insert a clob into Oracle. If I try this with an OdbcConnection it does not insert the data into the database. It returns 1 row affected and no errors but nothing is inserted into the database. It does work with an OracleConnection. However, using the Microsoft OracleClient makes our webservices often crash with an AccessV...
I've got Oracle XE installed on Debian linux and the character set is configured to AL32UTF8. There are several client applications that connects to a database from Windows with the different locales - French etc, not English. That's ok with all the client data these applications put into database, nothing converted and text data in Fren...
If I have a sql query that uses 'distinct' (in oracle), would it be faster than retrieving the non-distinct then getting the unique results via java programming? I heard somewhere that oracle sql distinct is heavy, but is it heavier than manual 'distinction' via java programming? Thanks, Franz ...