I have the following SQL statement: SELECT CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" , ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID, LEVEL -1 "LEVEL" FROM ANIMALS START WITH ANIMAL_ID IN( '2360000002558' ) CONNECT BY ((PRIOR SIRE_ANIMAL_ID = ANIMAL_ID and LEVEL < 5) OR (PRIOR DAM_ANIMAL_ID = ANIMAL_ID AND LEVEL < ...
Hi All, i have one table my_table,which contains three columnn namely name,address1,address2. Now i want to search a data from these 3 column which contains single quote. Can anybody suggest. Thanks in advance. ...
In Oracle 10g, how can I drop a unique constraint on a column without knowing the name of the constraint (e.g. a system generated name, which won't necessarily be the same across database instances)? Dropping and recreating the table isn't an option. Is it possible? ...
In Oracle 10g, how do I add a unique case-insensitive constraint on two varchar fields? For example, given the following records already in the table: "Stephen", "Swensen" "John", "Smith" The following inserts would be invalid: "stephen", "Swensen" "John", "smith" "stephen", "swensen" But the following inserts would be valid: "St...
Hello, Recently I have completed my Bachelor of Engineering in Computer Science.And joined as a Software developer in a small Software firm.From my university days I am interested in Oracle but unfortunately I did not manage to learn it.I want to learn Oracle now.Which one will be better for me for starting? ...
This seems so simple but I can't understand how do I do this query: I have a table users like this: user_id | name | role 1 | abc | a 2 | lol | b 3 | f | c and a table usersprojects (with user and project PKs) projectid | userid 1 | 1 1 | 2 2 | 2 2 | 3 How could I select all us...
I'm just learning Oracle and I understand the concept of triggers and sequences. What I can't seem to find is how they are used together in an auto-increment fashion similar to a SqlServer Identity setting. In a really simple example, I have a table called Employees that has three fields (all required): EmployeeID (PK), FirstName, LastNa...
I've got an Oracle 10g database which is accessed from an ASP.NET application. Although I've used SQL Server heavily in many different aspects and Oracle for querying and reporting, this is my first time using Oracle as the OLTP database for an application. The database-level procedures in the packages are typically of the form: -- TY...
Hi, In oracle, Can i run index rebuild in parallel , all indexes belonging to same table. We have a dyanmic sql procedure for rebuilding indexes, so can call that procedure in parallel for 3 indexes belonging to same table. Regards, Shi ...
I have two similar tables in oracle in two different databases. For example : my table name is EMPLOYEE and primary key is employee id. The same table with same columns(say 50 columns are is avlbl in two databases and two databases are linked. I want to compare these two tables column by column and find out which records are not matchi...
Question Example Table +-------------+----------------------+--------+ | category_id | name | parent | +-------------+----------------------+--------+ | 1 | ELECTRONICS | NULL | | 2 | TELEVISIONS | 1 | | 3 | TUBE | 2 | | 4 | LCD ...
I have to log slow queries , let's say queries which takes more than 3ms to run should be logged Most of the times these queries are SELECT how to log them in Oracle ? ...
I'm debugging a procedure which ... returns certain values. The procedure seems to use DBMS_SQL.DESCRIBE_COLUMNS2 which was, till now unknown to me. One of the out variables of the DBMS_SQL.DESCRIBE_COLUMNS2 procedure is a collection, and I want to examine that value is being returned into that - how can I observe/watch/examine this va...
I'm trying to write a query that brings back patients who have had an office visit at least a year before the most recent one. The various ways I'm thinking of get all twisted up in my head, so I was wondering if anyone out there can see an obvious way to write it that I'm missing. Don't worry too much about the exact syntax and naming -...
I'm guessing this is a matter of figuring out what oracle command to use, but after a couple of hours of googling, I haven't found anything that can do what I need. So here's what I need to have happen in a nutshell: Table 1 Table 2 | PrjID | | PrjID | UserID | |----------| |----------|------...
I am new to Oracle 10g. I have couple of tables as shown below: INCOME_MASTER INCOME_ID NUMBER(10) sEQ NUMBER, INCOME_TYPE VARCHAR2(10), INCOME_DATE DATE INCOME_DETAILS INCOME_DETAILS_SEQ_NO NUMBER(10) SEQUENCE, INCOME_ID NUMBER(10), ITEM_ID NUMBER(10), ITEM_VALUE NUMBER (10,...
Hi I have this update statement UPDATE OPENQUERY(linkedServerToOracle ,"SELECT T$TDAT FROM BAAN.TTDSLS031010 WHERE T$CUNO='003098' AND T$CPGS=' 4AN00'") SET T$TDAT=CONVERT(varchar, '4712-01-01 00:00', 121) and the current setting in linkedServerToOracle (10g) is supposed to take the value '4712-01-01 00:00' as pseudo infinite dat...
Hi! I am trying to connect to oracle db without full installation of oracle client done on the machine. I found this helpfull post Using the new ODP.Net to access Oracle from C# with simple deployment but when I try it, I am failed on the next exception: [Oracle.DataAccess.Client.OracleException ORA-12737: Instant Client Light: unsupp...
The compilation error says "mm" and "cc" is invalid identifier! with m as ( select instructor, count(*) as c from class group by instructor), mm as ( select max(m.c) as cc from m) select m.instructor from m where m.c = mm.cc; ...
I have a table my_table with column my_column and a large list my_list of LONGs. I'd like to find all values that present in the my_list but not present in the my_table.my_column. How can I do that with SQL without repeating huge list of LONGs twice? Please shed some light as I'm Oracle beginner. Thank you. EDIT: my_list is just a shor...