Learn Oracle with Yasser: Oracle Sql, Oracle PlSql Interview Questions

Dear my readers, here i collect for you, all questions which can be asked in interview,
i am glad with you visit, prepared by Yasser

SQL – PL/SQL I.V. questions

1- What is a stored procedure?

A stored procedure is a sequence of statements that perform specified function.

Named pl/sql block which perform an action. It is stored in db. as schema object.

2- What is SQL * Loader?

It is product to load data from external file to database. Two types of input must be provide to load: the data itself and control file which has the name and format for data file.

3- What are the 2 parts of package?

Package specification

4- What are date functions?

Add_months - last_day – next_day – months_between & sysdate.

5- What is NVL?

Convert a null value to a non-null value.

6- what is intersect? It is a product of list the matching between 2 tables.

7- how to know the last executed procedure?

select timestamp,owner,obj_name from dba_audit_trail;

8- what is minus? Minus is opposite of intersect, it list the un matches row from 2 tables.

9- what are % type and %rowtype? And advantage of use

fname employees.first_name%TYPE; for provide data type for column

emp_rec1  employees%ROWTYPE; for provide data type for all row of employees table

advantage if the column of database change the variable will also change.

10- what will be the output of this code?

Cursor c is

Select * from emp for update;

Z c1%rowtype;

Begin

Open c1 fetch c1 into z;

End;

By declaring this cursor we can update table emp through z, and by issuing coming or rollback cursor will be closed automatically.

10- What is commit?

make permanent all changes performed in the transaction

Until you commit a transaction: You can roll back (undo) any changes made during the transaction with the ROLLBACK statement.

11- Give the structure of Function.

Create or replace function function_name (arg1 in number) return number

Variables

Begin

End;

12- If the application running very slowly at what point u need to go in order to increase the performance?

We need to check the sql statement blocks, because for every sql statement execution transfer to sql engine and come back to pl engine that process takes more time.

13- What are the advantages of stored procedures?

Extensibility, Modularity, Reusability, Maintainability, one time compilation.

14 what is the difference between stored procedure and application procedure?

stored procedure are subprograms stored in the database, and can be called & executed many time, but application procedure used from particular application.

15- What is cursor and why cursor is required?

The Oracle server uses work areas, called private SQL areas, to execute SQL statements and to store processing information. You can use explicit cursors to name a private SQL area and access its stored information.

Implicit	Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL SELECT statements.
Explicit	For queries that return more than one row, explicit cursors are declared and managed by the programmer and manipulated through specific statements

16- what to use instead of cursor and in what cases?

Just use subquery in for clause for example:

For emprec in (select * from emp)

Loop

Dbms_output.put_line(emprec.empno);

End loop;

No exit statement needed just implicit open, fetch close occur.

17- what is pl/sql?

It is a product of Oracle, it is procedure language extension of sql.

18- How to disable all triggers of table at one time?

Alter table table_name disable all trigger

16. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution

The SET options FEEDBACK and VERIFY can be set to OFF.

17. How do you generate file output from SQL

By use of the SPOOL comm

1. What is a CO-RELATED SUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery. eg

    SELECT  field1 from table1 X

    WHERE  field2>(select avg(field2) from table1 Y

                                      where

                                      field1=X.field1);

(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)

2. What are various joins used while writing SUBQUERIES

Self join-Its a join foreign key of a table references the same table.

Outer Join--Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.

Equi-join--Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

3. What are various constraints used in SQL

NULL NOT NULL CHECK DEFAULT

4. What are different Oracle database objects

TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES etc

5. What is difference between Rename and Alias

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

6. What is a view

A view is stored procedure based on one or more tables, its a virtual table.

7. What are various privileges that a user can grant to another user

SELECT CONNECT RESOURCE

8. What is difference between UNIQUE and PRIMARY KEY constraints

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

9. Can a primary key contain more than one columns

Yes

10. How you will avoid duplicating records in a query

By using DISTINCT

11. What is difference between SQL and SQL*PLUS

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

12. Which datatype is used for storing graphics and images

LONG RAW data type is used for storing BLOB's (binary large objects).

13. How will you delete duplicating rows from a base table

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);

CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;

DROP old_table RENAME new_table TO old_table DELETE FROM table_name A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)

14. What is difference between SUBSTR and INSTR

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE INSTR provides character position in which a pattern is found in a string.

eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

15. There is a string '120000 12 0 .125' ,how you will find the position of the decimal place

