• No results found

Oracle Interview Questions

N/A
N/A
Protected

Academic year: 2021

Share "Oracle Interview Questions"

Copied!
87
0
0

Loading.... (view fulltext now)

Full text

(1)

What is a CO-RELATED SUBQUERY

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery.

2. eg

3. SELECT field1 from table1 X

4. WHERE field2>(select avg(field2) from table1 Y 5. where

field1=X.field1);

(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)

What are various joins used while writing SUBQUERIES

Self join-Its a join foreign key of a table references the same table.

Outer Join–Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.

Equi-join–Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

What are various constraints used in SQL NULL, NOT NULL, CHECK, DEFAULT

What are different Oracle database objects

TABLES, VIEWS, INDEXES, SYNONYMS, SEQUENCES, TABLESPACES etc What is difference between Rename and Alias

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

What is a view

A view is stored procedure based on one or more tables, its a virtual table. What are various privileges that a user can grant to another user

SELECT, CONNECT, RESOURCE

What is difference between UNIQUE and PRIMARY KEY constraints

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

(2)

Yes

How you will avoid duplicating records in a query By using DISTINCT

What is difference between SQL and SQL*PLUS

SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational

database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Which datatype is used for storing graphics and images

LONG RAW data type is used for storing BLOB’s (binary large objects). How will you delete duplicating rows from a base table

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);

CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;

DROP old_table RENAME new_table TO old_table DELETE FROM table_name A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)

What is difference between SUBSTR and INSTR

