Thursday, April 1, 2010

Oracle Programming with PL/SQL Collections

Oracle Programming with PL/SQL Collections


Introduction

PL/SQL applications typically consist of SQL statements intermixed with procedural logic to process data retrieved from the database. If compiled as a stored procedure, your PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.

Why Collections?

Just about all modern programming languages provide support for collections. A collection can be loosely defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index. Commonly used collection types used in the programming world include arrays, maps, and lists.

Storing elements in a collection can provide a number of advantages. For starters, collections can help to simplify code. If you need to process a number of items of a similar type, storing these items in a collection will allow you to loop through each element with ease, referencing each one by an index. In addition, most languages define collection types that contain built-in methods to operate on the collection. For example, a method might allow you to remove all elements from a collection in a single command.

Probably the biggest advantage a collection can provide is improved application performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database. As I stated earlier, PL/SQL programs are a good place to make expensive SQL calls but that doesn't mean that we shouldn't try to keep those calls to a minimum.

Oracle provides three types of PL/SQL collections: nested tables, varrays, and associative arrays. We will review each of these collection types in turn. But first, let's take a quick look at traditional approaches to collections programming. At its conclusion, this article will offer some suggestions on when to use each of them.

Traditional Approaches

With the release of Oracle 7, Oracle introduced the PL/SQL Table. By using PL/SQL Tables, it was possible to create a collection of items, all of the same type, indexed by an integer.

TYPE book_title_tab IS TABLE OF book.title%TYPE
     INDEX BY BINARY_INTEGER;

The only way to access the elements of a PL/SQL Table was through its numeric index. Still, it was the first construct that gave PL/SQL developers array-like access to data.

PL/SQL tables were often combined with PL/SQL Records. By creating a PL/SQL Record, developers could define a composite type that allowed you to group items of varying type together. Combining PL/SQL Tables and Records together was often referred to as a 'PL/SQL Table of Records'.

--Define a PL/SQL record type representing a book:
TYPE book_rec IS RECORD 
   (title                   book.title%TYPE,
    author                  book.author_last_name%TYPE,
    year_published published_date.%TYPE));
 
--define a PL/SQL table containing entries of type book_rec:
Type book_rec_tab IS TABLE OF book_rec%TYPE
    INDEX BY BINARY_INTEGER;
 
my_book_rec  book_rec%TYPE;
my_book_rec_tab book_rec_tab%TYPE;
...
...
my_book_rec := my_book_rec_tab(5);
find_authors_books(my_book_rec.author);
...
...

In version 8, Oracle introduced two collection types, Nested Tables and Varrays. At this time, the PL/SQL Table was renamed to 'index-by table'. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table of old did. However, the Associative Array does contain some enhanced functionality, as we will see.

Oracle Collections Today

Let's fast forward to today and take a good look at Oracle's three collection types: nested tables, varrays, and associative arrays.

Varrays

The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.

The following statements declare, and then populate, a Varray that will contain 4 elements of the same type as the column genre_name in table book_genre:

DECLARE
    TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
    Fiction_genres genres;
BEGIN
    fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');
END;

We could have declared genres to be of type VARCHAR2(30) because all values here are text. However, in keeping with good Oracle programming practices, you should always prefer to declare variables that are based on table columns with the %TYPE attribute. This allows your code to grow with the database schema. If we were to populate genres with a variable like v_genre (versus a text literal), it would be easy for the column type to change in the database without modifying our code.

All PL/SQL collections contain a number of built-in methods that prove useful when working with them. Table 1 lists these Collection methods.

Table 1. Collection Methods

Method

Action It Performs

COUNT

Returns number of elements in the Collection

EXISTS

Returns Boolean true if element at specified index exists; otherwise, false

EXTEND

Increases size of Collection by 1 or number specified, ie. EXTEND(n)
**Cannot use with Associative Array

FIRST

Navigates to the first element in the Collection

LAST

Navigates to the last element

PRIOR

Navigates to the previous element

NEXT

Navigates to the next element

TRIM

Removes the last element, or the last n elements if a number is specified, ie. TRIM(n)
**Cannot use with Associative Array

DELETE

Removes all elements of a Collection, or the nth element, if a parameter is specified

The following code sample demonstrates how to use a few of these methods. We are using a Varray in the example, but the methods function similarly on all collection types. We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration. This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.

