informatica

Solution for Informatica

problems

>> Scenario based solutions

 Classic  Flipcard  Magazine  Mosaic  Sidebar  Snapshot  Timeslide

1.

3

Diff between Primary key and unique key

Diff between Primary key and unique key

Unique key display the unique values which can have one null value where Primary key has unique values without null.

Whenever you create the primary key constraints, Oracle by default create a unique index with not null.

Posted 3rd December 2012 by Prafull Dangore 0 Add a comment 2.

3.

15

SQL Transformation with examples

==================================================================================== =========

SQL Transformation with examples

Use: SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.Use SQL transformation in script mode to run DDL (data definition language) statements like creating or dropping the tables.

The following SQL statements can be used in the SQL transformation.  Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)  DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)  DATA Retrieval Statement (SELECT)

 DATA Control Language Statements (GRANT, REVOKE)  Transaction Control Statements (COMMIT, ROLLBACK)

Scenario: Let’s say we want to create a temporary table in mapping while workflow is running for some intermediate calculation. We can use SQL transformation in script mode to achieve the same. Below we will see how to create sql transformation in script mode with an example where we will create a table in mapping and will insert some rows in the same table.

Solution: Step 1:

Create two text files in the $PMSourceFileDir directory with some sql queries. 1. sql_script.txt

File contains the below Sql queries (you can have multiple sql queries in file separated by semicolon) create table create_emp_table

(emp_id number,emp_name varchar2(100)) 2. sql_script2.txt

insert into create_emp_table values (1,'abc')

These are the script files to be executed by SQL transformation on database server.

Step 2:

We need a source which contains the above script file names with a complete path.

So, I created another file in the $PMSourceFileDir directory to store these script file names as Sql_script_input.txt.

File contains the list of files with their complete path:

E:\softs\Informatica\server\infa_shared\SrcFiles\sql_script.txt E:\softs\Informatica\server\infa_shared\SrcFiles\sql_script2.txt Step 3:

Now we will create a mapping to execute the script files using the SQL transformation. Go to the mapping designer tool, source analyzer and Import from file

=>then creates source definition by selecting a file Sql_script_input.txt Located at E:\softs\Informatica\server\infa_shared\SrcFiles.

Source definition will look like

Similarly create a target definition, go to target designer and create a target flat file with result and error ports. This is shown in the below image

Step 4:

 Go to the mapping designer and create a new mapping.  Drag the flat file into the mapping designer.

 Go to the Transformation in the toolbar, Create, select the SQL transformation, enter a name and click on create.

 The SQL transformation is created with the default ports.

 Now connect the source qualifier transformation ports to the SQL transformation input port.

 Drag the target flat file into the mapping and connect the SQL transformation output ports to the target.

 Save the mapping. The mapping flow image is shown in the below picture.

 Go to the workflow manager; create a new workflow and session.  Edit the session. For source, enter the source & target file directory.

 For the SQL transformation, enter the oracle database relational connection as shown below.  Save the workflow and run it.

 Open the target file, you will find the below data. "PASSED";

"PASSED";

"PASSED"; -: for sql_script.txt, where it will crate the table and

"PASSED"; -: For sql_scriptw.txt, where it will insert rows in to the table Fire a select query on the database to check whether table is created or not.

=============================================================================

Posted 15th June 2012 by Prafull Dangore

Add a comment

4.

JUN

7

Efficient SQL Statements : SQL Tunning Tips

Efficient SQL Statements

This is an extremely brief look at some of the factors that may effect the efficiency of your SQL and PL/SQL code. It is not intended as a thorough discussion of the

area and should not be used as such.

 Check Your Stats

 Why Indexes Aren't Used

 Caching Tables

 EXISTS vs. IN

 Presence Checking

 Inequalities

 When Things Look Bad!

 Driving Tables (RBO Only)

 Improving Parse Speed

 Packages Procedures and Functions

Check Your Stats

The Cost Based Optimizer (CBO) uses statistics to decide which execution plan to use. If these statistics are incorrect the decision made by the CBO may be incorrect. For this reason it is important to make sure that these statistics are refreshed regularly. The following article will help you achieve this aim.  Cost Based Optimizer (CBO) and Database Statistics

Why Indexes Aren't Used

The presence of an index on a column does not guarantee it will be used. The following is a small list of factors that will prevent an index from being used.  The optimizer decides it would be more efficient not to use the index. If your query

is returning the majority of the data in a table, then a full table scan is probably going to be the most efficient way to access the table.

 You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'. The solution to this is to use a Function-Based Index.

 You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001

 You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used. Index Skip Scanning in Oracle 9i and above allow

indexes to be used even when the leading edge is not referenced.

 The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use an INDEX hint.

EXISTS vs. IN

The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences.

TABLE1 - 1000 rows TABLE2 - 1000 rows (A)

