Saturday, 10 December 2016

Dynamic SQL Inside FORALL Statement

DECLARE
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
empids NumList;
enames NameList;
BEGIN
empids := NumList(101,102,103,104,105);
FORALL i IN 1..5
EXECUTE IMMEDIATE
'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
RETURNING last_name INTO :2'
USING empids(i) RETURNING BULK COLLECT INTO enames;
END;

Cursor Attributes with Dynamic SQL

BEGIN
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000';
DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
end;
--------------------------------------------------------------------------
Accessing %ROWCOUNT For an Explicit Cursor
DECLARE
TYPE cursor_ref IS REF CURSOR;
c1 cursor_ref;
TYPE emp_tab IS TABLE OF employees%ROWTYPE;
rec_tab emp_tab;
rows_fetched NUMBER;
BEGIN
OPEN c1 FOR 'SELECT * FROM employees';
FETCH c1 BULK COLLECT INTO rec_tab;
rows_fetched := c1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' || TO_CHAR(rows_fetched));
END;
/

DBA TABLES

select * from v$instance;

select name from v$datafile;

select * from v$database;

select * from v$parameter;  --user_dump_dest  --sql_trace

select * from all_tables;

select * from dba_tables;

select * from V$NLS_PARAMETERS;

SELECT object_name, original_name, createtime FROM recyclebin;

select * from PRODUCT_COMPONENT_VERSION;

select * from NLS_DATABASE_PARAMETERS;

select * from ALL_LOBS;

select * from DBA_DB_LINKS;

select * from ALL_TAB_COLUMNS;

Dynamic SQL2

Dynamic SQL Procedure that Accepts Table Name and WHERE Clause
-------------------------------------------------------------------
CREATE TABLE employees_temp AS SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE delete_rows (
table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL) AS
where_clause VARCHAR2(100) := ' WHERE ' || condition;
v_table VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
IF condition IS NULL THEN where_clause := NULL; END IF;
EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/

BEGIN
delete_rows('employees_temp', 'employee_id = 111');
END;
/

Dynamic SQL1

CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER,
emp_column VARCHAR2, amount NUMBER) IS
v_column VARCHAR2(30);
sql_stmt VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '
|| v_column || ' = :2';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column
|| ' = ' || column_value);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/
=====================================================================================
DECLARE
plsql_block VARCHAR2(500);
BEGIN
-- note the semi-colons (;) inside the quotes '...'
plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;
EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
USING 112, 'EMPLOYEE_ID', 10;
END;
/
================================================================================
DECLARE
sql_stmt VARCHAR2(200);
v_column VARCHAR2(30) := 'DEPARTMENT_ID';
dept_id NUMBER(4) := 46;
dept_name VARCHAR2(30) := 'Special Projects';
mgr_id NUMBER(6) := 200;
loc_id NUMBER(4) := 1700;
BEGIN
-- note that there is no semi-colon (;) inside the quotes '...'
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
USING dept_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;
/

Monday, 8 February 2016

Interview Questions PLSQL

iCreate interview questions





1.Dim_Account



Account_number Ac_open_date Ac_Status Ac_Branch

100            may 12 2001  opened    B1

200            apr 15 2001  closed    B1

300            jun 21 2002  opened    B2

400            jan 25 2001  opened    B2



Fact_Account



Account_number Balance Interest_rate

100            -20     4

200            -10     3.5

300             0      2.5

400             100    2



A)Display the account numbers whose balace is zero using SQL joins.

B)Display the account numbers whose balance is negative.

c)Display the branches whose balance is positive.

D)select the account number which has second maximum balance.

E)calculate average interest rate for each branch.

G)calculate the sum of balance for each brach





2)I/P                    O/P

0000000As12             As12

000000003DE             3DE

00000oytu               oytu



3)create a stored procedure which will takes three parameters

Original table name

Duplicate with data(1,0)

Duplicate table name

Duplicate with data=1 create a duplicate table with records.

Duplicate with data=0 create a duplicate table without records.