SUBSTR returns a specified portion of a string eg SUBSTR(’BCDEF’,4) output BCDE INSTR provides character position in which a pattern is found in a string. eg INSTR(’ABC-DC-F’,'-’,2) output 7 (2nd occurence of ‘-’ )

What does Opening a cursor do ?

- It executes the query and identifies the Result set What does Fetching a cursor do ?

- It reads the Result Set row by row. What does Closing a cursor do ?

- It clears the private SQL area and de-allocates the memory. What are Cursor Variables ?

(3)

- They are not tied to a single SQL. They point to any SQL area dynamically.

- Advantage is : You can declare variables at Client side and open them Server side. You can thus centralize data retrieval.

Why use Cursor Variables?

- You can pass cursor RESULT SETS between PL/SQL stored programs and clients. What are SQLCODE and SQLERRM ?

- Oracle Error code and detailed error message

- They are actually functions with no arguments, that can be used only in procedural statements ( not SQL)

What are Pseudocolumns ?

- They are not actual columns. They are like Functions without arguments. - They typically give a different value for each row.

- Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME Why use Truncate over Delete while deleting all rows ? - Truncate is efficient. Triggers are not fired.

- It deallocates space (Unless REUSE STORAGE is given). What is a ROWID composed of ?

- It's a hexadecimal string representing the address of a row. Prior to Oracle 8, it's a restricted rowid comprising block.row.file. Extended rowid ( the default on higher releases) comprises data object number as well ( comprising the segment number ).

What is the use of a ROWID ? - Retrieve data faster with ROWID.

- Shows you the physical arrangement of rows in the table. - Also unique identifier for each row.

Can rows from two different tables have the same ROWID? - Possible, if they are in a Cluster

What is ROWNUM and ROW_NUMBER ?

(4)

- ROW_NUMBER is an analytic function which does something similar, but has all the capabilities of PARTITION BY and ORDER BY clauses..

What is an inline view? - It's not a schema object

- It's a subquery in the FROM clause with an alias that can be used as a view within the SQL statement.

What are Nested and Correlated subqueries ?

- The subquery used in WHERE clause is a nested subquery.

- If this subquery refers to any column in the parent statement, it becomes a correlated subquery.

How do you retrieve a dropped table in 10g? - FLASHBACK table <tabname> to BEFORE DROP What are PSPs?

- PL/SQL Server Pages. Web pages developed in PL/SQL What is an index-organized table?

- The physical arrangement of rows of this table changes with the indexed column. - It's. in-short, a table stored like an index itself.

What is an implicit cursor?

- Oracle opens an implicit cursor to process each SQL statement not associated with an explicit cursor.

Name a few implicit cursor attributes.

- %FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS How to fill a refcursor with dataset in C#.i want to pass a dataset as parameter and fill a refcursor with the same dataset.

Latest Answer: you need to use ODP.Net connector.In OracleCommand object add OracleParameter object with following propertiesOracleDbType =

OracleDbType.RefCursor;Direction= ParameterDirection.Output;give the name of your stored procedures's refcursor parameter ...

(5)

The maximum output is determined by the size you specified in dbms_output.enable(<size>). Here is an Error when i tried to give too many characters in

Dbms_Output.put_lineORU-10028:Search results: The following seemingly harmless statement produces an ORU-10028: line length overflow, limit of 255 bytes per line error: This is because dbms_output.put_line ... Is the order in which the conditions are given in the 'WHERE' clause are important? Mean I heard from Oracle 8i onwards the order of where clause condition does not matter which oracle creates the explain plan...and execute..Is it true?

Can you add not null column to a table already containing data ? Yes But we have to Specify a Default value for the column ... What is flash back query and trip stop

Flahsback is used to take your database at old state like a system restore in windows. No DDL and DML is allowed when database is in flashback condition.

user should have execute permission on dbms_flashback package for example:

at 1030 am

from scott user : delete from emp; commit;

at 1040 am I want all my data from emp table then ? declare

cursor c1 is select * from emp; emp_cur emp%rowtype; begin dbms_flashback.enable_at_time(sysdate - 15/1440); open c1; dbms_flashback.disable; loop

fetch c1 into emp_cur; exit when c1%notfound;

insert into emp values(emp_cur.empno, emp_cur.ename, emp_cur.job, emp_cur.mgr,emp_cur.hiredate, emp_cur.sal, emp_cur.comm,

emp_cur.deptno); end loop;

commit; end; /

(6)

select * from emp;

Through flash back queries you can get the data which has been deleted permanently. Different types of methods are there : Use the following links to get the complete information

:http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_fl.htm ... Write a PL/SQL fuction named say TRUN, which does the same work as the oracle predefined function "TRUNCATE".

Latest Answer: It will truncate/delete all the rows in the table emp very fast as compared of delete command, but the table must have no Foriegn keyMr. Ajaz Ahmad Kumar ...

what is the rollforward in pl/sql

Roll forward refers to the process Oracle goes through to apply changes contained in the redo log files (both online and archive). The database clock (as measured by the system change number) is moved forward within the blocks of the datafile that are changed within the redo log vectors. Roll forward occurs during database, tablespace or datafile recovery and during crash recovery. Rollback is the process of undoing uncommitted database transactions. The blocks copied to the rollback segments during transactions as a copy of the block for other transaction to read. When the instance aborts, the undo information in the redo log files must be applied to the database during the roll forward process of recovery. Therefore, during recovery, the database must roll forward and roll back.

Procedure in package perfomes fastly over normal procedure, Explain.

All the database objects defined inside a package gets loaded in memory for first and only call to database from application, thus executing procedures in packages requires less

communication time from database thus faster. The procedures inside a package using other private procedures does not require to communicate from database again, it is already in the memory...thus executing procedures from packages is faster.

In case of stand alone procedures, each procedures or d/b objects requires one call to database to be loaded in memory and then executed from application, the number of calls to database makes the execution slower.

What is the purpose of Ref Cursor and OUT Parameter in PLSQL Function? Give Examples for each?

A function can return one value by return statement itself.

Both are used to return more than one value in the function. As long as we are executing function same as procedure (i.e. not from any select statement or DML), out parameter is sufficient to return more than one value from function.

Example :

Create or replace function func1 (id1 IN <datatype1>, id2 OUT <datatype1>, id3 OUT <datatype1>, id4 OUT <datatype1>, id5 OUT <datatype1>,

(7)

id6 OUT <datatype1>, ... ) return <datatype> AS BEGIN --body of procedure END func1;

Calling function from any block BEGIN ... var1= func1(var2,var3,var4,var5,var6,var7,...); .... END; ---

But when we need to call the function from any select statement or DML, we cant write OUT or IN OUT parameter in function, thus we can take the help of ref cursor to return more than one values without using out parameters.

Or without using OUT parameter, we can return more than one value from function normally (function not called from select , DML).

Also, definition of ref cursor can be decided at run time. Example of ref cursor:

DECLARE

A NUMBER := 10; B NUMBER ; C NUMBER ; D NUMBER ;

FUNCTION TEST_FUN (A IN NUMBER, B OUT NUMBER, C IN OUT NUMBER) RETURN NUMBER IS D NUMBER; BEGIN B := A; C := B + A; D := A + B + C; RETURN (D); END ; BEGIN D := TEST_FUN(A,B,C); DBMS_OUTPUT.PUT_LINE(' A =' || A); DBMS_OUTPUT.PUT_LINE(' B =' || B); DBMS_OUTPUT.PUT_LINE(' C =' || C); DBMS_OUTPUT.PUT_LINE(' D =' || D); END;

Using REF CURSOR we can return objects as return type. Here is example DECLARE

TYPE EMP_REFCUR IS REF CURSOR; EMP_CURVAR EMP_REFCUR; TYPE EMP_REC IS RECORD (

V_EMPNO EMP.EMPNO%TYPE, V_ENAME EMP.ENAME%TYPE,

(8)

V_JOB EMP.JOB%TYPE, V_SAL EMP.SAL%TYPE );

EMP_RECVAR EMP_REC;

FUNCTION TEST_FUN (V_EMPNO IN EMP.EMPNO%TYPE) RETURN EMP_REFCUR IS BEGIN

OPEN EMP_CURVAR FOR SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE EMPNO = V_EMPNO; RETURN(EMP_CURVAR);

END ; BEGIN

EMP_CURVAR := TEST_FUN(7369); FETCH EMP_CURVAR INTO EMP_RECVAR;

DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS ' || EMP_RECVAR.V_ENAME); DBMS_OUTPUT.PUT_LINE('EMPLOYEE DESIGNATION IS ' || EMP_RECVAR.V_JOB); DBMS_OUTPUT.PUT_LINE('EMPLOYEE SALARY IS ' || EMP_RECVAR.V_SAL); END;

What are Return Statement and OUT Parameter in PLSQL Function? Function must have return statement by which it returns one value.

Though we can use out parameter in function(function not getting called from select statement or DML), it is not good programming practice to write OUT and IN OUT in function. In case we wanted to return values using OUT parameters, always use procedures.

In case we wanted to return more than one value from function, use of ref cursor is preferred solution not OUT parameters.

What is Raise_application_error ?

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.

Raise_application_error (error_number,error_messages); where error_number is between -20000 to -20999..

Latest Answer : You can use this procedure to issue user-defined error messages from stored subprograms.You can report errors to your application and avoid returning unhandled exceptions.Syntax: raise_application_error (error_number,message[, {TRUE | FALSE}]); ... Where the Pre_defined_exceptions are stored ?

In the standard package. Procedures, Functions & Packages ; Predefined exceptions are globally declared in standard package What is Overloading of procedures ?

(9)

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of

Overloading procs are 2 or more procs with the same name but different arguments. Arguments needs to be different by class it self. ie char and Varchar2 are from same class. Packages -

The main advantages of packages are

-1- Since packages has specification and body separate so, whenever any ddl is run and if any proc/func(inside pack) is dependent on that, only body gets invalidated and not the spec. So any other proc/func dependent on package does not gets invalidated.

2- Whenever any func/proc from package is called, whole package is loaded into memory and hence all objects of pack is availaible in memory which means faster execution if any is called. And since we put all related proc/func in one package this feature is useful as we may need to run most of the objects.

30 we can declare global variables in the package Advantages

Modularity

Easier application design Hiding information Added functionality Better performance Overloading

What are two parts of package ?

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema.Package Body Latest Answer : A package usually has a specification and a body, stored separately in the database.The specification is the interface to your applications. It declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The package ... What is 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.

(10)

If you are not sure about row type at complile time you use ref cursors.

Sys_ref_cursors used when you want cursors to be passed from one procedures to another procedures or functions.

How packaged procedures and functions are called from the following? a. Stored procedure or anonymous block

b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS a. PACKAGE NAME.PROCEDURE NAME (parameters);

variable := PACKAGE NAME.FUNCTION NAME (arguments); EXEC SQL EXECUTE

b. BEGIN

PACKAGE NAME.PROCEDURE NAME (parameters)

variable := PACKAGE NAME.FUNCTION NAME (arguments); END;

END EXEC;

c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

a. PACKAGE NAME.PROCEDURE NAME (parameters);

variable := PACKAGE NAME.FUNCTION NAME (arguments); b.

BEGIN

PACKAGE NAME.PROCEDURE NAME (parameters)

variable := PACKAGE NAME.FUNCTION NAME(arguments); END;

(11)

c. EXECUTE PACKAGE NAME.PROCEDURE VARIABLE g_salary NUMBER

EXECUTE :g_salary := get_sal(117)

Name the tables where characteristics of Package, procedure and functions are stored ?

User_objects, User_Source and User_error. USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS b) USER_SOURCE, ALL_SOURCE, DBA_SOURCE c) USER_DEPENCENCIES

