Posts Tagged ‘Oracle’

Using Ref Cursor with Oracle’s ThinClient JDBC driver and Coldfusion via .Net

Tuesday, May 10th, 2011

So it’s a known issue that Coldfusion does not support returning a ref cursor as an out parameter of a stored procedure when using the Oracle Thin Client drivers. Since I have experienced this problem in the past I thought I would post a solution that uses C# to manage the Oracle stored procedure and return the result set to Coldfusion. So this solution will use Coldfusion’s ability to create object instances of a C# class using the ‘cfobject’ tag or the ‘createObject()’ function. Also keep in mind that if Coldfusion is returned a type DataTable from the C# method it will automatically convert it into a Coldfusion Query object that can then be handled like normal. So with this in mind let’s take a quick look at a basic Oracle stored procedure and the C# class that will execute the procedure and return a DataTable object.

Oracle Stored Procedure

create or replace
PROCEDURE "SP_TEST_CF9"
(
      arg_ArgumentOne IN NVARCHAR2,
      l_cursor OUT sys_refcursor
)
AS 

BEGIN

      OPEN l_cursor FOR
      SELECT column_name, column_id
      FROM  column_table
      WHERE column_argument_one = arg_ArgumentOne;

END SP_TEST_CF9;

(more…)

Tags: , ,
Posted in .Net | No Comments »

Using cfqueryparam list attribute when using ‘IN’ operator

Wednesday, January 12th, 2011

When writing SQL in Coldfusion where the ‘IN’ operator is used you will have to use the list attribute of the cfqueryparam tag, and if using a delimiter other than a comma will need to include the ‘separator’ attribute to indicate the delimiting character.  This, of course, only applies if the list of available values are stored externally in a variable.  Let’s take a look at how to use the ‘IN’ operator in Coldfusion and some varying scenarios that either will or will not work.

First: Using actual values (works)

<cfset variables.dsn = "cfartgallery" />

<cfquery name="variables.selectArt" datasource="#variables.dsn#">
	SELECT *
	FROM art
	WHERE artName IN ('Morph','Things','Ideas')
</cfquery>

(more…)

Tags: , ,
Posted in Coldfusion | 4 Comments »

ORA-00923: FROM keyword not found where expected

Wednesday, December 15th, 2010

This is a general reference to an error I received today when executing an Oracle statement. Essentially the statement was fairly complicated containing a number of subqueries. As such receiving ‘ORA-00923: FROM keyword not found where expected’ as an error in a statement was somewhat frustrating. As it turns out, not only is it a somewhat useless user facing error, it didn’t even turn out to have anything to do with the FROM clause.

After much debugging, a co-worker determined that the issue was a duplicate column reference in the SELECT clause. After determining the point of failure I decided to write up a simple test to determine if this error would be thrown in a statement far less complicated than the one being worked on at the time.

What I learned is rather interesting. This error is only thrown when a column is duplicated, in which the duplicate is contained within the all symbol (*). So on the assumption that the employee_table contains three columns – employee_id, employee_name, and employee_email – let’s take the following example that would throw this error when run against Oracle.

Throws: ORA-00923: FROM keyword not found where expected

SELECT *, employee_name
FROM employee_table

If I were to write out the columns and duplicate the columns I would get a different error. The following example would throw an ambiguous column reference.

Throws: ORA-00918: column ambiguously defined

SELECT employee_id, employee_name, employee_email, employee_name
FROM employee_table

While the above examples are no-brainers, the particular query I was working on was a very complex SQL statement with CF wrappers and Oracle functions and all kinds of good stuff. So under these conditions finding duplicate columns can be very tricky and unfortunately Oracle doesn’t do much to help. So I guess the lesson is that if you get either of these errors check for any duplicating columns. Note that my literal test did contain a JOIN, which may have something to do with the second error listed. That particular statement might throw a different error.

Tags: ,
Posted in SQL | 4 Comments »

Oracle SQL Developer Error: Exception firing addinsLoaded to oracle.jdevimpl.vcs

Monday, September 27th, 2010

I recently installed a new version the Oracle SQL Developer on my Windows machine and promptly received the following error upon opening the program: ‘SEVERE 46 0 oracle.ide.IdeCore Exception firing addinsLoaded to oracle.jdevimpl.vcs’.

After spending some time trying to determine the source of the problem I decided to read the instruction manual. While I should have read this first to avoid any issues, I figure better late than never. The docs clearly state: ‘… and do not unzip a kit over the SQL Developer files that are included with Oracle Database’. This is of course exactly what I had done.

I promptly cleaned up the old directory and unzipped the new files into a different directory, and sure enough problem solved.

Lessons Learned:

  1. Always read the installation manual before performing installation, duh!
  2. Do not unzip the SQL Developer files on top of an existing set of SQL Developer files.  It won’t work.

Tags: , ,
Posted in SQL | No Comments »