SELECT t1.id FROM table1 t1

WHERE t1.code IN (SELECT t2.code FROM table2 t2); (B)

SELECT t1.id FROM table1 t1

WHERE EXISTS (SELECT '1' FROM table2 t2

WHERE t2.code = t1.code)

For query A, all rows in TABLE2 will be read for every row in TABLE1. The effect will be 1,000,000 rows read from items. In the case of query B, a maximum of 1 row from TABLE2 will be read for each row of TABLE1, thus reducing the processing

overhead of the statement.

Rule of thumb:

 If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.

 If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.

I would suggest they you should try both variants and see which works the best. Note. In later versions of Oracle there is little difference between EXISTS and IN operations.

Presence Checking

The first question you should ask yourself is, "Do I need to check for the presence of a record?" Alternatives to presence checking include:  Use the MERGE statement if you are not sure if data is already present.

 Perform an insert and trap failure because a row is already present using theDUP_VAL_ON_INDEX exception handler.

If none of these options are right for you and processing is conditional on the presence of certain records in a table, you may decide to code something like the following.

SELECT Count(*) INTO v_count FROM items

WHERE item_size = 'SMALL'; IF v_count = 0 THEN

-- Do processing related to no small items present END IF;

If there are many small items, time and processing will be lost retrieving multiple records which are not needed. This would be better written like one of the following.

SELECT COUNT(*) INTO v_count FROM items

WHERE item_size = 'SMALL' AND rownum = 1;

IF v_count = 0 THEN

-- Do processing related to no small items present END IF;

OR

SELECT COUNT(*) INTO v_count FROM dual

WHERE EXISTS (SELECT 1 FROM items

WHERE item_size = 'SMALL'); IF v_count = 0 THEN

-- Do processing related to no small items present END IF;

In these examples only single a record is retrieved in the presence/absence check. Inequalities

If a query uses inequalities (item_no > 100) the optimizer must estimate the number of rows returned before it can decide the best way to retrieve the data. This estimation is prone to errors. If you are aware of the data and it's distribution you can use optimizer hints to encourage or discourage full table scans to improve performance.

If an index is being used for a range scan on the column in question, the performance can be improved by substituting >= for >. In this case, item_no > 100 becomes item_no >= 101. In the first case, a full scan of the index will occur. In the second case, Oracle jumps straight to the first index entry with an item_no of 101 and range scans from this point. For large indexes this may significantly reduce the number of blocks read.

When Things Look Bad!

If you have a process/script that shows poor performance you should do the following:

 Write sensible queries in the first place!

 Identify the specific statement(s) that are causing a problem. The simplest way to do this is to use SQL Trace, but you can try running the individual statements using

SQL*Plus and timing them (SET TIMING ON)

 Use EXPLAIN to look at the execution plan of the statement. Look for any full table accesses that look dubious. Remember, a full table scan of a small table is often more efficient than access by index.

 Check to see if there are any indexes that may help performance.

 Try adding new indexes to the system to reduce excessive full table scans. Typically, foreign key columns should be indexed as these are regularly used in join conditions. On occasion it may be necessary to add composite (concatenated) indexes that will only aid individual queries. Remember, excessive indexing can reduce INSERT, UPDATE and DELETE performance.

Driving Tables (RBO Only)

The structure of the FROM and WHERE clauses of DML statements can be tailored to improve the performance of the statement. The rules vary depending on whether the database engine is using the Rule or Cost based optimizer. The situation is further complicated by the fact that the engine may perform a Merge Join or a Nested Loop join to retrieve the data. Despite this, there are a few rules you can

use to improve the performance of your SQL.

Oracle processes result sets a table at a time. It starts by retrieving all the data for the first (driving) table. Once this data is retrieved it is used to limit the number of rows processed for subsequent (driven) tables. In the case of multiple table joins, the driving table limits the rows processed for the first driven table. Once processed, this combined set of data is the driving set for the second driven table etc. Roughly translated into English, this means that it is best to process tables that will retrieve a small number of rows first. The optimizer will do this to the best of it's ability regardless of the structure of the DML, but the following factors may help.

Both the Rule and Cost based optimizers select a driving table for each query. If a decision cannot be made, the order of processing is from the end of the FROM clause to the start. Therefore, you should always place your driving table at the end of the FROM clause. Subsequent driven tables should be placed in order so that those retrieving the most rows are nearer to the start of the FROM clause. Confusingly, the WHERE clause should be writen in the opposite order, with the driving tables conditions first and the final driven table last. ie.

FROM d, c, b, a

WHERE a.join_column = 12345

AND a.join_column = b.join_column AND b.join_column = c.join_column AND c.join_column = d.join_column;

If we now want to limit the rows brought back from the "D" table we may write the following.

FROM d, c, b, a

WHERE a.join_column = 12345