d) USER_ERRORS, ALL_ERRORS, DBA_ERRORS What is the output of the following pl/sql block ? declare v_empno emp.empno%type;

begin

select empno into v_empno from emp where empno = 10;exception

when others then

dbms_output.put_line ( 'no data found'); when no_data_found then

dbms_output.put_line ( 'ther is no data found '); end;

when others then *ERROR at line 6:ORA-06550: line 6, column 2:PLS-00370: OTHERS handler must be last among the exception handlers of a blockORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis

What happens when a package is initialized ?

when a package is initialised that is called for the first time the entire package is loaded into SGA and any variable declared in the package is initialises.

We have a trigger on data base.in the trigger body we have created a body using dbms_output.put_line(********)

We have a trigger on data base.in the trigger body we have created a body using dbms_output.put_line(********) ;this should be firedwhen ever trigger executed;

(12)

TRUE ... displays the messge from dbms_output.put_line

What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

% 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 % 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 changes accordingly.

What is difference between % ROWTYPE and TYPE RECORD ?

% 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 differenttable or views

Latest Answer : %ROWTYPE is used when you need to work with complete record.TYPE RECORD is used to create your own data type with specificed number of values to hold.Suppose If a table has 20 columns and you need to work with only seven columns . If I use %ROWTYPE, ... % 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 or views 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.

What is a cursor ? Why Cursor is required ?

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.

(13)

Latest Answer : Oracle uses work areas called private SQL area to Execute Sql statements and store information. A cursor is a mechanism by which we can assign name to that private sql area , there by access information stored in it.In Pl/Sql ...

cursor retrive data from a table. it can be done in two level 1 %type it retrive data from a particular row

2 %rowtype it retrive data from a full table

Cursor is a named private SQL area also called as context area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows. there are three types of cursors

1. Static Cursor * Implicit Cursor * Explicit Cursor 2. Dynamic Cursor 3. Reference Cursor IMPLICIT --- (1) Called as SQL.

(2) Never user CURSOR keyword explicitly

(3) Memory allocation and program controll will done automatically (4) Returns or updates or deletes only one record.

(5) Attributes : SQL%FOUND, SQL%NOTFOUND EXPLICIT

---

(1) called as cursors

(2) Uses CURSOR keyword explicitly

(3) Memory allocation and program control thru the cursor written. (4) Returns or updates or deletes multiple records.

(5) Attributes : SQL%FOUND, SQL%NOTFOUND, %ISOPEN, %ROWCOUNT Explain the two type of Cursors ?

There are two types of cursors, Implicit Cursor and Explicit Cursor.PL/SQL uses Implicit Cursors for queries.User defined cursors are called Explicit Cursors. They can be declared and

Latest Answer : Cursors are of two types1. Implicit Cursors: - Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed infomation. The most recently used work are can be accessed using SQL%. In implicit cursors ...

(14)

DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

There are two ways of processing a cursor output. These mainly depend on the type of cursor used. Cursors can be

1) Static : Declared in declarations section with a definate command

2) Dynamic : Usually passed a string, which is formulated in program based on various conditions.

If the cursor is dynamic, it needs to be processed thro open cursor, fetch into, loop and end loop. This is because such cursors need to be opened into a ref cursor with "open cursor" command.

If the cursor is static, it can be processed thro a cursored for loop or by the method mentioned above.

Cursored for loop takes the pain to open and close the cursor automatically. What are the cursor attributes used in PL/SQL ?

%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 featched. These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

%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 Latest Answer : %FOUND :- Returns True if successful fecth has been executed. Returns false if no rows returned ...

What is a cursor for loop ?

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes

when all the records have been processed. eg. FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal; END LOOP;

(15)

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closeswhen all the records have been processed.

Latest Answer : Cursor for loop implicitly declares a loop index as %rowtype. It uses result of query to determine dynamically no of times the loop is to be repeated. It performs open, fectch , close operations implicitly. ...

What will happen after commit statement ? Cursor C1 is

Select empno, ename from emp; Begin open C1; loop Fetch C1 into eno.ename; Exit When C1 %notfound;--- commit; end loop; end;

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

The cursor having query will get closed, because once the for update clause is fired it locks all the rows which is been modified and once it encounters a commit/rollback it automatically comes out of that session, and will be processing a fresh loop altogether.

Explain the usage of WHERE CURRENT OF clause in cursors ?

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

(16)

Latest Answer : When referencing the current row from an explicit cursor, use the WHERE CURRENT OF clause. This allows you to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the ROWID. You must include the FOR ... What are two parts of package ?

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema.Package Body Latest Answer : A package usually has a specification and a body, stored separately in the database.The specification is the interface to your applications. It declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The package ... SQL General Interview Questions ...

• What are the different types of joins? • Explain normalization with examples.

• What cursor type do you use to retrieve multiple recordsets? • Diffrence between a "where" clause and a "having" clause • What is the difference between "procedure" and "function"?

• How will you copy the structure of a table without copying the data? • How to find out the database name from SQL*PLUS command prompt? • Tadeoffs with having indexes

• Talk about "Exception Handling" in PL/SQL?

• What is the diference between "NULL in C" and "NULL in Oracle?" • What is Pro*C? What is OCI?

• Give some examples of Analytical functions.

• What is the difference between "translate" and "replace"? • What is DYNAMIC SQL method 4?

• How to remove duplicate records from a table? • What is the use of ANALYZing the tables? • How to run SQL script from a Unix Shell?

• What is a "transaction"? Why are they necessary?

• Explain Normalizationa dn Denormalization with examples.

• When do you get contraint violtaion? What are the types of constraints? • How to convert RAW datatype into TEXT?

