Difference between union and union all in Oracle Pl/SQL

UNION is used to select distinct values from two tables.

UNION ALL is used to select all values (including duplicates) from the tables.

Example.

table1 has row (1,'Test')
table2 has row (1,'Test')

Union gives output
1,'Test'

Union all gives output
1,'Test'
1,'Test'

Linux and UNIX Mail Command to send and receive mail

Sending and receiving mail from command line is quite command under UNIX or Linux operating system. You need to use following commands

a) mail: send and receive mail
b) from: print names of those who have sent mail
c) mutt: Mutt is a small but very powerful text based program for reading electronic mail under UNIX operating systems, including support color terminals, MIME, and a threaded sorting mode.
d) pine: Yet another text based program for reading electronic mail under UNIX operating systems. Originally written for inexperienced users, Pine's basic features are generally very easy to use.

How to send mail in UNIX Using the UNIX Mail Program

mail -s "Subject" email@address.com

The above works for me. If I want to include a text file (for example, a "automatic reply" type email) your command would look as follows:
mail -s "Subject" email@address.com > /path/text_file
Try removing the "x" from "mailx" and see if that doesn't work

what is the difference between malloc and calloc

There are two differences. First, is in the number of arguments. Malloc() takes a single argument (memory required in bytes), while calloc() needs two arguments (number of variables to allocate memory, size in bytes of a single variable). Secondly, malloc() does not initialize the memory allocated, while calloc() initializes the allocated memory to ZERO.
Here are more opinions and answers:
  • Calloc(m, n) is essentially equivalent to p = malloc(m*n); memset(p, 0, m*n); The zero fill is all-bits-zero, and does not therefore guarantee useful null pointer values or floating-point zero values. 'Free' is useable to release the memory allocated by malloc or calloc.
  • Malloc(s); returns a pointer for enough storage for an object of s bytes. Calloc(n,s); returns a pointer for enough contiguous storage for n objects, each of s bytes. The storage is all initialized to zeros.
  • Simply, malloc takes a single argument and allocates bytes of memory as per the argument taken during its invocation. Where as calloc takes two aguments, and calculates their product.

Oracle/PLSQL Tutorial: SQLERRM Function

The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:
EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

You could use the SQLERRM function to raise an error as follows:
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:
EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;

Oracle/PLSQL Tutorial: SQLCODE Function

The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:
EXCEPTION
    WHEN exception_name1 THEN
        [statements]
    WHEN exception_name2 THEN
        [statements]
    WHEN exception_name_n THEN
        [statements]
    WHEN OTHERS THEN
        [statements]
END [procedure_name];

In an exception handler, the SQLCODE function returns the numeric code of the exception being handled. (Outside an exception handler, SQLCODE returns 0.)
For an exception that the database raises, the numeric code is the number of the associated Oracle Database error. This number is negative except for the error "no data found", whose numeric code is +100.
For a user-defined exception, the numeric code is either +1 (the default) or the Oracle Database error number associated with the exception by the EXCEPTION_INIT pragma.
A SQL statement cannot invoke SQLCODE. To use the value of SQLCODE in a SQL statement, assign it to a local variable first.
If a function invokes SQLCODE, and you use the RESTRICT_REFERENCES pragma to assert its purity, you cannot specify the constraints WNPS and RNPS.

You could use the SQLCODE function to raise an error as follows:
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:
EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);
      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;


Oracle/PLSQL: Extract Function

In Oracle/PLSQL, the extract function extracts a value from a date or interval value.
The syntax for the extract function is:
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
You can only extract YEAR, MONTH, and DAY from a DATE.
You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.

For example:
extract(YEAR FROM DATE '2003-08-22') would return 2003
extract(MONTH FROM DATE '2003-08-22') would return 8
extract(DAY FROM DATE '2003-08-22') would return 22

Differences between OWB 10.2 and 11.1

The major differences between OWB 10.2 and 11.1 are:
* simplified installation
* new option for connecting to Siebel
* new option for integrating OWB with Oracle MDM
* bug fixes

Unless you need any of the new options or bug fixes, I'd recommend sticking with OWB 10.2 on Oracle 11.1. We have numerous customers doing this. And, when OWB 11.2 comes out, you will be able to directly upgrade to it from OWB 10.2.

More info regarding new features at http://download.oracle.com/docs/cd/B28359_01/owb.111/b31278/whatsnew.htm#CHDIBHGJ
Info about bug fixes at http://download.oracle.com/docs/cd/B28359_01/relnotes.111/b40098/toc.htm

FS - The Input Field Separator Variable in Awk

AWK can be used to parse many system administration files. However, many of these files do not have whitespace as a separator. as an example, the password file uses colons. You can easily change the field separator character to be a colon using the "-F" command line option. The following command will print out accounts that don't have passwords:
awk -F: '{if ($2 == "") print $1 ": no password!"}'

There is a way to do this without the command line option. The variable "FS" can be set like any variable, and has the same function as the "-F" command line option. The following is a script that has the same function as the one above.
#!/bin/awk -f
BEGIN {
 FS=":";
}
{
 if ( $2 == "" ) {
  print $1 ": no password!";
 }
}

Awk Basic Structure-Unix Tutorials

The essential organization of an AWK program follows the form:
pattern { action }
The pattern specifies when the action is performed. Like most UNIX utilities, AWK is line oriented. That is, the pattern specifies a test that is performed with each line read as input. If the condition is true, then the action is taken. The default pattern is something that matches every line. This is the blank or null pattern. Two other important patterns are specified by the keywords "BEGIN" and "END." As you might expect, these two words specify actions to be taken before any lines are read, and after the last line is read. The AWK program below:
BEGIN { print "START" }
      { print         }
END   { print "STOP"  }

adds one line before and one line after the input file. This isn't very useful, but with a simple change, we can make this into a typical AWK program:
BEGIN { print "File\tOwner"," } { print $8, "\t", $3} END { print " - DONE -" }

I'll improve the script in the next sections, but we'll call it "FileOwner." But let's not put it into a script or file yet. I will cover that part in a bit. Hang on and follow with me so you get the flavor of AWK.
The characters "\t" Indicates a tab character so the output lines up on even boundries. The "$8" and "$3" have a meaning similar to a shell script. Instead of the eighth and third argument, they mean the eighth and third field of the input line. You can think of a field as a column, and the action you specify operates on each line or row read in.
There are two differences between AWK and a shell processing the characters within double quotes. AWK understands special characters follow the "\" character like "t". The Bourne and C UNIX shells do not. Also, unlike the shell (and PERL) AWK does not evaluate variables within strings. To explain, the second line could not be written like this:
{print "$8\t$3" }
That example would print "$8 $3." Inside the quotes, the dollar sign is not a special character. Outside, it corresponds to a field. What do I mean by the third and eight field? Consider the Solaris "/usr/bin/ls -l" command, which has eight columns of information. The System V version (Similar to the Linux version), "/usr/5bin/ls -l," has 9 columns. The third column is the owner, and the eighth (or nineth) column in the name of the file. This AWK program can be used to process the output of the "ls -l" command, printing out the filename, then the owner, for each file. I'll show you how.
Update: On a linux system, change "$8" to "$9".
One more point about the use of a dollar sign. In scripting languages like Perl and the various shells, a dollar sign means the word following is the name of the variable. Awk is different. The dollar sign means that we are refering to a field or column in the current line. When switching between Perl and AWK you must remener that "$" has a different meaning. So the following piece of code prints two "fields" to standard out. The first field printed is the number "5", the second is the fifth field (or column) on the input line.
BEGIN { x=5 }
{ print x, $x}