DBLINK SERVER ORACLE

DB – LINK – SERVER

Connection from Oracle to SQL Server with DBLINK

Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. This article explores in a step-by-step fashion setting up this connection so that we may query from a SQL Server database.

1. Define a Data Source Name (DSN) for SQL Server

The first step is to define a system DSN within the Windows ODBC Data Sources. For 64 Bit Operating System choose odbcad32.exe in folder Windows\SysWOW64

Click on the System DSN tab and then click the Add button. Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.

Key in any name you would like to reference this ODBC data source. I have chose MYSQLSERVERDSN for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my local SQL Server that I will be connecting to. Click Next to continue.

 

 

Typically, this window is populated with the default SQL Server database of “master.” Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the sample HPCL database. Click Next to continue.

This window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.

This window should appear, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.

2. Create a Services Initialization File

Oracle has provided a sample heterogeneous services init file within the $ORACLE_HOME/hs/admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name “inithsconnect.ora” that corresponds to my DSN name “MYSQLSERVERDSN”.

The Oracle Listener listens for incoming requests from the Oracle database. For the Oracle Listener to listen for DG4ODBC, information about DG4ODBC must be added to the Oracle Listener configuration file, listener.ora. You need to:

• Create a SID_NAME for DG4ODBC.
• Specify the executable that the listener should start in response to DG4ODBC connection requests.

ORACLE_HOME/hs/admin/inithsconnect.ora altered file

 

# This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC # # HS init parameters #   HS_FDS_CONNECT_INFO = MYSQLSERVERDSN HS_FDS_TRACE_LEVEL  = OFF   # # Environment variables required for the non-Oracle system # #set <envvar>=<value>

 

 

 

 

 

 

 

 

 

 

 

3. Alter your listener.ora file

Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the $ORACLE_HOME/hs/admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..

1. Created my own listener name of LISTENERMYSQLSERVERDSN
2. Changed the Port number to 1522
3. Changed the SID_NAME to my DSN (hsconnect) * the file name inithsconnect.ora we have to give the “hsconnect”  after init
4. Changed the ORACLE_HOME location
5. Changed the PROGRAM to dg4odbc for 64 bit version of Oracle 11.2 XE version.

$ORACLE_HOME/hs/admin/listener.ora. sample file

 

listenermysqlserverdsn = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   SID_LIST_LISTENERMYSQLSERVERDSN= (SID_LIST= (SID_DESC= (SID_NAME=hsconnect) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) (PROGRAM=dg4odbc) ) )

 

 

 

 

 

 

 

 

 

4. Alter your tnsnames.ora file

Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the $ORACLE_HOME/hs/admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.

1. I made four distinct changes. Created a TNS entry named “hsconnectid “

2. Changed the Port number to 1522

3. Changed the SID to my (hsconnect)

4. Added OK to the HS= parameter

 

$ORACLE_HOME/hs/admin/tnsnames.ora sample file

 

 

hsconnectid  = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST = localhost )(PORT=1522)) (CONNECT_DATA=(SID = hsconnect )) (HS=OK) )

 

 

 

 

 

5. Start the new Listener

This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.

C:\…….> lsnrctl start listenermysqlserverdsn

or you can start in the services.msc also

6. Validate the connection to your DSN

You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.

C:\> ……. \> tnsping hsconnectid

 

7. Create a Database Link within Your Oracle Database

Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.

 

SQL>CREATE PUBLIC DATABASE LINK hslink CONNECT TO “sa” IDENTIFIED BY “gemini123” using ‘hsconnectid’;

 

 

 

8. Select some data

This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.

SQL> select * from SMS_OUTBOX@hslink;

 

To shorten the Tomcat application URL

To shorten the Tomcat server application URL currently we are using long form of http://localhost:8080/[blazeds]/myapp/application.html

1. First we need to remove the port Numbers of tomcat by configuring the server.xml in tomcat ../conf/server.xml