INSTR('120000 12 0 .125','.',1) output 13

16. There is a '%' sign in one field of a column. What will be the query to find it.

'\' Should be used before '%'.

17. When you use WHERE clause and when you use HAVING clause

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

18. Which is more faster - IN or EXISTS

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Appropriate answer will be....

Result of the subquery is small Then "IN" is typicaly more appropriate. and Result of the subquery is big/large/long Then "EXIST" is more appropriate.

19. What is a OUTER JOIN

Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.

20. How you will avoid your query from using indexes

SELECT * FROM emp Where emp_no+' '=12345;

i.e you have to concatenate the column name with space within codes in the where condition.

SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;

i.e using HINTS

Oracle Interview Questions

1. What is a pseudo column. Give some examples

It is a column that is not an actual column in the table.

eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.

Suppose customer table is there having different columns like customer no, payments.What will be the query to select top three max payments.

For top N queries, see http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post

2. What is the purpose of a cluster.

Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.

3. What is a cursor.

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

4. Difference between an implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

5. What are cursor attributes

%ROWCOUNT %NOTFOUND %FOUND %ISOPEN

6. What is a cursor for loop.

Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.

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

8. What a SELECT FOR UPDATE cursor represent.

SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.

9. What 'WHERE CURRENT OF ' clause does in a cursor.

LOOP

                         SELECT  num_credits  INTO  v_numcredits  FROM classes

                         WHERE  dept=123 and course=101;

                         UPDATE  students

                         SET current_credits=current_credits+v_numcredits

                         WHERE  CURRENT OF  X;

                         END  LOOP

                         COMMIT;

                         END;

10. What is use of a cursor variable? How it is defined.

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.

11. What should be the return type for a cursor variable.Can we use a scalar data type as return type.

The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE

12. How you open and close a cursor variable.Why it is required.

OPEN cursor variable FOR SELECT...Statement CLOSE 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.

13. How you were passing cursor variables in PL/SQL 2.2.

In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2,the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

14. Can cursor variables be stored in PL/SQL tables.If yes how.If not why.

No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

15. Difference between procedure and function.

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

16. What are different modes of parameters used in functions and procedures.

IN OUT INOUT

17. What is difference between a formal and an actual parameter

The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

18. Can the default values be assigned to actual parameters.

Yes

19. Can a function take OUT parameters.If not why.

Yes. A function return a value, but can also have one or more OUT parameters. it is best practice, however to use a procedure rather than a function if you have multiple values to return.

20. What is syntax for dropping a procedure and a function .Are these operations possible.

              Drop Procedure procedure_name

              Drop Function function_name

21. What are ORACLE PRECOMPILERS.

Using ORACLE PRECOMPILERS ,SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,... This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable.

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

23. Difference between database triggers and form triggers.

a) 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 screen b) Can be row level or statement level No distinction between row level and statement level. c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms. d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger. e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.

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

25. Can you use a commit statement within a database trigger.

26. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

1,000,000

Oracle Interview Questions

1. When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it

Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.

2. If you see contention for library caches how can you fix it

Increase the size of the shared pool.

3. If you see statistics that deal with "undo" what are they really talking about

Rollback segments and associated structures.

4. If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)

The SMON process won?t automatically coalesce its free space fragments.

5. If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)

In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#';? command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ?alter tablespace coalesce;? is best. If the free space isn?t contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.

6. How can you tell if a tablespace has excessive fragmentation

If a select against the dba_free_space table shows that the count of a tablespaces extents is greater than the count of its data files, then it is fragmented.

7. You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about? What if redo log space wait time is high? How can you fix this Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.

8. What can cause a high value for recursive calls? How can this be fixed

A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.

9. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it

This indicate that the shared pool may be too small. Increase the shared pool size.

10. If you see the value for reloads is high in the estat library cache report is this a matter for concern

Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.

11. You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem

A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.

12. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem

A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.

Oracle Interview Questions

1. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for

The first thing to check with a large SGA is that it isn?t being swapped out.

2. What OS user should be used for the first part of an Oracle installation (on UNIX)

You must use root first.

3. When should the default values for Oracle initialization parameters be used as is

Never

4. How many control files should you have? Where should they be located

At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.

5. How many redo logs should you have and how should they be configured for maximum recoverability

You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.

6. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces

At least 7, see disk configuration answer above.

7. Describe third normal form

Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key

8. Is the following statement true or false:

