Difference between Store Procedure and Trigger?

Difference between Store Procedure and Trigger
-------------------------------------------------------------
we can call stored procedure explicitly.
but trigger is automatically invoked when the action defined in trigger is done.
Ex: create trigger after Insert on
this trigger invoked after we insert something on that table.
Stored procedure can't be inactive but trigger can be Inactive.
Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.

What is the difference between oracle,sql and sql server?

Difference between oracle,sql and sql server
===================================

Oracle is based on RDBMS.
SQL is Structured Query Language.
SQL Server is another tool for RDBMS provided by MicroSoft.

What is Procedure Overloading in Oracle PL/SQL | PL/SQL Procedure overloading Example

Same Name. Each overloaded version must use the same procedure name.

Different Signature. Each overloaded version must differ from all other overloaded versions in at least one of the following respects:

Number of parameters

Order of the parameters

Data types of the parameters

Number of type parameters (for a generic procedure)

Return type (only for a conversion operator)

Together with the procedure name, the preceding items are collectively called the signature of the procedure. When you call an overloaded procedure, the compiler uses the signature to check that the call correctly matches the definition.

Items Not Part of Signature. You cannot overload a procedure without varying the signature. In particular, you cannot overload a procedure by varying only one or more of the following items:

Procedure modifier keywords, such as Public, Shared, and Static

Parameter or type parameter names

Type parameter constraints (for a generic procedure)

Parameter modifier keywords, such as ByRef and Optional

Whether it returns a value

The data type of the return value (except for a conversion operator)

The items in the preceding list are not part of the signature. Although you cannot use them to differentiate between overloaded versions, you can vary them among overloaded versions that are properly differentiated by their signatures.

Oracle Pl/SQL: What is Overloading of Procedures in Oracle

Two or more procedures/functions are called overloaded when

a) They have the same names
b) Different no of formal parameters defined
c) Formal parameters differ in their datatypes/Subtypes and not in the same family
d) They belong to same subprogram/package/PL SQL Block


They are not overloaded when

a) Any of the above points are not satisfied
b) RETURN datatype alone differs and not the formal parameters

The main purpose of overloading procedures is that, when a PL SQL block is found to do a same operation with different inputs, we names them same and feed different parameters

Statistics in Oracle: DBMS_UTILITY

With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for all the tables, clusters and indexes of a schema.
Code examples
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows =>
1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent
=> 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
Note: It's also possible to analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); command.

Statistics in Oracle:Transfering statistics between database

It can be very handy to use production statistics on your development database, so that you can forecast the optimizer behavior.

You can do this the following way:

1. Create the statistics table.
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME' ,stat_tab => 'STATS_TABLE' , tblspace => 'STATS_TABLESPACE');
Example:
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stat_tab => 'STATS_TABLE');

2. Export statistics to statistics table
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'SYSTEM');

3. Import statistics into the data dictionary.
exec DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA','STATS_TABLE',NULL,'SYSTEM');

4. Drop the statistics table.
exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');

Statistics in Oracle: DBMS_STATS

From Oracle 8i the DBMS_STATS package is the preferred method Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:
Syntax:
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed);

Code examples:

exec
DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
exec
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT'
                              ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT',
estimate_percent => 25);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

Statistics in Oracle: Analyze command

The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA,
and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete) statistics options
ANALYZE table indexName {compute|estimate|delete) statistics options
ANALYZE cluster clusterName {compute|estimate|delete) statistics options


Code examples

ANALYZE table scott compute statistics;
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;

How to gather statistics in Oracle

There are several options to create statistics.
1.Analyze command
2.DBMS_UTILITY.ANALYZE_SCHEMA
3.DBMS_STATS.GATHER_SCHEMA_STATS
4.Transfering statistics between database.

Oracle/PLSQL: To_Char Function

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.



Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g


Examples - Numbers

The following are number examples for the to_char function.

to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021'

JVM crash on a Red Hat machine ( Java 1.4.2 applicationwith ) with a SIGSEGV error. -Xms is 64M and -Xmx is 128M

I'm running a Java 1.4.2 application on a Red Hat machine that causes the JVM to crash with a SIGSEGV error. -Xms is 64M and -Xmx is 128M. I first thought that the perm gen size was too low since it always has a high percentage (98/99%) when the JVM crashes. After reading more about the perm gen, though, I'm not so sure. Am I looking down the right path by thinking it may be the perm gen size being too low or is the percentage so high just because the JVM will resize it dynamically? When it crashes the perm gen's total size is around 10/11 MB and its usually 99% full.

#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
#  SIGSEGV (0xb) at pc=0xb7c70b4c, pid=5342, tid=2810452880
#
# Java VM: Java HotSpot(TM) Client VM (1.4.2_16-b05 mixed mode)
# Problematic frame:
# V  [libjvm.so+0x196b4c]
#


Heap
 def new generation   total 4608K, used 3372K [0xa7900000, 0xa7e00000, 0xa82d0000)
  eden space 4096K,  78% used [0xa7900000, 0xa7c27780, 0xa7d00000)
  from space 512K,  27% used [0xa7d80000, 0xa7da3ad8, 0xa7e00000)
  to   space 512K,   0% used [0xa7d00000, 0xa7d00000, 0xa7d80000)
 tenured generation   total 60544K, used 21376K [0xa82d0000, 0xabdf0000, 0xaf900000)
   the space 60544K,  35% used [0xa82d0000, 0xa97b0210, 0xa97b0400, 0xabdf0000)
 compacting perm gen  total 11264K, used 11159K [0xaf900000, 0xb0400000, 0xb3900000)
   the space 11264K,  99% used [0xaf900000, 0xb03e5c30, 0xb03e5e00, 0xb0400000)
  
  
   ---------------  S Y S T E M  ---------------

OS:Red Hat Enterprise Linux Client release 5 (Tikanga)

uname:Linux 2.6.18-8.1.8.el5 #1 SMP Mon Jun 25 17:06:19 EDT 2007 i686
libc:glibc 2.5 NPTL 2.5
rlimit: STACK 10240k, CORE 0k, NPROC 16370, NOFILE 1024, AS infinity
load average:0.00 503...

CPU:total 2 family 15, cmov, cx8, fxsr, mmx, sse, sse2

Memory: 4k page, physical 1034572k(314260k free), swap 2096472k(2096472k free)

vm_info: Java HotSpot(TM) Client VM (1.4.2_16-b05) for linux-x86, built on Sep 17 2007 00:34:43 by unknown with unknown compiler