Archive for the ‘oracle’ Category

Query for Hash Map using iBatis (MyBatis)

September 27, 2012

An iBatis, oops MyBatis post. Donno why still am tend to call it iBatis only. Anyway just a small post to show how we can query data to fill a hashmap. Directly into code.

<resultMap id="hashMapResult" class="java.util.HashMap">
<result property="key" column="managerName"/>
<result property="value" column="count"/>
</resultMap>

<select id="mCount" resultMap="hashMapResult">
<![CDATA[
select managerName, count(reportees) AS count
from mgr_employee
group by managerName;
]]>
</select>

You can call this from java using the simple command below :

Map<String,Long> mCountMap = getSqlMapClientTemplate().queryForMap("mCount", "", "key", "value");

That’s it guys. I am experimenting this for having objects other than String as Values. I will update the post as soon as that is done. Happy Coding Guys 🙂

Advertisements

Getting Oracle Version Number

March 27, 2012

Ever wondered how to know the version number of the Oracle server you are connected to. You can very well use the v$session table.

Select * from v$session;

This will give you 4-5 rows showing all the version numbers of different tools used in the database. Happy Coding Guys. 🙂

Inserting / Updating NULL values in iBatis

March 1, 2012

Inserting or Updating nulls in iBatis should be done in a crewked way. One obvious fix is to put the isNotNull tag before the fields which can be null. But this will make the sqlmap file cumbersome or hard to read. For eg:, the below insert query will throw error, if you are trying to insert a null value for, say, infoId.

<insert id=”insertInformation” parameterClass=”Information”>
insert into info (Key, Name, InfoId)
values (#key#, #name#, #infoId#)
</insert>

We can put isNotNull tage like this :

<insert id=”insertInformation” parameterClass=”Information”>
insert into info (Key, Name, InfoId)
values (#key#, #name#
<isNotNull property=”infoId”>
, #infoId#
</isNotNull>
)
</insert>

Now doesn’t that became too much. So here comes the ultimate fix. You just need to put the Oracle datatype of the column separated by a colon in the statment like this :

#infoId:VARCHAR#

The complete sql tag will become like this :

<insert id=”insertInformation” parameterClass=”Information”>
insert into info (Key, Name, InfoId)
values (#key#, #name#, #infoId:VARCHAR#)
</insert>

Similarly the update statement also can be modified like this :

<update id=”updateInformation” parameterClass=”Information”>
update Info set
Key = #key#,
Name = #name#,
infoId = #infoId:VARCHAR#
where
id = #id#
</update>

Execute SQLLdr without tnsnames.ora entry

February 1, 2012

In my last post, I discovered that we can run sqlplus without having an entry in tnsnames. I was under the impression that it will work the same way in sqlldr also. But it was a mistake as sqlldr told me I am giving it the wrong syntax. I spend lots of time on that to find out finally that, I need to escape the brackets and need to put a double quotes which itself should be escaped around the connection string. So it goes like this :

sqlldr username/pwd@\”\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=yourhost.domain\)\(PORT=1521\)\)\(CONNECT_DATA=\(SERVER=DEDICATED\)\(SID=yourservice\)\)\)\” control=control_file.ctl errors=1000 ROWS=50000

That’s it guys. Happy Coding. 🙂

How to connect SQLPlus without tnsnames.ora entry

January 31, 2012

In some weird companies, like the one am working, will restrict many things in our machine which will reduce the productivity. Recently I was trying to add a new entry in tnsnames when our database server got changed. And hell, I was not having rights to change the tnsnames file in my local machine!!! WTF!!! At the end of day I need to run one sqlplus command from the command prompt. SQLPlus needs an entry in the TNS Names file in order to work. Then I found out an way to execute the command by directly giving the connection string in the sqlplus command itself. Like this :

sqlplus username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=servicename)))

BINGO! It works as hell. Please remember that there shouldn’t be any space in the connection string, also no carriage return (which might happen if you are copying the string from the tnsnames file.

Thats it guys. Happy Coding. 🙂

Execute an SQL script file in SQLPlus

December 9, 2008

To execute a script file in SQLPlus, type @ and then the file name.

SQL >  @{file}

For example, if your file was called script.sql, you’d type the following command at the SQL prompt:

SQL >  @script.sql

The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in before you launched SQLPlus.)

If you need to execute a script file that is not in the current directory, you would type:

SQL >  @{path}{file}

For example:

SQL >  @/oracle/scripts/script.sql

This command would run a script file called script.sql that was located in the /oracle/scripts directory.

Killing Oracle Sessions

December 9, 2008

Yesterday night I noticed that one of my update statement not at all executing in some table. It was late night and almost everyone had left home. I thought someone might have locked the table and thats causing the problem. I got interested and thought how can I force logout some other’s session. This is the solution. (you need dba rights for this ;P)

The SQL*Plus Approach

Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

First identify the offending session as follows:

SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program
FROM   v$session s;

       SID    SERIAL# OSUSER                         PROGRAM
---------- ---------- ------------------------------ ---------------
         1          1 SYSTEM                         ORACLE.EXE
         2          1 SYSTEM                         ORACLE.EXE
         3          1 SYSTEM                         ORACLE.EXE
         4          1 SYSTEM                         ORACLE.EXE
         5          1 SYSTEM                         ORACLE.EXE
         6          1 SYSTEM                         ORACLE.EXE
        20         60 SYSTEM                         DBSNMP.EXE
        43      11215 USER1                          SQLPLUSW.EXE
        33       5337 USER2                          SQLPLUSW.EXE

The SID and SERIAL# values of the relevant session can then be substituted into the following statement:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

In some situations the Oracle.exe is not able to kill the session immediately. In these cases the session will be “marked for kill”. It will then be killed as soon as possible.

Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session. If the marked session persists for some time you may consider killing the process at the operating system level, as explained below. Killing OS processes is dangerous and can lead to instance failures, so do this at your own peril.

It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This should prevent you ever needing to use the orakill.exe in Windows, or the kill command in UNIX/Linux.

The NT Approach

To kill the session via the NT operating system, first identify the session as follows:

SELECT s.sid,
       p.spid,
       s.osuser,
       s.program
FROM   v$process p,
       v$session s
WHERE  p.addr = s.paddr;

       SID SPID      OSUSER                         PROGRAM
---------- --------- ------------------------------ ---------------
         1 310       SYSTEM                         ORACLE.EXE
         2 300       SYSTEM                         ORACLE.EXE
         3 309       SYSTEM                         ORACLE.EXE
         4 299       SYSTEM                         ORACLE.EXE
         5 302       SYSTEM                         ORACLE.EXE
         6 350       SYSTEM                         ORACLE.EXE
        20 412       SYSTEM                         DBSNMP.EXE
        43 410       USER1                          SQLPLUSW.EXE
        33 364       USER2                          SQLPLUSW.EXE

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

To kill the session via the UNIX operating system, first identify the session in the same way as the NT approach, then substitute the relevant SPID into the following command:

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using:

% ps -ef | grep ora

The session thread should be killed immediately and all resources released.

For further information see:


%d bloggers like this: