What are Procedure, functions and Packages?
Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks. Procedures do not return values while Functions return one Value.
Packages: Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
What is a ROWID and Why do I need that
ROWID is the physical address (location) of the row on the disk. This is the fastest way to access a row in a table
How many types of SQL Statements are there in Oracle
There are basically 6 types of SQL statements. They are
Data Definition Language (DDL): The DDL statements define and maintain objects and drop objects.
Data Manipulation Language (DML): The DML statements manipulate database data.
Transaction Control Statements: Manage change by DML
Session Control: Used to control the properties of current session enabling and disabling roles and changing.
E.g. Alter Statements,
Set Role
System Control Statements: Change Properties of Oracle Instance.
E.g.: Alter System
Embedded SQL: Incorporate DDL, DML and T.C.S in Programming Language.
E.g. Using the SQL Statements in languages such as ‘C’, Open, Fetch,
execute and
close
How many Integrity Rules are there and what are they?
There are Three Integrity Rules. They are as follows:
Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null
Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
How do u implement the If statement in the Select Statement?
We can implement the if statement in the select statement by using the Decode statement.
e.g select DECODE (EMP_CAT,’1',’First’,’2',’Second’Null);
Here the Null is the else statement where null is done .
How many types of Exceptions are there
There are 2 types of exceptions. They are System Exceptions:
e.g. When no_data_found, When too_many_rows
User Defined Exceptions: e.g. My_exception exception,When My_exception then
How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code.
What are snap shots and views?
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated.
What is the difference between candidate key, unique key and primary key?
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.
Physical Storage of the Data
The finest level of granularity of the database are the data blocks.
Data Block: One Data Block correspond to specific number of physical database space
Extent: Extent is the number of specific number of contigious data blocks.
Segments: Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment: Non Clustered Table has data segment data of every table is stored in cluster data segment
b) Index Segment: Each Index has index segment that stores data
c) Roll Back Segment: Temporarily store ‘undo’ information
What is Row Chaining
The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks.
What is the difference between deleting and truncating of tables
Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.
What is the Difference between a post query and a pre query?
A post query will fire for every row that is fetched but the pre query will fire only once.
Deleting the Duplicate rows in the table?
We can delete the duplicate rows in the table by using the Rowid
query:
DELETE FROM table_name a
Where rowid>(select min(rowid) from table_name b where a.table_no=b.table_no);
Can U disable database trigger? How?
Yes. With respect to table
ALTER TABLE TABLE [DISABLE all_trigger ]
Is space acquired in blocks or extents?
In extents .
what is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values .
Can not applied for HASH.
what are the datatypes supported By oracle (INTERNAL)?
Varchar2, Number, Char, MLSLABEL.
What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
How can I view the dependencies of a PL/SQL block?
Basically there are two types of dependencies a pl/sql block may have. Dependency on another pl/sql block called as procedural dependency. Dependency on a database object called as object dependency. For viewing the procedural dependencies we have the following group of views.
USER_DEPENDECIES, ALL_DEPENDENCIES and DBA_DEPENDENCIES
in the data dictionary
DBA_DEPENDENCIES: Shows both the dependencies. This views primarily has these following columns OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE,REFERENCED_LINK_NAME
Another view can be used to find the object dependencies called as DEPTREE and IDEPTREE for indirect dependencies.
Can one read/write files from PL/SQL?
Included in Oracle is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be set in the init.ora by using UTL_FILE_DIR parameter. It can be set to * to allow writes to any directory in the file system before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
following is the example to open and write to a file ‘file1' and then close.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(‘/tmp’, ‘file1', ‘w’);
UTL_FILE.PUTF(fileHandler, ‘the lines of text\n’);
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, ‘ERROR: Invalid path for file or
path not in INIT.ORA.’);
END;
What are the various types of Exceptions?
User defined and Predefined Exceptions.
Can we define exceptions twice in same block?
No
What is Cursor?Why cursor is required?
cursor is named private sql area from where information can be accessed.cursor are required to process rows individually for queries returning multiple rows
What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI,a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.
What does the PL/SQL Optimizer do?
The PL/SQL Optimizer will rearrange code for better performance during the translation of source code to system code; this is done by default. Valid ranges for the parameter PLSQL_OPTIMIZE_LEVEL are from 0 to 3 where the higher the value the more the compiler will try and optimize.
Name the two profiler tools and describe what they do.
1. The Profiler API, DBMS_PROFILER package, will compute the time a PL/SQL program spends at each line of code and within each subprogram; very handy if you're trying to just figure out where time is spent. This package will save the statistics it generates into database tables so you can query them.
2. PL/SQL hierarchical profiler, DBMS_HPROF package, will report on the dynamic execution profile of the PL/SQL code; generating a report with an option to also store into database tables for reporting.
How might you determine to use PL/SQL Native Compilation to speed your code?
While you can natively compile any PL/SQL code, it is not always the best thing to do. Native compilation is suited better for those computational intensive procedures, not code that just runs SQL statements.
Have you ever used bulk processing? Why?
You've got to say yes here, right? Bulk processing enables you, through FORALL (for selecting data) and BULK COLLECT (for inserts, updates, deletes), to tune the communication layer (context switching) between the PL/SQL engine and SQL engine to improve performance. These are two very important PL/SQL performance enhancers that you cannot do without, both in your code and for an interview.
Well, there are the top questions that come to mind when I think about performance for PL/SQL code. Some of them are clearly PL/SQL code related but some are, (I hope you noticed), from a DBA perspective. I've said this before but many companies are expecting developers to have some DBA performance / tuning knowledge. Not having some experience with things like tracing, running an explain plan, or checking to see if an index is being used within your application are detrimental to you getting that next job. Do remember that these questions are not hard-n-fast questions. If I were the interviewer I'd most definitely jump off into additional real-world scenarios and see if the interviewee actually coded something, actually tuned something, and had some form of methodology in place that allowed them to circumvent performance issues. Giving your interviewer clear samples on how you have pieced together code as well as how you tested its performance can be priceless.
What are the modes of parameters that can be passed to a procedure?
Answer : IN,OUT,IN-OUT parameters.
What are advantages of Stored Procedures?
Answer : Extensibility,Modularity, Reusability, Maintainability and one time compilation.
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 don?t satisfy the join condition.
What are different Oracle database objects?
-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc
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 statements into calls to the precompiler runtime library. The output must be compiled and linked with this library to creator an executable.
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.
What is Database Trigger ?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.
What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
Procedures and Functions,Packages and Database Triggers.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
What is an Exception? What are types of Exception?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
What are the return values of functions SQLCODE and SQLERRM?
SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.
Is it possible to disable the parameter from while running the report?
Yes
What are the different file extensions that are created by oracle reports?
Rep file and Rdf file.
What are the various types of parameter modes in a procedure?
IN, OUTAND INOUT.
How can I get the source code for a function or a procedure or a package from the database?
Query the view ALL_SOURCE. It has a field called TYPE, which says whether the object is a FUNCTION or a PACKAGE or a PACKAGE BODY or a PROCEDURE.
The field TEXT gives the actual source code for that object.
Example:
SELECT TEXT FROM ALL_SOURCE WHERE
NAME=’FUNCTION_NAME';
What is the maximum no. of statements that can be specified in a trigger statement ?
One.
Describe Oracle database’s physical and logical structure ?
Physical: Data files, Redo Log files, Control file.
Logical: Tables, Views, Table spaces, etc.
What is the difference between unique key and primary key?
Unique key can be null; Primary key cannot be null.
What are the various types of RollBack Segments?
Public Available to all instances
Private Available to specific instance
Is Sysdate a system variable or a system function?
System Function
What are the disadvantages of SQL?
Disadvantages of SQL are: Cannot drop a field, Cannot rename a field, Cannot manage memory, Procedural Language option not
When to create indexes?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
How can you avoid indexes?
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 difference between a view and a synonym?
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.
Can dual table be deleted, dropped or altered or updated or inserted? -
Yes
What are various types of joins?
Equi joins, Non-equi joins, self-join, outer join
What is a package cursor?
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?
Yes. because create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the creates statement fails internally (ex. table already exists error) and not syntactically.
What are the various types of queries?
Normal Queries, Sub Queries, Co-related queries, Nested queries, Compound queries
Is there a PL/SQL Engine in SQL*Plus?
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.
Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks. Procedures do not return values while Functions return one Value.
Packages: Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
What is a ROWID and Why do I need that
ROWID is the physical address (location) of the row on the disk. This is the fastest way to access a row in a table
How many types of SQL Statements are there in Oracle
There are basically 6 types of SQL statements. They are
Data Definition Language (DDL): The DDL statements define and maintain objects and drop objects.
Data Manipulation Language (DML): The DML statements manipulate database data.
Transaction Control Statements: Manage change by DML
Session Control: Used to control the properties of current session enabling and disabling roles and changing.
E.g. Alter Statements,
Set Role
System Control Statements: Change Properties of Oracle Instance.
E.g.: Alter System
Embedded SQL: Incorporate DDL, DML and T.C.S in Programming Language.
E.g. Using the SQL Statements in languages such as ‘C’, Open, Fetch,
execute and
close
How many Integrity Rules are there and what are they?
There are Three Integrity Rules. They are as follows:
Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null
Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
How do u implement the If statement in the Select Statement?
We can implement the if statement in the select statement by using the Decode statement.
e.g select DECODE (EMP_CAT,’1',’First’,’2',’Second’Null);
Here the Null is the else statement where null is done .
How many types of Exceptions are there
There are 2 types of exceptions. They are System Exceptions:
e.g. When no_data_found, When too_many_rows
User Defined Exceptions: e.g. My_exception exception,When My_exception then
How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code.
What are snap shots and views?
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated.
What is the difference between candidate key, unique key and primary key?
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.
Physical Storage of the Data
The finest level of granularity of the database are the data blocks.
Data Block: One Data Block correspond to specific number of physical database space
Extent: Extent is the number of specific number of contigious data blocks.
Segments: Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment: Non Clustered Table has data segment data of every table is stored in cluster data segment
b) Index Segment: Each Index has index segment that stores data
c) Roll Back Segment: Temporarily store ‘undo’ information
What is Row Chaining
The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks.
What is the difference between deleting and truncating of tables
Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.
What is the Difference between a post query and a pre query?
A post query will fire for every row that is fetched but the pre query will fire only once.
Deleting the Duplicate rows in the table?
We can delete the duplicate rows in the table by using the Rowid
query:
DELETE FROM table_name a
Where rowid>(select min(rowid) from table_name b where a.table_no=b.table_no);
Can U disable database trigger? How?
Yes. With respect to table
ALTER TABLE TABLE [DISABLE all_trigger ]
Is space acquired in blocks or extents?
In extents .
what is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values .
Can not applied for HASH.
what are the datatypes supported By oracle (INTERNAL)?
Varchar2, Number, Char, MLSLABEL.
What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
How can I view the dependencies of a PL/SQL block?
Basically there are two types of dependencies a pl/sql block may have. Dependency on another pl/sql block called as procedural dependency. Dependency on a database object called as object dependency. For viewing the procedural dependencies we have the following group of views.
USER_DEPENDECIES, ALL_DEPENDENCIES and DBA_DEPENDENCIES
in the data dictionary
DBA_DEPENDENCIES: Shows both the dependencies. This views primarily has these following columns OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE,REFERENCED_LINK_NAME
Another view can be used to find the object dependencies called as DEPTREE and IDEPTREE for indirect dependencies.
Can one read/write files from PL/SQL?
Included in Oracle is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be set in the init.ora by using UTL_FILE_DIR parameter. It can be set to * to allow writes to any directory in the file system before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
following is the example to open and write to a file ‘file1' and then close.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(‘/tmp’, ‘file1', ‘w’);
UTL_FILE.PUTF(fileHandler, ‘the lines of text\n’);
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, ‘ERROR: Invalid path for file or
path not in INIT.ORA.’);
END;
What are the various types of Exceptions?
User defined and Predefined Exceptions.
Can we define exceptions twice in same block?
No
What is Cursor?Why cursor is required?
cursor is named private sql area from where information can be accessed.cursor are required to process rows individually for queries returning multiple rows
What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI,a transaction begins with first executable statement and ends when it is explicitly committed or rolled back.
What does the PL/SQL Optimizer do?
The PL/SQL Optimizer will rearrange code for better performance during the translation of source code to system code; this is done by default. Valid ranges for the parameter PLSQL_OPTIMIZE_LEVEL are from 0 to 3 where the higher the value the more the compiler will try and optimize.
Name the two profiler tools and describe what they do.
1. The Profiler API, DBMS_PROFILER package, will compute the time a PL/SQL program spends at each line of code and within each subprogram; very handy if you're trying to just figure out where time is spent. This package will save the statistics it generates into database tables so you can query them.
2. PL/SQL hierarchical profiler, DBMS_HPROF package, will report on the dynamic execution profile of the PL/SQL code; generating a report with an option to also store into database tables for reporting.
How might you determine to use PL/SQL Native Compilation to speed your code?
While you can natively compile any PL/SQL code, it is not always the best thing to do. Native compilation is suited better for those computational intensive procedures, not code that just runs SQL statements.
Have you ever used bulk processing? Why?
You've got to say yes here, right? Bulk processing enables you, through FORALL (for selecting data) and BULK COLLECT (for inserts, updates, deletes), to tune the communication layer (context switching) between the PL/SQL engine and SQL engine to improve performance. These are two very important PL/SQL performance enhancers that you cannot do without, both in your code and for an interview.
Well, there are the top questions that come to mind when I think about performance for PL/SQL code. Some of them are clearly PL/SQL code related but some are, (I hope you noticed), from a DBA perspective. I've said this before but many companies are expecting developers to have some DBA performance / tuning knowledge. Not having some experience with things like tracing, running an explain plan, or checking to see if an index is being used within your application are detrimental to you getting that next job. Do remember that these questions are not hard-n-fast questions. If I were the interviewer I'd most definitely jump off into additional real-world scenarios and see if the interviewee actually coded something, actually tuned something, and had some form of methodology in place that allowed them to circumvent performance issues. Giving your interviewer clear samples on how you have pieced together code as well as how you tested its performance can be priceless.
What are the modes of parameters that can be passed to a procedure?
Answer : IN,OUT,IN-OUT parameters.
What are advantages of Stored Procedures?
Answer : Extensibility,Modularity, Reusability, Maintainability and one time compilation.
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 don?t satisfy the join condition.
What are different Oracle database objects?
-TABLES
-VIEWS
-INDEXES
-SYNONYMS
-SEQUENCES
-TABLESPACES etc
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 statements into calls to the precompiler runtime library. The output must be compiled and linked with this library to creator an executable.
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.
What is Database Trigger ?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.
What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?
Procedures and Functions,Packages and Database Triggers.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
What is an Exception? What are types of Exception?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined execptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
What are the return values of functions SQLCODE and SQLERRM?
SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.
Is it possible to disable the parameter from while running the report?
Yes
What are the different file extensions that are created by oracle reports?
Rep file and Rdf file.
What are the various types of parameter modes in a procedure?
IN, OUTAND INOUT.
How can I get the source code for a function or a procedure or a package from the database?
Query the view ALL_SOURCE. It has a field called TYPE, which says whether the object is a FUNCTION or a PACKAGE or a PACKAGE BODY or a PROCEDURE.
The field TEXT gives the actual source code for that object.
Example:
SELECT TEXT FROM ALL_SOURCE WHERE
NAME=’FUNCTION_NAME';
What is the maximum no. of statements that can be specified in a trigger statement ?
One.
Describe Oracle database’s physical and logical structure ?
Physical: Data files, Redo Log files, Control file.
Logical: Tables, Views, Table spaces, etc.
What is the difference between unique key and primary key?
Unique key can be null; Primary key cannot be null.
What are the various types of RollBack Segments?
Public Available to all instances
Private Available to specific instance
Is Sysdate a system variable or a system function?
System Function
What are the disadvantages of SQL?
Disadvantages of SQL are: Cannot drop a field, Cannot rename a field, Cannot manage memory, Procedural Language option not
When to create indexes?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
How can you avoid indexes?
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 difference between a view and a synonym?
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.
Can dual table be deleted, dropped or altered or updated or inserted? -
Yes
What are various types of joins?
Equi joins, Non-equi joins, self-join, outer join
What is a package cursor?
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?
Yes. because create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the creates statement fails internally (ex. table already exists error) and not syntactically.
What are the various types of queries?
Normal Queries, Sub Queries, Co-related queries, Nested queries, Compound queries
Is there a PL/SQL Engine in SQL*Plus?
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.
