Monday, March 31, 2008

Good Topics

Dictionary managed tablespaces
Extents are allocated according to the following storage parameters
initial
next
pctincrease
The information about used and free extents is stored in the dictionary.
Locally managed tablespaces
A 'bitmap' is stored within the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free or used.
The extent sizes are either uniform or autoallocate. Hence, the following storage parameters don't make sense and are not permitted:
next
pctincrease
minextents
maxextents
default storage
Locally managed tablespaces have two advantages: recursive space management is avoided and adjacent free space is automatically coalesced.

REF CURSOR Overview
A cursor variable is a cursor that actually contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable.
A cursor variable is not tied to a single particular query like a static cursor. The same cursor variable may be opened a number of times with OPEN FOR statements containing different queries. Each time, a new result set is created from that query and made available via the cursor variable.
REF CURSOR types may be passed as parameters to or from stored procedures and functions. The return type of a function may also be a REF CURSOR type. This provides the capability to modularize the operations on a cursor into separate programs by passing a cursor variable between programs.
We can define ref cursor with SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR.
Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different. The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types.
The following is the syntax for declaring a SYS_REFCURSOR cursor variable: name SYS_REFCURSOR;
DECLARE
emp_refcur SYS_REFCURSOR;
You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:
1. Create a referenced cursor TYPE
2. Declare the actual cursor variable based on that TYPE
The syntax for creating a user defined REF CURSOR type is as follows: TYPE cursor_type_name IS REF CURSOR [RETURN return_type];
The following is an example of a cursor variable declaration. DECLARE
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
my_rec emp_cur_type;
CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno emp.deptno%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno ' ' v_ename);
END LOOP;
CLOSE emp_refcur;
END;

A VARRAY is a collection with maximum limit set. A VARRAY can hold any defined datatype. A VARRAY is a dense collection, meaning that you can only add or remove objects from the end. You define a VARRAY first as a type, and then define a variable that is the actual collection.
SQL> declare 2 type auth_var is varray(10) of 3 author.author_last_name%type; 4 a_auth auth_var := auth_var(); --Initilization 5 begin 6 a_auth.extend; 7 a_auth(1) := 'Arpit'; 8 a_auth.extend; 9 a_auth(a_auth.last) := 'Nirav';
10 a_auth.extend; 11 a_auth(a_auth.last) := 'Amit'; 10 dbms_output.put_line( a_auth(1) ' and ' a_auth(2)); 11 end; /
A VARRAY can not be used until it is initialized. In line 4 the auth_var() function actually does the initialization.
Once the array is initialized you can extend it and add elements, which we do in lines 6 through 9. We access each element using the VARRAY variable and the index number. When the PL/SQL block ends (or the array variable goes out of scope), the memory used by the array is recovered automatically by the PL/SQL engine. Unlike a cursor, you do not close a collection.




