Persistent And Transient Objects( SQL/PLSQL )

There are two different types of objects namely

1. Persistent and    2. Transient.

Persistent Objects: are those that are stored in the database [Objects created using abstract data types varrays, nested tables etc.].   These can be used both with SQL commands and also in PL/SQL blocks.  These reside in the data dictionary.  Persistent objects are available to the user until they are deleted explicitly.  They can be implemented as tables, columns or attributes.  

Transient object exists only within the scope of the PL/SQL block. These get automatically de-allocated once they go out of the scope of the PL/SQL block.  Examples of transient objects are PL/SQL variables. 

IN PL/SQL:

create or replace type Type_adds as object

(

Country varchar2(40),

phase_no varchar2(100),

postal varchar2(50),

city varchar2(200),

state varchar2(150),

pin number(10)

);

Type created.

USAGE OF PARAMETERIZED CURSOR (Oracle SQL/PLSQL)

Parameter is passed in cursor in the same way as how a procedure is passed a parameter except that the parameter can only be IN mode

  

The general form of a cursor with parameters is,

cursor cursor_name [(parameter_name datatype, ……..)]

is select_statement;

Pass parameter values to a cursor when the cursor is opened and the query is executed.

Open an explicit cursor several times with a different active set each time.

open cursor_name(parameter_value,……..);

• PASSING PARAMETERS USING DYNAMIC VARIABLES

The following example displays the usage of parameterized cursor, which asks for the value from the user at runtime and passes the parameter value. 

SQL>

declare

cursor c1 (dd number) is select * from emp where deptno =dd;

y number;

begin

    for x in c1(&y)

    loop

    insert into PROD values(x.empno,x.ename);

    end loop;

    end;

/

Enter value for y: 10

old   5: for x in c1(&y)

new   5: for x in c1(10)

PL/SQL procedure successfully completed.

SQL> select * from PROD;

   EMPNO ENAME

--------- ----------

     7782 CLARK

     7839 KING

     7934 MILLER

SQL> truncate table PROD;

Table truncated.

• USE THE CURSOR ALSO FOR A “COMPUTED FIELDS” (ORACLE SQL/PLSQL)

The following example displays the use of computed fields to define a cursor. 

SQL> declare

    y number :=100;

    cursor c1 is select sal+y salary from emp;

    begin

    for x in c1

    loop

    insert into PROD values(x.salary,'in for');

    end loop;

    end;

   /

PL/SQL procedure successfully completed.

SQL> select * from PROD;

   EMPNO ENAME

--------- ----------

      900 in for

     1700 in for

     1350 in for

     3075 in for

     1350 in for

     2950 in for

     2550 in for

Components under Workflow Manager Tool

Here is a concise idea about the Workflow Manager in Informatica.

1.  Workflow: This is top level object and the entire task (process) for data loading has to be defined under the workflow. It is like a Mapping that integrates different kind of tasks as a Unit.

2.  Task: A task is an individual process to perform a very specific activity during data loading. There are 10 different kinds of tasks that can be grouped under a Workflow:

Informatica Stored Procedure Properties

Informatica Stored Procedure

------------------------------------------

Some of the properties of stored procedure transformation in Informatica are listed below

Location Information:

Use system variables $SOURCE or $TARGET or ODBS Data Source Name to define the location of Stored Procedures.

Stored Procedure Type:

• ·         Normal:

In this case it is a Connected Stored procedure that receives values from One or More Transformations and passes the value to Target Table or Other Transformations.

• ·         Source Pre Load:

It is Unconnected Mode and is used to perform Database Level task silently without any return value. This type of Stored Procedure is executed automatically when Data Query is about to start from Source Databases.

• ·         Source Post Load:

Same as Source Pre Load but it is executed automatically when Data Query is completed from source databases and Data Transformation is about to start.

• ·         Target Pre Load:

Same as Source Pre Load but it is executed automatically when Data Loading is about to start after Transformation.

• ·         Target Post Load:

Same as Source Pre Load but it is executed automatically when Data Loading into target Database is completed.

      

 Some of the uses of Unconnected Mode Stored Procedures are:

• To delete all records from Target Database,
• To make backup/copy of target database tables,
• To enable or disable database users before data loading
• To restore data from Backup Tables if data loading fails, etc.

Note: In the case of Unconnected Stored Procedure, we mostly use Procedures since procedures can be defined without any Return Value while Functions must return a value.

Call Text

Write the syntax to call Procedure or Function if the Stored Procedure is defined for other than 'Normal'. In case of PL/SQL procedures or functions, syntax will be:

            <Procedure name> [(<value list>)];

            <Function name> [(<value list>)];

 Note: Stored Procedure must have an Output Port to receive value.

Learn more on creating Stored Procedures Here

 

NO SQL- NOT ONLY SQL

“NoSQL  just means non-relational and not SQL”

NoSQL is a class of database management system that doesn’t use structured query language for data processing. This set of database is useful while working with huge volume of data. NoSQL databases are known for being able to handle high volume data that doesn't fit the relational model, i.e. why companies like Amazon, Facebook, and Google go for it. NoSQL was an answer for all the shortcomings of relational databases. MongoDB (Document Database) is one of examples of NoSQL database.

Relational database where introduced in the late 70s to store data in an organized manner. With the arrival of social networks and more web products the amount of data has become out of control. Relational database were not designed to handle the huge amount of data

Big Data, Big Users, and Cloud Computing are taking the option of NoSQL technology as an alternative to relational databases.Examples are Apache Cassandra,CouchDB,Hadoop & Hbase,MongoDB,StupidDB,BigTable Etc.

Comparison with SQL Systems

1. More flexible, faster and high performance

2. Schema Free, No predefined Schema

3. ACID transaction properties are not needed. ACID (atomic, consistent, integrity, durable) is a set of properties that guarantees that database transactions are processed reliably.

TYPE OF NO SQL DATABASES

1.Column: Each storage block contains data from only one colum

Eg:HBase, Accumulo

2.Document: Stores documents made up of tagged elements

Eg: MongoDB, Couchbase 

3.Key-value : Allow the application to store its data in a schema-less (key, value) pairs

Eg: Dynamo, Riak, Redis, Cache, Project Voldemort 

4.Graph: Example : Neo4J, Allegro, Virtuoso