• Difference - Primary Key and Aggregate Key

• How functional dependency is related to database table design? • What is a "trigger"?

• Why can a "group by" or "order by" clause be expensive to process? • What are "HINTS"? What is "index covering" of a query?

• What is a VIEW? How to get script for a view?

• What are the Large object types suported by Oracle? • What is SQL*Loader?

• Difference between "VARCHAR" and "VARCHAR2" datatypes.

• What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.

• Difference between "ORACLE" and "MICROSOFT ACCESS" databases. • How to create a database link ?

(17)

PL/SQL interview questions...

• Normalize many to many relationships • Difference - Equijoin and union

• What is TEMP table space in Oracle, what is rollback segment • How do we find row chaining?

• Pattern matching operators • Features in oracle 9i and 10g

• Why truncating table is faster than delete • copy commit syntax

• Convert Zulu time zone to US Eastern time zone • Difference - union and union all

• Difference - Group by, Order by clause • Which Ranking functions are available? • Difference - Decode, NVL, NVL2 • Tradeoffs of using partitioned tables

• How can we call stored procedure in SQL query • What are the restrictions on calling PL/SQL from SQL • Why EXISTS is preferable to distinct

• Give 2 examples of avoiding unnecessary parsing.

Explain the usage of WHERE CURRENT OF clause in cursors ?

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor in order to allow you to easily make changes to the most recently fetched row of data. The general format for the WHERE CURRENT OF clause is as follows:

UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name;DELETE FROM table_name WHERE CURRENT OF cursor_name;

Notice that the WHERE CURRENT OF clause references the cursor and not the record into which the next fetched row is deposited.

The most important advantage to using WHERE CURRENT OF where you need to change the row fetched last is that you do not have to code in two (or more) places the criteria used to

uniquely identify a row in a table. Without WHERE CURRENT OF, you would need to repeat the WHERE clause of your cursor in the WHERE clause of the associated UPDATEs and DELETEs. As a result, if the table structure changes in a way that affects the construction of the primary key, you have to make sure that each SQL statement is upgraded to support this change. If you use WHERE CURRENT OF, on the other hand, you only have to modify the WHERE clause of the SELECT statement.

This might seem like a relatively minor issue, but it is one of many areas in your code where you can leverage subtle features in PL/SQL to minimize code redundancies. Utilization of

(18)

WHERE CURRENT OF, %TYPE, and %ROWTYPE declaration attributes, cursor FOR loops, local modularization, and other PL/SQL language constructs can have a big impact on reducing the pain you may experience when you maintain your Oracle-based applications.

Let's see how this clause would improve the previous example. In the jobs cursor FOR loop above, I want to UPDATE the record that was currently FETCHed by the cursor. I do this in the UPDATE statement by repeating the same WHERE used in the cursor because (task, year) makes up the primary key of this table:

WHERE task = job_rec.task AND year = TO_CHAR (SYSDATE, 'YYYY');

This is a less than ideal situation, as explained above: I have coded the same logic in two places, and this code must be kept synchronized. It would be so much more convenient and natural to be able to code the equivalent of the following statements:

Delete the record I just fetched. or:

Update these columns in that row I just fetched.

A perfect fit for WHERE CURRENT OF! The next version of my winterization program below uses this clause. I have also switched to a simple loop from FOR loop because I want to exit

conditionally from the loop:

DECLARE CURSOR fall_jobs_cur IS SELECT ... same as before ... ; job_rec fall_jobs_cur %ROWTYPE;BEGIN OPEN fall_jobs_cur; LOOP FETCH fall_jobs_cur INTO job_rec; IF fall_jobs_cur%NOTFOUND THEN EXIT; ELSIF job_rec.do_it_yourself_flag = 'YOUCANDOIT' THEN UPDATE winterize SET responsible = 'STEVEN' WHERE CURRENT OF fall_jobs_cur; COMMIT; EXIT; END IF; END LOOP; CLOSE fall_jobs_cur;END;

Write the order of precedence for validation of a column in a table ? I. done using Database triggers.

II. ii. done using Integarity Constraints. I & ii. Exception

