Hello World Oracle Procedure.

Creating a Precedure.

CREATE OR REPLACE PROCEDURE skeleton (my_name IN varchar, last_name IN varchar )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(last_name ||' ' || my_name);
END;

Calling a Precedure.

SET SERVEROUTPUT ON
/
EXECUTE SKELETON('Fawad', 'Nazir')
/

Checking For Table Dependencies in Oracle.

The following query shows all the tables that have Column names as XXXXX and YYYYYY and the Schema owner is DB1.

SELECT *

FROM ALL_TAB_COLUMNS

WHERE COLUMN_NAME IN ('XXXXX','YYYYYY')

AND OWNER = 'DB1'

;

Getting the Current Schema Name in Oracle...

SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
INTO V_OWNER
FROM DUAL;

Do we have Double in Oracle?

Yes, Use this:

number(p,s)

Where p is the precision and s is the scale.

For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.

alter table
_TABLE_NAME_
modify
(
VAR_LIMIT NUMBER(38,127),
EAR_LIMIT NUMBER(38,127)
);

ERD and UML for Eclipse...

http://amateras.sourceforge.jp/cgi-bin/fswiki_en/wiki.cgi?page=EclipseHTMLEditor

Amateras provides both UML (AmaterasUML) and ERD (AmaterasERD).

Both require installing GEF First.

Installing GEF: https://publib.boulder.ibm.com/infocenter/cchelp/v7r1m0/index.jsp?topic=/com.ibm.rational.clearcase.cc_ms_install.doc/topics/t_install_gef.htm

Installing Graphical Editing Framework (GEF)

Rational ClearCase Remote Client for Eclipse requires that GEF be installed in your Eclipse environment. If you do not have GEF, you must install it before installing the Rational ClearCase Remote Client for Eclipse.

Before you begin

About this task

If your environment already has GEF installed, you do not need to perform this task. To check if it is installed, start Eclipse and click Help > About Eclipse, then click the Feature Details button. If there is an entry with "Graphical Editing Framework" in the Feature Name field and "org.eclipse.gef" in the Feature ID field, then you have GEF installed in your Eclipse environment.

Note: You must be logged in as a local Administrator with sufficient privileges for updating your installation of Eclipse or the IBM Rational Software Delivery Platform.

