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 ...
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 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? ...
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...
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...
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 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...
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. ...
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...
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...
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 ...
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? ...
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...
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...
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...
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; ...
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 ...
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 |...
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...
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...