"All relational databases must be in third normal form" False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.

9. What is an ERD

An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.

10. Why are recursive relationships bad? How do you resolve them

A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn?t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.

11. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")

Expected answer: This means the two entities should probably be made into one entity.

12. How should a many-to-many relationship be handled

By adding an intersection entity table

13. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used

A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.

Oracle Interview Questions

1. When should you consider denormalization

Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.

2. How can you determine if an Oracle instance is up from the operating system level

There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is acceptable. For example, on UNIX a ps -ef|grep dbwr will show what instances are up.

3. Users from the PC clients are getting messages indicating : ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)

What could the problem be The instance name is probably incorrect in their connection string.

4. Users from the PC clients are getting the following error stack: ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. HP-UX Error: 2: No such file or directory

What is the probable cause The Oracle instance is shutdown that they are trying to access, restart the instance.

5. How can you determine if the SQLNET process is running for SQLNET V1? How about V2

For SQLNET V1 check for the existence of the orasrv process. You can use the command "tcpctl status" to get a full status of the V1 TCPIP server, other protocols have similar command formats. For SQLNET V2 check for the presence of the LISTENER process(s) or you can issue the command "lsnrctl status".

6. What file will give you Oracle instance status information? Where is it located

The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.

7. Users aren?t being allowed on the system. The following message is received: ORA-00257 archiver is stuck. Connect internal only, until freed What is the problem The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.

8. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs

There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert.log file for this information.

9. You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.

10. You look at your fragmentation report and see that smon hasn?t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem

Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.

11. Your users get the following error: ORA-00055 maximum number of DML locks exceeded What is the problem and how do you fix it The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.

12. You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do

As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following: CONNECT INTERNAL STARTUP MOUNT (Take any read-only tablespaces offline before next step ALTER DATABASE DATAFILE .... OFFLINE;) RECOVER DATABASE USING BACKUP CONTROLFILE ALTER DATABASE OPEN RESETLOGS; (bring read-only tablespaces back online) Shutdown and backup the system, then restart If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well. If no backup of the control file is available then the following will be required: CONNECT INTERNAL STARTUP NOMOUNT CREATE CONTROL FILE .....; However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.

ORACLE SQL PL/SQL Interview Questions

What are the various types of queries ?
Answer: The types of queries are:
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries

What is a transaction ?
Answer: A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

What is implicit cursor and how is it used by Oracle ?
Answer: An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Answer: Public synonyms

What is PL/SQL?
Answer: PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

Is there a PL/SQL Engine in SQL*Plus?
Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

Is there a limit on the size of a PL/SQL block?
Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'

Can one read/write files from PL/SQL?
Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).

Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

How can I protect my PL/SQL source code?
Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap name=myscript.sql

oname=xxxx.yyy

Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)',

DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

What are the various types of Exceptions ?
Answer: User defined and Predefined Exceptions.

Can we define exceptions twice in same block ?
Answer: No.

What is the difference between a procedure and a function ?
Answer: Functions return a single variable by value whereas procedures do not return any variable by value.Rather they return multiple variables by passing variables by reference through their OUT parameter.

Can you have two functions with the same name in a PL/SQL block ?
Answer: Yes.

Can you have two stored functions with the same name ?
Answer: Yes.

Can you call a stored function in the constraint of a table ?
Answer: No.

What are the various types of parameter modes in a procedure ?
Answer: IN, OUT AND INOUT.

What is Over Loading and what are its restrictions ?
Answer: OverLoading means an object performing different functions depending upon the no.of parameters or the data type of the parameters passed to it.

Can functions be overloaded ? Answer: Yes.

Overloading is the idea that the functionality of a PL/SQL stored procedure of function can be changed based on the input datatype Polymorphism was a spin off of the PL/SQL concept called “overloading” Overloading a stored procedure or function refers to the ability of a programming method to perform more than one kind of operation depending upon the context in which the method is used.

For a simple example of overloading, you can write a PL/SQL function that does one thing when a numeric argument is passed to the procedure and another thing when a character string is passed as an argument.

Can 2 functions have same name & input parameters but differ only by return datatype Answer: No.

What are the constructs of a procedure, function or a package ?
variables and constants, cursors, exceptions

Why Create or Replace and not Drop and recreate procedures ? Answer: to preserve the object grants. Grant cannot be dropped.

Can you pass parameters in packages? How? You can pass parameters to procedures or functions in a package.

