Creating a DB Link in Oracle

Its wierd.. I tried to create a dbLink from the regurlar whizard from the dbLinks tab in toad.. but it didnt work.. so tried to find and alternative from the SQL Editor and surprisingly it worked.. yayyy..!!!!

create database link SAMPLE_DEV
connect to fdrdbo2
identified by fdrdbo2
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ada8asff2)(PORT = 3104)))(CONNECT_DATA =(SERVICE_NAME = SONU)))'

You can get this description from your TNSnames.ora file n copy it over here.

this is cool..!!! I can test my sp with real time data connected to the right db. =)

Some Classic SQL's:

SQL to get Second Max form a column:

select * from EMPLOYEE where SALARY = (
select
max(SALARY)
from
EMPLOYEE
where
SALARY < (select max(SALARY) from EMPLOYEE)

--In the condition we are trying to select max from a list which would contain values less than max(salary) and it works =)

-------------------------------------------------------------------------------------

FINDING DUPLICATE ROWS IN A TABLE

-------------------------------------------------------------------------------------

select FAC_OID, count(*) from v_XYX_TABLE group by FAC_OID HAVING count(*) > 1;

will retrieve all values of fac_oid from the table where the fac_oid is repeating. :)



-------------------------------------------------------------------------------------
DELETE AND RENAME A COLUMN FROM ORACLE TABLE

-------------------------------------------------------------------------------------


alter table srgt_path_prelim_lgd drop column PRELIM_LGD_PCT

alter table srgt_path_prelim_lgd rename COLUMN PRELIM_LGD_PCT TO PRELIM_LGD_PCT1

-Sunny

Executing Dynamic SQL with Ref Cursors

Here is the SP to execute Dynamic SQL built at Runtime:

CREATE OR REPLACE procedure SP_TEST_REF_CURSOR(i_n_proposalId number)as


TYPE FacilityDetails IS REF CURSOR;

facilities_data FacilityDetails;

l_s_facQuery varchar2(10000);

lengthOfString integer;

l_s_status_code varchar2(20);
l_n_edit_code number;

l_n_counter number:=0;
begin

dbms_output.put_line('hello world');


l_s_facQuery:='SELECT FACILITY_STATUS_CODE,FACILITY_ID
FROM
fdrdbo.v_credit_facility@RODR_DEV.NY.SONU.COM
WHERE
(facility_id=198000589 and
facility_version_no =2 and
facility_edit_no=0 and
facility_edit_session_no=0) or (facility_id=299074678 and
facility_version_no =1 and
facility_edit_no=0 and
facility_edit_session_no=0) ';

lengthOfString:=length(l_s_facQuery);

--dbms_output.put_line(lengthOfString);

--put_line(l_s_facQuery,lengthOfString);
--l_s_facQuery:='select proposal_element_id from srgt_proposal_element where proposal_id=489';

OPEN facilities_data FOR l_s_facQuery;

LOOP

FETCH facilities_data into l_s_status_code,l_n_edit_code;

EXIT WHEN facilities_data%NOTFOUND;
l_n_counter:=l_n_counter+1;


dbms_output.put_line('FacilityStatus Code is : ' || l_s_status_code || 'facilityId is :' || l_n_edit_code);


--DBMS_OUTPUT.PUT_LINE('hello world');
--DBMS_OUTPUT.PUT_LINE(l_s_status_code || ' ' || l_n_edit_code);
END LOOP;

CLOSE facilities_data;

DBMS_OUTPUT.PUT_LINE('hello world' || l_n_counter);

end;
/

dbArtisan tips and tricks

Importing data from an external file, csv,or tab separated etc.

Create a table from an existing table

in the tables rt click on the table and do Create Like






Importing data from a file to table:

Rt click on the table

do Import data from





and walk thru the whizard here is the pics

Sybase Client and dbArtisan

Here is the steps to import Sybase Open client

1) Import open client

2) Put your ini file into the following directory. ( C:\Program Files\Sybase Open Client 12\ini )

Sample ini file:

[CR_HUB_DEV]
master=TCP,10.19.9.90,3325
query=TCP,10.19.9.90,3325

[CR_HUB_UAT]
master=TCP,10.19.9.92,3325
query=TCP,10.19.9.92,3325

[CR_HUB_PDS]
master=TCP,10.19.9.91,3325
query=TCP,10.19.9.91,3325


==================DO NOT ADD BELOW THIS LINE=======================

Testing connection from command prompt:

GOTO the following directory:

1) C:\>cd %sybase%\%sybase_ocs%\bin

2)C:\Program Files\Sybase Open Client 12\OCS-12_0\bin>isql -S CR_HUB_DEV -U rogc
-P j2002

issql <-- Command Name

-S <-- Server Name. In this case CR_HUB_DEV from ini file

-U <-- User Id to connect to server in -S

-P <-- Password to connect to the server in -S

If its connected you shud see the following at the command prompt

1>




Once this test is done,

Open your dbArtisan

Will ask you a question if you want dbArtisan to automatically configure the Data Sources for you.

Click Yes

it will configure it for you and you will see the following screen




And then click on SQL server you shud see all your servers configured in ini file.

TNS Ping & SQLPLUS

