Move Ahead

Career Advancement

Career advancement encompasses everything you do from the time you begin your career to the time you retire. Advancement in career includes climbing the corporate ladder until you reach the executive suite, doing a great job and being recognized for it, and staying out of trouble at work. A few points here regarding contract staffing to remember during career Advancement

Benefits of Contract Staffing

• Potential for higher earnings: Companies typically pay a premium to get the right contractor, with the right skills, at the right time
• Travel opportunities: With each new assignment comes the chance to work at another location of your choosing, from across town to across the country.
• Skills Portfolio: Every contract assignment offers the opportunity to build your resume by learning new skills, as well as sharpening existing ones.
• Potential for permanent employment: As a contract employee, you'll have the advantage to evaluate the company from the inside to see if the company is right for you
• Work-Life balance: Contract employees have more flexibility to prioritize and balance their personal life and work schedule.
• Access to benefits: Contract employees have access to an array of benefits through our MRINetwork benefit provider.

SMEs from PL/SQL and SSIS has shared few infromation based on the Technical discussion.
Few points for your reference

PL/SQL Interview Questions

1. What is difference between stored procedures and application procedures,stored

     function and application function?

Stored procedures are sub programs stored in the database and can be called & execute multiple times where in an application procedure is the one being used for a particular application same is the way for function

---

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

2.Difference between Procedures and Functions:

a. Functions can be used in a select statement where as procedures cannot
b. Procedure takes both input and output parameters but Functions takes only input parameters
c. Functions cannot return values of type text, ntext, image & timestamps where as procedures can
d. Functions can be used as user defined datatypes in create table but procedures cannot
***Eg:-create table <tablename>(name varchar(10),salary getsal(name))
Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get?s executed and the return
Type is returned as the result set.
 

Variable Declration in PL/SQL

PL/SQL Variables

These are placeholders that store the values that can change through the PL/SQL Block.

The General Syntax to declare a variable is:

variable_name datatype [NOT NULL := value ]; 

• variable_name is the name of the variable.
• datatype is a valid PL/SQL datatype.
• NOT NULL is an optional specification on the variable.
• value or DEFAULT valueis also an optional specification, where you can initialize a variable.
• Each variable declaration is a separate statement and must be terminated by a semicolon.

For example, if you want to store the current salary of an employee, you can use a variable.

DECLARE

salary  number (6);

* “salary” is a variable of datatype number and of length 6.

When a variable is specified as NOT NULL, you must initialize the variable when it is declared.

For example: The below example declares two variables, one of which is a not null.

DECLARE

salary number(4);

dept varchar2(10) NOT NULL := “HR Dept”;

The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below.
1) We can directly assign values to variables.
    The General Syntax is:         

  variable_name:=  value;

2) We can assign values to variables directly from the database columns by using a SELECT.. INTO statement. The General Syntax is:

SELECT column_name INTO variable_name FROM table_name [WHERE condition];

Example: The below program will get the salary of an employee with id '1116' and display it on the screen.

DECLARE 

 var_salary number(6); 

 var_emp_id number(6) = 1116; 

BEGIN

 SELECT salary 

 INTO var_salary 

 FROM employee 

 WHERE emp_id = var_emp_id; 

 dbms_output.put_line(var_salary); 

 dbms_output.put_line('The employee ' 

        || var_emp_id || ' has  salary  ' || var_salary); 

END; 

/

NOTE: The backward slash '/' in the above program indicates to execute the above PL/SQL Block.

Scope of Variables

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

•  Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.

• Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.

For Example: In the below example we are creating two variables in the outer block and assigning thier product to the third variable created in the inner block. The variable 'var_mult' is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block.

1> DECLARE 2>  var_num1 number; 3>  var_num2 number; 4> BEGIN 5>  var_num1 := 100; 6>  var_num2 := 200; 7>  DECLARE 8>   var_mult number; 9>   BEGIN 10>    var_mult := var_num1 * var_num2; 11>   END; 12> END; 13> /

 SSIS  - Interview Questions

Difference between Control flow and Data Flow

Control Flow

• Process is the key:  precedence constraints control the project flow based on task completion, success or failure
• Task 1 needs to complete before task 2 begins
• Smallest unit of the control flow is a task
• Control flow does not move data from task to task
• Tasks are run in series if connected with precedence or in parallel
• Package control flow is made up of containers and tasks connected with precedence constraints to control package flow

Data Flow:

• Streaming
• Unlink control flow, multiple components can process data at the same time
• Smallest unit of the data flow is a component
• Data flows move data, but are also tasks in the control flow, as such, their success or failure effects how your control flow operates
• Data is moved and manipulated through transformations
• Data is passed between each component in the data flow
• Data flow is made up of source(s), transformations, and destinations.

 Checkpoints in SSIS

SQL Server Integration Services (SSIS) packages are designed to import data from different data sources like databases, flat files and FTP locations etc. Some packages may take several hours to execute depending on the implementation. In middle of the package execution, in case the FTP location is not available or the source text file is missing, execution will fail. Most of the times, you need to attend to the relevant issue and re-run the package. This means that package will run from the beginning and which means that you have to wait again for long time. In simple terms, you are wasting your times by re-running the entire package. If you have an option where you can restart the package from the point of failure it will be easier. The Checkpoint feature in SSIS is about restarting package from the point of failure. Configuration The technique behind checkpoint is to save the variables values at the time of failure to a file in a XML format. When it re-run, it reads from the saved file and load the variables and will start from the failed flows. When the package completes successfully, the Checkpoint file is removed; the next time the package runs it starts executing from the beginning since there will be no Checkpoint file. When a package fails, the Checkpoint file remains on disk and can be used the next time the package is executed to restore the values of package variables and restart at the point of failure.  CheckpointFileName is path and name of the file in which you are going to save the check point values. You can have any extension you like. However, it will be better to have a extension such as .xml since the checkpoint file is an XML file. I have also seen that some people are using .CHKP as the extension. The point that I want to stress is that you can have any extension name you want for your checkpoint file name. Also, rather than hard-coding your file name it is better to use some kind of strategy for the file names. The following strategy is the recommended method that I used and it is working fine up to now. 1. Have a common folder like C:\checkpointfiles to save all the package configuration files in your SSIS project. In case you need to restart from the start, you simply need to delete the file in the above folder. 2. Pass the folder name to a variable in the child packages from the parent package by using parent package variable type in the package configuration. We have already discussed how to create package configurations for SSIS packages. By following above two steps, by simply changing the main package variable which contains the value for checkpoint folder, all the locations will be changed. 3. The best way to assign a name for package is to assign the package name to the checkpoint file name as there can be only one checkpoint file for a package. For this you can use an expression in the package properties which will be like following.