AND a.join_column = b.join_column AND b.join_column = c.join_column AND c.join_column = d.join_column AND d.name = 'JONES';

Depending on the number of rows and the presence of indexes, Oracle my now pick "D" as the driving table. Since "D" now has two limiting factors (join_column and name), it may be a better candidate as a driving table so the statement may be better written as follows.

FROM c, b, a, d

WHERE d.name = 'JONES' AND d.join_column = 12345

AND d.join_column = a.join_column AND a.join_column = b.join_column AND b.join_column = c.join_column

This grouping of limiting factors will guide the optimizer more efficiently making table "D" return relatively few rows, and so make it a more efficient driving table. Remember, the order of the items in both the FROM and WHERE clause will not force the optimizer to pick a specific table as a driving table, but it may influence it's decision. The grouping of limiting conditions onto a single table will reduce the number of rows returned from that table, and will therefore make it a stronger candidate for becoming the driving table.

Caching Tables

Queries will execute much faster if the data they reference is already cached. For small frequently used tables performance may be improved by caching tables. Normally, when full table scans occur, the cached data is placed on the Least Recently Used (LRU) end of the buffer cache. This means that it is the first data to be paged out when more buffer space is required. If the table is cached (ALTER TABLE employees CACHE;) the data is placed on the Most Recently Used (MRU) end of the buffer, and so is less likely to be paged out before it is re-queried. Caching tables may alter the CBO's path through the data and should not be used without careful consideration.

Improving Parse Speed

Execution plans for SELECT statements are cached by the server, but unless the exact same statement is repeated the stored execution plan details will not be reused. Even differing spaces in the statement will cause this lookup to fail. Use of bind variables allows you to repeatedly use the same statements whilst changing the WHERE clause criteria. Assuming the statement does not have a cached execution plan it must be parsed before execution. The parse phase for statements can be decreased by efficient use of aliasing. If an alias is not present, the engine must resolve which tables own the specified columns. The following is an example.

SELECT first_name, last_name, country FROM employee, countries WHERE country_id = id AND lastname = 'HALL'; SELECT e.first_name, e.last_name, c.country FROM employee e, countries c WHERE e.country_id = c.id AND e.last_name = 'HALL';

Packages Procedures and Functions

When an SQL statement, or anonymous block, is passed to the server it is processed in three phases.

Phase Actions

Parse Syntax Check and Object Resolution ExecutionNecessary Reads and Writes performed

Fetch Resultant rows are Retrieved, Assembled, Sorted and Returned

The Parse phase is the most time and resource intensive. This phase can be avoided if all anonymous blocks are stored as Database Procedures, Functions, Packages or Views. Being database objects their SQL text and compiled code is stored in Data Dictionary and the executable copies reside in the Shared Pool.

Posted 7th June 2012 by Prafull Dangore 0 Add a comment

5.

7

Function : NVL2 and COALESCE

NVL2

The NVL2function accepts three parameters. If the first parameter value is not null it returns the value

in the second parameter. If the first parameter value is null, it returns the third parameter.

The following query shows NVL2 in action.

SQL> SELECT * FROM null_test_tab ORDER BY id;

ID COL1 COL2 COL3 COL4 --- --- --- --- --- 1 ONE TWO THREE FOUR 2 TWO THREE FOUR

3 THREE FOUR 4 THREE THREE

4 rows selected.

SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id; ID OUTPUT --- --- 1 TWO 2 THREE 3 THREE 4 THREE 4 rows selected. SQL> COALESCE

TheCOALESCE function was introduced in Oracle 9i. It accepts two or more parameters and returns the

first non-null value in a list. If all parameters contain null values, it returns null.

SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id; ID OUTPUT --- --- 1 ONE 2 TWO 3 THREE 4 THREE 4 rows selected. SQL>

Posted 7th June 2012 by Prafull Dangore 0 Add a comment 6.

7.

8

Load the session statistics such as Session Start & End Time,

Success Rows, Failed Rows and Rejected Rows etc. into a database

Scenario:

Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose.

Solution:

After performing the below solution steps your end workflow will look as follows:

START => SESSION1 => ASSIGNMENT TASK => SESSION2

SOLUTION STEPS SESSION1

This session is used to achieve your actual business logic. Meaning this session will perform your actual data load. It can be anything File Table.File or TableTable, File 

WORKFLOW VARIABLES

Create the following workflow variables.

=> $$Workflowname => $$SessionStartTime => $$SessionEndTime => $$TargetSuccessrows => $$TargetFailedRows ASSIGNMENT TASK

Use the Expression tab in the Assignment Task and assign as follows:

$$workflowname = $PMWorkflowName

$$sessionStartTime = $ SESSION1.StartTime

$$SessionEndTime = $ SESSION1.Endtime

$$ TargetSuccessrows = $ SESSION1. TgtSuccessRows

