Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Oracle 23c JSON Type #22983

Closed
melisha-git opened this issue Feb 28, 2024 · 6 comments · Fixed by #33149
Closed

Oracle 23c JSON Type #22983

melisha-git opened this issue Feb 28, 2024 · 6 comments · Fixed by #33149
Assignees
@HocKu7 @Matvey16
Labels
bug pkg:CE pkg:EE sp:2 trivial x:oracle xf:data xp:normal
Milestone
24.1.0

Comments

@melisha-git
Copy link

melisha-git commented Feb 28, 2024 •
edited

Description

When I create and insert table

  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document JSON);
   INSERT INTO j_purchaseorder2
  VALUES (SYS_GUID(),
          to_date('30-DEC-2014'),
          '{"PONumber"             : 1600,
            "Reference"            : "ABULL-20140421",
            "Requestor"            : "Alexis Bull",
            "User"                 : "ABULL",
            "CostCenter"           : "A50",
            "ShippingInstructions" : "{...}",
            "Special Instructions" : "null",
            "AllowPartialShipment" : "true",
            "LineItems"            : "[...]"}');

I take error in table

	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetImpl.getAttributeValue(JDBCResultSetImpl.java:150)
	at org.jkiss.dbeaver.model.impl.data.DefaultValueHandler.fetchValueObject(DefaultValueHandler.java:49)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataReceiver.fetchRow(ResultSetDataReceiver.java:126)
	at org.jkiss.dbeaver.model.impl.jdbc.struct.JDBCTable.readData(JDBCTable.java:199)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:114)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: ORA-18722: Не задано свойство подключения oracle.jdbc.jsonDefaultGetObjectType
	at oracle.jdbc.driver.JsonAccessor.getObject(JsonAccessor.java:287)
	at oracle.jdbc.driver.GeneratedStatement.getObject(GeneratedStatement.java:196)
	at oracle.jdbc.driver.GeneratedScrollableResultSet.getObject(GeneratedScrollableResultSet.java:322)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetImpl.getAttributeValue(JDBCResultSetImpl.java:147)
	... 9 more

image

DBeaver Version

23.3.5 community

Operating System

Windows 11

Database and driver

Oracle
image

Steps to reproduce

No response

Additional context

No response
@melisha-git
Copy link
Author

If drivers 12.2.0.1, then json type is worked
image

@ShadelessFox
Copy link
Member

Hello @melisha-git,

It seems that they have changed something in the driver.

In the connection configuration, go to the "Driver properties" tab and add the following property:

Name Value
oracle.jdbc.jsonDefaultGetObjectType oracle.sql.json.OracleJsonValue

Please let me know if it helps.

@melisha-git
Copy link
Author

@ShadelessFox Thanks for the speed and solution. It helped me. Can I close the ticket?

@ShadelessFox
Copy link
Member

Thanks for the response. No, please keep it open. We want to specify this parameter in DBeaver by default.

@Numerok
Copy link

Numerok commented Apr 17, 2024

@ShadelessFox Hello, is there any updates on this issue?
Solution that you posted in this issue worked only for reading the column but when trying to write to it it says:
Object parameter [{"test":"test"}] binding not supported

Oracle 21.13.0
OJDBC11 23.2.0.0

@ShadelessFox
Copy link
Member

The issue is not yet assigned to a particular milestone.

Thanks for the information. This error is reported by DBeaver, so it should be pretty trivial to fix it on our side.

@ShadelessFox ShadelessFox added this to the 24.0.5 milestone Apr 17, 2024
HocKu7 added a commit that referenced this issue May 16, 2024
@HocKu7
#22983 fix oracle 23 json
22dc119
HocKu7 added a commit that referenced this issue May 16, 2024
@HocKu7
#22983 fix oracle 23 json
a591e81
HocKu7 added a commit that referenced this issue May 16, 2024
@HocKu7
#22983 fix oracle 23 json
9c55386
@HocKu7 HocKu7 linked a pull request May 16, 2024 that will close this issue
dbeaver/dbeaver#22983 fix oracle 23c json type #33149
Merged
HocKu7 added a commit that referenced this issue May 16, 2024
@HocKu7
#22983 fix oracle 23 json
0bf0961
HocKu7 added a commit that referenced this issue May 16, 2024
@HocKu7
#22983 fix oracle 23 json
279a247
@arhayka arhayka modified the milestones: 24.0.5, 24.1.0 May 19, 2024
@Matvey16 Matvey16 self-assigned this May 21, 2024
Matvey16 added a commit that referenced this issue May 22, 2024
@Matvey16
Merge branch 'devel' into #22983-fix-oracle-23c-json-type
e24235c
Matvey16 added a commit that referenced this issue May 23, 2024
@Matvey16
Merge branch 'devel' into #22983-fix-oracle-23c-json-type
b482cb5
serge-rider pushed a commit that referenced this issue May 23, 2024
@HocKu7 @Matvey16
#22983 fix oracle 23c json type (#33149)
3625527 
* #22983 fix oracle 23 json

* #22983 fix oracle 23 json

* #22983 fix oracle 23 json

---------

Co-authored-by: Matvey16 <82543000+Matvey16@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@HocKu7 HocKu7

@Matvey16 Matvey16

Labels
bug pkg:CE pkg:EE sp:2 trivial x:oracle xf:data xp:normal
Projects
None yet
Milestone
24.1.0
Development

Successfully merging a pull request may close this issue.

dbeaver/dbeaver#22983 fix oracle 23c json type dbeaver/dbeaver
8 participants
@Numerok @serge-rider @arhayka @E1izabeth @ShadelessFox @HocKu7 @Matvey16 @melisha-git