SQL/ PLSQL faqs important for interviews

8/1/2007 1:17:54 AM [-] What is SQL profiling?

SK SQL Server Profiler is a tool that captures SQL Server 2005 events from a server. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. SQL Server Profiler is used for activities such as:
Stepping through problem queries to find the cause of the problem.
Finding and diagnosing slow-running queries.
Capturing the series of Transact-SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.
Monitoring the performance of SQL Server to tune workloads. For information about tuning the physical database design for database workloads.
Correlating performance counters to diagnose problems



8/1/2007 1:16:34 AM [-] What are locks?

SK There are three main types of locks that SQL Server 7.0/2000 uses:
• Shared locks
• Update locks
• Exclusive locks
Shared locks are used for operations that do not change or update data, such as a SELECT statement.
Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.



8/1/2007 1:15:39 AM [-] What is a global variable in SQL?

SK A global variable is a variable that does not belong to any Stored Proecdure or trigger, cursor and can be accessed from anywhere in a database by any stored procedure or trigger, cursor.



8/1/2007 1:14:47 AM [-] 9.What are hints?

SK Hints are options or strategies specified for enforcement by the SQL Server 2005 query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.
There three types of hints
•Join Hints
•Query Hints
•Table Hints



2/26/2007 5:37:11 AM [-] How we can implement Ref Cursors?

Mohan Reddy S create or replace procedure my_procedure ( p_ename in varchar2 default NULL,
p_hiredate in date default NULL,
p_sal in number default NULL)
as
type rc is REF CURSOR;

l_cursor rc;
l_query varchar2(512)
default 'select * from emp where 1 = 1 ';

cursor l_template is select * from emp;
l_rec l_template%rowtype;

begin

if ( p_ename is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'ENAME',
'%'||upper(p_ename)||'%');
l_query := l_query ||
' and ename like
sys_context( ''MY_CTX'', ''ENAME'' ) ';
end if;

if ( p_hiredate is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'HIREDATE',
to_char(p_hiredate,'yyyymmddhh24miss'));
l_query := l_query ||
' and hiredate >
to_date(
sys_context( ''MY_CTX'',
''HIREDATE'' ),
''yyyymmddhh24miss'') ';
end if;



if ( p_sal is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'SAL', p_sal);
l_query := l_query ||
' and sal >
to_number(
sys_context( ''MY_CTX'',
''SAL'' )
) ';
end if;

dbms_output.put_line(l_query);

p( l_query );

open l_cursor for l_query;

loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;

dbms_output.put_line( l_rec.ename || ',' ||
l_rec.hiredate || ',' ||
l_rec.sal );
end loop;

close l_cursor;
end;
/




2/26/2007 5:35:07 AM [-] Example for autonomous_transaction

Mohan Reddy S create table log (sf varchar2(10));
create table log1 (v number);

create or replace procedure ins_log (p_str varchar2)
AS
pragma autonomous_transaction;
BEGIN
Insert into log values (p_str);

commit;
end;
/

create or replace procedure main (p_val number)
AS

BEGIN

insert into log1 values (p_val);

if p_val>100 then
ins_log('success');
commit;
else
ins_log('fail');
rollback;
end if;
end;
/



2/26/2007 5:34:19 AM [-] How we can use ARRAYS in PL/SQL

Mohan Reddy S Example for Arrays
declare
TYPE v_array IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
a v_array;
b number:=0;
BEGIN
FOR I in 0..5 loop
b:=b+1;
a(I):=b;
END LOOP;
dbms_output.put_line(a(0));
END;
/


2/26/2007 5:27:01 AM [-] Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a
procedure ? How


Mohan Reddy S 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;



2/26/2007 5:26:02 AM [-] How can I protect my PL/SQL source code?

Mohan Reddy S 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 iname=myscript.sql oname=xxxx.yyy



2/26/2007 5:25:22 AM [-] Is there a limit on the size of a PL/SQL block?

Mohan Reddy S 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



