Does oracle jdbc driver only support oracle database (21c,18c,19c etc) or does it support the oracle virtualbox as well?

See also questions close to this topic

• How to remove non-numeric characters and search for phone number?

  SELECT * FROM Customer WHERE REGEXP_REPLACE(HOME_PHONE, '[^0-9]', '') LIKE '%1234567'

  This is my SQL query and I get error, Some fields have more than 7 character that’s why I am using % for searching last 7 characters in phone number. FYI I’m using oracle database.
• Multi-part geometries: Get line & part number when extracting parts (via SDO_UTIL.EXTRACT_ALL)

  I have multi-part SDO_GEOMETRIES in Oracle 18c.

  I can extract the parts as individual rows using the SDO_UTIL.EXTRACT_ALL() function:

  with cte as (
  --Each part is wrapped in brackets and separated by commas: (p1),(p2)
  select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))')    as shape from dual union all --3D: (X,Y,Z)
  select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') as shape from dual union all --2D: (X,Y)
  select sdo_geometry('MULTILINESTRING((1 1, 2 2))')                       as shape from dual           --2D: (X,Y)
      )
  select
      a.object_value,
      sdo_util.to_wktgeometry(a.object_value)
  from
      cte,
      table(sdo_util.extract_all(shape)) a
  
  OBJECT_VALUE         SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)                                         
  -------------------- ---------------------------------------
  [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0)                                           
  [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0)                                           
  [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0)                                                   
  [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0)                                                   
  [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0)                                                   
  [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)
  

  db<>fiddle here

  ---

  I want to add the following columns to the query:

  • MULTILINE_NUM (the original multi-part line number)
  • PART_NUM

  MULTILINE_NUM PART_NUM OBJECT_VALUE         SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)                               
  
  ------------- -------- -------------------- ---------------------------------------
              1        1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0)                                           
              1        2 [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0)                                           
              2        1 [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0)                                                   
              2        2 [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0)                                                   
              2        3 [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0)                                                   
              3        1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)
  

  When adding those columns, I don't want to "fake it" after-the-fact using window functions, etc.

  I want to extract that information directly from the geometries, to ensure the numbers are correct (i.e., part number shouldn't be arbitrary; it should reflect the part number order from the original geometry).

  ---

  Is there a way to get the MULTILINE_NUM and PART_NUM from the table(sdo_util.extract_all(shape)) query?
• Java queryForStream use cases

  What are the functionalities in using Java queryForStream? I generally use jbdcTemplate.query to obtain data from a database. I'm trying to understand the specific use cases for queryForStream. There is not a lot of documentation written around this.
• Specify VARCHAR/NVARCHAR length in CallableStatement (mssql-jdbc driver)

  I am using SQL Server 2017 Ent with Spring Framework (5.3.19) and mssql-jdbc driver (8.4.0.jre8). Is there a way to specify VARCHAR/NVARCHAR length in CallableStatement ?

  Pseudo-code

  CallableStatement callableStatement = 
  conn.prepareCall(concatenatedCallableStmt (index,paramVal));
  
  concatenatedCallableStmt (int index,Object parameterValue) 
  {
     case Types.VARCHAR:
        //stmt.setString(index, String.valueOf(parameterValue));
         stmt.setObject(index,parameterValue,Types.VARCHAR ,String.valueOf(parameterValue).length());
      break;
  }
  

  Final CallableStatement for this execution :

  {? = CALL [dbo].[<SP_NAME>] (@P1=?,@P2=?,@P3=?,@P4=?)}

  All the params are getting submitted with nvarchar(4000) to the database if I use setString or setObject

  Profiler screenshot:

  The SP in the DB has VARCHAR(60) for the concerned parameters.

  Is there a way I could specify the length of the parameters (like varchar(60) instead of nvarchar(4000)) in setString/setObject or any other way?