What are the parts of a database trigger? A trigger event or statement - A trigger restriction - A trigger action

What are the various types of database triggers? There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.

What is the advantage of a stored procedure over a database trigger ?
Answer: We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

What is the maximum no.of statements that can be specified in a trigger statement ? Answer: One.

Can views be specified in a trigger statement ? Exactly one table or view can be specified in the triggering statement. If the INSTEAD OF option is used, then the triggering statement must specify a view; conversely, if a view is specified in the triggering statement, then only the INSTEAD OF option can be used.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the values of :new and :old in Insert/Delete/Update Triggers ?
Answer: INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value

What are cascading triggers? What is the maximum no of cascading triggers at a time?
Answer: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

Describe Oracle database's physical and logical structure ?
Answer:

Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.

Can you increase the size of a tablespace ? How ?
Answer: Yes, by adding datafiles to it.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you increase the size of datafiles ? How ?
Answer: No (for Oracle 7.0)
Yes (for Oracle 7.3 by using the Resize clause )

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can Check constraint be used for self referential integrity ? How ?
Answer: Yes.In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.

ONLINE
OFFLINE
PARTLY AVAILABLE
NEEDS RECOVERY
INVALID.

What is the difference between unique key and primary key ?
Answer: Unique key can be null; Primary key cannot be null.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
Answer: No.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can you pass a parameter to a cursor ?
Answer: Explicit cursors can take parameters, as the example below shows.A cursor parameter can appear in a query wherever a constant can appear.

CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

What are the various types of RollBack Segments ?
Answer: The types of Rollback sagments are as follows :

Public Available to all instances
Private Available to specific instance

Can you use %RowCount as a parameter to a cursor ?
Answer: Yes

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is the query below allowed :
Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15))
Answer: Yes

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is the assignment given below allowed :
ABC = PQR (Where ABC and PQR are records)
Answer: Yes

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How many rows will the following SQL return : Select * from emp Where rownum < 10;
Answer: 9 rows

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How many rows will the following SQL return : Select * from emp Where rownum = 10;
Answer: No rows

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
Answer: Last Record

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A table has the following data : [[5, Null, 10]].What will the average function return ?
Answer: 7.5

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2.Suppose at this point we issue an rollback and again issue a nextval.What will the output be ?
Answer: 3

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Definition of relational DataBase by Dr.Codd (IBM)?
Answer: A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.

RDBMS - R system
Hierarchical - IMS
N/W - DBTG

Difference between Oracle 6 and Oracle 7
Answer:

ORACLE 7                                 ORACLE 6
Cost based optimizer                   Rule based optimizer
Shared SQL Area                       SQL area allocated for each user
Multi Threaded Server                  Single Threaded Server
Hash Clusters                             Only B-Tree indexing
Roll back Size                            Adjustment No provision
Truncate command                      No provision
Distributed Database                   Distributed Query
Table replication & snapshots       No provision
Client/Server Tech                       No provision

What is Functional Dependency?
Answer: Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
While designing in client/server what are the 2 imp.things to be considered ?
Answer: Network Overhead (traffic), Speed and Load of client server

Cannot drop a field
Cannot rename a field
Cannot manage memory
Procedural Language option not provided
Index on view or index on index not provided
View updation problem

When to create indexes ?
Answer: To be created when table is queried for less than 2% or 4% to 25% of the table rows.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How can you avoid indexes ?
Answer: To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. Use an expression in the Where Clause of the SQL.

What is the result of the following SQL : Select 1 from dual UNION Select 'A' from dual;
Answer: Error

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Answer: Yes, database trigger would fire.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the difference between a view and a synonym ?
Answer: Synonym is just a second name of table used for multiple link of database.View can be created with many tables, and with virtual columns and with conditions.But synonym can be on view.

What's the length of SQL integer ?
Answer: 32 bit length

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is the difference between foreign key and reference key ?
Answer: Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
For which relational operators in where clause, index is not used ?
Answer: <> , like '%...' is NOT functions, field +constant, field||''

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Assume that there are multiple databases running on one machine.How can you switch from one to another ?
Answer: Changing the ORACLE_SID

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are the advantages of Oracle ?
Answer: Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols. Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available. Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure.You can also do point-in-time recovery. Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases.Oracle can manage > 100GB databases. Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What is a forward declaration ? What is its use ?
Answer: PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declaration consists of a subprogram specification terminated by a semicolon.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
What are actual and formal parameters ?
Answer: Actual Parameters : Subprograms pass information using parameters.The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg.raise_salary(emp_num, amount);Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.For example, the following procedure declares two formal parameters named emp_id and increase:
Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