2/26/2007 5:24:33 AM [-] Is there a PL/SQL Engine in SQL*Plus?

Mohan Reddy S 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.



2/26/2007 5:23:49 AM [-] What is PL/SQL?

Mohan Reddy S 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.



2/26/2007 5:22:45 AM [-] What is the difference between a view and a synonym ?

Mohan Reddy S 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.



2/26/2007 5:21:13 AM [-] Can you pass a parameter to a cursor ?

Mohan Reddy S 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;



2/26/2007 5:20:31 AM [-] What is the significance of the & and && operators in PL SQL ?

Mohan Reddy S The & operator means that the PL SQL block requires user input for a
variable. The && operator means that the value of this variable should be
the same as inputted by the user previously for this same variable.
If a transaction is very large, and the rollback segment is not able to hold
the rollback information, then will the transaction span across different
rollback segments or will it terminate ?
It will terminate (Please check ).



2/26/2007 5:19:06 AM [-] What are the values of :new and :old in Insert/Delete/Update Triggers ?

Mohan Reddy S INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value



2/26/2007 5:18:20 AM [-] What are the various types of database triggers ?

Mohan Reddy S There are 12 types of triggers, they are combination of :
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
(3*2*2=12)



2/26/2007 5:16:53 AM [-] What are the various types of parameter modes in a procedure ?

Mohan Reddy S IN, OUT AND INOUT


2/26/2007 5:15:13 AM [-] What is the difference between a procedure and a function ?

Mohan Reddy S 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.



2/26/2007 5:14:16 AM [-] Can we define exceptions twice in same block ?

Mohan Reddy S No.


2/26/2007 5:13:38 AM [-] What are the various types of Exceptions ?

Mohan Reddy S User defined and Predefined Exceptions.


2/26/2007 5:12:00 AM [-] : I have a table in Oracle that contains a field with the data type of LONG. How can I extract the contents of this LONG field?

Mohan Reddy S Answer: In Oracle, LONG fields are a bit tricky. However, you can use PLSQL code to determine the value of a LONG field.
Here is an example of a function that returns the value of a LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the value of the SEARCH_CONDITION field for the selected record.
CREATE or REPLACE function Find_Value
( av_owner varchar2, av_cname varchar2)
RETURN varchar2
IS
long_var LONG;
BEGIN
SELECT SEARCH_CONDITION INTO long_var
FROM ALL_CONSTRAINTS
WHERE owner = av_owner
AND constraint_name = av_cname;
return long_var;
END;



2/26/2007 5:10:52 AM [-] How can I count the number of characters in a LONG data type field?

Mohan Reddy S Answer: It doesn't appear that you can find the length of a LONG field in SQL. However, you can use PLSQL code to determine the length of a LONG field.
Here is an example of a function that returns the length of the LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the length of the SEARCH_CONDITION field for the selected record.
CREATE or REPLACE function Find_Length
( av_owner varchar2, av_cname varchar2)
RETURN number
IS
long_var LONG;
BEGIN
SELECT SEARCH_CONDITION INTO long_var
FROM ALL_CONSTRAINTS
WHERE owner = av_owner
AND constraint_name = av_cname;
return length(long_var);
END;



2/26/2007 5:07:50 AM [-] Procedure to Reverse a String


Mohan Reddy S
The following tip uses a procedure that reverses a string provided as an argument.


SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin

5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /


Procedure created.

SQL> set serverout on
SQL> exec rev('Java')
avaJ

PL/SQL procedure successfully completed.


SQL> exec rev('Oracle')
elcarO

PL/SQL procedure successfully completed.




2/26/2007 4:57:13 AM [-] How many types of Exceptions are there?

Mohan Reddy S There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then



2/26/2007 4:55:50 AM [-] What are Database Triggers and Stored Procedures

Mohan Reddy S Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
eg. operations insert,update ,delete 3 before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12. Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures : Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.



2/26/2007 4:52:52 AM [-] Why do stored procedures reduce network traffic ?

Mohan Reddy S When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.


