oracle10g interview questions
Top oracle10g frequently asked interview questions
I am using oracle database. i want to execute one query to check the data between two dates.
NAME START_DATE
------------- -------------
Small Widget 15-JAN-10 04.25.32.000000 PM
Product 1 17-JAN-10 04.31.32.000000 PM
select * from <TABLENAME> where start_date
BETWEEN '15-JAN-10' AND '17-JAN-10'
But I dont get any results from above query. I think I have to use "like" and "%". But I dont know where to use them. Please throw some lights on this.
thanks in advance.
Source: (StackOverflow)
On oracle 10gr2, i have several sql queries that i am comparing performance, but after first run, the v$sql table has the execution plan stored for caching, so for one of the queries i go from 28 seconds on first run to .5 seconds after.
I've tried
ALTER SYSTEM FLUSH BUFFER_CACHE; -- after running this, the query consistently runs at 5 seconds, which i do not believe is accurate.
thought maybe deleting the line item itself from the cache:
delete from v$sql where sql_text like 'select * from....
but get an error about not being able to delete from view.
Source: (StackOverflow)
I am using Linux, Oracle10g.
I have created one user called test. and granted create session and select any dictionary permission to the same user.
i also granted sysdba and sysoper roles to the same users.
Now i want to display all the privileges and roles granted to the user.
I found following query but it shows only create session and select dictionary privileges.
select privilege
from dba_sys_privs
where grantee='SAMPLE'
order by 1;
please help to resolve the issue.
Thanks
Source: (StackOverflow)
I am creating a .NET program that uses odp.net, specifically the 11g version. Our oracle server is running 10g. I am too late in the development process to make a change. Am I heading for trouble? Have you had any experience running 11g client against a 10g server?
Source: (StackOverflow)
Am I correct in understanding that CREATE OR REPLACE basically means "if the object exists, drop it, then create it either way?"
If so, what am I doing wrong? This works:
CREATE TABLE foo (id NUMBER,
title VARCHAR2(4000) DEFAULT 'Default Title')
And this doesn't (ORA-00922: missing or invalid option):
CREATE OR REPLACE TABLE foo (id NUMBER,
title VARCHAR2(4000) DEFAULT 'Default Title')
Am I doing something stupid? I don't seem to be able to find much documentation about this syntax.
Source: (StackOverflow)
I can understand that many years ago there would be this kind of limitation, but nowadays surely this limit could easily be increased. We have naming conventions for objects, but there is always a case that turns up where we hit this limit - especially in naming foreign keys.
Does anybody actually know why this isn't a bigger size - or is it bigger in 11g?
Apparently the answer is that it will break currently scripts that aren't defensively coded. I say that is a very worrying thing, Oracle is trying to be the database, surely this is the kind of thing that you must constantly improve, otherwise your product will die the death of a thousand cuts.
Whenever I see this kind of objection in-house, I think it is time to bite the bullet and sort it out. If people are running scripts that they do not check or maintain when they upgrade Oracle versions, then let them suffer the consequences of that choice. Provide them a compatibility flag, up the size to 4000, then save me the wasted time when I'm creating objects of having to constantly count to 30 to check the name is 'OK'.
Source: (StackOverflow)
Possible Duplicate:
Why does Oracle 9i treat an empty string as NULL?
I have a table in Oracle 10g named TEMP_TABLE with only two columns - id and description just for the sake of demonstration.
The column id is a sequence generated primary key of type NUMBER(35, 0) not null and the column DESCRIPTION is a type of VARCHAR2(4000) not null.
The basic table structure in this case would look something like the following.
+--------------+-----------+---------------+
|Name | Null? | Type |
+--------------+-----------+---------------+
|ID | NOT NULL | NUMBER(35) |
|DESCRIPTION | NOT NULL | VARCHAR2(4000)|
+--------------+-----------+---------------+
After creating this table, I'm trying to insert the following INSERT commands alternatively.
INSERT INTO temp_table (id, description) VALUES (1, null); ->unsuccessful
INSERT INTO temp_table (id, description) VALUES (2, ''); ->unsuccessful
Both of them are unsuccessful as obvious because the not null constraint is enforced on the DESCRIPTION column.
In both of the cases, Oracle complains
ORA-01400: cannot insert NULL into ("WAGAFASHIONDB"."TEMP_TABLE"."DESCRIPTION")
An empty string is treated as a NULL value in Oracle.
If I dropped the not null constraint on the DESCRIPTION column then the basic table structure would look like the following
+--------------+-----------+---------------+
|Name | Null? | Type |
+--------------+-----------+---------------+
|ID | NOT NULL | NUMBER(35) |
|DESCRIPTION | | VARCHAR2(4000)|
+--------------+-----------+---------------+
and both of the INSERT commands as specified would be successful. They would create two rows one with a null value and another with an empty string '' in the DESCRIPTION column of the TEMP_TABLE.
Now, if I issue the following SELECT command,
SELECT * FROM temp_table WHERE description IS NULL;
then it fetches both the rows in which one has a null value and the other has an empty string '' in the DESCRIPTION column.
The following SELECT statement however retrieves no rows from the TEMP_TABLE
SELECT * FROM temp_table WHERE description='';
It doesn't even retrieve the row which has an empty string in the DESCRIPTION column.
Presumably, it appears that Oracle treats a null value and an empty string '' differently here which however doesn't appear to be the case with the INSERT statement in which both a null value and an empty string '' are prevented from being inserted into a column with a not null constraint. Why is it so?
Source: (StackOverflow)
I know this has been answered to some degree with PHP and MYSQL, but I was wondering if someone could teach me the simplest approach to splitting a string (comma delimited) into multiple rows in Oracle 10g (preferably) and 11g.
The table is as follows:
Name | Project | Error
108 test Err1, Err2, Err3
109 test2 Err1
I want to create the following:
Name | Project | Error
108 Test Err 1
108 Test Err 2
108 Test Err 3
109 Test2 Err1
I've seen a few potential solutions around stack, however they only accounted for a single column (being the comma delimited string). Any help would be greatly appreciated.
Source: (StackOverflow)
When executing the following code, it just says the procedure is completed and doesn't print the infomation i want it to (firstName, lastName) and then the other values from the select query in a table below.
CREATE OR REPLACE PROCEDURE PRINT_ACTOR_QUOTES (id_actor char)
AS
CURSOR quote_recs IS
SELECT a.firstName,a.lastName, m.title, m.year, r.roleName ,q.quotechar from quote q, role r,
rolequote rq, actor a, movie m
where
rq.quoteID = q.quoteID
AND
rq.roleID = r.roleID
AND
r.actorID = a.actorID
AND
r.movieID = m.movieID
AND
a.actorID = id_actor;
BEGIN
FOR row IN quote_recs LOOP
DBMS_OUTPUT.PUT_LINE('a.firstName' || 'a.lastName');
end loop;
END PRINT_ACTOR_QUOTES;
/
When setting server output on, I get
a.firstNamea.lastName
a.firstNamea.lastName
a.firstNamea.lastName
a.firstNamea.lastName
multiple times!
Source: (StackOverflow)
This is the first time I've dealt with Oracle, and I'm having a hard time understanding why I'm receiving this error.
I'm using Oracle's ODT.NET w/ C# with the following code in a query's where clause:
WHERE table.Variable1 = :VarA
AND (:VarB IS NULL OR table.Variable2 LIKE '%' || :VarB || '%')
AND (:VarC IS NULL OR table.Variable3 LIKE :VarC || '%')
and I'm adding the parameter values like so:
cmd.Parameters.Add("VarA", "24");
cmd.Parameters.Add("VarB", "test");
cmd.Parameters.Add("VarC", "1234");
When I run this query, the server returns:
ORA-01008: not all variables bound
If I comment out either of the 'AND (....' lines, the query completes successfully.
Why would the query run through alright if I'm only querying with two parameters, but not with three? The error I'm receiving doesn't even make sense
Source: (StackOverflow)
IMO, this query should return A=1,B=2,
SELECT regexp_substr('A=1,B=2,C=3,', '.*B=.*?,') as A_and_B FROM dual
But it returns whole string A=1,B=2,C=3, instead. Why?
UPD: Oracle 10.2+ required to use Perl-style metacharacters in regular expressions.
UPD2:
More clear form of my question (to avoid questions about Oracle version and availability of Perl-style regex extension):
Why ON THE SAME SYSTEM non-greedy quantifier sometimes works as expected and sometimes doesn't?
This works correctly:
regexp_substr('A=1,B=2,C=3,', 'B=.*?,')
This doesn't work:
regexp_substr('A=1,B=2,C=3,', '.*B=.*?,')
fiddle
UPD3:
Yes, it seems to be a bug.
Can anyone provide Oracle Support reaction on this issue?
Is the bug already known?
Does it have an ID?
Source: (StackOverflow)
I have the table with 1 column and has following data
Status
a1
i
t
a2
a3
I want to display the following result in my select query
Status| STATUSTEXT
a1 | Active
i | Inactive
t | Terminated
a2 | Active
a3 | Active
One way I could think was using a Switch When expression in select query
SELECT
status,
CASE status
WHEN 'a1' THEN 'Active'
WHEN 'a2' THEN 'Active'
WHEN 'a3' THEN 'Active'
WHEN 'i' THEN 'Inactive'
WHEN 't' THEN 'Terminated'
END AS StatusText
FROM stage.tst
Is there any other way of doing this where I don't need to write When expression 3 times for Active Status and the entire active status can be checked in one single expression?
Source: (StackOverflow)
What is the query to find the no. of current open cursors in an Oracle Instance?
Also, what is the accuracy/update frequency of this data?
I am using Oracle 10gR2
Source: (StackOverflow)
select DISTINCT a.FNAME||' '||a.LNAME
from AUTHOR a, books B, BOOKAUTHOR ba, customers C, orders
where C.firstname='BECCA'
and C.lastname='NELSON'
and a.AUTHORID=ba.AUTHORID
and b.ISBN=bA.ISBN
order by a.LNAME
gives ORA-01791: not a SELECTed expression
but works without DISTINCT.
How to make it work?
Source: (StackOverflow)
In Oracle, when converting a number with a leading zero to a character, why does the leading number disappear? Is this logic Oracle specific, or specific to SQL?
Example:
SELECT TO_CHAR(0.56) FROM DUAL;
/* Result = .56 */
Source: (StackOverflow)