$$ TargetFailedRows = $ SESSION1. TgtFailedRows

SESSION2

This session is used to load the session statistics into a database table. => This should call a mapping say ‘m_sessionLog’ => This mapping m_sessionLog should have mapping variables for the above defined workflow variables such as $$wfname, $$Stime, $$Etime, $$TSRows and $$TFRows. => This mapping m_sessionLog should use a dummy source and it must have a expression transformation and a target => database Audit table) => Inside the expression you must assign the mapping variables to the output ports

workflowname=$$wfname starttime=$$Stime

endtime=$$Etime SucessRows=$$TSRows FailedRows=$$TFRows

=> Create a target database table with the following columns Workflowname, start time, end time, success rows and failed rows. => Connect all the required output ports to the target which is nothing but your audit table.

PRE-Session Variable

=> Session 2: In the Pre-session variable assignment tab assign the mapping variable = workflow variable => In our case $$wfname=$$workflowname $$Stime=$$sessionStartTime $$Etime=$$sessionEndTime $$TSRows=$$TargetSuccessrows $$TFRows=$$TargetFailedrows Workflow Execution

Posted 8th February 2012 by Prafull Dangore 0 Add a comment

8.

30

Use Target File Path in Parameter File

I want to use mapping parameter to store target file path. My question is can define file path in parameter file? If possible can anyone explain how to assign target file path as parameter?

Solution:

You can define the file path in parameter file.

$OutputFileName=your file path here

Give the above mentioned parameter in your parameter file.

Posted 30th December 2011 by Prafull Dangore

0 Add a comment

9.

DEC

27

Insert and reject records using update strategy.

Insert and reject records using update strategy.

There is an emp table and from that table insert the data to targt where sal<3000 and reject other rows.

Solution:

1. connect out-puts from SQF to Update Strategy transformation.

2. In properties of Update Strategy write the condition like this

IIF(SAL<3000,DD_INSERT,DD_REJECT)

3. Connectthe Update Strategy to target

Posted 27th December 2011 by Prafull Dangore 0 Add a comment 10.

11.

27

Convert Numeric Value to Date Format

Suppose you are importing a flat file emp.csv and hire_date colummn is in numeric format, like 20101111 .Our objective is convert it to date,with a format 'YYYYMMDD'. Source EMPNO HIRE_DATE(numeric) --- --- 1 20101111 2 20090909 Target

EMPNO HIRE_DATE (date)

1 11/11/2010 2 09/09/2009

Solution:

1. Connect SQF to an expression.

2. In expression make hire_date as input only and make another port hire_date1 as o/p port with date

data type.

3. In o/p port of hire_date write condition like as below

TO_DATE(TO_CHAR(hire_date),’YYYYMMDD’)

Posted 27th December 2011 by Prafull Dangore 1 View comments

2.

26

How to change a string to decimal with 2 decimal places in

informatica?

Scenario:

How to change a string to decimal with 2 decimal places in informatica?

Eg:: input data 12345678

I want output as 123456.78

Solution:

output = to_decimal(to_integer(input)/100,2) OR

SUBSTR(INPUT_FIELD, 1, LENGTH(INPUT_FIELD) - 2) || '.' || SUBSTR(INPUT_FIELD, -2) Posted 26th December 2011 by Prafull Dangore

0 Add a comment

3.

26

Append the data in a flat file for a daily run

I have the flat file in our server location; I want to append the data in a flat file for a daily run.

Solution:

We have an option in Informatica "Append if exists" in target session properties.

Posted 26th December 2011 by Prafull Dangore 0 Add a comment 4.

5.

22

Convert Day No. to corresponding month and date of year

Scenario:

Suppose you have a source is like this

Source

E_NO YEAR DAYNO

--- --- - ---

1 01-JAN-07 301

2 01-JAN-08 200

Year column is a date and dayno is numeric that represents a day ( as in 365 for 31-Dec-Year). Convert the Dayno to corresponding year's month and date and then send to target. Target E_NO YEAR_MONTH_DAY --- --- --- 1 29-OCT-07 2 19-JUL-08 Solution:

Use below date format in exp transformation Add_to_date(YEAR,’DD’,DAYNO)

Posted 22nd December 2011 by Prafull Dangore

0 Add a comment

6.

DEC

22

How to delete duplicate rows in a table?

How to delete duplicate rows in a table?

Solution:

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno); OR

delete from emp a where rowid != (select min(rowid) from emp b where a.empno=b.empno); Posted 22nd December 2011 by Prafull Dangore

0 Add a comment

7.

22

How to get nth max salaries ?

How to get nth max salaries ?

Solution:

select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);

Posted 22nd December 2011 by Prafull Dangore 0 Add a comment 8.

9.

22

How to get 3 Max & Min salaries?

How to get 3 Max & Min salaries?