--Add a new genre.
IF adding_new_genre  THEN
 
     --Is this genre id already in the collection?
     IF NOT fiction_genres.EXISTS(v_genre_id)     THEN
       --**Add** another element to the varray.
       fiction_genres.EXTENDS(1);
       fiction_genres(v_genre_id) := v_genre;
   END IF;
    --Display the total # of elements.
    DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is :
                         '||fiction_genres.COUNT();
 
END IF;
...
...
--Remove all entries.
IF deleting_all_genres THEN 
     Fiction_genres.DELETE();
END IF;

The advantage that Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database. For example, you could add the genres type, a Varray, to a DML statement on the library table.

CREATE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
/
CREATE TABLE book_library (
    library_id    NUMBER,
    name          VARCHAR2(30),
    book_genres   genres);
/

When a new library record is added, we can supply values to our genres type, book_genres, by using its constructor:

--Insert a new collection into the column on our book_library table.
INSERT INTO book_library (library_id, name, book_genres)
  VALUES (book_library_seq.NEXTVAL,'Brand New Library',
          Genres('FICTION','NON-FICTION', 'HISTORY',
                 'BUSINESS AND FINANCE'));

The query SELECT name, book_genres from book_library returns us:

NAME                  BOOK_GENRES
--------------------  ---------------------------------------------
Brand New Library     GENRES('FICTION', 'NON-FICTION', 'HISTORY',
                      'BUSINESS AND FINANCE')

Note how the insertion order of elements in book_genres is retained. When a table contains a Varray type, its data is included in-line, with the rest of the table's data. When a Varray datatype is selected from a database table, all elements are retrieved. The Varray is ideal for storing fixed values that will be processed collectively. It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray. If you require your collection to be stored in the database but would like the flexibility to manipulate elements individually, Nested Tables are a better solution.

Nested Table

Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table. Let's rework our earlier example using a Nested Table. First, you declare your type:

CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;
/

The 'IS TABLE OF' syntax was also used when declaring a PL/SQL Table. However, this declaration omits the 'INDEX BY BINARY_INTEGER' clause required by the former type. Note that we have not specified the size of the collection. This is because Nested Tables, unlike the Varray, require no size specification. In other words, they are unbound. Here is a definition for the book_library database table, which now contains a Nested Table column:

CREATE TABLE book_library (
    library_id     NUMBER,
    name           VARCHAR2(30),
    book_genres_tab genres_tab)
    NESTED TABLE book_genres_tab STORE AS genres_table;
/

As stated earlier, a Varray's contents are stored in the same table as the other columns' data (unless the collection is exceedingly large, then Oracle stores it in a BLOB, but still within the same tablespace). With Nested Tables, a seperate database table will store the data. This table is specified following the 'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table will store data for all the Nested Tables on that parent table. These storage tables contain a column called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.

--Insert a record into book_library, with a Nested Table of book genres.
INSERT INTO book_library (library_id, name, book_genres_tab)
  VALUES (book_library_seq.NEXTVAL,'Brand New Library', 
          genres_tab('FICTION','NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'));
/
 
--Declare a nested table type
DECLARE 
  updated_genres_tab genres_tab;
BEGIN
  updated_genres_tab := 
    genres_tab('FICTION','NON-FICTION','HISTORY','BUSINESS AND FINANCE',
               'SCIENCE','PERIODICALS','MULTIMEDIA');
 
  --Update the existing record with a new genres Nested Table.
  UPDATE book_library 
    SET book_genres_tab = updated_genres_tab;
 
END;
/

These examples show an insert and an update to the book_table and are similar to what you might see if you were working with a Varray. Both Nested Tables and Varrays allow you to use SQL to select individual elements from a collection. However, Nested Tables have an advantage over Varrays in that they allow for inserts, updates, and deletes on individual elements. The Varray type does not because Varray data is stored as one single, delimited piece of data within the database.

To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.

--1.)Select all genres from library 'Brand New Library' that are like
     '%FICTION%'.
SELECT column_value FROM TABLE(SELECT book_genres_tab
                               FROM book_library 
                               WHERE name = 'Brand New Library')
   WHERE column_value LIKE '%FICTION%';
/
 
COLUMN_VALUE
------------------------------
FICTION
NON-FICTION
 
--2.)Update entry 'MULTIMEDIA' to a new value.  Only possible with
     a nested table!!
