Oracle analytical functions - Part 2

That is part 2 that I will explain purpose of every analytic function I mentioned it in part 1

AVG

Returns a running average.

COUNT 

Returns a running count of all records or by partition.

FIRST

Returns the row ranked first using DENSE_RANK.

Syntax :-

aggregate_function(column_name) KEEP
(DENSE_RANK FIRST ORDER BY <column_name> [<ASC|DESC> NULLS <FIRST|LAST> ])

Thinking in ROWID

SA,
We hear ‘ROWID’ many times from database developers, So we should think about what purpose it serves.

Oracle says, ‘They are the fastest way to access a single row’

ROWID gives us the location of the data for a given row in a table. The datafile – then the position of the row in the block, then the data block in the datafile. 

As you can imagine, this data is UNIQUE. So, each row in your table will have a unique ROWID. 

This is only because the data for 2 rows can’t exist in the same space.

It’s not uncommon for a table to lack any unique columns or PKs or unique indexes. But, if you know the ROWID, then you will have at least 1 unique differentiator for that record.

Oracle analytical functions - Part 1

Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Oracle Virtual Private Database

SA,
What Is Oracle Virtual Private Database?
Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

To implement Oracle Virtual Private Database, you must create a function to generate the dynamic WHERE clause, and a policy to attach this function to the objects that you want to protect.
I will explain that definition using example below.

Summary Functions in ADF Business Component

SA,
I will present how to create summary function (sum, count, min, max and avg)   in ADF BC and clarify how these can be used at Entity level and View level. That is done using Groovy syntax.
I will use scott schema (EMP and DEPT Tables)

Entity Level

We will create example to get employees count in every departments.
First, we need to have association between entities representing master-detail relationship and the destination accessor name is what we are going to use in our groovy

Syntax: <Accessor>.aggregate_function(Groovyexpression)

Oracle DB 11g New Feature ( Virtual Columns )

SA,
Today I will explain the oracle 11g new feature "Virtual Columns"

Introduction
 
In the old version in oracle when we want to use expressions and computations we create database views and If we want create index for that expression we create Function-Based Indexes.
Now oracle db 11g allows us to store that expressions in the tables themselves as virtual columns.

PLSQL Naming Convention

SA,
Today I will clarify how to standardize naming convention at PLSQL that may differ from one to another but I will present my view that I believe it is the most suitable to make your code more readable.
I want the post to be shortly so I will enter the subject directly by summarizing in the following table.

How to install Oracle workflow standalone

The main purpose of this post is how to install oracle standalone workflow repository

Prerequisites

• Download the Companion CD on OTN : Oracle Database 10g Release 2 (10.2.0.1.0) , File : 10201_companion_win32.zip
• Database 10g Installed
  

Steps to install oracle workflow

1- unzip file of companion CD

2-Run the setup.exe file

3-choose the second choice : Oracle Database 10 Products then click on the next button

4-choose the same location and the same oracle home than the database.

Steps to install Oracle Workflow Manager

1-Start the Oracle Universal Installer (already installed). For instance, from the Menu : Start /   All  Programs        Oracle - OraDb10g_Home1 / Oracle Installation Product / Universal Installer.

2-Click next

3-Locate the products.xml of the companion software. Example :<path of companion CD>\10201_companion_win32\stage\products.xml and click next

4-Choose Oracle Database 10 Companion Products (The third one)

5-Then select the two products

6-In the Home Details, specify a new Home in a new Location.

           Name : OraDb10gCompanion_home1 

           Path : C:\oracle\product\10.2.0\OraDb10gCompanion_home1

7-Enter the Workflow Parameters 

   Workflow Schema : owf_mgr

   DbHostName : 127.0.0.1

   Port : 1521 

   SID : ORCL

8-Then fill the workflow user password

9-After the end of the installation, you are normally able to reach this url :

http://hostname:7777/pls/wf/wfa_html.home

10-open the previous link

11-Enter the oracle workflow user and password

Steps to install Oracle standalone Workflow Repository

1-Stop all running OC4J instances

2-From the windows menu : Start/Oracle - OraDb10g_Home1/Configuration and Migration Tools

/Worklow Configuration Assistant 

3-Enter the following parameter

Install Options: select Server Only

Workflow Account: workflow Schema Name (default owf_mgr)

Workflow password: workflow Schema password

SYS Password: SYS password

TNS Connect Descriptor: <hostname>:<port>:<SID>

4-The configuration process can take several minutes. When the process is 
   complete, the Workflow Configuration Assistant displays a message of 
   completion 

Thanks

How to get numbers that its summation is equal to specific result

SA,
Today I will present plsql function that return all available numbers that its summation is equal to specific result with condition that numbers formed of specific count of  digits.

Prerequisites
We will create collection (table of varchar2) to be as output result of function

/*****************************************************************
/*   That type will output of function[table of varchar2]
/****************************************************************/

CREATE OR REPLACE TYPE varchar2_nt AS TABLE OF VARCHAR2 (30);

Function Code

/****************************************************************
/*author           : Mahmoud Ahmed El-sayed
/*Email            : mahmoud_ahmed01@yahoo.com
/*creation date    : 10/07/2011
/*Function Purpose : That function used to get the number from n digit that its
/*                   addition is equal to the input result
/*$parameters      :
/*         in_result      ==> Summation of numbers
/*         in_digit_count ==> count digit
/****************************************************************/

CREATE OR REPLACE FUNCTION sum_digit (
   in_result        NUMBER,
   in_digit_count   PLS_INTEGER
)
   RETURN varchar2_nt
IS
   ret_value   varchar2_nt;
BEGIN
   SELECT numbers.RESULT
   BULK COLLECT INTO ret_value
     FROM (SELECT     LTRIM (SYS_CONNECT_BY_PATH (num, '+'),
                             '+'
                            ) addition_equation,
                      REPLACE (SYS_CONNECT_BY_PATH (num, '.'), '.') RESULT
                 FROM (SELECT     LEVEL - 1 num
                             FROM DUAL
                       CONNECT BY LEVEL <= 10)
                WHERE LEVEL = in_digit_count
           CONNECT BY LEVEL <= in_digit_count) numbers
    WHERE dbms_aw.eval_number (numbers.addition_equation) = in_result;

   RETURN ret_value;
END;

How to make split string separated by specific character

SA,
Today I will present plsql function that do solution for splitting string separated by specific character.
for example If I have string 'year,month,day,hour,minute,second' its elements separated by comma and We want the output to be like the following
year
month
day
hour
minute
second

Three-Valued Logic

SA,
Three-Valued logic mean a  boolean variable have three values( true ,false and intermediate value that isn't false nor true).
You can read in details about that topic at Wikipedia.
But at that article I am focusing in how to use Three-Valued logic in PLSQL and its effecting of code control statements.

Toad Code Template

SA,
A lot of developers waste a long time writing repeated code,So Toad offers feature named "Code Template" that I can use it to overcome this.

To use code template in editor you must press CTRL+SPACE, it will show list of reserved code template as below, Then choose the required template