Latest Answer : create table a ( b number, c number check (c >100) );create or replace trigger t1 before insert on a begindbms_output.put_line('this is before insert trigger'); end;create or replace trigger t2after insert on a begindbms_output.put_line('this ...

Based on what conditions can we decide whether to use a table or a view or a materialized view ?

Latest Answer : Table is the basic entity in any RDBMS , so for storing data you need table .for view - if you have complex query from which you want to extract data again and again , moreover it is a standard data which is required by many other user also for REPORTS ... What is a database trigger ? Name some usages of database trigger ?

(19)

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.

A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens.

Data auditing , Implementing complex business rules, security are main uses of database triggers.

How many types of database triggers can be specified on a table ? What are they ? Insert Update Delete

Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.

If WHEN clause is specified, the trigger fires according to the returned Boolean value. --There are five types of database triggers

1. Row Level Trigger 2. Statement Level Trigger 3. Instead of Trigger 4. Schema Trigger 5. Database Trigger

Out of these five types of triggers Only two are used for the table purpose, i.e. two row level trigger and statement level triggers are used for insert, update or/and delete operation on table

A trigger may be a 1. DML Trigger on tables 2. Instead of triggers on views

3. System triggers on database or schemaBased on the way it executes statements triggers are of two types

(20)

1. Statement leve trigger

2. Row level triggerA trigger fires ...

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ?

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 are two virtual tables available during database trigger execution ?

The table columns are referred as OLD.column_name and NEW.column_name.For triggers related to INSERT only NEW.column_name values only available.For triggers related to UPDATE only OLD.column_name

Latest Answer : OLD and NEW are two virtual tables available during database trigger execution.UPDATE statement has access to both old and new values.INSERT statement has access only to new values. Old values are NULL for insert statement.DELETE ...

The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available.

For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.

For triggers related to DELETE only OLD.column_name values only available.

What happens if a procedure that updates a column of table X is called in a database trigger of the

What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?

Mutation of table occurs.

To avoid the mutation table error ,the procedure should be declared as an AUTONOMOUS TRANSACTION.

By this the procedure will be treated as an separate identity.

(21)

I. done using Database triggers. II. ii. done using Integarity Constraints.

III.

IV. I & ii. Exception

create table a ( b number, c number check (c >100) ); create or replace trigger t1

before insert on a begin

dbms_output.put_line('this is before insert trigger'); end;

create or replace trigger t2 after insert on a

begin

dbms_output.put_line('this is after insert trigger'); end;

insert into a values (1,99); /

this is before insert trigger insert into a values (1,99) *

ERROR at line 1:

ORA-02290: check constraint (XXINV.SYS_C00106711) violated

insert into a values (1,199); /

this is before insert trigger this is after insert trigger 1 row created.

So, if it is before insert trigger then the trigger will run first before the constriants in the table. if it is after insert trigger , contraints are checked first and then trigger will run.

(22)

SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE. Latest Answer : •

SQLCODE: Returns the numeric value for the error code• SQLERRM: Returns the message associated with the error numberSQLCODE Value Description ...

Answer Question Subscribe

What is Data Concarency and Consistency?

Data Concarency => Means that many users can access data at the same time.

Data Consistency => Means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

Concurrency

How well can multiple sessions access the same data simultaneously Consistency

How consistent is the view of the data between and within multiple sessions, transactions or statements

What is the output of the following pl/sql block ?declare v_empno emp.empno%type;begin select empno

What is the output of the following pl/sql block ?declare v_empno emp.empno%type;begin select empno into v_empno from emp where empno = 10;exception when others then dbms_output.put_line ( 'no data found'); when no_data_found then dbms_output.put_line ( 'ther is no data found ');end;

when others then *ERROR at line 6:ORA-06550: line 6, column 2:PLS-00370: OTHERS handler must be last among the exception handlers of a blockORA-06550: line 0, column 0:PL/SQL: Compilation unit analysis

always when others exception should be the last exception handling in sequence.

You have compiled some PL/SQL packages in your schema, and found aome errors in one procedure.how do you find which procedure produced the error? how do you find which section of the code produced the error and look at?

(23)

It give list of all procedures/ package/ functions/ types wherever error occurs Along with line no and what is the error.

What is PL/SQL table ?

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.

Based on what conditions can we decide whether to use a table or a view or a materialized view ?

Table is the basic entity in any RDBMS , so for storing data you need table .

for view - if you have complex query from which you want to extract data again and again , moreover it is a standard data which is required by many other user also for REPORTS generation then create view . Avoid to insert / update / delete through view unless it is essential. keep view as read only (FOR SHOWING REPORTS)

for materialized view - this view ia mainly used in datawarehousing . if you have two databases and you want a view in both databases , remember in datawarehousing we deal in GB or TB datasize . So create a summary table in a database and make the replica(materialized view) in other database.

when to create materialized

view-[1] if data is in bulk and you need same data in more than one database then create summary table at one database and replica in other databases

[2] if you have summary columns in projection list of query. main advatages of materialized view over simple view are

-[1] it save data in database whether simple view's definition is saved in database

[2] can create parition or index on materialize view to enhance the performance of view , but cannot on simple view.

What is the order of execution if there is a statement level and row level trigger on a same table?

Latest Answer : The correct order is as below. Trigger firing sequence: 1) Before statement level triggers, if present 2) For each row a) Before row level triggers, if present b) After row level triggers, if present 3) Actual Statement 4) After statement level ...

Read Answers (5) | Asked by : joseph Answer Question Subscribe

(24)

In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or

In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?

Read Answers (6) | Asked by : ddkdhar Answer Question Subscribe

Write sample code that can create a hierachical set of data without using a start with and connect by

Write sample code that can create a hierachical set of data without using a start with and connect by clause in PL/SQL

Read Answers (1) | Asked by : soorajsk_84 Answer Question Subscribe

Convert SQL to Oracle Procedure using cursor

SELECT APE.DAT_INSERT_DATE as "Payment Entry Date", Case when APE.TXT_INTERMEDIARY_CD is null or APE.TXT_PAYER_CUSTOMER_ID

APE.TXT_INTERMEDIARY_CD then (Select TXT_CUSTOMER_NAME from GENMST_CUSTOMER Latest Answer : create or replace procedure sql_to_procedure as declare cursor c1 is SELECT APE.DAT_INSERT_DATE as "Payment Entry Date",APE.TXT_INTERMEDIARY_CD AS "Intermediary ID",APE.TXT_PAYER_CUSTOMER_ID as "CustomerID/Payer ID",TXT_CUSTOMER_NAME ...

Read Answers (1) | Asked by : preeti Answer Question Subscribe

oracle is compiler or interpretter,can any one tell me the answer? 6 I have a procedure in a procedure. The inner procedure contains out

parameter. How I can call the inner procedure in the out procedure and send the inner procedure parameter value(out parameter value) into out procedure?

1

what is the difference between the query and corelated query IBM 4

What is INSTEAD OF trigger ? TCS 4

Difference between views and materialized views? BirlaSoft 4 What is clustered and non-clustered indexes? Microsoft 3

How the execution will be done in exceptions? 2

(25)

What are the types of triggers ? TCS 2 Whatis yhe use of cursor ? how cursor allocate context area for

executing the sql statement? HCL 2

If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?

2 How toimport .dmp file in lower version of oracle from higher version ? TCS 1

What is Raise_application_error ? 1

What cursor type do you use to retrieve multiple recordsets? Microsoft 9 a procedure one in two out parameters i am waiting it in a sql query

can i get the output

1

Types of locks in database ? TCS 3

write the Sql query for creating database backup? TCS 3

What is the difference between join and union. 9

What are the cursor attributes used in PL/SQL ? 3

Give which cursor is better for better performance means type of cursors?

Debug PL SQL

How can we generate debugging output from PL/SQL?

use the oracle supplied package DBMS_OUTPUT with procedures PUT, PUT_LINE, NEW_LINE etc...

What is the need of primary key as opposed to using not null and unique ? we can create a column with' not null+unique' with out using primary key

1) Primary key creates clustered index by default which actually reorders the row in a table based on index while unique key creates non-clustered index.

2) Replication of table possible only if it contains primary key. How to find error line of a package at run time

How can the error line can be identified for a package at run time ? Latest Answer: Show error ...

Read Answers (1) | Asked by : sanjoy.dubey Answer Question Subscribe

(26)

Hi All,I have requirement of sending the records based on the input received from web.I have a ref_cursor returning 30 rows..Here I am supposed to do paging on the set of records present in the above ref