UPDATE TABLE(SELECT book_genres_tab 
             FROM book_library 
             WHERE name = 'Brand New Library')
  SET column_value   = 'MUSIC AND FILM'
  WHERE column_value = 'MULTIMEDIA';
 
 
--3.)Select all book_genre_tab entries for this library.
SELECT column_value FROM TABLE(SELECT book_genres_tab
                               FROM book_library 
                               WHERE name = 'Brand New Library');
 
COLUMN_VALUE
------------------------------
FICTION
NON-FICTION
HISTORY
BUSINESS AND FINANCE
SCIENCE
PERIODICALS
MULTIMEDIA

The first of the preceding three statements simply selects an individual element from the Nested Table, book_genres_tab, in our book_library database table. The second statement performs an update on an individual element, something possible only with Nested Tables. The last query shown selects all Nested Table elements from the parent table. This demonstrates an important feature of the TABLE operator. An ealier query we performed on a database column of type Varray returned a single comma-delimited list of values ('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'). Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.

Associative Array

Earlier, we reviewed the definition of a PL/SQL Table (also know as an index-by table). The statement

TYPE book_title_tab IS TABLE OF book.title%TYPE
    INDEX BY BINARY_INTEGER;
book_titles   book_title_tab;

defines a collection of book titles, accessible by a numeric index. Although it is feasible to locate an element by its numeric index, the limitation to this approach is that the value we have to search by is often not an integer.

SELECT title FROM book;
 
TITLE
------------------------------
A Farewell to Arms
For Whom the Bell Tolls
The Sun Also Rises

Above are values from the title column of the book table. If we needed to remove an entry, given only the book title, we would have to search the entire collection in a somewhat inefficient manner. The following is code illustrates this:

   FOR cursor_column IN  book_titles.FIRST..book_titles.LAST LOOP
       IF book_titles(cursor_column) = 'A Farewell to Arms' THEN
          book_titles.DELETE(cursor_column);
       END IF;
   END LOOP;

With Associative Arrays, it is now possible to index by the title of the book. In fact, there are numerous different indexing options, including by VARCHAR2, using the %TYPE keyword, and more. This is a improvement over indexing everything by an integer then having to shuffle through entries to find what you're looking for. Now, if we want to remove the book A Farewell to Arms, we can use an Associative Array:

DECLARE
   TYPE book_title_tab IS TABLE OF book.title%TYPE
       INDEX BY book.title%TYPE;
   book_titles book_title_tab;
BEGIN
      book_titles.DELETE('A Farewell to Arms');
END;

By using an Associative Array of book.title%TYPE, we accomplish our mission in one line, without the need to loop through the set. The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database. They are strictly for internal use in PL/SQL applications. If this is all you require of a collection, the Associative Array's indexing flexibility make it a good choice.

When to Use What

If you're new to PL/SQL collections, you may have a fair understanding of their mechanics by this point, but are uncertain when to use a particular type. Table 2 summarizes each collection's capabilities.

Figure 2. Collection Capabilities

Has Ability To

Varray

Nested Table

Associative Array

be indexed by non-integer

No

No

Yes

preserve element order

Yes

No

No

be stored in database

Yes

Yes

No

have elements selected indidually in database

Yes

Yes

--

have elements updated indidually in database

Yes

No

--

In addition, the following bullet points can be referred to when deciding what collection best suits a particular solution.

Varray

  • Use to preserve ordered list
  • Use when working with a fixed set, with a known number of entries
  • Use when you need to store in the database and operate on the Collection as a whole

Nested Table

  • Use when working with an unbounded list that needs to increase dynamically
  • Use when you need to store in the database and operate on elements individually

Associative Array

  • Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.

Conclusion

Oracle PL/SQL is not a difficult language to learn. However, like all good programming languages, there are many things we can do to maximize efficiency and minimize complexity. Given PL/SQL's power to interact with the database, it can be tempting to simply to fall into the habit of making excessive database calls to do our work. Collections can help you build simpler, faster Oracle database applications, the goal of every good PL/SQL developer.

If you are a member of the Oracle Technology Network, you can read the following chapter in the PL/SQL User's Guide and Reference for additional information about PL/SQL Collections.


About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.


Question&Answer:
1.

What is really the difference and when you would use each of oracle datatypes:

1. Index-by-tables
2. Nested Tables
3. Varrays

