Vamsi Pavan’s Place

One of the very common task in oracle environment is export/import of CSV files to Oracle database. Export is pretty straight forward as many of oracle clients provide this as standard functionality for any query. But for import, we can do by writing our own scripts using pl/sql or java or .NET or any other […]

[Read more →]

If you get this error while trying to create a foreign key, it can
be pretty frustrating. The error about not being able to create a .frm
file seems like it would be some kind of OS file permission error or
something but this is not the case. This error has been reported as a
bug on the MySQL […]

[Read more →]

In my PL/SQL procedure I had to add a text including newline (\n in
java, ascii #10) in a text to be read by a java application. Tried with
the code
l_text := ‘hello\nworld’;
This did not work, the \n character behaves clearly different in PL/SQL than
in Java and only wrote ‘\n’, probably the same as writing a String […]

[Read more →]

Seems like this is very common error in pl/sql programming. I know there are many last minute frustrations just because of this error which is no where related to any logic. This seems to be some format error in the programming language itself.
The error message is:

java.sql.SQLException: Non supported SQL92 token at position: 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at […]

[Read more →]

Some of the new concepts introduced as a part of Oracle new releases are:
Analytical Functions
In addition to the normal aggregate function like Max() etc. Oracle came up with some more commonly useful analytical functions, which can be implemented by PL/SQL logic. Mainly 5 commonly used functions are there:

rank() :– In a select statement after selecting […]

[Read more →]

Note: Information & code samples from this article are tested on SQL Server 2005 RTM (Yukon) and found to be working. Will update the article in case of any compatibility issues.
unfortunately, there is no built-in support for arrays in SQL Server’s T-SQL. SQL Server 2000 did add some new datatypes like sql_variant, bigint etc, but […]

[Read more →]

A stored procedure or function can be created with no parameters, IN parameters, OUT parameters, or IN/OUT parameters. There can be many parameters per stored procedure or function.
An IN parameter is a parameter whose value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can’t be changed […]

[Read more →]

This example demonstrates how to call stored procedures with IN, OUT, and IN/OUT parameters.

CallableStatement cs;
try {
// Call a procedure with no parameters
cs = connection.prepareCall(”{call myproc}”);
cs.execute();

[…]

[Read more →]