SYS_REFCURSOR

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

SYS_REFCURSOR

Postby me_lucky » Mon Jun 10, 2013 12:13 pm

Hi Tim,

I have the below package to set the CONTEXT.
due to some restriction when calling it for JAVA, I need to make use of SYS_REFCURSOR.

How do I modify the below package to use SYS_REFCURSOR as input value?

CREATE OR REPLACE PACKAGE CONTEXT_CAR IS
PROCEDURE P_SET_CAR(P_VAL VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY CONTEXT_CAR IS
PROCEDURE P_SET_CAR(P_VAL VARCHAR2) IS
BEGIN
DBMS_SESSION.set_context(namespace => 'CAR_CONTEXT',
attribute => 'CAR_ID',
VALUE => P_VAL);
END;
END;

Thanks in Advance,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: SYS_REFCURSOR

Postby Tim... » Mon Jun 10, 2013 1:00 pm

Hi.

I don't understand what you are trying to achieve here. I can tell you how to use SYS_REFCURSOR.

http://www.oracle-base.com/articles/mis ... rdsets.php

But I'm not sure this answers your question.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17957
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: SYS_REFCURSOR

Postby me_lucky » Tue Jun 11, 2013 4:57 am

Hi Tim,

Sorry for the confusion.

Actually am creating context using :

Create context CAR_CONTEXT using CONTEXT_CAR;

CREATE OR REPLACE PACKAGE CONTEXT_CAR IS
PROCEDURE P_SET_CAR(P_VAL VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY CONTEXT_CAR IS
PROCEDURE P_SET_CAR(P_VAL VARCHAR2) IS
BEGIN
DBMS_SESSION.set_context(namespace => 'CAR_CONTEXT',
attribute => 'CAR_ID',
VALUE => P_VAL);
END;
END;

Am able to set the context like below:
Exec CONTEXT_CAR.P_SET_CAR ('UserName');

But when am trying to call the same package using hibernate (From application), Its not working. after googling I understood that it will only consider the argument type as SYS_REFCURSOR (In my package CONTEXT_CAR.P_SET_CAR type is varchar2) .

I want to modify the above package to use the input parameter value type as SYS_REFCURSOR instead of varchar2. How can I achieve that , I tried but I didn't succeed .

Hope you have understood my requirement. Pl help me.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: SYS_REFCURSOR

Postby Tim... » Tue Jun 11, 2013 7:11 am

Hi.

OK. Here is the code.

Code: Select all
CREATE CONTEXT car_context USING context_car;

CREATE OR REPLACE PACKAGE context_car IS
PROCEDURE p_set_car(p_val SYS_REFCURSOR);
END;
/

CREATE OR REPLACE PACKAGE BODY context_car IS

PROCEDURE p_set_car(p_val SYS_REFCURSOR) IS
  l_val VARCHAR2(32767) := NULL;
BEGIN
  FETCH p_val INTO l_val;
  CLOSE p_val;
 
  IF l_val IS NOT NULL THEN
    DBMS_SESSION.set_context(namespace => 'CAR_CONTEXT',
                             attribute => 'CAR_ID',
                             VALUE     => l_val);
  END IF;
END;

END;
/


You can call it like this.

Code: Select all
DECLARE
  l_val SYS_REFCURSOR;
BEGIN
  OPEN l_val FOR
    SELECT 'UserName' FROM dual;
   
  CONTEXT_CAR.P_SET_CAR(l_val);
END;
/


You can see the context value has been set.

Code: Select all
SELECT SYS_CONTEXT('CAR_CONTEXT', 'CAR_ID') FROM dual;

SYS_CONTEXT('CAR_CONTEXT','CAR_ID')
---------------------------------------
UserName

1 row selected.

SQL>


Seems an odd thing to do, but it works. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17957
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: SYS_REFCURSOR

Postby me_lucky » Tue Jun 11, 2013 11:50 am

Hi Tim,

Thanks a ton for the immediate response.

Thanks & Regards,
Lucky.
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: SYS_REFCURSOR

Postby Tim... » Tue Jun 11, 2013 11:57 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17957
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest