sql questions

Explain normalization ?
Normalisation means refining the redundancy and maintain stablisation. there are four types of normalisation :
first normal forms, second normal forms, third normal forms and fourth Normal forms.

why you need indexing ? where that is stroed and what you mean by schema object? For what purpose we are using view?

We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.

indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

What is diffrence between Co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
WHAT OPERATOR PERFORMS PATTERN MATCHING?
Pattern matching operator is LIKE and it has to used with two attributes
1. % and
2. _ ( underscore )
% means matches zero or more characters and under score means mathing exactly one character

What is cluster.cluster index and non cluster index ?
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.

How can i hide a particular table name of our schema?
you can hide the table name by creating synonyms.
e.g) you can create a synonym y for table x
create synonym y for x;

pl/sql interview questions 1

How to find dependencies referenced Inside PL/SQL Store Procedure

The Oracle data dictionary tracks the object types referenced in PL/SQL with the dba_dependencies view. To track the dependency among packages and tables, try this dictionary query:

select
  referenced_owner,
  referenced_name,
  referenced_type
from
  DBA_DEPENDENCIES
where
  name= 'YOUR_PROCEDURE or PACKAGE NAME' and   owner = 'SCOTT'
order by
  referenced_owner, referenced_name, referenced_type;

Finding PL/SQL compilation errors

PL/SQL does not always tell you about compilation errors.

SQL>SHOW ERRORS
No errors.

But how to see the error?

You have to give the full command as follows:

SQL>SHOW ERROR PROCEDURE 


Show Errors syntax:

Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
   PACKAGE BODY | TRIGGER | VIEW
   | TYPE | TYPE BODY | DIMENSION
   | JAVA SOURCE | JAVA CLASS } [schema.]name]

 
Finding Record count of all tables?

   PROMPT
     PROMPT ROWCOUNT for Table &Table_Name.

     SET FEEDBACK OFF
     SET SERVEROUTPUT ON
     DECLARE N NU MBER ;
     V VARCHAR2(100) ;
     BEGIN
     V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;
     EXECUTE IMMEDIATE V INTO N ;
     DBMS_OUTPUT.PUT_LINE (N);
     END;
     /

SQL>SELECT COUNT(*) FROM EMP;
14 Records. 
SQL>SELECT COUNT(*) FROM DEPT;
4 Records. 
...
...

How do you find when an object was created , last modified date?

SELECT OWNER, OBJECT_NAME, CREATED, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'enter_object_name_here';
 
 
Sending E-Mail in PL/SQL

Oracle allows us to send email from PL/SQL. You can create a procedure to send mail as follows:

CREATE OR REPLACE PROCEDURE SEND_MAIL (
    msg_from     VARCHAR2 := 'sender@domain.com'
  , msg_to       VARCHAR
  , msg_subject  VARCHAR2 := 'E-Mail message from your database'
  , msg_text     VARCHAR2 := ''
)
IS
  c   UTL_TCP.CONNECTION;
  rc  INTEGER;
BEGIN
  c  := UTL_TCP.OPEN_CONNECTION('localhost', 25);       -- open the SMTP port 25 on local machine
  rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
  rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
  rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
  rc := UTL_TCP.WRITE_LINE(c, 'DATA');                  -- Start message body
  rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
  rc := UTL_TCP.WRITE_LINE(c, '');
  rc := UTL_TCP.WRITE_LINE(c, msg_text);
  rc := UTL_TCP.WRITE_LINE(c, '.');                     -- End of message body
  rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
  UTL_TCP.CLOSE_CONNECTION(c);                          -- Close the connection
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

SQL>send_mail('sender@domain.com','recipient@domain.com','Test message from first PLSQL','your message here ');

pl/sql interview questions

What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database Proprietary to Oracle and difficult to port to other database systems Data manipulation is slightly faster in PL/SQL than in Java Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle Incurs some data conversion overhead between the Database and Java type systems Java is more difficult to use (depending on your background)



How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
        SELECT OBJECT_NAME,
               TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATE_TIME,
               TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
               STATUS
        FROM   USER_OBJECTS
        WHERE  LAST_DDL_TIME > '&CHECK_FROM_DATE';



How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
        SELECT TYPE, NAME, LINE
        FROM   USER_SOURCE
        WHERE  UPPER(TEXT) LIKE '%&KEYWORD%';



How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:
        CREATE TABLE SOURCE_HIST                     -- Create history table
          AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
             FROM   USER_SOURCE WHERE 1=2;

        CREATE OR REPLACE TRIGGER change_hist        -- Store code in hist table
               AFTER CREATE ON SCOTT.SCHEMA          -- Change SCOTT to your schema name
        DECLARE
        BEGIN
          if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                          'PACKAGE', 'PACKAGE BODY', 'TYPE') then
             -- Store old code in SOURCE_HIST table
             INSERT INTO SOURCE_HIST
                SELECT sysdate, user_source.* FROM USER_SOURCE
                WHERE  TYPE = DICTIONARY_OBJ_TYPE
                  AND  NAME = DICTIONARY_OBJ_NAME;
          end if;
        EXCEPTION
          WHEN OTHERS THEN
               raise_application_error(-20000, SQLERRM);
        END;
        /
        show errors



How can I protect my PL/SQL source code?
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.plb



Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:
    set serveroutput on
    begin
       dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
    end;
    /
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.



Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system 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.
Copy this example to get started:
    DECLARE
      fileHandler UTL_FILE.FILE_TYPE;
    BEGIN
      fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
      UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\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;
    /


Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
    begin
       EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
    end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.



Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
    EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

    -- Using bind variables...
    sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
    EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

        -- Returning a cursor...
    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
    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;
    /
More complex DBMS_SQL example using bind variables:
    CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
      v_cursor integer;
      v_dname  char(20);
      v_rows   integer;
    BEGIN
      v_cursor := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
      DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
      DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
      v_rows := DBMS_SQL.EXECUTE(v_cursor);
      loop
        if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
           exit;
        end if;
        DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
        DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
      end loop;
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    EXCEPTION
      when others then
           DBMS_SQL.CLOSE_CURSOR(v_cursor);
           raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
    END;
    /



What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
    DECLARE
       v_EmpRecord  emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
    DECLARE
       v_EmpNo  emp.empno%TYPE;



What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
    declare
      a number := NULL;
      b number := NULL;
    begin
      if a=b then
         dbms_output.put_line('True, NULL = NULL');
      elsif a<>b then
         dbms_output.put_line('False, NULL <> NULL');
      else
         dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
      end if;
    end;



How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:
    i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
    select sq_sequence.NEXTVAL into :i from dual;
Thanks to Ronald van Woensel



Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.



How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
    DECLARE
       CURSOR dept_cur IS
       SELECT deptno
         FROM dept
        ORDER BY deptno;
       -- Employee cursor all employees for a dept number
       CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
       SELECT ename
         FROM emp
        WHERE deptno = v_dept_no;
    BEGIN
       FOR dept_rec IN dept_cur LOOP
          dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
          FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
             dbms_output.put_line('...Employee is '||emp_rec.ename);
          END LOOP;
      END LOOP;
    END;
    /



How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
    FOR records IN my_cursor LOOP
       ...do some stuff...
       COMMIT;
    END LOOP;
... to ...
    FOR records IN my_cursor LOOP
       ...do some stuff...
       i := i+1;
       IF mod(i, 10000) THEN    -- Commit every 10000 records
          COMMIT;
       END IF;
    END LOOP;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.



I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
•    Grant direct access on the tables to your user. Do not use roles!
    GRANT select ON scott.emp TO my_user;
   
•    Define your procedures with invoker rights (Oracle 8i and higher);
•    Move all the tables to one user/schema.



What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
•    A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
•    A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
•    Etc.



Can one pass an object/table as an argument to a remote procedure?
The only way the same object type can be referenced between two databases is via a database link. Note that it is not enough to just use the same type definitions. Look at this example:
    -- Database A: receives a PL/SQL table from database B
    CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
    BEGIN
       -- do something with TabX from database B
       null;
    END;
    /

    -- Database B: sends a PL/SQL table to database A
    CREATE OR REPLACE PROCEDURE pcalling IS
       TabX DBMS_SQL.VARCHAR2S@DBLINK2;
    BEGIN
       pcalled@DBLINK2(TabX);
    END;
    /



Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.



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



Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. 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';

Imp queries

 How to display 1 to 10 numbers

select level from dual connect by level<=10;

How to delete duplicates records

Delete emp where rowid not in (select min(rowid) from emp group by sal);

How to find highest salary in a table

select * from emp where sal in (select  max(sal) from emp);

How to find second highest salary

1. Select * from emp where sal=(select max(sal) from emp where sal <(select max(sal) from emp));

2. Select * from (select sal,rank() over (order by sal desc) as rnk from (select  distinct sal from emp)) where rnk=2;

3. Select level,max(sal) from emp where level=&levelno connect by prior sal> sal group by level;

4. Select max(sal) from (select distinct sal from emp where sal not in (select max(sal) from emp));

5. select sal from(select sal from  (select distinct sal from emp order by sal desc) where rownum<=2 order by sal asc) where rownum=1;

To find highest and lowest values

select max(sal) from emp

union

select min(sal) from emp;

How to get fist and last record from a table in oracle?

select * from EMP where rownum=1

union

select * from EMP where rowid=(select max(rowid) from EMP);

To find the count of duplicate rows

Select ename, count (*) from emp group by ename having count(*) >= 1;

To see the duplicate records