Solution:

Max - select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

Min - select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

Posted 22nd December 2011 by Prafull Dangore 0 Add a comment

10.

22

Find FIRST & LAST n records from a table.

Find FIRST & LAST n records from a table.

Solution:

First - select * from emp where rownum <= &n;

Last - select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

Posted 22nd December 2011 by Prafull Dangore 0 Add a comment

11.

22

Find the 3rd MAX & MIN salary in the emp table

Find the 3rd MAX & MIN salary in the emp table

Solution:

Max

-select distinct sal from emp e1 where 3 =

(select count(distinct sal) from emp e2 where e1.sal <= e2.sal); Min

-select distinct sal from emp e1 where 3 =

(select count(distinct sal) from emp e2 where e1.sal >= e2.sal);

Posted 22nd December 2011 by Prafull Dangore 0 Add a comment 12.

13.

22

Sql query to find EVEN & ODD NUMBERED records from a table.

Scenario:

Sql query to find EVEN & ODD NUMBERED records from a table.

Solution:

Even - select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp); Odd - select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

Posted 22nd December 2011 by Prafull Dangore

0 Add a comment

14.

22

SQL questions which are the most frequently asked in interviews.

Complex Queries in SQL ( Oracle )

 To fetch ALTERNATE records from a table. (EVEN NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

 To select ALTERNATE records from a table. (ODD NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

 Find the 3rd MAX salary in the emp table.

select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

 Find the 3rd MIN salary in the emp table.

select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

 Select FIRST n records from a table.

select * from emp where rownum <= &n;

 Select LAST n records from a table

select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

 List dept no., Dept name for all the departments in which there are no employees in the department.

select * from dept where deptno not in (select deptno from emp); alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);

altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

 How to get 3 Max salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

 How to get 3 Min salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

 How to get nth max salaries ?

select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);

 Select DISTINCT RECORDS from emp table.

select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);

 How to delete duplicate rows in a table?

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

 Count of number of employees in department wise. select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;

 Suppose there is annual salary information provided by emp table. How to fetch monthly

select ename,sal/12 as monthlysal from emp;

 Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

 Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

 Select all record from emp where job not in SALESMAN or CLERK. select * from emp where job not in ('SALESMAN','CLERK');

 Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'. select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

 Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

 Select all records where ename may be any no of character but it should end with ‘R’.

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

 Count MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

 In emp table add comm+sal as total sal .

select ename,(sal+nvl(comm,0)) as totalsal from emp;

 Select any salary <3000 from emp table.

select * from emp where sal> any(select sal from emp where sal<3000);

 Select all salary <3000 from emp table.

select * from emp where sal> all(select sal from emp where sal<3000);

 Select all the employee group by deptno and sal in descending order. select ename,deptno,sal from emp order by deptno,sal desc;

 How can I create an empty table emp1 with same structure as emp? Create table emp1 as select * from emp where 1=2;

 How to retrive record where sal between 1000 to 2000? Select * from emp where sal>=1000 And sal<2000

 Select all records where dept no of both emp and dept table matches. select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

 If there are two tables emp1 and emp2, and both have common record. How can I fetch all

the recods but common records only once?

(Select * from emp) Union (Select * from emp1)

 How to fetch only common records from two tables emp and emp1? (Select * from emp) Intersect (Select * from emp1)

 How can I retrive all records of emp1 those should not present in emp2?

(Select * from emp) Minus (Select * from emp1)

 Count the totalsa deptno wise where more than 2 employees exist.

SELECT deptno, sum(sal) As totalsal

FROM emp

GROUP BY deptno

HAVING COUNT(empno) > 2

Posted 22nd December 2011 by Prafull Dangore

0 Add a comment

15.

22

Complex Queries in SQL ( Oracle )

Complex Queries in SQL ( Oracle )

 To fetch ALTERNATE records from a table. (EVEN NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

 To select ALTERNATE records from a table. (ODD NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

 Find the 3rd MAX salary in the emp table.

select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

 Find the 3rd MIN salary in the emp table.

select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

 Select FIRST n records from a table.

select * from emp where rownum <= &n;

 Select LAST n records from a table

select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

 List dept no., Dept name for all the departments in which there are no employees in the department.

select * from dept where deptno not in (select deptno from emp); alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);

altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

 How to get 3 Max salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

 How to get 3 Min salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

 How to get nth max salaries ?

select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);

 Select DISTINCT RECORDS from emp table.

select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);

 How to delete duplicate rows in a table?

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

 Count of number of employees in department wise. select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;

 Suppose there is annual salary information provided by emp table. How to fetch monthly

select ename,sal/12 as monthlysal from emp;

 Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

 Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

 Select all record from emp where job not in SALESMAN or CLERK. select * from emp where job not in ('SALESMAN','CLERK');

 Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'. select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

 Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

 Select all records where ename may be any no of character but it should end with ‘R’.

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

 Count MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

 In emp table add comm+sal as total sal .

