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)

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

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;

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.