<!–<Connector port=”8080″ protocol=”HTTP/1.1″

connectionTimeout=”20000″

redirectPort=”8443″ />

–>

<Connector port=”80″ protocol=”HTTP/1.1″

connectionTimeout=”20000″

redirectPort=”8443″ />

the commented one which we used currently , the new one is just remove the port number as 8080 as 80 .

so that you can access the tomcat path in URL like http://localhost

1. To shorten the URL just copy  [myapp] flex application files into the [blazeds]

1. Rename the [blazeds] folder as [myapp] (your favourite Name)

1. Change the content.xml in myapp folder

<?xml version=”1.0″ encoding=”utf-8″?>

<content>

<page location=”URL”>

<text>http://localhost/ myapp /messagebroker/amf</text>

</page>

<page location=”URL1″>

<text>http://localhost/ myapp /jasperReport.jsp</text>

</page>

</content>

1. Restart the tomcat Server.

1. Now you can access your application http://localhost/ myapp / myapp.html

If you want to avoid myapp.html file name rename the file as index.html

You can access your application like http://localhsot/ myapp

Referred URL

1. http://www.coderanch.com/t/81669/Tomcat/remove-port-number-URL
2. http://www.coderanch.com/t/87915/Tomcat/tomcat-define-context-root-name
3. http://www.velocityreviews.com/forums/t370192-tomcat-redirect-localhost.html

PostgresSQL Install _ windows user account problem

Solution for the below @ installation time

The password specified was incorrect. Please enter the correct password for the postgres windows user account.

in command prompt type the below

” net user postgres /delete ”

The account will be deleted.

and you again try to install the postgressql 8.x

Java – Connection String Oracle / MS-SQL / MY-SQL

Oracle

 
String databaseName = “jdbc:oracle:thin:@10.178.18.26:1521:ORCL”;
String userName = “lims”;
String password = “Admin123”;
Connection jdbcConnection = null;
jdbcConnection = DriverManager.getConnection(databaseName,userName,password);

mySQL

String databaseName = “jdbc:mysql://10.178.18.26:3306/lims”;
String userName = “root”;
String password = “Admin123”;
Connection jdbcConnection = null;
jdbcConnection = DriverManager.getConnection(databaseName,userName,password);

ms-SQL
String databaseName = “jdbc:sqlserver://10.178.18.26;Database=limstest;user=lims;Password=Admin123”;
String userName = “lims”;
String password = “Admin123”;
Connection jdbcConnection = null;
jdbcConnection = DriverManager.getConnection(databaseName,userName,password);

Jasper Report Export All Formats (Passing Parameters) using JSP Page

<%@ page language="java"  import="net.sf.jasperreports.engine.,net.sf.jasperreports.engine.export."  %>
<%@ page import="java.sql.,java.io.,java.util."  %>
<html>
<head>
<title>report generation in  jsp</title>
</head>
<body>
<%
String filename =  request.getParameter("filename");
String reporttype =  request.getParameter("reporttype");
String Paramtype =  request.getParameter("paramtype");;

/*creating database  connection/
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection  conn =  DriverManager.getConnection("dburl","user","pwd");
System.out.println("Connection  Established");

// jasperParameter is a Hashmap contains the  parameters
// passed from application to the jrxml layout
Map  jasperParameter = new  HashMap();
jasperParameter.put("paramtype",Paramtype);

/file path  of .jasper file/
String path =  application.getRealPath("/");
JasperPrint jasperPrint =  JasperFillManager.fillReport(path "/" filename, jasperParameter,  conn);
//JasperPrint jasperPrint = JasperFillManager.fillReport(path "/" filename, null, conn);
System.out.println("Report Created... in  "reporttype " Format");
OutputStream ouputStream =  response.getOutputStream();
JRExporter exporter = null;

/Report  generated in - PDF/
if( "pdf".equalsIgnoreCase(reporttype)  )
{
response.setContentType("application/pdf");
response.setHeader("Content-Disposition",  "inline; filename=\"report.pdf\"");

exporter = new  JRPdfExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT,  jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,  ouputStream);
}

/Report generated in - rtf/
else if(  "rtf".equalsIgnoreCase(reporttype)  )
{
response.setContentType("application/rtf");
response.setHeader("Content-Disposition",  "inline; filename=\"report.rtf\"");

exporter = new  JRRtfExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT,  jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,  ouputStream);
}

/Report generated in - html/

else if(  "html".equalsIgnoreCase(reporttype)  )
{
response.setContentType("application/html");
response.setHeader("Content-Disposition",  "inline; filename=\"report.html\"");

exporter = new  JRHtmlExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT,  jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,  ouputStream);
exporter.setParameter(JRHtmlExporterParameter.IMAGES_URI,"image?image=");
}
/Report  generated in - xls/

else if( "xls".equalsIgnoreCase(reporttype)  )
{
response.setContentType("application/xls");
response.setHeader("Content-Disposition",  "inline; filename=\"report.xls\"");
exporter = new  JRXlsExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT,  jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,  ouputStream);
}

/Report generated in - csv/

else if(  "csv".equalsIgnoreCase(reporttype)  )
{
response.setContentType("application/csv");
response.setHeader("Content-Disposition",  "inline; filename=\"report.csv\"");

exporter = new  JRCsvExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT,  jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM,  ouputStream);
}

try
{
exporter.exportReport();
}
catch  (JRException e)
{
throw new  ServletException(e);
}
finally
{
if (ouputStream !=  null)
{
try
{
ouputStream.close();
}
catch (IOException  ex){}
}
}
%>
</body>
</html>

Oracle Date – Time Arithmetic Calculations

Subtracting the two dates and find Years,Months and Days

Calculating Years , Months, and Days from the Current date with whole values as below shown oracle query updates.

select months_between(
to_date(’03/06/2001′,’MM/DD/YYYY’),to_date(’11/17/1992′,’MM/DD/YYYY’))/12 as “Years” from dual;

Years
———————-
8.30376344086021505376344086021505376344

select months_between(to_date(’03/06/2001′,’MM/DD/YYYY’), to_date(’11/17/1992′,’MM/DD/YYYY’)) as “Months” from dual;

Months
———————-
99.64516129032258064516129032258064516129

select to_date(’03/06/2001′,’MM/DD/YYYY’) – to_date(’11/17/1992′,’MM/DD/YYYY’) as “Total Days” from dual;

Total Days
———————-
3031

Calculating Years , Months, and Days from the Current date with calculated values from the query updates.

select trunc(months_between(to_date(’07/09/2009′,’MM/DD/YYYY’), to_date(’12/12/1975′,’MM/DD/YYYY’))/12) as “Years” from dual;

Years
———————-
33

select mod(trunc(months_between(to_date(’07/09/2009′,’MM/DD/YYYY’),to_date(’12/12/1975′,’MM/DD/YYYY’))),12) as “Months” from dual;

Months
———————-
6

select trunc(to_date(’07/09/2009′,’MM/DD/YYYY’) – add_months(to_date(’12/12/1975′,’MM/DD/YYYY’),
months_between(to_date(’07/09/2009′,’MM/DD/YYYY’), to_date(’12/12/1975′,’MM/DD/YYYY’)))) as “Days” from dual;

Days
———————-
27

and finally complete single query to fetch the Total Years , Months and Days

select trunc(months_between(to_date(’07/09/2009′,’MM/DD/YYYY’), to_date(’12/12/1975′,’MM/DD/YYYY’))/12) as “Years” ,
mod(trunc(months_between(to_date(’07/09/2009′,’MM/DD/YYYY’),to_date(’12/12/1975′,’MM/DD/YYYY’))),12) as “Months”,
trunc(to_date(’07/09/2009′,’MM/DD/YYYY’) – add_months(to_date(’12/12/1975′,’MM/DD/YYYY’),
months_between(to_date(’07/09/2009′,’MM/DD/YYYY’), to_date(’12/12/1975′,’MM/DD/YYYY’)))) as “Days” from dual;

Years Months Days
———————- ———————- ———————-
33 6 27

1 rows selected

The other way to work out the same problem in the below :

If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases.

DEFINE DateDay = 8752.44056

SELECT

TO_NUMBER(SUBSTR(A,1,4)) – 2000 years,

TO_NUMBER(SUBSTR(A,6,2)) – 01 months,

TO_NUMBER(SUBSTR(A,9,2)) – 01 days,

SUBSTR(A,12,2) hours,

SUBSTR(A,15,2) minutes,

SUBSTR(A,18,2) seconds

FROM (SELECT TO_CHAR(TO_DATE(‘20000101′,’YYYYMMDD’)

+ &DateDay,’YYYY MM DD HH24:MI:SS’) A

FROM DUAL);

YEARS MONTHS DAYS HO MI SE

———- ———- ———- — — —

23 11 17 10 34 24

The new TIMESTAMP datatype

Convert DATE datatype to TIMESTAMP datatype

CREATE TABLE date_table (

date1 DATE,

time1 TIMESTAMP,

time2 TIMESTAMP

);

INSERT INTO date_table (date1, time1, time2)

VALUES (SYSDATE,

TO_TIMESTAMP (‘17.12.1980:00:00:00′,’DD.MM.YYYY:HH24:MI:SS’),

TO_TIMESTAMP (‘03.12.2004:10:34:24′,’DD.MM.YYYY:HH24:MI:SS’)

);

COMMIT;

SELECT CAST(date1 AS TIMESTAMP) “Date” FROM date_table;

Date

—————————————————————————

03-DEC-04 11.36.45.000000 AM

Formatting of the TIMESTAMP datatype:

SELECT TO_CHAR(time1,’MM/DD/YYYY HH24:MI:SS’) “Date”

FROM date_table;

Date

——————-

12/17/1980 00:00:00

Formatting of the TIMESTAMP datatype with fractional seconds:

SELECT TO_CHAR(time1,’MM/DD/YYYY HH24:MI:SS:FF3′) “Date”

FROM date_table;

Date

—————————–

12/17/1980 00:00:00:000

Silent Valley Trip @ Kovai

from – K810i @ Kovai

Gym @ Down

Top View from Roof

Swimming Pool @ Roof

Marudha Malai Temple

Team on Marudhamali TOP

Bakthiyudan

Marudhamalai Thanga Ther

Thanga Ther Ula

GKNM @ Kovai

Hospital Front Elevation

Hoarding NABH Accrediation

Tree @ Green Pathway

Path Way

GKNM Hospital

TOMCAT – BlazeDS – JavaClass

BlazeDS – Tomcat – Java Class Configuration for Flex3 Version

STEP -I
Tomcat Configuration

 1. Installing Apache Tomcat Version in any of your Root Folder (ver. -6.0.18)
 2. In the Bin Folder the Tomcat Service can be start / stop
 3. User / Password for Tomcat Service may added in — “tomcat-users.xml”
 4. Port can change in “server.xml” file default Port is :8080 for BlazeDS 8400
 5. Every Configuration the Service should Stop and Start.

 

STEP – II
BlazeDS Configuration

 1. blazeds.war (downloaded)  file should place in \Tomcat[xxxx]\webapps folder
 2. After place the file the “blazeds” folder and insider created..(Automatically)
 3. in  “\webapps\blazeds\flex” some of xml file is available for configuration like remoting
    proxy-config, service-config,messaging-config files (depends upon developer request)

 

STEP – III
Java Class files
 1. In the \webapps\blazeds\WEB-INF\classes  – created classes can put inside…(relevant path)
STEP – IV
Configuration in Flex-3