PAGE 2
Qus 8 . Difference between Bind and Lexical parameters?
Ans. BIND VARIABLE :
are used to replace a single value in sql, pl/sql
bind variable may be used to replace expressions in select, where, group, order by, having, connect by, start with cause of queries.
bind reference may not be referenced in FROM clause (or) in place of reserved words or clauses.
LEXICAL REFERENCE:
you can use lexical reference to replace the clauses appearing AFTER select, from, group by, having, connect by, start with.
you can?t make lexical reference in a pl/sql statmetns.
OR
Formula column is a non_attribute column. THis is used to calculte Report level calculations based
on the values of the columns of the tables or any function. Every formula column is fuction.
Qus 9 . What is the package?
Ans. ---- Group logically related pl/sql types, items and subprograms.
package specification
package body
Advantages of a package:
? Modularity
? Easier Application Design
? Information Hiding
? Overloading
Qus 10 . Order of firing triggers in form
Ans. item level, block level, form level
Advantage :In pl/sql if you want perform some actions more than one records you should user these cursors only. bye using these cursors you process the query records. you can easily move the records and you can exit from procedure when you required by using cursor attributes.
Disadvantage:using implicit/explicit cursors are depended by situation. if the result set is les than 50 or 100 records it is better to go for implicit cursors. if the result set is large then you should use explicit cursors. Other wise it will put burden on cpu.
Qus 11. What is FORWARD DECLARATION in Packages?
Ans. PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon.
You can use forward declarations to do the following:
? Define subprograms in logical or alphabetical order.
? Define mutually recursive subprograms.(both calling each
other).
? Group subprograms in a package
Example of forward Declaration:
CREATE OR REPLACE PACKAGE BODY forward_pack
IS
PROCEDURE calc_rating(. . .);
-- forward declaration
PROCEDURE award_bonus(. . .)
IS
-- subprograms defined
BEGIN
-- in alphabetical order
calc_rating(. . .);
. . .
END;
PROCEDURE calc_rating(. . .)
IS
BEGIN
. . .
END;
END forward_pack;
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database
trigger.
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.
Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.
Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions 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.
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers
In the standared package Procedures, Functions & Packages ;
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table orviews and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
Implicit Cursor are declared and used by the oracle internally. whereas the explicit cursors are declared and used by the user. More over implicitly cursors are no need to declare oracle creates and process and closes automatically. The explicit cursor should be declared and closed by the user.
Implict cursor can be used to handle single record (i.e.) the select query used should not yield more than one row.
if u have handle more than one record then Explicit cursor should be used. It is not mandatory that when the query retrieves more than one record then explicit cursor should be used. If the number of records is less than 100 then implicit cursor should be used.
e.g.
set serverout on;
begin
for i in (select * from emp) loop
dbms_output.put_line(i.empno);
end loop;
end;
/
OR
Stored procedures are subprogrammes stored in the database and can be called &executee multiple times wherein an application procedure is the one being used for a particular application same is the way for function
OR
Stored procedures are subprogrammes stored in the database and can be called &executee multiple times wherein an application procedure is the one being used for a particular application same is the way for function
ROWID–Hexadecimal number each and every row having unique.Used in searching
ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysys.Other Psudo Column are
NEXTVAL,CURRVAL Of sequence are some examples
One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also
ORACLE ERROR NO starts with ORA 00001
ALTER TABLE TABLE_NAME DISABLE ALL TRIGGER
ALTER TABLE TABLE_NAME DISABLE ALL TRIGGER
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable
MORE
Posts