select ename,(sal+nvl(comm,0)) as totalsal from emp;

 Select any salary <3000 from emp table.

select * from emp where sal> any(select sal from emp where sal<3000);

 Select all salary <3000 from emp table.

select * from emp where sal> all(select sal from emp where sal<3000);

 Select all the employee group by deptno and sal in descending order. select ename,deptno,sal from emp order by deptno,sal desc;

 How can I create an empty table emp1 with same structure as emp? Create table emp1 as select * from emp where 1=2;

 How to retrive record where sal between 1000 to 2000? Select * from emp where sal>=1000 And sal<2000

 Select all records where dept no of both emp and dept table matches. select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

 If there are two tables emp1 and emp2, and both have common record. How can I fetch all

the recods but common records only once?

(Select * from emp) Union (Select * from emp1)

 How to fetch only common records from two tables emp and emp1? (Select * from emp) Intersect (Select * from emp1)

 How can I retrive all records of emp1 those should not present in emp2?

(Select * from emp) Minus (Select * from emp1)

 Count the totalsa deptno wise where more than 2 employees exist.

SELECT deptno, sum(sal) As totalsal

FROM emp

GROUP BY deptno

HAVING COUNT(empno) > 2

Posted 22nd December 2011 by Prafull Dangore

0 Add a comment

16.

17.

DEC

22

Informatica Quiz: Set 2

Quiz: Informatica Set 2

A lookup transformation is used to look up data in Explanation:

 flat file

 Relational table  view

 synonyms

 All of the above (correct)

Which value returned by NewLookupRow port says that Integration Service does not update or insert the row in the cache?

Explanation:  3 (wrong)  2

 1  0

Which one need a common key to join? Explanation:

 source qualifier  joiner (correct)  look up

Which one support hetrogeneous join? Explanation:

 source qualifier  joiner (correct)  look up

What is the use of target loader? Explanation:

 Target load order is first the data is load in dimension table and then fact table.  Target load order is first the data is load in fact table and then dimensional table.  Load the data from different target at same time. (wrong)

Which one is not tracing level? Explanation:

 terse  verbose

 initialization

 verbose initialization  terse initialization (correct)

Which output file is not created during session running? Explanation:

 Session log  workflow log  Error log  Bad files

 cache files (correct)

Is Fact table is normalised ? Explanation:

 yes

 no (correct)

Which value returned by NewLookupRow port says that Integration Service inserts the row into the cache? Explanation:  0 (wrong)  1  2  3

Which transformation only works on relational source?

Explanation:  lookup  Union  joiner  Sql (correct)

Which are both connected and unconnected? Explanation:

 External Store Procedure (omitted)  Stote Procedure (correct)

 Lookup (correct)

 Advanced External Procedure Transformation

Can we generate alpha-numeric value in sequence generator? Explanation:

 yes

 no (correct)

Explanation:

 Advanced External Procedure Transformation  Cobol Transformation

 Unstructured Data Transformation  Normalizer (correct)

What is VSAM normalizer transformation? Explanation:

 The VSAM normalizer transformation is the source qualifier transformation for a COBOL source definition.

 The VSAM normalizer transformation is the source qualifier transformation for a flat file source definition.

 The VSAM normalizer transformation is the source qualifier transformation for a xml source definition. (wrong)

 Non of these

What is VSAM normalizer transformation? Explanation:

 The VSAM normalizer transformation is the source qualifier transformation for a COBOL source definition.

 The VSAM normalizer transformation is the source qualifier transformation for a flat file source definition.

 The VSAM normalizer transformation is the source qualifier transformation for a xml source definition. (wrong)

 Non of these

Posted 22nd December 2011 by Prafull Dangore

0 Add a comment

18.

22

Informatica Quiz: Set 1

Quiz: Informatica Set 1

Which one is not correct about filter transformation?

Explanation: Filter generally parses single condition. For multiple condition we can use router  Act as a 'where' condition

 Can't passes multiple conditions  Act like 'Case' in pl/sql (wrong)

 If one record does not match condition, the record is blocked Can we calculate in aggrigator ?

Explanation:  No

 Yes (correct)

Which one is not a type of fact? Explanation:

 Semi-aditive  Additive  Confirm fact

 Not additive (wrong)

Which one is not a type of dimension ? Explanation:

 Conformed dimension

 Rapidly changing dimension (correct)  Junk dimension

 Degenerated dimension

Which of these not correct about Code Page? Explanation:

 A code page contains encoding to specify characters in a set of one or more languages  A code page contains decoding to specify characters in a set of one or more languages  In this way application stores, receives, and sends character data.

 Non of these (wrong) What is a mapplet? Explanation:

 Combination of reusable transformation.  Combination of reusable mapping

 Set of transformations and it allows us to reuse (correct)  Non of these

