Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

REF_CURSOR with ojdbc8 gives Invalid column type: 2012 error

User_P0FSYJun 6 2018 — edited Jun 7 2018

Hi All,

We are using ojdbc8.jar and Oracle 12.1.0.2.0 Database.

We have stored procedure which returns SYS_REFCURSOR. we are calling this stored procedure from java using hibernate s below

sp.registerStoredProcedureParameter(1, Class.class, ParameterMode.REF_CURSOR);

sp.execute();

resultList = sp.getResultList();

we get the below error when we run the above code.

I see that hibernate is registering the out parameter as cstmt.registerOutParameter(1, Types.REF_CURSOR) when we use ojdbc8.

hibernate registers the out parameter as -10 when we use ojdbc7.

This code worked with ojdbc7.jar

We upgraded weblogic to 12.2.1.3 and it comes with ojdbc8 so we have to use ojdbc8 and make the above code work.

Do we need to upgrade database server to 12.2 to make the above code work with ojdbc8.

Thanks,

Archana.

Aketi Jyuuzou

I like case expression :D

with table1 as  (
select 'xx' col1 from dual union all 
select 'yy' col1  from dual union all
select '01' col1  from dual union all
select '02' col1  from dual union all
select '03' col1  from dual union all
select '04' col1  from dual   ),
table2 as (
select 1 col1 from dual union all
select 2 col1 from dual union all
select 3 col1 from dual union all
select 4 col1 from dual )
select *
  from table1 a,table2 b
 where case when not RegExp_Like(a.col1,'^[0-9]+$')
            then 0
            when to_number(a.col1) = b.col1 then 1
            else 0 end = 1;

CO  COL1
--  ----
01     1
02     2
03     3
04     4

Frank Kulash

Answer

user642297 wrote:
Hello experts,

I have two tables ...

create table1
(
col1 varchar2(20)
);

create table2
(
col1 number(10)
);

with their data as below

Above, you said that table2.col1 was a NUMBER.
Below, it's a VARCHAR2:

with table1 as
(  select 'xx' col1 from dual union all 
select 'yy' col1  from dual union all
select '01' col1  from dual union all
select '02' col1  from dual union all
select '03' col1  from dual union all
select '04' col1  from dual 
),table2 as 
(
select '01' col1 from dual union all
select '02' col1 from dual union all
select '03' col1 from dual union all
select '04' col1 from dual 
)

Woudn't a better approximation of your real tables be:

   ),table2 as 
    (
      select 1 col1 from dual union all
      select 2 col1 from dual union all
      select 3 col1 from dual union all
      select 4 col1 from dual 
     )

My question
how would i join table1.col1 to table2.col1?? since they are different data types ....i tried to use To_char but then i got 0 zero's,
is ther is any functions i can use ??

Post your code. I think the best way is to use TO_CHAR, but maybe you were using it differently.

SELECT	*
FROM	table1
JOIN	table2	ON	NVL ( LTRIM (table1.col1, '0')
			    , '0'
			    )	= TO_CHAR (table2.col1)
;

This assumes that table1.col1 never contains decimal points, unary + signs, exponents, or other things like that.
It will handle negative numbers, but if table1.col1 contains leading 0's after the minus sign (e.g. '-0012'), then the solution is a little more complicated.

Marked as Answer by 645300 · Sep 27 2020

645300

Thanks you so much frank!! That worked to me !!

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 5 2018

Added on Jun 6 2018

#java-database-connectivity-jdbc

4 comments

5,179 views

Java Database Connectivity (JDBC)

REF_CURSOR with ojdbc8 gives Invalid column type: 2012 error

Comments

Post Details