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