My understanding is that index by tables are for data of same type that is stored in
memory.

For nested tables you can store the variables values in oracle table.

Varrays are same as nested except they are confined to a certain number.

AM I correct?

Would you use a nested table for two tables like a PO table and items table iinstead of
referring to two tables.


ANSwer:
=============

The major difference between:

(index by tables) and (nested tables/varrays)

is that index by tables are only available in PLSQL, nested tables/varrays are avaialable
in both PLSQL *and* SQL.

Index by tables are basically "sparse" arrays that need no allocation. For example:

SQL> declare
2 type array is table of number index by binary_integer;
3 data array;
4 begin
5 data(1000) := 1;
6 end;
7 /

PL/SQL procedure successfully completed.


Here plsql gladly accepts an entry in the 1,000'th slot -- without doing anything else.
There is nothing in slots 1, 2, ... 999 (or 1001 or -1, -2,.... )

That array has allocated space for 1 element in the 1,000th position. Nested
tables/varrays do not behave that way:


SQL> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data(1000) := 1;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5

Here it is saying "you haven't allocated me any space up there...". So, we use the
.extend attribute:

SQL> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1000);
6 data(1000) := 1;
7 end;
8 /

PL/SQL procedure successfully completed.


and we can do so.

Note that we do have to allocate 1,000 entries:

SQL> declare
2 type array is table of number ;
3 data array := array();
4 begin
5 data.extend(1);
6 data(1000) := 1;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 6

the nested table/varray type isn't "sparse" like the index by table.


Day to day, in plsql code, i generally use index by tables exclusively. They are a
little faster, a little more flexible. It is when I need to use the table type in SQL
that I use a nested table (see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061
for an example of what I mean by that)....


There are other differences between varrays and nested tables when you use them for
storage in a database table. For example -- varrays maintain their "order". Nested
tables do not. Varrays are stored in lobs -- nested tables in separate tables. There
are certain DML operations available for nested tables that are not avaialable for
varrays. See

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76976/toc.htm
for more details.

=================================================================================

2.

What is the difference between

type l_array is table of varchar2(256);

type l_array is table of varchar2(256) index by binary integer;

which is faster? When should index by binary integer used?

ANS:

one is a collection, the other a plsql index by table.

declare
type collection_type is table of varchar2(256);
type plsql_table_type is table of carchar2(256) index by binary_integer;

l_coll collection_type;
l_tab plsql_table_type;
begin

One (collection) needs to be "extended" to allocate space, the other does not.

l_coll.extend;
l_coll(1) := 'foo';

l_tab(1) := 'bar';


One (collection) can be initialized easily, the other -- not:

l_coll := collection_type( 'hello', 'world', 'foo', 'bar' );

l_tab(1) := 'hello';
l_tab(2) := 'world';
l_tab(3) := 'foo';
l_tab(4) := 'bar';


Those are the "major" differences -- I find plsql table types generally "easier" to use since they
need not be extended and "contigous" (eg: in order to have l_coll(100) - I must have 1..99
allocated. In order to have l_tab(100) i only need that entry)





Wednesday, March 31, 2010

What are the advantages of view over table in oracle

What is the differences between table, view , synonym and alias in Oracle.

What are advantages and limitations of one over the other.

==============================================================================

Table :
========

Relational Database is composed of tables that contain related data.
Storage Unit Contain Rows and Columns.

View :
========

A view is a virtual table. Every view has a query attached to it.
(The query is a SELECT statement that identifies the columns
and rows of the table(s) the view uses.)

advantages of views
-------------------

1. Views are created from one or more than one table by joins with selected columns.
Hide data complexity. Simplify commands for the user and Store complex queries.

2. Views acts as a layer between user and table.

3. Provide an additional level of table security, by restricting access
to a predetermined set of rows and columns of a table.
(that is not done in tables)

4. Views reduces the effort for writing queries to access specific columns every time.

5. Reports can be created on views.

6. View doesn't contain any data.

7. Modification through a view (e.g. insert update delete) generally
not permitted (complex view).

8. Logical Subset of Tables

9. A VIEW is only a mirror image of table which is used at places where
large access to a table is required.

10.Present the data in a different perspective from that of the base table.



synonyms
================
A synonym is an alias for a table, view, sequence or program unit.
Provide location transparency for tables,views ,sequencies
Provide public access to an object

