Posts

Showing posts from July, 2011

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

Image
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…

Summary Functions in ADF Business Component

Image
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

Image
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, …

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
/*****…

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

Image
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