Home » SQL & PL/SQL » SQL & PL/SQL » json filter unusual behaviour (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)
json filter unusual behaviour [message #676300] Wed, 29 May 2019 04:54 Go to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Dear All,

I faced unusual behaviour while putting filter in where clause. It is working fine when direct value(MILLER) is provided but throws the below error in SQL Developer and database getting disconnected when the value stored in the variable i.e l_ename used. Please help.

Error report -
No more data to read from socket

ALTER TABLE EMP ADD JSON_DATA VARCHAR2(100);
update EMP SET JSON_DATA ='{"data": {"ename": "MILLER"}}'WHERE ENAME ='MILLER' ;
update EMP SET JSON_DATA ='{"data": {"ename": "KING"}}'WHERE ENAME ='KING' ;
commit;

DECLARE
    l_ename    VARCHAR2(20) := 'MILLER';
    l_exists   NUMBER;
BEGIN
    SELECT
        1
    INTO l_exists
    FROM
        emp,
        JSON_TABLE ( json_data, '$.data'
                COLUMNS (
                    newename VARCHAR2 PATH '$.ename'
                )
            )
        t
    WHERE
        deptno = 10
        AND t.newename = 'MILLER';--l_ename 

END;    

Regards.
Re: json filter unusual behaviour [message #676302 is a reply to message #676300] Wed, 29 May 2019 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried running it in sqlplus?
Re: json filter unusual behaviour [message #676303 is a reply to message #676302] Wed, 29 May 2019 07:39 Go to previous messageGo to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Thanks CookieMonster,

You have guessed it correctly. I have tried in https://livesql.oracle.com as there is no access in sqlplus in office and its working fine. The what would be the problem in sql developer.

Regards.
Re: json filter unusual behaviour [message #676304 is a reply to message #676303] Wed, 29 May 2019 07:48 Go to previous message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
Presumably it's a SQL Developer bug - you need to talk to oracle support at this point (or upgrade to the latest version if you don't already have it).

Why on earth do you not have sqlplus when you do have SQL Developer?
Previous Topic: can we call an sql script from the code of a stored procedure
Next Topic: varchar2 byte or char semantics
Goto Forum:
  


Current Time: Thu Nov 21 23:04:10 CST 2019