There are two types of synonyms private and public.
private synonyms

Only its owner can access a private synonym.

public synonym?
Any database user can access a public synonym.
synonyms used
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.


alias
=============
it just like nick name
we can used it in quries when two tables have same column name
and also in big quries we can use alias name
for easy understand and readability.
always use alias name

Tuesday, March 30, 2010

windows commands list

CMD Shell command in Windows XP

ADDUSERS Add or list users to/from a CSV file
ARP Address Resolution Protocol
ASSOC Change file extension associations•
ASSOCIAT One step file association
ATTRIB Change file attributes
b
BOOTCFG Edit Windows boot settings
BROWSTAT Get domain, browser and PDC info
c
CACLS Change file permissions
CALL Call one batch program from another•
CD Change Directory - move to a specific Folder•
CHANGE Change Terminal Server Session properties
CHKDSK Check Disk - check and repair disk problems
CHKNTFS Check the NTFS file system
CHOICE Accept keyboard input to a batch file
CIPHER Encrypt or Decrypt files/folders
CleanMgr Automated cleanup of Temp files, recycle bin
CLEARMEM Clear memory leaks
CLIP Copy STDIN to the Windows clipboard.
CLS Clear the screen•
CLUSTER Windows Clustering
CMD Start a new CMD shell
COLOR Change colors of the CMD window•
COMP Compare the contents of two files or sets of files
COMPACT Compress files or folders on an NTFS partition
COMPRESS Compress individual files on an NTFS partition
CON2PRT Connect or disconnect a Printer
CONVERT Convert a FAT drive to NTFS.
COPY Copy one or more files to another location•
CSCcmd Client-side caching (Offline Files)
CSVDE Import or Export Active Directory data
d
DATE Display or set the date•
DEFRAG Defragment hard drive
DEL Delete one or more files•
DELPROF Delete NT user profiles
DELTREE Delete a folder and all subfolders
DevCon Device Manager Command Line Utility
DIR Display a list of files and folders•
DIRUSE Display disk usage
DISKCOMP Compare the contents of two floppy disks
DISKCOPY Copy the contents of one floppy disk to another
DISKPART Disk Administration
DNSSTAT DNS Statistics
DOSKEY Edit command line, recall commands, and create macros
DSACLs Active Directory ACLs
DSAdd Add items to active directory (user group computer)
DSGet View items in active directory (user group computer)
DSQuery Search for items in active directory (user group computer)
DSMod Modify items in active directory (user group computer)
DSMove Move an Active directory Object
DSRM Remove items from Active Directory
e
ECHO Display message on screen•
ENDLOCAL End localisation of environment changes in a batch file•
ERASE Delete one or more files•
EVENTCREATE Add a message to the Windows event log
EXIT Quit the current script/routine and set an errorlevel•
EXPAND Uncompress files
EXTRACT Uncompress CAB files
f
FC Compare two files
FIND Search for a text string in a file
FINDSTR Search for strings in files
FOR /F Loop command: against a set of files•
FOR /F Loop command: against the results of another command•
FOR Loop command: all options Files, Directory, List•
FORFILES Batch process multiple files
FORMAT Format a disk
FREEDISK Check free disk space (in bytes)
FSUTIL File and Volume utilities
FTP File Transfer Protocol
FTYPE Display or modify file types used in file extension associations•
g
GLOBAL Display membership of global groups
GOTO Direct a batch program to jump to a labelled line•
GPUPDATE Update Group Policy settings
h
HELP Online Help
i
iCACLS Change file and folder permissions
IF Conditionally perform a command•
IFMEMBER Is the current user in an NT Workgroup
IPCONFIG Configure IP
k
KILL Remove a program from memory
l
LABEL Edit a disk label
LOCAL Display membership of local groups
LOGEVENT Write text to the NT event viewer
LOGOFF Log a user off
LOGTIME Log the date and time in a file
m
MAPISEND Send email from the command line
MBSAcli Baseline Security Analyzer.
MEM Display memory usage
MD Create new folders•
MKLINK Create a symbolic link (linkd)
MODE Configure a system device
MORE Display output, one screen at a time
MOUNTVOL Manage a volume mount point
MOVE Move files from one folder to another•
MOVEUSER Move a user from one domain to another
MSG Send a message
MSIEXEC Microsoft Windows Installer
MSINFO Windows NT diagnostics
MSTSC Terminal Server Connection (Remote Desktop Protocol)
MUNGE Find and Replace text within file(s)
MV Copy in-use files
n
NET Manage network resources
NETDOM Domain Manager
NETSH Configure Network Interfaces, Windows Firewall & Remote access
NETSVC Command-line Service Controller
NBTSTAT Display networking statistics (NetBIOS over TCP/IP)
NETSTAT Display networking statistics (TCP/IP)
NOW Display the current Date and Time
NSLOOKUP Name server lookup
NTBACKUP Backup folders to tape
NTRIGHTS Edit user account rights
p
PATH Display or set a search path for executable files•
PATHPING Trace route plus network latency and packet loss
PAUSE Suspend processing of a batch file and display a message•
PERMS Show permissions for a user
PERFMON Performance Monitor
PING Test a network connection
POPD Restore the previous value of the current directory saved by PUSHD•
PORTQRY Display the status of ports and services
POWERCFG Configure power settings
PRINT Print a text file
PRNCNFG Display, configure or rename a printer
PRNMNGR Add, delete, list printers set the default printer
PROMPT Change the command prompt•
PsExec Execute process remotely
PsFile Show files opened remotely
PsGetSid Display the SID of a computer or a user
PsInfo List information about a system
PsKill Kill processes by name or process ID
PsList List detailed information about processes
PsLoggedOn Who's logged on (locally or via resource sharing)
PsLogList Event log records
PsPasswd Change account password
PsService View and control services
PsShutdown Shutdown or reboot a computer
PsSuspend Suspend processes
PUSHD Save and then change the current directory•
q
QGREP Search file(s) for lines that match a given pattern.
r
RASDIAL Manage RAS connections
RASPHONE Manage RAS connections
RECOVER Recover a damaged file from a defective disk.
REG Registry: Read, Set, Export, Delete keys and values
REGEDIT Import or export registry settings
REGSVR32 Register or unregister a DLL
REGINI Change Registry Permissions
REM Record comments (remarks) in a batch file•
REN Rename a file or files•
REPLACE Replace or update one file with another
RD Delete folder(s)•
RMTSHARE Share a folder or a printer
ROBOCOPY Robust File and Folder Copy
ROUTE Manipulate network routing tables
RUNAS Execute a program under a different user account
RUNDLL32 Run a DLL command (add/remove print connections)
s
SC Service Control
SCHTASKS Schedule a command to run at a specific time
SCLIST Display NT Services
SET Display, set, or remove environment variables•
SETLOCAL Control the visibility of environment variables•
SETX Set environment variables permanently
SFC System File Checker
SHARE List or edit a file share or print share
SHIFT Shift the position of replaceable parameters in a batch file•
SHORTCUT Create a windows shortcut (.LNK file)
SHOWGRPS List the NT Workgroups a user has joined
SHOWMBRS List the Users who are members of a Workgroup
SHUTDOWN Shutdown the computer
SLEEP Wait for x seconds
SLMGR Software Licensing Management (Vista/2008)
SOON Schedule a command to run in the near future
SORT Sort input
START Start a program or command in a separate window•
SU Switch User
SUBINACL Edit file and folder Permissions, Ownership and Domain
SUBST Associate a path with a drive letter
SYSTEMINFO List system configuration
t
TASKLIST List running applications and services
TASKKILL Remove a running process from memory
TIME Display or set the system time•
TIMEOUT Delay processing of a batch file
TITLE Set the window title for a CMD.EXE session•
TLIST Task list with full path
TOUCH Change file timestamps
TRACERT Trace route to a remote host
TREE Graphical display of folder structure
TYPE Display the contents of a text file•
u
USRSTAT List domain usernames and last login
v
VER Display version information•
VERIFY Verify that files have been saved•
VOL Display a disk label•
w
WHERE Locate and display files in a directory tree
WHOAMI Output the current UserName and domain
WINDIFF Compare the contents of two files or sets of files
WINMSD Windows system diagnostics
WINMSDP Windows system diagnostics II
WMIC WMI Commands
x
XCACLS Change file and folder permissions
XCOPY Copy files and folders
:: Comment / Remark•