What does reusable transformation mean? Explanation:

 It can be re-used across repositories  I can only be used in mapplet.

 It can use in multiple mapping only once

 It can use in multiple mapping multiple times (correct) Which one is not an option in update strategy? Explanation:

 dd_reject  4 (correct)  2

 dd_delete

Can we update records without using update strategy? Explanation:

 Yes (correct)  No

How to select distinct records form Source Qualifier? Explanation:

 Choose 'non duplicate' option

 Choose 'select distinct' option (correct)  Choose 'Select non duplicate'

What type of repository is no available in Informatica Repository Manager? Explanation:

 Standalone Repository  Local Repository  User Defined

 Versioned Repository  Manual Repository (wrong)

Joiner does not support flat file. Explanation:

 False (correct)  True

How to execute PL/SQL script from Informatica mapping? Explanation:

 Lookup

 Store Procdure (correct)  Expression

 Non of these

NetSal= bassic+hra. In which transformation we can achive this? Explanation:

 Aggrigator  Lookup  Filter

 Expression (correct)

Which one is not an active transformation? Explanation:

 Sequence generator  Normalizer

 Sql

 Store Procedure (wrong)

Posted 22nd December 2011 by Prafull Dangore 0 Add a comment

19.

22

Scenario:

How large is the database,used and free space?

Solution:

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 )

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes

from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p

Posted 22nd December 2011 by Prafull Dangore 0 Add a comment 20.

21.

20

Batch File to Append Date to file name

Batch File to Append Date to file name

Solution:

@echo off

REM Create a log file with the current date and time in the filename REM the ~4 in the Date skips the first four characters of the echoed date stamp and writes the

remainder and so on

set

LOG_FILE_NAME=Example_File_Name.%date:~4,2%%date:~7,2%%date:~10,4%.%time:~0,2%%ti me:~3,2%%time:~6,2%.txt

Echo This is much easier in UNIX > c: emp\%LOG_FILE_NAME% :exit

OR

@echo off

for /F "tokens=2,3,4 delims=/ " %%i in ('date/t') do set y=%%k for /F "tokens=2,3,4 delims=/ " %%i in ('date/t') do set d=%%k%%i%%j for /F "tokens=5-8 delims=:. " %%i in ('echo.^| time ^| find "current" ') do set t=%%i%%j

set t=%t%_

if "%t:~3,1%"=="_" set t=0%t%

set t=%t:~0,4%

set "theFilename=%d%%t%"

echo %theFilename%

Posted 20th December 2011 by Prafull Dangore 0 Add a comment

22.

19

PL/SQL Interview Questions

PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and datatypes are similar to that of ADA. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

PL SQL is a block structured programming language. It combines data manipulation & data

processing power. It supports all SQL data types. Also has its own data types i,e BOOLEAN,BINARY INTEGER

2. What is the basic structure of PL/SQL ? A PL/SQL block has three parts: a declarative part,

an executable part,

and an exception-handling part.

First comes the declarative part, in which items can

be declared. Once declared, items can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.

3. What are the components of a PL/SQL block ?

PL/SQL Block contains :

Declare : optional

Variable declaration

Begin : Manadatory

Exception : Optional

any errors to be trapped

End : Mandatory

5. What are the datatypes a available in PL/SQL ?

Following are the datatype supported in oracle PLSQL

Scalar Types BINARY_INTEGER DEC DECIMAL DOUBLE PRECISION FLOAT INT INTEGER NATURAL NATURALN NUMBER NUMERIC PLS_INTEGER POSITIVE POSITIVEN REAL SIGNTYPE SMALLINT CHAR CHARACTER LONG LONG RAW NCHAR NVARCHAR2 RAW ROWID STRING UROWID VARCHAR VARCHAR2 DATE

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

BOOLEAN Composite Types RECORD TABLE VARRAY LOB Types BFILE BLOB CLOB

NCLOB

Reference Types

REF CURSOR

REF object_type

6. 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 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.

Advantage is, if one change the type or size of the column in the table, it will be reflected in our

program unit without making any change.

%type is used to refer the column's datatype where as %rowtype is used to refer the whole record in

a table.

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

8. What is PL/SQL table ?

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -231_{-1 to 2}31_{-1, so} you have an awful lot of rows with which to work

9. 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.

10. Explain the two type of Cursors ?

implicit cursor: implicit cursor is a type of cursor which is automatically maintained by the Oracle

Explicit Cursor: Explicit Cursor is defined by the Proframmer,and it has for phases:declare,open,fetch and close.explicit Cursor returns more than one row.

11. What are the PL/SQL Statements used in cursor processing ?

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

12. 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

13. 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;

