Sunday 31 March 2013

Packages in Plsql with Examples

Package is a schema object for storing logically related items.Package has mainly two sections:

  • Package Specification(HEADER): Used to declare types, variables, constants, exceptions, cursors, and subprograms
  • Package BODY: Defines the cursors and subprograms declared in the Specification.
Packages can have two or more subprograms with the same name but different set of parameters or different datatypes. This is known as overloading of subprograms

Package Dependencies:
The package header doesn’t depend on the package body and we can change the package body without changing the header.

Referencing Package Contents:
  • Package_name.item_name
  • Package_name.subprogram_name
Package Syntax:
CREATE OR REPLACE PACKAGE package_name
      {IS | AS}
   <collection_type_definition ...>
   <record_type_definition ...>
   <subtype_definition ...>
   <collection_declaration ...>
   <constant_declaration ...>
   <exception_declaration ...>
   <object_declaration ...>
   <record_declaration ...>
   <variable_declaration ...>
   <cursor_spec ...>
   <function_spec ...>
   <procedure_spec ...>
END package_name;

CREATE OR REPLACE PACKAGE BODY package_name
{IS | AS}
   <collection_type_definition ...>
   <record_type_definition ...>
   <subtype_definition ...>
   <collection_declaration ...>
   <constant_declaration ...>
   <exception_declaration ...>
   <object_declaration ...>
   <record_declaration ...>
   <variable_declaration ...>
   <cursor_body ...>
   <function_spec ...>
   <procedure_spec ...>
   <call_spec ...>
BEGIN
   initialization section
    Exception
Exception handling seciton
END package_name;

Example 1:
create or replace package Test_package as
procedure proc_test;
function func_test return varchar2;
end pack1;
/
create or replace package body Test_package as
procedure proc_test is
         begin
         dbms_out.put_line('This is a test Procedure');
         end proc1;

function func_test return varchar2 is
begin
         return('This is a test fucntion);
end func_test;
end proc_test;


Example 2:
create or replace package test_pack as
procedure amount(n number);
procedure proc_emp;
end test_pack;
/
create or replace package body mypack2 as
procedure amount (n number) is
         begin
         dbms_output.put_line('Rs.'||n||'only');
end amount;

procedure proc_emp as
for emprec in c2
loop
exit when c2%rowcount:=5;
dbms_output.put_line(emprec.ename);
dbms_output.put_line(' proc_emp executed');
end loop;

end proc_emp;
end test_pack;
/

Saturday 30 March 2013

The Informatica Power Exchange Change Data Capture


Change Data Capture is the process of identifying the changed records in the source system. In Datawarehouse to maintain the data we mostly have to track the newly inserted or updated records especially when it’s a Incremental load. This can be done using the timestamp of the record (LAST_UPDATE) or through any Version Numbers (VERSION_NUMBER,), Status indicators, Triggers, Log scanners on databases.

The Informatica Power Exchange Change Data Capture maintains the Business by capturing the inserts updates and deletes on the source system. The captured records can be then send to multiple target tables without any intermediates .Something to be noted here is that the Informatica Power Exchange Change Data Capture finds captured changes only on committed records.

Read more on Change Data Capture

Thursday 28 March 2013

Query Rewrite Feature

Query Rewrite can be very useful in Data warehouse where we have tables with huge amounts of data, more than millions. When we run query on these huge tables we might have to join with other tables like date or time and with all these join its going to take a lot of time to fetch the results.

Here we can make use of Query rewrite. Query rewrite will transform our SQL statements in terms of tables or views into a statement accessing one or more materialized views.

Points to be taken care of while using Query Rewrite Feature
  • DBMS_MVIEW.EXPLAIN_REWRITE procedure tells us whether Query rewrite is possible.
  • Query rewrite runs on following queries:
SELECT
CREATE TABLE … AS SELECT
INSERT INTO … SELECT

  • Also, Query Rewrite must be enabled at the session level
Alter session set query_rewrite_enabled = TRUE;
  • A materialized view must be enabled for query rewrite:
Syntax:
CREATE MATERIALIZED VIEW SUM_AMT
ENABLE QUERY REWRITE AS
SELECT ...

Tuesday 19 March 2013

How to Configure Write Back in OBIEE Report?

Follow the steps, screenshots and other referenced posts to configure write back in OBIEE reports.

Table Properties

First, you should choose a report in Answers to be able to writing back to the database.
  1. Go to the Table Component
  2. Click on the write back properties icon.Remark that each column have on this head a letter C and a number (Ex. c1, C2, … )

  1. Fill the template name with for example:SetWriteBackValue. (This name will indicate later the statement to update or insert in the database)

 

Column Properties

  1. Set to “write back” the value integration type of the column you want to be able to update.


Also refer the below mentioned topics to get more knowledge on Write Back.

Related Posts Plugin for WordPress, Blogger...

ShareThis