4)declare

  cursor A is select * from cust where 1=2;

  begin

  open A;

  loop

   <logic starts>



   <logic ends>

  end loop;

  close A;

  end;

will it successfully work? or will it give no data found error?





5)How can we define two dimentional array in plsaql block.



6)write three exceptions name.



7)select tab1.col1

 from tab1,tab2

 where rownum=1

 and tab1.col1<>1234

 and tab2.col1=tab1.col1



if it give no data found then



 select tab1.col1

 from tab1,tab2

 where rownum=1

 and tab1.col1<>1234

 and tab2.col1=tab1.col1



if it give no data found then

return null



how do you compine these two sql queries?



8)In my schema I have only Test1 table



 what will happen for the below queries?

 insert into test1(col1) values (1);

 drop table test2;



9)I have two tables one is account_details another one is account_history



which account no is not present in the account_details.







*Round  (f2f)  1*

1)Tell  about yourself?

2)Tell about your project?

3)Tell about scd’s ?

4) How do u handle errors using try and catch block?

5)what is your  approach in Incremental extract?

6)Tell  about cdc concepts?sql server

7) Difference between  Datawarehouse and Data mart?

8) What is the difference between Star Schema and Snowflake Schema?

9) What is the difference between OLTP and OLAP?

10) Create dynamic view in sql server and plsql?

11) How do you populate Scd1 and Scd2 with example ?

Table 1

P_id

P_type

P_desc

P_price

Draw table for Scd1 and Scd2?

12) Difference between  Additive Measure and Semi Additive Measure?

13) What are challenges you faced in your project    ?











Round (f2f) 2

1)      Tell  about your project and company?

2)      Can u explain 3’rd  normal form ??

3)      Do you have experience plsql?

4)      Are you ready to work in plsql?

5)      What is challenge you faced in your project?

6)      Have you create index on view?









Round (f2f) 3

1)      Tell about your project ?

2)      Five naming standards in ssis?

3)      What are the challenges  you faced on your project?

4)      How will you explain scenario to your  junior?

5)      Tell about scd’s ?







 Written Test:

1.    Write a query for 2nd highest salary?

2.    What is repayment loan schedule?

3.    Write a query to delete duplicates?

4.    We have two tables Account_master and Account_history, each have a
column cod_acct_no. cod_acct_no has to be there is in account_history but
not in account_mastrer.

5.    Scenario like

Account_Detail

1111      2

1111      1

2222      3

2222      2

2222      1

Output:

1111    2

2222    3



6.            Create a view for all the tables in the schema and view
should start from v_’table_name’.

7.    For the below PL/SQL block. What error may raise



Declare

Cursor C1 is select * from emp where 1=2;

V_emp emp%rowtype;

Begin

Open c1;

Fetch c1 into v_emp;

Loop

<loop statements>

<loop statements>

End loop;

Close c1

End;



i.                Error

          ii.        No data found

         iii.        Successfully works



8.            Write a query  where Sal should be greater than 300

Name Sal

Jack     300

Jill        200

Mick    300

Gen    300



9.            Types of Joins

10.  There will be one billion of data. Each time it has to delete 1000 and
commit. Write a procedure for this.

11.  Explain about your current project.

12.  Write star schema of ur project.

13.  Write a block  to get top ten suppliers  from your star schema .

14.  Performance tuning .



Icreate interview question:-

1.Tell about your self.
2.Rate your self in sql/plsql
3.Scenrio 1
      Src

                     tgt

Zone product_name qty saleprice             count_qty      totsalprice
  tvqty  tvtotsal
North tv                    30   15000                     62
               45000         50      25000
South computer         12   20000
North tv                      20   10000

4.  Scenario 2

I have three table source
cust relational table,sale1 flat file,sal2 flat file
I want all the record from
Customer to target


5.Diff between lookup and joiner
6.How you handle errors
7.Do you know debug how it will use
8.What is reading thread? explain
9.What is dtm? explain
10.   Scenario

I have 30000 records when I ran the mapping it will take more time to load
explain the steps  to rectify.