tnsping is a utility to test your various oracle connections in tnsnames.ora.

it comes with your oracle.

Run the following tests once u install Oracle:

1)Start --> Run --> cmd

2) at the command prompt type in

tnsping

ex: if your tns entry is :

SRGTDEV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cmc8asff2.ny.jpmorgan.com)(PORT = 10005))
)
(CONNECT_DATA =
(SERVICE_NAME = APPRDEV)
)
)

type in :

tnsping SRGTDEV

YOU SHUD SEE THE FOLLOWING SCREEN




ALSO YOU CAN TYPE IN sqlplus at the command prompt to make sure ur oracle is properly installed. In that case you will see the following:

JDK and JRE Big lesson learnt

JDK: Java Development tool kit also includes JRE (Java Runtime Environment)

JRE: Java Runtime Environment need not be installed separately. I comes along with JDK. Just make sure if its installed properly

Also biggest mistake made today:

JDK and JRE are of separate versions.And code compiled with a different version of JDK cannot be run on separate version of JRE.

So Ant is showing the following error

class file has wrong version 49.0, should be 48.0

To read more abt this goto: http://forum.java.sun.com/thread.jspa?threadID=517933&tstart=0


Resolution: What I did is uninstall both JDK and JRE and reinstall the same verison from http://java.sun.com/javase/downloads/index_jdk5.jsp

Everything went well and my Build is successful.

Setting Class Path Variables in Windows

Setting Class Path variables in windows:

Rt click on My Computer

2) Advanced

3) Environment Variables

4) New

5) JAVA_HOME

6) C:\j2sdk1.4.2_08.

Also see the attachments:

Strting WebLogic From Eclipse My experience

After you start your weblogic from outside for the first time.. and custom set up of weblogic Admin user Id and Password,Here is the steps to proceed further:

1)Open my eclipse

2)Window-->Preferences -->My Eclipse --> Application Server -->Weblogic 8

3) open + on weblogic 8.

4) Open JDK and set up the path as follows: C:\bea\jdk142_08

5) This will import a set of jdk files needed for starting the server including JRE

6) Now go to Weblogic tab and enter the following:


BEA Home Directory: C:\BEA

Choose enable weblogic Radio button on the top of this panel.

and enter the following:

Weblogic Installation Directory: C:\bea\weblogic81

Admin userName: weblogic (PS: ITs the uid and pwd u set up while starting ur server)

Admin password: weblogic (PS: ITs the uid and pwd u set up while starting ur server)

Execution Domain Root: C:\bea\weblogic81\server\bin

Execution Domain Name: mydomain

Execution Server Name: myserver

Host:portNumber: localhost:7001

Thats it its that easy you are all set to start weblogic server from your Eclipse.





Enjoy,
Suneetha.

Installing and Starting Weblogic Server

After default installing the weblogic server in the folder c:\bea

You can start your server from the following:

C:\bea\weblogic81\server\bin

In the bin folder click on the following: startWLS.cmd

And will ask you for the user id and pwd.

Since this is the first time setup, just give any user id and password. But remember it for future use

Then after running a couple of checks it will ask you if you want to install a custom build for this startup. (Something like that with a question Y/N).

Type in Y

Now will prompt u to re-enter your password

re-enter your pwd

Thats it ur set up is complet and now you have your Weblogic server running.

To confirm GOTO:

http://localhost:7001/console

Will prompt you for a user id and pwd just enter the one that u used for your set up. And you are all set.. console is open for use. =)

YEPPEEEEE

Installing Eclipse

Installing Eclipse:

Goto eclipse.org and get the latest version of eclipse.

Download it to a folder.

Extract the zip file to a separate folder. Now you will see the eclipse icon in the extracted folder.

Double click on the icon.. your eclipse is ready to use.

You dont have to install eclipse separately.Just double clicking on the icon is enough.

You will get an error if you dont have JVM installed.


Before using eclipse make sure you have java virtual machine (JVM) installed on ur machine.

starting weblogic from eclipse

--------------------------------------------------------------------------------

Navigate to 'Preferences' from the 'Window' menu
Select the 'WebLogic' node and set the following parameters
Version of WebLogic Server
BEA Home Directory
WebLogic Home Directory
Domain Name
Domain Directory
Server Name
User Name (the name of a user with privileges to boot this server)
Password
Hostname (the hostname used by weblogic.Admin)
Port (the port number used by weblogic.Admin)
Select the 'WebLogic->Classpath' node and set the following parameters
Classpath before the WebLogic libraries
Classpath after the WebLogic libraries
Select the 'WebLogic->Project' node and set the following parameter
Project added to the end of the classpath
Select the 'WebLogic->JavaVM Options' node and set the following parameters
JavaVM (JDK used to launch WebLogic Server)
JavaVM Options (arguments to pass the JavaVM)
JNI library path (paths to search when loading libraries)

Starting WebLogic Server

--------------------------------------------------------------------------------

Navigate to 'Start WebLogic Server...' from the 'Run' menu
or Click the 'Start WebLogic Server...' button
Stopping WebLogic Server

--------------------------------------------------------------------------------

Navigate to 'Stop WebLogic Server...' from the 'Run' menu
or Click the 'Stop WebLogic Server...' button