cursor for loop is use for automatically open ,fetch,close 15. Explain the usage of WHERE CURRENT OF clause in cursors ?

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 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:

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;

16. What is a database trigger ? Name some usages of database trigger ? A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database triggers enable DBA's (Data Base Administrators) to create additional relationships

between separate databases.

For example, the modification of a record in one database could trigger the modification of a record

in a second database.

17. 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.

the different types of triggers: * Row Triggers and Statement Triggers * BEFORE and AFTER Triggers * INSTEAD OF Triggers * Triggers on System Events and User Events

18. 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 NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.

The two virtual table available are old and new.

19.What happens if a procedure that updates a column of table X is called in a database trigger of the same table ? 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. 20. Write the order of precedence for validation of a column in a table ?

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

21. What is an Exception ? What are types of Exception ?  Predefined

Do not declare and allow the Oracle server to raise implicitly

NO_DATA_FOUND TOO_MANY_ROWS

INVALID_CURSOR ZERO_DIVIDE INVALID_CURSOR

WHEN EXCEPTION THEN …  Non predefined

Declare within the declarative section and allow allow Oracle server

toraise implicitly

o SQLCODE– Returns the numeric value for the seeor code o SQLERRM– Returns the message associated with error number

DECLARE -- PRAGMA EXCEPTION_INIT (exception, error_number) RAISE – WHEN EXCEPTION_NAME THEN …

 User defined

Declare within the declarative section and

raise explicitly.

IF confidition the

RAISE EXCEPTION or RAISE_APPLICATION_ERROR

22. What is Pragma EXECPTION_INIT ? Explain the usage ?

Pragma exception_init Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program,and handle it accoding to your

own way.

It should be declare at the DECLARE section.

example declare salary number; FOUND_NOTHING exception; Pragma exception_init(FOUND_NOTHING ,100); begin

select sal in to salaryfrom emp where ename ='ANURAG';

dbms_output.put_line(salary); exception

WHEN FOUND_NOTHING THEN

dbms_output.put_line(SQLERRM); end;

23. What is Raise_application_error ?

Raise_application_error is used to create your own error messages which can be more descriptive

than named exceptions.

Syntax is:-

Raise_application_error (error_number,error_messages);

where error_number is between -20000 to -20999..

24. What are the return values of functions SQLCODE and SQLERRM ? Pl / Sql Provides Error Information via two Built-in functions, SQLCODE & SQLERRM.

SQLCODE Returns the Current Error Code.

Returns 1.

SQLERRM Returns the Current Error Message Text.

Returns " User Defined Exception "

25. Where the Pre_defined_exceptions are stored ?

26. What is a stored procedure ? Stored Procedure is the PlSQL subprgram stored in the databasse .

Stored Procedure

Aprogramrunning in the databasethat can take complex actions based on the inputs you send it. Using a stored procedure is faster than doing the same work on a client, because the program runs right inside the databaseserver. Stored procedures are nomally written in PL/SQLor Java.

advantages fo Stored Procedure

Extensibility,Modularity, Reusability, Maintainability and one time compilation. 28. What are the modes of parameters that can be passed to a procedure ? 1.in:

in parameter mode is used to pass values to subprogram when invoked. 2.out:

out is used to return values to callers of subprograms

3.in out:

it is used to define in and out

29. What are the two parts of a procedure ? PROCEDURE name (parameter list...) is

local variable declarations BEGIN

Executable statements. Exception.

exception handlers end;

31. Give the structure of the function ?

FUNCTION name (argument list ...) Return datatype is local variable declarations

Begin

executable statements Exception

execution handlers End;

32. Explain how procedures and functions are called in a PL/SQL block ?

Procedure can be called in the following ways

a) CALL <procedure name> direc

b) EXCECUTE <procedure name> from calling environment

c) <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways

a) EXCECUTE <Function name> from calling environment. Always use a variable to get the return value.

b) As part of an SQL/PL SQL Expression

33. 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 contains actual procedures and local declaration of the procedures and cursor declarations.

33.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.

The scope of A cursor declared in a procedure is limited to that procedure only. The Scope of cursor declared in a package specification is global . Example:

create or replace package curpack is

cursor c1 is select * from emp;

end curpack;

This will create a package Now You can use this cursor any where. Like:

set serveroutput on

begin

for r1 in curpack.c1 loop

dbms_output.put_line(r1.empno||' '||r1.ename);

end loop;

end;

this will dispaly all empno and enames.

It will be better to use ref cursor in packages 35. 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.

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

The Data dictionary tables/ Views where the characteristics of subprograms and Packages are stored

are mentioned below

a) USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS

b) USER_SOURCE, ALL_SOURCE, DBA_SOURCE

c) USER_DEPENCENCIES

d) USER_ERRORS, ALL_ERRORS, DBA_ERRORS 37. What is Overloading of procedures ?

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.