How do I do a manual uninstall of Oracle?
Sometimes my Oracle database on Windows gets hosed. How do I do a manual uninstall of Oracle? ...
oracle9i
Problems importing SQL script from Oracle 9i to Oracle 10g express
I am currently running into a problem when trying to import an Oracle 9i SQL script into my local Oracle 10g Express database. I am trying to import the DDL from the 9i database to the 10g express database. I keep getting "Not compatible - Your export file is not supported". Has someone been able to get this working? Please let me know w...
.NET and Oracle: Joining a table to an empty set of another table
Hi all, I am trying to join tableA with some data to an empty set of another tableB. The main purpose is to get all the columns of tableB. I do not need any data of tableB. I have constucted the following SQL: SELECT uar.*, s.screen_id, s.screen_name FROM crs_screen s LEFT JOIN crs_user_access_right uar ON s.rid IS ...
What is the best layer to use to format Currency numbers?
I use Javascript on frontend, C# as middle layer and Oracle 9i as DB. What is the best practice when formatting numbers with 2 decimal precision into a currency? And what layers should be preferred amongst any of these three layers? Keeping in mind that the User will be able to edit these amounts on a few of my screens on the applicati...
when will oracle discontinue the 9i database?
The Oracle 9i database is starting to look long in the tooth. When will Oracle discontinue support for it? ...
Oracle performance with multiple same column indexes
I'm Working with a new Oracle DB, with one table having the following indexes: Index 1: ColA, ColB Index 2: ColA Is the second index redundant, and Will this have a negative impact on performance? ...
Oracle Builtin String Character Classes
Does Oracle have built-in string character class constants (digits, letters, alphanum, upper, lower, etc)? My actual goal is to efficiently return only the digits [0-9] from an existing string. Unfortunately, we still use Oracle 9, so regular expressions are not an option here. Examples The field should contain zero to three letters...
Does Oracle 10g automatically escape double quotes in recordsets?
I am encountering an interesting issue with an application that was migrated from Oracle 9i to 10g. Previously, we had a problem when a field contained double quotes since Oracle recordsets encapsulated fields in double quotes. Example: "field1"||"field2"||"field "Y" 3"||"field4" Since the move to 10g, I believe that the Oracle client-...
Delete an oracle-user with single quotes in the username
Through a faulty script I have created a user with single quotes around his username (i.e. his username is 'username', not username) on an Oracle 9i system. Now I want to remove that user. Neither "DROP USER 'username'" nor "DROP USER \'username\'" nor "DROP USER (SELECT username FROM all_users where user_id = 123)" worked. How do I get ...
Configuring Oracle 11g client to work with 9i, in order to use ODP.NET for VS 2008 and C# or ASP.net, issues
Hi there, i just have some issues regarding the usage of the latest oracle ODP.NET client , and using it to connect with ASP.NET 2008 and C# to an old Oracle 9i database. As stated in the ODP.NET instalation instructuins it should work, but i'm a litte bit rusty when it comes to configure oracle. The old 9i client has issues for .NET, it...
count number of rows that occur for each date in column date range.
I have a table with data such as below Group Start Date End Date A 01/01/01 01/03/01 A 01/01/01 01/02/01 A 01/03/01 01/04/01 B 01/01/01 01/01/01 ETC I am looking to produce a view that gives a count for each day, like this Group Date Count A 01/01/01 2 A 01/02/01 2 A 01/03/01 ...
Oracle Date format - Strange behaviour
I am writing a SQL query to retrive data from a table between two dates. I give two inputs as shown. I convert the Date TO_CHAR(DD/MON/YYYY). 1. StartDate > 01/SEP/2009 EndDate < 01/OCT/2009 2. StartDate > 01/SEP/2009 EndDate < 1/OCT/2009 I dont get any result for the first input. When I change it to second one i get the r...
Oracle 9 - Resetting Sequence to match the state of the table
I have a sequence used to seed my (Integer based) primary keys in an oracle table. It appears this sequence has not always been used to insert new values into the table. How do I get the sequence back in step with the actual values in the table? ...
oracle 9i get highest member of tree with given child
I have a parent-child relationship in an Oracle 9i database-table like: parent | child 1 | 2 2 | 3 2 | 4 null | 1 1 | 8 I need to get the absolute parent from a given child. Say, I have child 4, it has to give me parent: 1 I already looked to CONNECT BY , but I can't find the solution. ...
Problem with joining db tables
I have problem when joining tables (left join) table1: id1 amt1 1 100 2 200 3 300 table2: id2 amt2 1 150 2 250 2 350 my Query: select id1,amt1,id2,amt2 from table1 left join table2 on table2.id1=table1.id2 My supposed o/p is: id1 amt1 id2 amt2 row1: 1 100 1 150 row2: 2 200 2 250 row3: 2...
Pattern Matching with Like Clause
I am attempting to use a LIKE clause in a SQL statement to match a certain pattern within Oracle. I would like to do something as such: LIKE '[A-Z][A-Z][1-4]%' ..but I can't use a regex because this is on Oracle9i (regex support came in 10g). I am attempting to match something that has two characters before it, then a number between...
Oracle Dynamic Join Challenge
EDIT: Far simpler example. (Formerly titled: Oracle Column Injection) GOAL: Complete the query below generate the following results? PURPOSE: Create a column dependent on an existing column in a table without putting the table in a subquery. Rules: Restructuring the query to put tbl in a subquery is not an option. The query must use...
plsql oracle parent child
I have a parent-child relationship in an Oracle 9i database-table like: parent | child 1 | 2 2 | 3 2 | 4 null | 1 1 | 8 I have an absolute parent (e.g. child 1) and i need a csv list or resultset of all childs of this parent. ...
Oracle: Find previous record for a ranked list of forecasts
Hi I am faced with a difficult problem: I have a table (oracle 9i) of weather forecasts (many 100's of millions of records in size.) whose makeup looks like this: stationid forecastdate forecastinterval forecastcreated forecastvalue --------------------------------------------------------------------------------- varchar (...
How to run REPLACE function in Oracle9i with strings larger than 4000 characters
I have the following block of PLSQL that succeeds when ln_length is 4000 characters or less but fails with "ORA-01460: unimplemented or unreasonable conversion requested" when ln_length is > 4000 characters. The block is: DECLARE ls_string VARCHAR2(32767); ls_temp VARCHAR2(32767); ln_length NUMBER := 4000; BEGIN ls_stri...