oracle

Efficient Alternative to Outer Join

The RIGHT JOIN on this query causes a TABLE ACCESS FULL on lims.operator. A regular join runs quickly, but of course, the samples 'WHERE authorised_by IS NULL' do not show up. Is there a more efficient alternative to a RIGHT JOIN in this case? SELECT full_name FROM (SELECT operator_id AS authorised_by, full_name ...

Any good C# code generator for using oracle stored procedures?

I'm looking for a C# code generator which generate C# automatically to access stored procedures on Oracle Database. For instance, if I have some packages on schema DEV like: PACKAGE1 with FUNC1, FUN2 PACKAGE2 with FUNC3, FUN4 The generator creates code to run from C# the following code: int a = PACKAGE1.FUNC1(12, 34, "hello"); An...

Does the order of tables referenced in the ON clause of the JOIN matter?

Does it matter which way I order the criteria in the ON clause for a JOIN? select a.Name, b.Status from a inner join b on a.StatusID = b.ID versus select a.Name, b.Status from a inner join b on b.ID = a.StatusID Is there any impact on performance? What if I had multiple criteria? Is one order more maintainable than another? ...

ORA-08177: can't serialize access for this transaction

I have a very simple code using ADO.NET which throws ORA-08177 exception. I am not sure what's wrong with this. I am trying this on a windows vista machine which has oracle 32 bit client installed. My compile option for visual studio is set to x86 platform. Dim connection As OracleConnection = Nothing Dim transaction As OracleTransactio...

Oracle PL/SQL: Loop Over Trigger Columns Dynamically

Inside of a trigger I'm trying to loop over all columns on a table and compare the new values to the old values. Here is what I have so far: CREATE OR REPLACE TRIGGER "JOSH".TEST#UPD BEFORE UPDATE ON "JOSH"."TEST_TRIGGER_TABLE" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare oldval varchar(2000); newval varchar(200...

validation on email / postcode fields in sql/oracle

Hi, Would be gratefull for some advice on the following - Is it possible to validate email and postcode fields through some kind of check constraint in the sql in oracle ? or this kind of thing as i suspect pl/sql with regular expressions ? Thanks ...

sqlldr corrupts my primary key after the first commit

Sqlldr is corrupting my primary key index after the first commit in my ctl file. After the first, no matter what I set the rows value to in my control file, I get: ORA-39776: fatal Direct Path API error loading table PE_OWNER.CLINICAL_CODE ORA-01502: index 'PE_OWNER.CODE_PK' or partition of such index is in unusable state SQL*Loader-20...

Application that works in both SQL Server And Oracle Databases

What is the best approach to build a small (but scalable) application that works with Sql Server or Oracle? I'm interested in build apps that supports multiple databases, in the process behind the feature. ...

OracleDependency not firing event

I have an Oracle Connection to a ORACLE 10.2 database and want to listen for changes to a table. The user of the connection was granted the privilege "CHANGE NOTIFICATION". Listening is initialized with the following method: private OracleDependency SubscribeToTable(string tableName) { string sql = "select * from " + tableName; var...

Oracle datafiles on a Network Share

I have an Oracle 8.1.7 Server running on Windows 2000 Advanced Server in a Virtual Machine. We are currently using MS Virtual Server to host this. (The allocated hardware is powerful enough - we have 3.5GB RAM assigned, and a single 2GHz processor core, more than most servers in 1999) One of the limitations of Virtual Server i sthe ma...

Use Oracle 6 from ASP.NET application

I have an ASP.NET 2.0 web application. It uses "System.Data.OracleClient, Version=2.0.0.0" to access an oracle database. I get the following exception on connection: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. I looked it up and found several articles/posts that say that the error is misleading and ...

SQL Tuning

How do we insert data about 2 million rows into a oracle database table where we have many indexes on it? I know that one option is disabling index and then inserting the data. Can anyone tell me what r the other options? ...

SQL CASE STATEMENT in COUNT CLAUSE

I'm trying to get a product's name and its number of sales from two separate tables. My tables look something like this: BOOK Book_ID | Book_Title | Book_Author SOLD Transaction_ID | Book_ID | Customer_ID I can get most of the results I want from the following query SELECT b.Book_Title, COUNT(s.Book_ID) FROM Book b, Sold s W...

Why doesn't ORACLE allow consecutive newline characters in commands?

I write: :CREATE TABLE Person ( :name CHAR(10), : :ssn INTEGER); and save it to a file "a.sql" (colon represents beginning of line, is not in actual code.) If I then run it by typing "@a" in the mysql command prompt, it will tell me that the line starting with "ssn" is not recognized as a command, and is ignored. From what I gathe...

How to load Java Stored Procedure through JDBC into Oracle 10g?

I'm trying to load some java stored procedures into an Oracle 10g database through JDBC. The statement I'm executing is - CREATE OR REPLACE JAVA SOURCE NAMED "test.Test" AS package test; public class Test { public static String myMethod(String a) { return a; } }; Running this through TOAD works just fine, but when running...

How to create an Oracle sequence starting with max value from a table?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work? CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH (SELECT MAX(trans_seq_no) FROM TRANSACTION_LOG) INCREMENT BY 1 CACHE 20; ...

Always show decimal places in SQL?

Hi, I'm working on a query which returns numeric values (currency). Some of the values are whole numbers and are being displayed as 3, 5, 2 etc whilst other numbers are coming up like 2.52, 4.50 etc. How can I force oracle to always show the decimal places? Thanks ...

SQL AVG(COUNT(*))?

Hi, I'm trying to find out the average number of times a value appears in a column, group it based on another column and then perform a calculation on it. I have 3 tables a little like this DVD ID | NAME 1 | 1 2 | 1 3 | 2 4 | 3 COPY ID | DVDID 1 | 1 2 | 1 3 | 2 4 | 3 5 | 1 LOAN ID | DVDID |...

Is there a method to copy a new column in an existing table and copy the column info from other table in Oracle?

Can you please forward me the answer for my oracle project? I have two different tables, invoice and customer. In the invoice table I already have a column called date and in the customer table I don't have the date column and I already created. I don't know how to bring the date data from the invoice table. Can anyone answer this quest...

Oracle WITH CLAUSE not working?

Hi, I'm trying to use a WITH clause in a query but keep getting the message ORA-00942: table or view does not exist I've tried to create a simple query just as an example here: WITH test AS ( SELECT COUNT(Customer_ID) FROM Customer ) SELECT * FROM test; But even this dosen't work, it just gives the message: SELE...