- Explain disadvantages in file based processing.(markch/ap09,oc/nov09)
- Differentiate between hierarchical model and network model.(mark/ap2009)
- Define instance and scheme( oct/nov09 08)
- List the responsibilities of database manager.
- What are symbols used in E-R diagrams?(March/April.2009,oct/nov.07)
- Explain data independence. (march/april.09,oct/nov.07/nov2010)
- What are the difference between generalization and specialization
- Explain about concept of aggregation. (oct/nov.09)
- What do you means by SQL? What are the benefits of SQL?.
- What is an embedded SQL?
- What do you meant by pseudo column? Explain with examples. (oct/nov.09,08)
- What are the differences between Char and varchar2 data types?(M/A.09)
- Write any four data functions with syntax and examples
- State the four features of SQL. (March/April 2009)
- What is the process of creating a Table
- Define a sequence
- Difference between view and table.
- Write the names of four schema objects.
- Define is PL/SQL global and system variables.
- List the features of PL/SQL.
- What are Naming Conventions in PL/SQL?
- List any four predefined PL/SQL exceptions.
- Compare various parameter modes.
- What is a cursor? Explain the management of explicit cursor.
- What is implicit cursor?
- What is a stored sub program
- List the advantages of packages
- How to compile a PL/SQL program?
- What is packaged procedure? Give example.
- Define package.
- What is database model?(oct/nov08)
- What do you meant by generalization, specialization and aggregation. (Oct/Nov.09,08,07)
- Explain the various mapping cardinalities. (Oct/Nov.07)
- Explain the various normal forms in details. (Oct/Nov.09)
- What is database abstraction? Explain with the help of a diagram.(m/ap09,oc/no-09)
- Explain the functions of Database Administrator
- Explain various data types available in SQL. (Oct/Nov.2009, March/April.2009)
- Explain about any 12 function of SQL. (MARCH/APRIL 2009)
- Explain the following SQL statements (a) insert (b)update (March/April.2009)
- (a)Explain the various data format models in SQL. (b)Explain set operators of SQL
- Explain group function of SQL. (Oct/Nov.2008, 2007)
- (a.)Explain about DML statements (Oct/Nov.2009) (b) explain about data types supported by SQL. (Oct/Nov.2009)
- Explain the process of creating (a) sequence (b)cluster (c)synonym (d) index
- Explain the procedure for creating, altering and dropping a sequence
- (a.) Explain the space management of database table. (b) Explain about indexes.
- What is an integrity constraint? List the various types of integrity constraints.
- What is a view? Explain the syntax for creating a view.
- Explain the procedure of creating, altering and dropping tables.
- Explain the following commands: (i.)commit (ii)rollback and save point.
- Explain the various data types of PL/SQL in detail.
- Explain propagation and re-raising of exceptions.
- What is an exception? Explain the rising of an exception.
- Explain about implicit and explicit cursor.
- (a)Explain recursion with an example.
- Explain package state and dependency.
- Explain procedure overloading in PL/SQL with an example.
- What is stored Sub Program?
Ans: it is a sub program which may be procedure or function in a database object that are logically group of SQL and PL/SQL programming language statements together to perform a specific task.A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL:
Implicit cursors:These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
Explicit cursors:They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.
Implicit Cursors:When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected.
When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table.
| %FOUND || The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row. || |
| The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row. |
| %NOTFOUND || The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row. || |
| The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row. |
| %ROWCOUNT || Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT || SQL%ROWCOUNT |
For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
DECLARE var_rows number(5);
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END;In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.