10g New Features:
Provides support for regular expressions as in Unix/Perl
Ability to receive warning when compiling the code. We can get warnings using parameter plsql_warning or DBMS_WARNING package.
BINARY_FLOAT / BINARY_DOUBLE (Native floating point data types and is alternative to NUMBER datatype)
DBMS_LOB offers support of large LOBs (between 8 and 12 terrabytes)
Handling of quotes in string (') can be done using q'! <text with quotes...>!'
ErWin

When you create a relationship between entities, ERwin automatically migrates the primary key attribute(s) of the parent entity, to the child entity. The designator (FK) indicates the name of the migrated key attribute, also known as a foreign key. This migration occurs automatically in the physical model.
In an identifying relationship (displayed using solid line), the FK migrates above the line and becomes part of the primary key of the child entity. In a non-identifying relationship (displayed using dotted line), the foreign key migrates below the line and becomes a non-key attribute in the child entity. In ERwin, a dashed line represents a non-identifying relationship.


Data warehousing questions
What is a Star Schema?
Star schema is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

What is Snow Flake Schema?
Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.

Differences between star and snowflake schema?
Star schema - A single fact table with N number of Dimension, all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results.
Snow schema - Any dimensions with extended dimensions are know as snowflake schema, dimensions maybe interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results.

What is Difference between ER Modeling and Dimensional Modeling?
ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is degenerate dimension table?
If a table contains the values, which are neither dimension nor measures is called degenerate dimensions.

Why is Data Modeling Important?
Data modeling is probably the most labor intensive and time consuming part of the development process. The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users.In computer science, data modeling is the process of creating a data model by applying a data model theory to create a data model instance. A data model theory is a formal data model description. When data modelling, we are structuring and organizing data. These data structures are then typically implemented in a database management system. In addition to defining and organizing the data, data modeling will impose (implicitly or explicitly) constraints or limitations on the data placed within the structure.Managing large quantities of structured and unstructured data is a primary function of information systems. Data models describe structured data for storage in data management systems such as relational databases. They typically do not describe unstructured data, such as word processing documents, email messages, pictures, digital audio, and video. (Reference : Wikipedia)

Friday, March 28, 2008

Query Examples [Delete Duplicate, Top-N Analysis, Alternate Records]

SQL Query to delete duplicate records

delete from t2 a
where a.rowid > (select min(rowid)
from t2 b
where a.c1 = b.c1)

delete from t2 a
where a.rowid not in (select max(rowid)
from t2 b
where a.c1 = b.c1)

Query to select nth highest row in the table:
1] select a.sal
from emp a
where (&n-1) = (select count(distinct(b.sal))
from emp b
where b.sal > a.sal)
Oracle 10g new feature : Handling of quotes in string
insert into emp
values (1234, q'!Arpit'sdfs!', 'Engg', 7902, '24-feb-1968',2334, 12 , NULL)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
2] SELECT * FROM ( SELECT deptno, ename, sal, DENSE_RANK() OVER ( PARTITION BY deptno ORDER BY sal desc ) TopN FROM emp)
WHERE TopN <= 3ORDER BY deptno, sal DESC/

3] SELECT * FROM ( SELECT deptno, ename, sal, ROW_NUMBER() OVER ( PARTITION BY deptno ORDER BY sal DESC ) Top3 FROM emp)
WHERE Top3 <= 3 ;

[Gives only first 3. Duplicates r counted as 2]

Query to select alternate records from table

SELECT *
FROM emp
WHERE (rowid,1) IN (SELECT rowid,mod(rownum,2) FROM emp);

SELECT * FROM ( SELECT rownum r, emp.* FROM emp ) temp WHERE MOD(temp.r,2) = 1 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1234 Arpit'sdfs Engg 7902 24-FEB-68 2334 12
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

Wednesday, March 12, 2008

SQL Report in CSV format with column heading


In SQL Reports, if you use "set pages 0" then it will remove heading from the report. The solution is to
either use "set pages 99999", which will create page break after specified number of pages or to use "set emb on pages 0 newp 0"

Report::::

set feedback off;
set heading on;
set pages 9999;
set lines 365;
set underline off;
set colsep ',';
spool c:\try.csv
select empno as emp_no from emp ;.
spool off;

Monday, March 10, 2008

To convert Oracle forms 3.0 inp to fmb (Oracle 4.5/ 6i)




To convert forms 3.0 inp to fmb

Open Start > Programs > Oracle Developer Suite > Forms Developer > forms Compiler

1. Select inp file
2. Give uid,pwd, db
3. Tick Upgrade 3.0 Forms or 5.0 Menu to current release.
4. Tick Widen Field Display length by 1 when upgrading
5. Uncheck Build a runform/runmenu file when upgrading.
6. Version to upgrade : 30
7. Tick Compile all PL/SQL code
8. Tick Strip PL/SQL source from library.
9. Upgrade PL/SQL from V1 to V2.
10. Write output to File :: Give output file name and path
11. Tick Add key-up and down triggers when upgrading.



Unlimited freedom, unlimited storage. Get it now

Developer 2000 installation configuration



After installing D2K forms 6, you are required to configure tnsnames.ora
file.
Location of tnsnames.ora file.

C:\DevSuiteHome\network\admin

C:\devsuitehome is the folder path in which u have installed oracle forms.

OPEN tnsnames.ora.

Add following lines in it.

As a host you required to give ip or pc name of pc in which oracle
database is installed to which u want to connect.
Port is port at which oracle db listener is installed. It will be 1521
fixed in most of the cases so dont need to change.

And service name is name of SID of Oracle database.

XYZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.6.0.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ABCD)
)
)

After adding this lines in tnsnames.ora file you are ready to use D2K.

In the Connect dialog box give user name , password, and database which
the one you have specified in tnsnames.ora. In above case it will be XYZ.



Get the freedom to save as many mails as you wish. Click here to know how.

Session id of current user who is using Oracle ias form or sqlplus



Oracle fuction to know session id of current user who is using either
ias form or sqlplus.

SELECT 'SID='TO_CHAR(SID)' SERIAL#='TO_CHAR(SERIAL#)'
AUDSID='sys_context('USERENV','SESSIONID')
' OSUSER='OSUSER' MACHINE='MACHINE' 'NVL(TERMINAL,'')
INTO :session_info
FROM V$SESSION
WHERE AUDSID= sys_context('USERENV','SESSIONID');

select sys_context('USERENV','SESSIONID') from dual;

Using sys_context('USERENV','SESSIONID') this function you
will get audsid of current user and then
by using this id on V$session table
you will get detail like sid,session#,
machine through which user is using sqlplus.








Chat on a cool, new interface. No download required. Click here.

Converting Number to Text using Oracle SQL Explained

Query:
select to_char(to_date('456','J'),'Jsp') --replace 456 with your number or variablefrom dual
Output:
Four Hundred Fifty-Six

How it works:
Step 1:

Following query will convert your number to date format. As this is number we need to specify 'J'ulian format.

Query:
select to_date('456','J') --replace 456 with your number or variablefrom emp
Output:
01-APR-11

Step 2:
Following query will convert your number (date in 'J'ulian format) to text. If you write 'sp' after any format, the format will be spelled. So the number 456 will be converted to text as "Four Hundred Fifty-Six "

Query:
select to_char(to_date('456','J'),'Jsp') --replace 456 with your number or variablefrom dual
Output:
Four Hundred Fifty-Six

Sunday, March 9, 2008

US Elcetions Explained

In US there are only two parties, namely Democratic and Republican. US presidential election occurs after every four years. In US Congress consist of two parts. House of Representatives and Senators. There are two senators from every state where as number of house of representatives will depend on population of particular state. Currently total senators are 100 and House of representatives are 435. And 3 members from Washington D.C. . So in total there are 538 members of House of Representatives.
Before more than 1 year of election both the parties will start searching for suitable candidate for presidential election. In US presidential candidate will also be chosen by people. For that in every state primary or caucus are organised. Primary or caucus are almost same as election. But they will be held by state government and one for each party. People who are loyal to particular party will participate in Primary/Caucus for that party. In primary people will caste there vote and based on majority, the candidate will be selected. In caucus people marketing for particular candidate will discuss about the candidates with other people in group. The candidate, whose group are smaller will have to merge into other groups. And finally there will be elections between two groups.

Use of truss and lsof in Unix

First command is truss.
The truss utility executes the specified command and produces a trace of the system calls it performs, the signals it receives, and the machine faults it incurs.
Each line of the trace output reports either the fault or signal name or the system call name with its arguments and return value(s).The command can be used as::: truss ./abc [abc is executable program]On following link you can find examples of the same.
http://www.ibm.com/developerworks/aix/library/au-unix-tracingapps.html
Another useful command is lsof.
lsof lists all the open files used by all the programs.
An open file may be a regular file, a directory, a block special file, a character special file, an executing text reference, a library, a stream or a network file.lsof can also be used to retrieve accidently deleted files, if they are currently being used by any process.On following link you can find examples of the same.
http://www.ibm.com/developerworks/aix/library/au-lsof.html?S_TACT=105AGX20&S_CMP=EDU