2/26/2007 4:50:20 AM [-] How we make encrypt and decrypt in Oracle(PL/SQL)

Mohan Reddy S Create or Replace Package pkg_security
IS
FUNCTION eCyrpt (p_Str in Varchar2 ) Return VARCHAR2;
FUNCTION DCyrpt (p_Str in VARCHAR2 ) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_security
IS

function eCrypt( p_str in varchar2 ) return varchar2 is
l_data varchar2(255);
begin
l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
dbms_obfuscation_toolkit.DESEncrypt
( input_string => l_data,
key_string => 'MagicKey',
encrypted_string=> l_data );
return l_data;
end;

function DCrypt (p_str VARCHAR2)retur@n VARCHAR2 is
l_data varchar2(255);
begin
dbms_obfuscation_toolkit.DESDecrypt
( input_string => p_str,
key_string => 'MagicKey',
decrypted_string=> l_data );

return(rtrim( l_data, chr(0) ));
end;

END;
/


2/26/2007 4:23:00 AM [-] How to we use Raise_application_error(Oracle PL/SQL) ?

Mohan Reddy S DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
IF a > b THEN
Raise_application_error('A sal is greater than b');
END IF;
END;
/


2/26/2007 4:21:46 AM [-] How we find User defined error message for Oracle Error?

Mohan Reddy S DECLARE
a NUMBER;
b VARCHAR2(10);
deadlock_detected EXCEPTION
pragma EXCEPTION_INIT(deadlock_detected,-0060);
BEGIN
select count(*) INTO a FROM emp;
EXCEPTION
WHEN deadlock_detected THEN
dbms_output.put_line('deadlock_detected');
WHEN OTHERS THEN
dbms_output.put_line('Other error');

END;



2/26/2007 4:20:18 AM [-] How we difine user defined Exceptions in PL/SQL(Oracle)?

Mohan Reddy S User defined Exception


DECLARE
a NUMBER;
b VARCHAR2(10);
user_ex EXCEPTION;
BEGIN
if a > b THE
RAISE user_ex;
end if;
EXCEPTION
WHEN user_ex TEHN
dbms_output.put_line('User Defined Exception');
END;
/


2/26/2007 4:19:15 AM [-] How we difine pre defined Exceptions in PL/SQL(Oracle)?

Mohan Reddy S It's very simple to define,for that i am giving one simple examle..


DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
EXCEPTION
WHEN No_data_found THEN
dbms_output.put_line('Data not found');
WHEN Dul_value_on_index THEN
dbms_output.put_line('Inserting duplicate record');
WHEN Too_many_rows THEN
dbms_output.put_line('Too many values selected');
WHEN zero_devide
dbms_output.put_line('Deviding with ZERO');
WHEN Others THEN
dbms_output.put_line('Othere Error'||SQLERRM);
END;


2/26/2007 4:16:47 AM [-] How we difine user defined Exceptions in PL/SQL(Oracle)?

Mohan Reddy S It's very simple to define,for that i am giving one simple examle..
DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN

EXCEPTION
WHEN No_data_found THEN
dbms_output.put_line('Data not found');
WHEN Dul_value_on_index THEN
dbms_output.put_line('Inserting duplicate record');
WHEN Too_many_rows THEN
dbms_output.put_line('Too many values selected');
WHEN zero_devide
dbms_output.put_line('Deviding with ZERO');
WHEN Others THEN
dbms_output.put_line('Othere Error'||SQLERRM);
END;

An exciting news about frameworks

Friends we all know well about Frameworks but not more ... only few

This is an intresting news to all the developers there are across 4000 frameworks
among them we only very few frameworks .


i hope this message will generate an exciting news to all of you ..


For more details regarding this frameworks....

Mail Me:-shyam.shyre@gmail.com

new Light weight FrameWork

hai friends nowa days mostbusy framework used by all the people is EMULE

This is the most downloaded framework across the world....




http://sourceforge.net/projects/emule/

Plz download it from here it will help u a lot in the aplication development....