SELECT * FROM  EMP WHERE (SAL IN (SELECT sal FROM emp GROUP BY sal HAVING COUNT(sal) > 1));

Query to find a_b format names

select * from emp where ename like '%A_B%';

Explain set operators

pl/sql questions

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.

1) Row level trigger - An event is triggered for each row updated, inserted or deleted. 
2) Statement level trigger - An event is triggered for each sql statement executed. 

Cursor: It is automatically created by oracle for all sql dml statements including the query that returns one row.
Explicit cursor: These are created and managed by the user. And used for multi row select statement.

What are the cursors attributes in plsql

%ISOPEN - to check whether cursor is open or not

% ROWCOUNT - number of rows fetched/updated/deleted.

% FOUND - to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine. Difference is Stored Procedure must return multiple value and function must return single value.

How to debug the procedure
We can also do line by line execution using tools such as Pl/SQL Developer, Toad etc

If you want to tune a package then you can use dbms_profiler.

if you   want to debug use dbms_debug procedure.

 To see the user priviliges

Select *from user_SYS_PRIVS

What are difference between a cursor declared in a procedure and cursor declared in a package specification

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
One more difference is cursor declared in a package specification must have RETURN type
Or 

·        A cursor declares in the package specification that can be accessed in the other procedure or procedures of the package.

·        A cursor declares in the procedure
that can't be accessed by other procedure.

IN, OUT, IN-OUT parameters.

1) IN parameter is the default mode which acts as a constant inside calling environment. value passed in this parameter can not be changed.
OUT parameter mode is used to pass value from calling environment into main block, here we can change the value. It acts as a variable inside calling environment.

INOUT parameter mode which pass value into calling environment and will get the value back in main block.

IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.
OUT & INOUT parameter mode uses call by value method to pass values.
Or 
2) You can also give a compiler hint - 'NOCOPY' when defining function parameters.
e.g. : create or replace function add(var1 in number,var2 innumber, result inout nocopy number)

Main purpose of using NOCOPY:

Even if exception occured in function block your result state is maintained in defined variable (parameter: result).

If you haven't declared NOCOPY then the processed value for 'result'  parameter will gets copied only when function ends.
Or 
3) IN ,OUT,and INOUT

parameters are actual and formal

-- IN mode is default and is passed as constant
IN parameters cannot change their values in procedure
--OUT parameters can change their values in procedure and ispassed as a variable
--INOUT parameters can change their values in procedure and arepassed back to the calling environment as a new value. a nocopy hint can also be used 

What is overloading of procedure

Same procedure name with different parameters called procedure overloading, parameters may different by their data types, sequence and position.

What is sql*loader

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control filedescribes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename <options>.

What is the use of dynamic sql in oracle

Dynamic SQL enables you to write programs that reference SQL statement whose full text is not known until runtime.

Write a pl/sql block to update the sal of an emp whose empno is 7369 and print the msg as ? record updated

begin
update employee(tablename) set salary=(salary)*o.1 where empno=7839;
dbms_output.put_line('record updated');
end;

If the application is running very slow? At what points you need to go about the database in order to improve the performance

For improving performance, we need to check the sql statementblocks , because for every sql satement execution transfor to sql engine and come back to plsq engine that process takes more time to process the plsql block

What is a database trigger ?name some usage of db trigger

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.

Is it possible to use tcl statements in db 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.

yes WE can use COMMIT and  ROLLBACK triggers, but by using PRAGAMA AUTONAMOUS_TRANSATIONS. Now the transation treated as a autonomous tansation.

Advantages of package:

A. Modularity

  - Encapsulate related constructs.

B. Easier Application Design

  - Code and compile specification and body separately.

C. Hiding Information

  - Only the declarations in the pacakge specification

    are visible and accessible to application.

  - Private constructs in the package body are hidden

    and inaccesible.

  - All coding is hidden in the package body.

D. Added Functionality

  - Persistency of variables and cursors.

E. Better Performance

  - The entire package is loaded into memory when the

    package is first referenced.

  - There is only one copy in memory for all users.

  - The dependency hierarchy is simplified.

F. Overloading

  - Multiple subprograms of the same

ODBC Provider: ODBC Provides access to any data source that has an ODBC driver.

Unit Testing:

Unit testing focuses verification efforts on the smallest unit of software design module. The unit test is always white box oriented. The tests that occur as part of unit testing are testing the module interface, examining the local data structures, testing the boundary conditions, execution all the independent paths and testing error-handling paths.

      

 Integration Testing:

Integration testing is a systematic technique or construction the program structure while at the same time conducting tests to uncover errors associated with interfacing. Scope of testing summarizes the specific functional, performance, and internal design characteristics that are to be tested. It employs top-down testing and bottom-up testing methods for this case.

Performance Testing:

          Timing for both read and update transactions should be gathered to determine whether system functions are being performed in an acceptable timeframe