Download the GEF Runtime feature that corresponds to your version of Eclipse from the Eclipse Web site (http://www.eclipse.org) and unzip it into your Eclipse directory, or follow these steps to install GEF with the Eclipse Update Manager:

Procedure

• Installing GEF with Eclipse 3.2.x and 3.3.x
  1. On the Eclipse main menu, click Help > Software Updates > Find and Install.
  2. On the Feature Updates page, select Search for new features to install and then click Next
  3. On the Update sites to visit page, select Callisto Discovery Site (for Eclipse 3.2) or Europa Discovery Site (for Eclipse 3.3) and expand the option to see a list of available features.
  4. Select Graphical Editing Framework from the Graphical Editors and Frameworks section of the Callisto Discovery Site for Eclipse 3.2 or Graphical Editing Framework from the Graphical Editors and Frameworks section of the Europa Discovery Site for Eclipse 3.3 and then click Next. (Note that you should choose a version of GEF that corresponds with the version of Eclipse that you are using.)
  5. On the Search Results page, select Graphical Editing Framework and then click Next.
  6. On the Feature License page, select I accept the terms in the license agreement... and then click Next. If you do not accept the terms, select Cancel to end the installation.
  7. On the Install Location page, select the Eclipse sites where you want to install the feature and then click Finish.
• Installing GEF with Eclipse 3.4
  1. On the Eclipse main menu, click Help > Software Updates.
  2. On the Available Software page, select Ganymede Update Site and expand the option to see a list of available features.
  3. Expand the Graphical Editors and Frameworks section and select the Graphical Editing Framework version that corresponds with the version of Eclipse that you are using.
  4. Click Install.
  5. On the Install page, click Next.
  6. On the Review Licenses page, read the license agreement carefully. If you accept the terms, select I accept the terms in the license agreement... and then click Finish. If you do not accept the terms, select Cancel to end the installation.
  7. When the installation completes, you are prompted to restart Eclipse or apply changes. Click Yes to restart Eclipse.
• Installing GEF with Eclipse 3.5
  1. On the Eclipse main menu, click Help > Install New Software.
  2. On the Available Software page, select Galileo - http://download.eclipse.org/releases/galileo from the Work with pull down menu, expand the Modeling option, select the Graphical Modeling Framework SDK option, and click Next.
  3. On the Install Details page, verify that Graphical Modeling Framework SDK appears as an item to be installed and click Next.
  4. On the Review Licenses page, read the license agreement carefully. If you accept the terms, select I accept the terms in the license agreements and then click Finish. If you do not accept the terms, select Cancel to end the installation.
  5. When the installation completes, you are prompted to restart Eclipse or apply changes. Click Yes to restart Eclipse.

Once this is done. Now its easy to install both UML (AmaterasUML) and ERD (AmaterasERD).

For AmaterasUML, just get the latest jars and put in ECLIPSE_HOME/plugins.

For AmaterasERD, the latest jar which is net.java.amateras.db_1.0.7.1.jar does not work for me, so I tried net.java.amateras.db_1.0.6.jar and it worked very well.

BTW, I have eclipse latest version which is 3.5 and the above jar also worked for Eclipse version 3.3.

Subversion Plugin for Eclipse

SubClipse...

http://subclipse.tigris.org/

http://www.eclipse.org/subversive/

Data Versioning Methods : Using COALESCE, Union All, Not Exists

Data Versioning Methods

Method: 1 (Joins & UNION ALL)

select * from
(
(select fawad_id, fawad_value from
(select fawad_id, fawad_value, nazir_id, nazir_value from fawad left outer join nazir on fawad_id = nazir_id)
where nazir_id is NULL)
UNION
(select nazir_id, nazir_value from
(select fawad_id, fawad_value, nazir_id, nazir_value from fawad right outer join nazir on fawad_id = nazir_id)
where fawad_id is NULL)
UNION
(select nazir_id, nazir_value from
(select fawad_id, fawad_value, nazir_id, nazir_value from fawad full outer join nazir on fawad_id = nazir_id)
where fawad_id is not NULL and nazir_id is not NULL)
)
order by fawad_id;

Method: 2 ( UNION ALL & NOT Exists)

select * from
(
select nazir_id, nazir_value from nazir
UNION
select fawad_id, fawad_value from fawad where not exists (select 1 from nazir where fawad.fawad_id = nazir.nazir_id)
)
order by nazir_id;

Method: 3 (Using COALESCE)

select COALESCE(nazir_id, fawad_id) as ID, COALESCE(nazir_value, fawad_value) as VALUE from fawad full outer join nazir on fawad.fawad_id = nazir.nazir_id order by ID;

Reviweing Joins

Reviweing Joins:

create table Fawad(
Fawad_ID INTEGER NOT NULL,
Fawad_value INTEGER NOT NULL
)

create table Nazir(
Nazir_ID INTEGER NOT NULL,
Nazir_value INTEGER NOT NULL
)

insert into fawad(Fawad_ID, FAWAD_VALUE) Values(1,100);
insert into fawad(Fawad_ID, FAWAD_VALUE) Values(2,200);
insert into fawad(Fawad_ID, FAWAD_VALUE) Values(3,300);

insert into Nazir(Nazir_ID, Nazir_VALUE) Values(3,100);
insert into Nazir(Nazir_ID, Nazir_VALUE) Values(4,200);
insert into Nazir(Nazir_ID, Nazir_VALUE) Values(5,300);

commit;

select * from fawad,nazir; -- product
select * from fawad join nazir on fawad.fawad_id = nazir.nazir_id; -- join
select * from fawad inner join nazir on fawad.fawad_id = nazir.nazir_id; -- inner join
select * from fawad left outer join nazir on fawad.fawad_id = nazir.nazir_id; -- left outer join
select * from fawad right outer join nazir on fawad.fawad_id = nazir.nazir_id; -- right outer join
select * from fawad full outer join nazir on fawad.fawad_id = nazir.nazir_id; -- full outer join

drop table fawad;
drop table nazir;

Difference between YYYY and RRRR format...

http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/difference-between-yyyy-and-rrrr-format-519525

Oracle has always stored dates in four digits, regardless of the
NLS_DATE_FORMAT setting in the parameter file (init.ora). Having said that,
it is still essential to understand where problems may arise! It doesn=92t
matter whether you use =91YYYY=92 or =91RRRR=92 for the TO_CHAR function (character
representation of a date), as this function doesn=92t actually convert the
date. You should instead use =91RRRR=92 for the TO_DATE function, which does
convert dates to a Y2K-compliant format.

So what=92s the difference between YY, YYYY, RR, and RRRR? YY
allows you to retrieve just two digits of a year, for example, the 99 in
1999. The other digits (19) are automatically assigned to the current
century. RR converts two-digit years into four-digit years by rounding.
50-99 are stored as 1950-1999, and dates ending in 00-49 are stored as
2000-2049. RRRR accepts a four-digit input (although not required), and
converts two-digit dates as RR does. YYYY accepts 4-digit inputs but doesn=92t
do any date converting

Essentially, the differences between using RR, RRRR, YY, and YYYY come down
to differences in how an Oracle database stores and displays values. As an
example, consider the following scenario. A simple SELECT statement of the
date column is done without the use of the TO_DATE (or TO_CHAR) function.

USING
ENTERED
STORED
SELECT of date column


YY
22-FEB-01
22-FEB-1901
22-FEB-01


YYYY
22-FEB-01
22-FEB-0001
22-FEB-0001


RR
22-FEB-01
22-FEB-2001
22-FEB-01


RRRR
22-FEB-01
22-FEB-2001
22-FEB-2001



If you have dates which need to be made Y2K compliant, use the TO_DATE
function and DD-MM-RRRR:

UPDATE ORDER

SET order_date =3D TO_DATE (order_date, =91DD-MON-RRRR=92);

There are instances, however, in which RR or RRRR won=92t convert dates to the
Year 2000. For example, if the original dates were entered as four digits
then the TO_DATE function with RR (or RRRR) will not change that value to a
Year 2000 date. For solutions to related scenarios, Oracle has provided a
document, "Oracle Products and Year 2000 Compliance", on their web site,
www.oracle.com/year2000.

Oracle: Inforcing Date Format ...

Sometime we can have a problem with the date format in Oracle DATE column. In this case i was having a problem that Java was not able to read the DATE properly.

Below is the scripts that inforces the correct format in the DB.

UPDATE TABLE_NAME
set DATE_Column = TO_DATE ( DATE_Column, 'DD/MM/RRRR' )

RRRR: Here enforces the Year to be four digit.

Oracle: Command to change the format of the DATE Column.

ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

Shell Scripting Example...

First line of a Shell Script:
#!/bin/bash

Initializing Variables (Note no spaces):
SUCCESS=0
FAILURE=1

Reading a conf file (Format LOG_FILE_PATH=/home/logs/xyz):
CONF_FILE_PATH=batch_jobs.conf
LOG_FILE_PATH=$(cat ${CONF_FILE_PATH} | grep LOG_FILE_PATH= | gawk -F = '/./ {print $2}')

Functions (Example function to check if java command exists):

function init
{
echo "Initialising Import Script"

JAVA_EXECUTABLE_LOCATION=$(which ${JAVA_EXECUTABLE})
if [[ -z "${JAVA_EXECUTABLE_LOCATION}" ]] ; then
echo "Unable to execute import script as the import executable [${JAVA_EXECUTABLE}] is not on the path"
exit ${FAILURE}
fi

}

Function: (Execute java command)

App_AAA_BBB_args=$(cat ${CONF_FILE_PATH} | grep App_Align_Sequences_args= | gawk -F = '/./ {print $2}')
App_XXX_YYYY_jar=$(cat ${CONF_FILE_PATH} | grep App_Align_Sequences_jar= | gawk -F = '/./ {print $2}')

function runAppXXXYYYYY
{
echo "java -Xmx4g -jar ${App_XXX_YYYY_jar} ${App_AAA_BBB_args} 2>&1 | tee -a ${LOG_FILE_PATH}/App_App_XXX_YYYY.log"
java -Xmx4g -jar ${App_XXX_YYYY_jar} ${App_AAA_BBB_args} 2>&1 | tee ${LOG_FILE_PATH}/App_XXX_YYYY.log
}

Calling Functions:

init

runAppYYYYXXXX #function name


exit ${SUCCESS}

Linux command to redirect data to a file and also show it in standard output screen...

$cat main.conf 2>&1 | tee error.txt

Enjoy :).

Linux command to print directory tree in Linux.

$tree

Enjoy :)

Create a jar file with .class file and properties files.

1. You need a menifest file:

App_Data_Export.mf

its content should look like this:

Manifest-Version: 1.0
Main-Class: App_Data_Export
Class-Path: . jars/commons-cli-1.0.jar jars/commons-lang-2.4.jar jars/commons-logging-1.1.1.jar jars/jdbcpersistence.jar jars/log4j-1.2.8.jar conf/test/.

all the paths are relative to the jar file being created and conf/test folder contains all the properties file.

2. Create a jar file.
$jar cmf App_Data_Export.mf App_Data_Export.jar App_Data_Export.class

3. To run the jar.
java -Xmx1024M -jar ${DATA_EXPORT_JAR} -config ${BASE_CONFIG_FILE} -filename ${DATA_FILE_PATH}/${DATA_FILE_NAME} -user cmms -password cmms | tee -a ${LOG_FILES}/${LOG_FILE_NAME}

where:
1. ${DATA_EXPORT_JAR} is the path of the JAR File we just created
2. -config and -filename are arguments to the main class.
3. tee -a ${LOG_FILES}/${LOG_FILE_NAME}, allows you to put the output to the output file and also see the output on the standard output.

Enjoy :).