View Question | Asked by : vijay.patil2005 Answer Question Subscribe

What are optimizer hints?

Optimizer hints are used to alter execution plan of SQL statement. For ex, certain index can be sometimes more selective for certain queries. Based on the information you can choose a more efficient execution plan by using hint and telling the optimizer to choose a more optimal path. Hints used for Optimization are :

All_rows First_rows

Answer Question Subscribe Referenced and Dependent objects

How oracle manages dependency between referenced and dependent objects ? View Question | Asked by : KD09714

Answer Question Subscribe PL SQL Logs

List at least three common ways to write logs in PL SQL?

Latest Answer: i.feel,using a ref cursor, we can return more than one value to a fucntion ... Read Answers (1) | Asked by : liaohanming

Answer Question Subscribe Problem with Utl_file.fopen

Hi,There is problem with ult_file.fopen. When I run from the server side, there is no issue with utl_file and it is working fine.If I run the same procedure from Client machine, I am facing problem. It

the parameter set in configuration file init.oraand UTL_FILE_DIR belongs to the directory structure in oracle server machine. And the directory structure should be on same machine as oracle server. Thus fopen can run in oracle server machine not client.

Thus fopen call on client machine will give invalid_operation i.e. file could not be opened. Global Temporary Table

(27)

What is Global temporary table? and what are the benefits of it?

GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global

temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

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 exceptions are.

Latest Answer : Exception is nothing but error in the PL/SQL program. If any error occured in the PL/SQL program that terminates from the program. To handle that exceptions we are using exception handling part in the PL/SQLThere are three types of exceptions1. predefined2. ... 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.

Exception is nothing but error in the PL/SQL program. If any error occured in the PL/SQL program that terminates from the program. To handle that exceptions we are using exception

(28)

handling part in the PL/SQL

There are three types of exceptions 1. predefined

2. non-predefined 3. user defined

Predefined exceptions are defined by the system we have some predefined exception names to handle some exceptions

non-predefined exceptions are also raised by the system only but we dont have any predefined names we have to trap the exceptions with error code defined by the system

User defined exceptions are defined by the user. He has to declare the exception, he has to raise the exception.

Why Functions are used in oracle ?

Can Functions Return more than 1 values? Why Procedures are used in oracle ? What are the Disadvantages of packages? What are the Global Variables in Packages?

1) A function will return a value, adding to that , it is possible to use a function in SQL statements, whereas procedures cannot be used.

2)No, functions cannot retrun more than one value, instead one can get this done in other way round by using OUT parameters for a function.

3)To perform validations and transactions on database tables. 4)Memory usage is more.

5)The variable defined inside Package Specification are treated as Global variables, which can be referred externally.

1)what is the starting "oracle error number"?2)what is meant by forward declaration in functions?

1)what is the starting "oracle error number"?

2)what is meant by forward declaration in functions? 1. STARTING ORACLE ERROR NO IS ORA 00001

2. Forward Declaration means.. If you are defining a package body having two

procedures , If u want to use second procedure in the defination of first procedure.. You have to declare the second package with its arguments(if have) before using in the defination of first procedure.. its labled as forward declaration

PL/SQL does not allow forward references. You must declare an identifier before using it. Therefore, a subprogram must be declared before calling it. You can use forward declarations to do the following:

(29)

• Define mutually recursive subprograms • Group subprograms in a package

Mutually recursive programs are programs that call each other directly or indirectly.

ibabu

Answer Question Subscribe

How can i import .dmp file in lower version of oracle from higher version ? Latest Answer : No. You cannot export a higher version to the lower version. ...

Export the higher version data using lower version Export utility and import it to the lower version DB.

1.What is bulk collect?2.What is instead trigger3.What is the difference between Oracle table & PL/SQL

1.What is bulk collect?2.What is instead trigger3.What is the difference between Oracle table & PL/SQL table?4.What R built in Packages in Oracle?5.what is the difference between row migration & row changing?

1.What is bulk collect?

Bulk collect is part of PLSQL collection where data is stored/ poped up into a variable. example:

declare

type sal_rec is table of number; v_sal sal_rec;

begin

select sal bulk collect into v_sal from emp; for r in 1.. v_sal.count loop

dbms_output.put_line(v_sal(r)); end loop;

(30)

2.What is instead trigger

instead triggers are used for views.

3.What is the difference between Oracle table & PL/SQL table?

Table is logical entity which holds the data in dat file permanently . where as scope of plsql table is limited to the particular block / procedure . refer above example sal_rec table will hold data only till programme is reaching to end;

4.What R built in Packages in Oracle?

There R more then 1000 oracle builtin packges like: Dbms_output, dbms_utility dbms_pipe ...

5.what is the difference between row migration & row changing?

Migration: The data is stored in blocks whic use Pctfree 40%

and pctused 60% ( normally). The 40% space is used for update and delete statements . when a condition may arise that update/delete statement takes more then pctfree then it takes the space from anther block. this is called migration.

RowChaining: while inserting the data if data of one row takes more then one block then this row is stored in two blocks and rows are chained.

sir,pls send me some more oftanily asked oracle interview questions iam an one year exprience person...i

Sir,pls send me some more oftanily asked oracle interview questions iam an one year exprience person...i need one year oracle interview questions...

Read Answers (2) | Asked by : basavarajkolur Answer Question Subscribe

What is the use of nocopy parameter in oracle procedure

Hi, What is nocopy parameter in oracle procedure. what is the use of it. In which situation,we can use the nocopy parameter.Thanks,Saravanan.P

(31)

Latest Answer : Ussually IN paramter is pass by value and OUT/IN OUT are pass by

refererence....If one wants to send the IN paramter too as pass by reference he could add NOCOPY parameter....advantage is always less memory usagedisadvantage is when there is a change ...

Read Answers (5) | Asked by : ily_saravanan Answer Question Subscribe

PL/SQL Block output

begin For i in 1..5 loop insert into A values(i); savepoint 1; end loop; rollback to savepoint 1; commitend;--initially there are no data in table A. So my question is after execution of this After the loop exits 1 to 5 is inserted into the table and a savepoint is also created with the name savepoint1.

Now after rollback no data will be deleted and the table A will have values 1 to 5. Read Answers (4) | Asked by : Kanhucharan

Answer Question Subscribe Example for calling procedure

Give an example for calling procedure with user and system exception

/* simple example for calling procedure from a another procedure with system and user define exception */

set serveroutput on

create or replace procedure proc1 is -- calling procedure from emp_id. v_time timestamp; -- time stamp for calling procedure.

begin

select systimestamp -- selecting time stamp from dual table. into v_time from dual;

dbms_output.put_line(v_time); update emp -- update employees. set salary = 25000

where employee_id = 100; end proc1;

/

create or replace procedure emp_id -- mail procedure.

(emp in emp.employee_id%type, sal out emp.salary%type) is excep exception;

excep1 exception;

(32)

begin

select systimestamp -- selecting time stamp from dual table. into v_time from dual;

dbms_output.put_line(v_time);

select salary -- fetch the salary for analyze. into sal

from emp

where employee_id = emp; /* check conditions */

if sal = 24000 then proc1;

elsif sal >= 25000 then raise excep; else raise excep1; -- raise exception. end if;

exception

when no_data_found -- system exception.

then dbms_output.put_line('no record found'); when excep -- user define exception.

then dbms_output.put_line('already have the salary'); when excep1 -- user define exception.

then dbms_output.put_line('not in employee_id in proc1'); end emp_id;

/

show errors; /

variable sa number execute emp_id (100, :sa) show errors;

Answer Question Subscribe PL/SQL tables

How will implement the PL/SQL tables?

PL/SQl tables were introduced in Oracle 7.PL/SQL tables are similar to arrays in C.By using PL/SQL Tables, it is possible to create a collection of items, all of the same type, indexed by an integer.

eg:TYPE name IS TABLE OF varchar2 INDEX BY BINARY_INTEGER;

- Pl/sql is in memory representation of a table. - It is just a data type. ( Collection type) - It may store any number of rows from table. Here is a simple example

(33)

CREATE OR REPLACE PROCEDURE TEST_PROC AS TYPE EMP_TYPE IS TABLE OF EMP%ROWTYPE; EMP_VAR EMP_TYPE;

BEGIN

SELECT * BULK COLLECT INTO EMP_VAR FROM EMP; FOR I IN EMP_VAR.FIRST..EMP_VAR.LAST

LOOP

DBMS_OUTPUT.PUT_LINE(' EMPLOYEE ' ||EMP_VAR(I).ENAME || ' SALARY IS '|| EMP_VAR(I).SAL);

END LOOP; END;

Oracle triggers

can we issue rollback, commit in the trigger body. if we issue what is the result

Commit and roll back statements are not possible from triggers. Although we can create triggers with COMMIT and ROLLBACK, it will result in an error when the trigger has fired. Ideally this is desirable since the original transaction may be rolled back and so the effects of trigger also should be rolled back.

However we can use commit if it is declared as an autonomous transaction. Generally that will be used for writing to log tables.

Read Answers (5) | Asked by : maheshveeragoni Answer Question Subscribe

Difference between 'TABLE OF' and 'REF CURSOR'

What exactly the difference between 'TABLE OF' and 'REF CURSOR' in PL/SQL?For what purposes these both might be used?

A ref cursor is a dynamic cursor in which the contents of cursor can be changed dynamically at run time depending upon our requirement.

A ref cursor is basically a data type. A variable declared based on such data type is called cursor variable. We can assosiate differenct statement to a cursor variable dynamically. The main advantage of ref cursor is

1. We can return result set to a client which we can't do using normal cursors. 2. It can be passed as parameter to sub programs.

Table of is use to define a collection type.

Collections are generally used for bulk processing. In cursors the data is processed sequentially. Using collections we can process all the data in one go.

Answer Question Subscribe Oracle refcursor and procedure

(34)

how to pass result set using refcursor from one package procedure to another package procedure? and code also

CREATE TABLE employees ( empid NUMBER(5), empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan'); INSERT INTO employees (empid, empname) VALUES (2, 'Jack Cline'); INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small'); COMMIT;

CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS TYPE array_t IS TABLE OF VARCHAR2(4000)

INDEX BY BINARY_INTEGER; rec_array array_t;

BEGIN

FETCH p_cursor BULK COLLECT INTO rec_array; FOR i IN rec_array.FIRST .. rec_array.LAST LOOP dbms_output.put_line(rec_array(i)); END LOOP; END pass_ref_cur; / set serveroutput on DECLARE rec_array SYS_REFCURSOR; BEGIN

OPEN rec_array FOR

'SELECT empname FROM employees'; pass_ref_cur(rec_array);

CLOSE rec_array; END;

/

Which two statements are true?A. A function must return a value.B. A procedure must return a value.C.

A. Which two statements are true? A. A function must return a value. B. A procedure must return a value.

C. A function executes a PL/SQL statement. D. A function is invoked as part of an expression. E. A procedure must have a return Data

(35)

A function must return a value

- It's True . " A function has to return a value "

--- B. A procedure must return a value

- It's fase. " A Procedure may or may not return a value "

--- C. A function executes a PL/SQL statement

-It's True. We can write Data Manipulation Statements such as insert, update, delete inside the Function.

--- D. A function is invoked as part of an expression.

-It's True. We can invoke a function as a part of expression in sql statements. --- E. A procedure must have a return Data

--It's false. No need ...

Oracle extract records from temporary table

Assume that I am using a temporary table in a procedure and I am inserting records and updating another set of records through Merge statement. If I use cursor in that temporary table, How can I extract

Create Global temporary tables and say on commit preserve rows. The global temporary tables have data available till the session is active. So you if you insert it at one procedure and want to access it somewhere else you can do that very easily till the session is active

What is nested table in Oracle and and difference between table and nested table A Table is a basic unit of storage in oracle.

A nested table is a collection type. The main advantage of collections is instead of processing data sequentially, we may process all the date in one step.

- It is a collection of rows stored as a column in a table. - It is a table withing a table.

- There is no restriction on number of rows in a nested table - There is no restriction on number of columns in a nested table. - We can not index nested table.

- The nested table is stored as a seperate table and main table maintains a pointer to the nested table as reference.

- Nested table stored data in no particular order

- But when you retrieve data into pl/sql variable it assigns serial number starts with 1 - Intially they are dense you may sparse later.

(36)

How do you call procedure have a DDL or commit/rollback statement from a trigger? Yes using pragma autonomous_transaction as per example below for commit.

we have two table employee and employee_bak of same structure (empid, emp_name, location, manager_id)

--autonomous procedure will insert value in employee_bak, procedure is getting --called from trigger on insert in employee.

create or replace procedure replicate_employee (p_emp_id employee.emp_id%type, p_emp_name employee.emp_name%type, p_location employee.location%type, p_manager_id employee.manager_id%type) as pragma autonomous_transaction; begin

insert into employee_bak values(p_emp_id, p_emp_name, p_location, p_manager_id); commit;

end replicate_employee;

--trigger after insert on employee

create or replace trigger trg_backup_employee after insert on employee

for each row declare begin

replicate_employee(:new.emp_id, :new.emp_name, :new.location, :new.manager_id); end;

--Note : if pragma autonomous_transaction is not used, trigger will give error at run –time

What are mutating tables?

When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.

Mutate means chage. Mutating table is a table that is being modified by update, delete or insert statement

Answer Question Subscribe

(37)

Char(20) = 'name' varchar2(20)='name' When comparing these two values, are the spaces padded in char are considered or not? Are both values equal?

In Char type spaces will be padded after 'name' uptp max length 20. in Varchar spaces will not be padded.

Is it possible to use commit or rollback in exception section. Yes you can use commit or rollback in exception block;

select * from test123; A --- 51 Declare s varchar2(2); Begin

update test123 set a = a+10;

select a into s from test123 where a=20; exception

when others then rollback;

dbms_output.put_line('test'|| s); End;

SQL> / test

PL/SQL procedure successfully completed. SQL> select * from test123;

A --- 51

How do you encrypt the function to prevent accessing from users without specific permission. ?

Using wrap utility we can encrypt source code of a function to byte code (Not a readable format). Becareful there is no decrypt utility to convert source code back to ascii format. if you aim is just to prevent function accessing from users do not give privelege on that function to other users.

I think you can use wrap utility encrypt the function / procedure program units for example create a function store it as xx.sql file

(38)

c:oracle> wrap iname=<full path>xx.sql

This process will result xx.plb file which is encrypted

What are purity rules for functions? why they use ? what effects if not follow these rules? The purity level defines what structure the function reads or modifies.

the types of purity level:

1)wnds--write no database stage I.e the function does not modify any database or tables. 2)rnds--reads no database state. i.e function does not read any database tables.

3)wnps--write no package state i.efunction does not modify any packaged variables. 4)rnps-read no package state i.e function does not read any package variables.

To be callable from SQL statements, a stored function (and any subprograms called by that function) must obey certain "purity" rules, which are meant to control side effects:

• When called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.

• When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement.

• When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit.

If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed).

To check for violations of the rules, you can use the pragma (compiler directive)

RESTRICT_REFERENCES. The pragma asserts that a function does not read or write database tables or package variables. For example, the following pragma asserts that packaged function credit_ok writes no database state (WNDS) and reads no package state (RNPS):

CREATE PACKAGE loans AS FUNCTION credit_ok RETURN BOOLEAN; PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS); END loans;

Write sample code that can create a hierachical set of data without using a start with and connect by

Write sample code that can create a hierachical set of data without using a start with and connect by clause in PL/SQL

select empno, ename, level from emp where connect by mgr=empno start with job='PRESIDENT';

(39)

Both are same. Right outer join returns all the reocrds that satisfy the join condtion + rest of the records from right (or second) table.

Why DUAL table is not visible?

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this table.

It contains only one row and one column of VARCHAR2 datatype.

Used to refer an object which does not have any pysical reference in database table. Ex:- Select sysdate from dual.

What is the need for using function purity in pl/sql

To be callable from SQL statements, a stored function must obey the following "purity" rules, which are meant to control side effects:

1. When called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.

2. When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement.

3. When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit. Which type of binding does PL/SQL use?

PL/SQL uses early binding which makes the execution of block because as fast as possible as the database objects are already verified by the compiler.

Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.

How a database language implements binding affects runtime efficiency and flexibility. Binding at compile time, called static or early binding, increases efficiency because the definitions of database objects are looked up then, not at run time. On the other hand, binding at run time, called dynamic or late binding, increases flexibility because the definitions of database objects can remain unknown until then.

What is the difference between using IS and AS while creating a procedure, function package and package body?

There is absolutely no difference between using IS and AS while creating a procedure, a function or a package.

(40)

Functions with "out" parameters cannot be used in SQL statements, however I disagree because functions without parameters can also be called in select statements as long as they have a return() (Eg: SYSDATE)

What do you mean by OCI, Data guard and Advance queue responsibilities for a Oracle developers?

OCI stands for Oracle Call Interface, it is a interface api used to access oracle database from C programs. Data Guard stands for it is multiple DB servers environment configured for Physical as well as Logical Standby Databases. And Oracle Advance Queuing provides

interfacing/messing mechanism between oracle and non- oracle systems. Compare EXISTS and IN Usage with advantages and disadvantages. IN or EXISTS which gives better performance depends upon the situation.

Select * from T1 where x in (select y from T2 ) is typically processed as:

select *

from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.

While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.

select * from t1 where exists ( select null from t2 where y = x ) That is processed more like:

for x in ( select * from t1 ) loop

if ( exists ( select null from t2 where y = x.x ) then

OUTPUT THE RECORD end if

end loop

It always results in a full scan of T1

Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes sense.

References

Related documents

Secondly, the development of a common language (ontology of competencies, concepts of similarity and complementarity) reduces the cognitive distance between the

DMTF shall have no liability to any party implementing such standard, whether such implementation is foreseeable or not, nor to any patent owner or claimant, and shall have

Результати навчання Курсант, який успішно завершив вивчення дисципліни, повинен: знати: вимоги законодавства України, відомчих нормативних актів та

However, high power dissipation across these switches demands tolerating capabilities (high voltage and current stress) of the semiconductor and hence integration of power

Creative Pro Qualifying Revenue is defined as the value to Adobe of purchases, less any returns, effected by Distributor (“sellthrough value”) directly from Adobe, of all

The motion seat for driving car simulator is developed for controlling the angle of dc motor which actuates the designed frame seat by using data extracted from

Because the underlying interest of the studies constituting this thesis was response to institutional change, i.e., change in member relationships with the

is no better allocation of the five individual funds available to achieve a maximum return for the level of risk assumed (FRTIB – Thrift Savings Plan, 2010:2). This research will