What are the types of Notation ?

Answer: Position, Named, Mixed and Restrictions.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
Answer: Yes

Equijoins
Non-equijoins
self join
outer join

What is a package cursor ?
Answer: A package cursor is a cursor which you declare in the package specification without an SQL statement.The SQL statement for the cursor is attached dynamically at runtime from calling procedures.

If you insert a row in a table, then create another table and then say Rollback.In this case will the row be inserted ?
Answer: Yes.Because Create table is a DDL which commits automatically as soon as it is executed.The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.

Yasser

To know the name of database:

select * from global_name;

select name from v$database;

To know the version of database:

SELECT version

         FROM v$instance;

To check the privilege of current user:

select * from session_privs;

To unlock user:

ALTER USER user_name ACCOUNT UNLOCK;

Operators:

SQL> select id from test1;

ID

---------

6 rows selected.

SQL> select id from test2;

ID

---------

SQL> select id from test1

  2  union

  3  select id from test2;

ID

---------

select id from test1

 union all

 select id from test2

ID

---------

select id from test1

intersect

select id from test2

ID

---------

select id from test1

minus

select id from test2

ID

---------

n  To show primary key of table

select column_name from user_cons_columns c,user_constraints s

where c.constraint_name = s.constraint_name

and c.table_name = s.table_name

and lower(s.table_name) = lower('&tab_name')

and constraint_type = 'P'

n To see the procedure source

select text

from user_source

where name = upper('&proc_name')

order by line

n Adjust your sql+

set linesize 1200;

set serveroutput on;

set verify off;

n To see the triggers of table

select trigger_name,status

from user_triggers

where upper(table_name) = upper('&v_tabn')

n Wrap the text

CREATE PROCEDURE wraptest IS

TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;

all_emps emp_tab;

BEGIN

SELECT * BULK COLLECT INTO all_emps FROM employees;

FOR i IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).employee_id);

END LOOP;

END;

اولا الSEQUENCE هي عبار عن OBJECT يتم بنائه في قاعدة البيانات لبناء التسلسلات يعني لو فرضنا عندك جدول كل سجل فيه عند الاضافه يجب ان ياخذ رقم متسلسل

غير عن الذي قبله يعني مثلا يكون مفتاح رئيسي ... 



نستعمل الSEQUENCE لمثل هذه الامور لانه مستحيل يجب قيمتين متشابهتين ... مما يؤدي الى ان يكون الرقم UNIQU اي وحيد لا يوجد له شبيه بالجدول....



ويجب ان يكون له قيمه للبدايه (ولا يشترط قيمه للنهايه)

مثلا يبدأ من 1 ..... وينتهي الى 999



اما عن الاسئله اللي سألتي عنها وهي :



NOCYCLE وتعني ان العداد او الSEQUENCE لا يجب ان يعود من البداية في حالة انه وصل للقيمه العليا لو فرضنا بالمثال السابق ان العداد وصل لغاية 999 لا يصفر من جديد.



CYCLE وهي عكسها يعني اعادة التصفير.



CACH يعني يحتفظ بمعدل 20 قيمه الى الامام في الذاكره دائما وليس كل ما تستدعيه هذا الSEQUENCE يحسب من جديد الى اين وصل بل كل مره يضيف 20 ويحتفظ فيهم بالذاكره

يعني لو فرضنا ان الان العداد واصل ل30 تجد انه محتفظ لغاية 50 بالارقام التاليه دون اعادة احتساب بالذاكره.

NOCACH توقفها......



NEXTVAL وهي القيمه التاليه للقيمه CUURVAL لو فرضنا ان CURRVAL = 10 اذن NEXTVAL=11 ....

Find duplication of record

cursor dup is

  select bank_out, count(*)

from ap_ord_CHK

where rec_no = wrec_no

and system_type = wSYSTEM_TYPE and ar_typ = wAR_TYP and year = wYEAR

group by bank_out

having count(*) > 1;

Pages

Monday, June 8, 2015

Oracle Sql, Oracle PlSql Interview Questions

Implicit

Oracle Interview Questions

Oracle Interview Questions

Oracle Interview Questions

Oracle Interview Questions

ORACLE SQL PL/SQL Interview Questions

No comments:

Post a Comment

Interesting Links