-
Difference between Optimizer=ALL_ROWS and Optimizer=CHOOSE
Choose - Choose either Rule based or Cost based depend on theavailability of statistics. If statistics is available on the table it usesCBO and if not it uses RBO. ALL_ROWS - Choose based optimization, statistics is needed. U couldpass as hint in ur query.But in the majority of cases with optimizer_mode=choose ( and with a goodstatistics ) the CBO will be able to find a good...
Manoj Seemar
- Nov 2nd, 2007
The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance.CHOOSEThe optimizer chooses between a cost-based approach and a rule...
-
-
Difference between database triggers and form triggers?
Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screenCan be row level or statement level No distinction between row level and statement level.Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.Can be fired from any...
rajakumar_na
- Nov 14th, 2007
Database Trigger: We can't invoke a Form Trigger in DatabaseFrom Trigger: We can invoke a Database Trigger in Forms
Mohit Dhamija
- Jul 3rd, 2006
Even 'Form Trigger' can be fired before and after the event. ex- Triggers like Pre Block / Pre Record / Post Block / Post Record ..
-
How to add a primary key to a table that is not having any primary key?How to display the Date of birth from a table in the dd-mm-yyyy format?
Waseem Mehmood
- Nov 20th, 2007
ALTER TABLE table_nameADD CONSTRAINT table_column_pk PRIMARY KEY (column_name);It is a table level primary key constraint applied on an existing table. SELECT TO_CHAR(DOB, 'DD-MM-YYYY') FROM table_name;
-
How to know the size of the table we created?
Waseem Mehmood
- Nov 20th, 2007
SELECT BYTES/1024 FROM DBA_EXTENTS WHERE OWNER = 'SCOTT' AND SEGMENT_NAME = 'EMP'; This will return you size of table in Kilo Bytes. User is Scott and Table is Emp.
-
How to Create a table in SQL with a table name depending on the date the code is run ?
For e.g. Create table tempOCT as Select....If i run the code in any day of Nov the table name should be tempNOV.
vishal gupta
- Nov 21st, 2007
You can do one this?in the code itself that is going to create the table at run time incorporate the logic to get the currect date,?take it as string variable and then in the code itself you can write the query?Create table as(..)Might help you.Vishal.
Waseem Mehmood
- Nov 20th, 2007
I think it is not possible, i try to concate date with table name but no vail.You can not rename the table name by cancating date
-
Index on coloumns
How to see on what coloumns the indexes have been created?
Waseem Mehmood
- Nov 29th, 2007
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'table_name'-- It will give u all indexes in required table
-
Date functions
How to know the days or months or years between two employees in emp table.
jbabu
- Dec 10th, 2007
Select hiredate,lead(hiredate,1) over(order by hiredate) next_months, round(months_between(lead(hiredate,1) over(order by hiredate),hiredate),3) months from emp;
-
How to drop a column level constraint?
I cant drop the whole column because that is interlinked with my process? I have declared not null on a column, I have to change it
kishore.giri
- Dec 10th, 2007
Dear Sarika,Instead dropping the constraint just disable the constarint.The command is:Alter table table_name disable constraint constraint_name;try it and reply me
-
How to delete a constraint if it is not named?
kishore.giri
- Dec 10th, 2007
Dear Shruti,First query the data dictionary view "USER_CONS_COLUMNS" to find the constraint name.Then instead of dropping the constraint just disable it.step1:--> select constraint_name,c...
-
SQL Query help
Needed a SQL query for this----------I have table with columns firstname, lastname and phone number and we have 2 entries in that table ...now I want to know the persons with this combination firstname of person = lastname of second person? ln phone----------------------------------john cubick 1223sam course 4556output:john course
geminine79
- Jan 3rd, 2008
I think you can use the cartesian product on the same table and try to get the data out of that. try this and let me know.
naveen.papineni
- Nov 26th, 2007
Select first_name ,last_name ,phone_number from table where first_name in (select last_name from table )I think this will work out.RegardsNaveen.
-
How to find the difference between two dates omitting weekends and holidays?
vanigreat
- Jan 17th, 2008
select (to_date('31/01/2008','dd/mm/rrrr')-to_date('01/01/2008','dd/mm/rrrr')+1) - (select count(days) from (selectto_char(to_date('01/01/2008','dd/mm/rrrr&...
-
Display the records between two range?
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);
LRANI
- Jul 31st, 2007
Hi,minus is used for finding the missing records between 2 tables and not finding the records between the range.It is BETWEEN operator that displays the records between 2 ranges from single table.
-
What is SQL Tuning?
What is SQL Tuning? How does it work? Thanks
abhi2433
- Feb 6th, 2008
SQL tuning is the process of ensuring that the SQL statements that an application will issue will run in the fastest possible time
-
Difference between NO DATA FOUND and %NOTFOUND
NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.
g_sidhu
- Feb 6th, 2008
NO DATA FOUND: Is an exception which is raised when no rows are retrieved from the database in a SELECT statement, then PL/SQL raises the exception NO_DATA_FOUND.%NOTFOUND: is a Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows.
syam sundar
- Aug 17th, 2007
NO DATA FOUND is an exceptionWhen your?SELECT INTO?statement?returns no rows that time an error raised by oracle. The error is ORA-01403 NO DATA FOUNDWhereas %NOTFOUND?is a?cursor attributeIt ret...
-
How you open and close a cursor variable.Why it is required?
OPEN cursor variable FOR SELECT...StatementCLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.
g_sidhu
- Feb 6th, 2008
• The OPEN-FOR statement associates a cursor variable with a multirow query, executes the query, identifies the result set, and positions the cursor to point to the first row of the result set.&b...
S.Senthil Kumar
- Oct 31st, 2006
Without Open Cursor statement the data which is in the table cannot be load into cursor variable.The Close Cursor statement deallocates the memory occupied by the cursor
-
What is OCI. What are its uses?
Oracle Call Interface is a method of accesing database from a 3GL program. Uses--No precompiler is required,PL/SQL blocks are executed like other DML statements. The OCI library provides-functions to parse SQL statemets-bind input variables-bind output variables-execute statements-fetch the results
g_sidhu
- Feb 6th, 2008
The Oracle Call Interface (OCI) is a set of low-level APIs (Application Programming Interface Calls) used to interact with Oracle databases. It allows one to use operations like logon, execute, parse,...
-
What is an UTL_FILE.What are different procedures and functions associated with it?
UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
g_sidhu
- Feb 7th, 2008
With the Oracle-supplied UTL_FILE package, you can read from and write to operating system files. It Provides security for directories on the server through the init.ora file. UTL_FILE Procedures and ...
Rakesh
- Mar 20th, 2007
Utl_file is oracle procedure tht enable file access .You can write ,read to file.Also possible is to del or rename a file.It requires the use of a directory object.
-
Difference between case expresssion and case statement
g_sidhu
- Feb 7th, 2008
The CASE statement supported by PL/SQL is very similar to the CASE expression. The main difference is that the statement is finished with an END CASE statement rather than just END. The PL/SQL statements are essentially an alternative to lists of IF .. THEN .. ELSIF statements:
oraappsexp
- Feb 9th, 2006
CASE Expressions And StatementsThe CASE expression was first added to SQL in Oracle8i. Oracle9i extends its support to PL/SQL to allow CASE to be used as an expression or statement:Value Match CASE Ex...
-
What is the difference between single quote (') and double quote(") in relates to using in SQL. When do you use 'xxx' and "xxx"? what is the difference both of them?
g_sidhu
- Feb 7th, 2008
Date and character literals must be enclosed within single quotation marks (' ');If a column alias contains a space, it should be enclosed in double quotation marks.
rampratap409
- Dec 4th, 2006
double quote(") is used to give user defined column alias example:select empno , ename "employee name" from emp;this will print column heading as: EMPNO ...
Oracle SQL Interview Questions
Ans