Declaring type ref cursor in datagrip for oracle database

Answered

Hi,

i am having issues with declaring ref cursor types in datagrip, the package and the function that uses the ref cursor is working 100% fine in SQL developer, however, i am unable to figure out what is the issue in datagrip with this?!, i am unable to execute/run any function/procedure that uses this cursor.

the weird thing is, it works fine anywhere else, toad/sql developer.

any help would be appreciated.

5 comments
Comment actions Permalink

This is what i should get, from sql developer:

SELECT USERS_STATUS_PKG.USERS_STATUS_GET_LIST FROM DUAL;

{<STATUS_ID=1,STATUS_DESCRIPTION=ACTIVE>,<STATUS_ID=2,STATUS_DESCRIPTION=INACTIVE>,}

meanwhile, in datagrip:

SELECT CLINIC_SYSTEM.USERS_STATUS_PKG.USERS_STATUS_GET_LIST FROM DUAL;
Proxy[RemoteResultSet,RemoteObjectInvocationHandler[UnicastRef [liveRef: [endpoint:[](remote),objID:[-4aef11c:16df7876e57:79de, 868006117179217535]]]]]

Any ideas?!
0
Comment actions Permalink

H Habarah,

It looks like IDE can't parse reference correctly.

To execute your statement you need to change execution behavior a bit:

 

 

0
Comment actions Permalink

Could you provide a minimal working example to reproduce the issue with

SELECT CLINIC_SYSTEM.USERS_STATUS_PKG.USERS_STATUS_GET_LIST FROM DUAL;

?

0
Comment actions Permalink

Hi vasily,

So, i checked my "execution" options, it is already set at "exactly as statements"

 

here is my code:

---TABLE
CREATE TABLE USERS_STATUS
(
STATUS_ID NUMBER GENERATED AS IDENTITY (MAXVALUE 999)
CONSTRAINT USERS_STATUS_PK
PRIMARY KEY,
STATUS_DESCRIPTION VARCHAR2(20) NOT NULL
CONSTRAINT USERS_STATUS_STATUS_DESCRIPTION_UK
UNIQUE
);

--FILL DATA INTO TABLE
INSERT INTO USERS_STATUS(STATUS_DESCRIPTION)
VALUES ('ACTIVE');
INSERT INTO USERS_STATUS(STATUS_DESCRIPTION)
VALUES ('INACTIVE');

---PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE USERS_STATUS_PKG AS
TYPE RC_USERS_STATUS IS REF CURSOR RETURN USERS_STATUS%ROWTYPE;
FUNCTION USERS_STATUS_GET_LIST RETURN RC_USERS_STATUS;
END USERS_STATUS_PKG;


---PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY USERS_STATUS_PKG AS
FUNCTION USERS_STATUS_GET_LIST RETURN RC_USERS_STATUS IS
C_USERS_STATUS RC_USERS_STATUS;
BEGIN
OPEN C_USERS_STATUS FOR SELECT *
FROM USERS_STATUS;
RETURN C_USERS_STATUS;
END USERS_STATUS_GET_LIST;
END USERS_STATUS_PKG;

And then when i try the statement, it gives that issue.
SELECT CLINIC_SYSTEM.USERS_STATUS_PKG.USERS_STATUS_GET_LIST FROM DUAL;

Proxy[RemoteResultSet,RemoteObjectInvocationHandler[UnicastRef [liveRef: [endpoint:[xxxxxxxx](remote),objID:[-5e6da5ba:16e2609289a:-6c6f, -123552891533211835]]]]]
1
Comment actions Permalink

@H Habarah,

Thank you for detailed description.
I reproduced the issue and found similar one https://youtrack.jetbrains.com/issue/DBE-6807 you can vote for.

0

Please sign in to leave a comment.