Sunday, March 28, 2010

How to return multiple rows with oracle function

There are three basic ways to return multiple rows by oracle function:
======================================================================

A) use a refcursor as the return type
B) use an object table as the return type
C) use a mulit-delimited string as a return type (clob most likely)

A) use a refcursor as the return type
======================================

1.

CREATE OR REPLACE PACKAGE TYPES
AS
TYPE ref_c IS REF CURSOR;
END;

2.

CREATE OR REPLACE FUNCTION multiple_return
RETURN TYPES.ref_c
AS
l_cursor TYPES.ref_c;
BEGIN
OPEN l_cursor FOR
SELECT *
FROM emp;
RETURN l_cursor;
END;

3.

SELECT multiple_return
FROM DUAL




B)use an object table as the return type
==========================================

1.

CREATE OR REPLACE TYPE num_tab IS TABLE OF NUMBER;


2.

CREATE OR REPLACE PACKAGE pipeline
AS
FUNCTION get_num_row
RETURN num_tab PIPELINED;
END;


3.

CREATE OR REPLACE PACKAGE BODY pipeline
AS
FUNCTION get_num_row
RETURN num_tab PIPELINED
IS
BEGIN
FOR i IN 1 .. 20
LOOP
PIPE ROW (i);
END LOOP;

RETURN;
END get_num_row;
END;



4.

SELECT * FROM TABLE (pipeline.get_num_row);

why Perl.exe consume most of CPU% in oracle

Perl.exe consume most of CPU% in oracle

Some Questions:


Questions 1:-
=====================

when i open the oracle this perl.exe occupay all my cpu .
and then my PC is going to very very very slow .
plz i want know what to do now ..


Questions 2:-
=====================

After starting dbconsole (emctl start dbconsole) (Latest Oracle on a
Win2KSP4 box) and waiting a while two perl.exe processes come up and
start eating all the cpu and memory on the machine. I searched Metalink
and found that one possible cause may be the lack of a C: drive on the
host. This is true for me, so I mapped C: to a network drive (Metalink
suggested to install an USB drive) but the problem did not go away.

Any ideas?

Question 3 :-
====================

I installed 11g 64 bit on another win2003 64 bit machine,
did not patch this time. so the it's a clean plain vanilla version of 11g.
Perl.exe processes are still appearing and ramping up to G of memory
when the dbconsole is started.
It seems the perl.exe process hangs on this script
sysman/admin/scripts/lsnrresp.pl

I tested the listener and it is up and works perfectly,
I can connect to the machine remotely etc.

any ideas to help me progress on this?


===========================
Why run perl.exe in oracle
=========================

These scripts are part of the Enterprise Manager monitoring,
they are invoked by default. Dbresp.pl checks for user response time.
These perl scripts are internals .


Check metalink Note:419668.1,
it is one of the most common reason EM consume 100% CPU
and resolution/workaround is given in above ML note.



=======================================
Solutions
=======================================

solution:- 1
==================
I have stopped the enterprise manager and the perl script is
not running anymore

cmd> emctl stop dbconsole ;


solution:- 2
===================
I searched Metalink
and found that one possible cause may be the lack of a C: drive on the
host. This is true for me, so I mapped C: to a network drive (Metalink
suggested to install an USB drive) but the problem did not go away.
Came to work today, installed a USB drive to get a proper C: drive (the
mapped one does not suffice) and it solved the problem.


solution:- 3
====================
Turns out the machine had no C: drive, and some perl scripts
refer specifically to c:, had to edit a few perl files changing any
reference from c: to e:
all ok now.


solution:- 4
====================
I have had good luck with similar problems by reducing the DB_FILE_MULTIBLOCK_READ_COUNT.
Oracle used to say in its docs that MULTIBLOCK for OLTP should be between 4 and 16.




you must identified if there are other operating system proceess that
consuming hight porcent of resources, if you enviroment is Unix, check this command

$ top

$ ps -aef -o pcpu= -o pid= -o time= -o vsz= -o user= -o args=

If you enviroment is Windows,
you must check the Task Manager.



=============================================================

How to Recreate Enterprise Manager EM in oralce

How to Drop Enterprise Manager (EM).
=======================================

CMD> emca -deconfig dbcontrol db -repos drop

CMD> emctl status agent

CMD> emctl start dbconsole

CMD> NET HELPMSG 2185


How to Recreate Enterprise Manager (EM).
========================================

CMD> emca -repos create

CMD> emca -config dbcontrol db

Then log in:

http://localhost:5500/em