11.Whether source qualifier active or passive ? explain.
12.Why should we hire you?
13.Do you have any question?

Sunday, 17 January 2016

Regular Expressions

What Are Regular Expressions?
A regular expression is a pattern template you define that a Linux utility Uses to filter text. A Linux utility (such as the sed editor or the gawk program)matches the regular expression pattern against data as that data flows Into the utility. If the data matches the pattern, it’s accepted for processing.
              If the data doesn’t match the pattern, it’s rejected. The regular expression pattern makes use of wildcard characters to represent one or more characters in the data stream.
Types of regular expressions:

There are two popular regular expression engines:
  • The POSIX Basic Regular Expression (BRE) engine
  • The POSIX Extended Regular Expression (ERE) engine
Defining BRE Patterns:
The most basic BRE pattern is matching text characters in a data stream.
Eg 1: Plain text

$ echo "This is a test" | sed -n ’/test/p’
This is a test.
$ echo "This is a test" | sed -n ’/trial/p’
$
$ echo "This is a test" | gawk ’/test/{print $0}’
This is a test.
$ echo "This is a test" | gawk ’/trial/{print $0}’
$
Eg 2: Special characters

The special characters recognized by regular expressions are:
.*[]^${}\+?|()
For example, if you want to search for a dollar sign in your text, just precede it with a backslash character:
$ cat data2
The cost is $4.00
$ sed -n ’/\$/p’ data2
The cost is $4.00
$
Eg 3: Looking for the ending

The dollar sign ($) special character defines the end anchor.

$ echo "This is a good book" | sed -n ’/book$/p’
This is a good book
$ echo "This book is good" | sed -n ’/book$/p’
$
Eg 4: Using ranges

You can use a range of characters within a character class by using the dash symbol.
Now you can simplify the zip code example by specifying a range of digits:
$ sed -n ’/^[0-9][0-9][0-9][0-9][0-9]$/p’ data8
60633
46201
45902
$
Extended Regular Expressions:

The POSIX ERE patterns include a few additional symbols that are used by some Linux applications and utilities. The gawk program recognizes the ERE patterns, but the sed editor doesn’t.
Eg 1: The question mark

The question mark indicates that the preceding character can appear zero or one time, but that’s all. It doesn’t match repeating occurrences of the character:
$ echo "bt" | gawk ’/be?t/{print $0}’
bt
$ echo "bet" | gawk ’/be?t/{print $0}’
Bet
$ echo "beet" | gawk ’/be?t/{print $0}’
$
$ echo "beeet" | gawk ’/be?t/{print $0}’
$
Eg 2: The plus sign

The plus sign indicates that the preceding character can appear one ormore times, but must be present at least once. The pattern doesn’t match if the character is not present:
$ echo "beeet" | gawk ’/be+t/{print $0}’
beeet
$ echo "beet" | gawk ’/be+t/{print $0}’
beet
$ echo "bet" | gawk ’/be+t/{print $0}’
bet
$ echo "bt" | gawk ’/be+t/{print $0}’
$
Eg 3: The pipe symbol

The pipe symbol allows to you to specify two or more patterns that the regular expression engine uses in a logical OR formula when examining the data stream. If any of the patterns match the data stream text, the text passes. If none of the patterns match, the data stream text fails.
The format for using the pipe symbol is:
expr1|expr2|...
Here’s an example of this:
$ echo "The cat is asleep" | gawk ’/cat|dog/{print $0}’
The cat is asleep
$ echo "The dog is asleep" | gawk ’/cat|dog/{print $0}’
The dog is asleep
$ echo "The sheep is asleep" | gawk ’/cat|dog/{print $0}’
$

Eg 4: Grouping expressions

When you group a regular expression pattern, the group is treated like a standard character. You can apply a special character to the group just as you would to a regular character.
For example:
$ echo "Sat" | gawk ’/Sat(urday)?/{print $0}’
Sat
$ echo "Saturday" | gawk ’/Sat(urday)?/{print $0}’
Saturday
$