Pass oracle object type to java stored procedure
NickName:Simas.B Ask DateTime:2017-01-24T02:14:13

Pass oracle object type to java stored procedure

Is is possible to pass oracle object type to java stored procedure. Here's what i have so far.

Java stored procedure:

create or replace and compile java source named Example as
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Example {
    
    public static void test(ExObj obj) {
        System.out.println(obj.client);
    }
    
    public static class ExObj implements SQLData {

        public String client = "";

        public String sql_type = "EXAMPLE_OBJECT";

        public ExObj(String client, String sql_type) {
            this.client = client;

            this.sql_type = sql_type;
        }

        /* IMPLEMENTS SQLData */

        public void setSqlType(String sqlType) {
            sql_type = sqlType;
        }

        public void writeSQL(SQLOutput stream) throws SQLException {
            stream.writeString(this.client);
        }

        public String getSQLTypeName() {
            return sql_type;
        }

        public void readSQL(SQLInput stream, String sqlTypeName) throws SQLException {
            sql_type = sqlTypeName;

            this.client = stream.readString();
        }

    }
}

Oracle procedure:

CREATE OR REPLACE PROCEDURE example(ex_obj in example_object)
AS LANGUAGE JAVA
NAME 'Example.test(java.sql.Struct)';

Oracle object type:

create or replace type example_object as object(
  client varchar2(40)
)

Script to call procedure:

declare
  l_output DBMS_OUTPUT.chararr;
  l_lines  INTEGER := 1000;
  
  l_obj example_object;
begin
  DBMS_OUTPUT.enable(1000000);
  DBMS_JAVA.set_output(1000000);
  
  l_obj := example_object('client');
  example(l_obj);

  DBMS_OUTPUT.get_lines(l_output, l_lines);

  FOR i IN 1 .. l_lines LOOP
    -- Do something with the line.
    -- Data in the collection - l_output(i)
    DBMS_OUTPUT.put_line(l_output(i));
  END LOOP;
end;

When running script i get:

ORA-29531: no method test in class Example

ORA-06512: at "EXAMPLE", line 1

ORA-06512: at line 17

I presume it's because oracle object_type doesn't map to java class correctly. Is there a way to do this and if so what am i doing wrong?

Copyright Notice:Content Author:「Simas.B」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/41812935/pass-oracle-object-type-to-java-stored-procedure

More about “Pass oracle object type to java stored procedure” related questions

Pass oracle object type to java stored procedure

Is is possible to pass oracle object type to java stored procedure. Here's what i have so far. Java stored procedure: create or replace and compile java source named Example as import java.sql.SQLD...

Show Detail

Passing ArrayList to Oracle Stored Procedure in Java

Is there a way to pass a Java ArrayList object as a parameter to an Oracle Stored Procedure? I have seen examples of passing an Array object to an Oracle Stored Procedure, but not an ArrayList obj...

Show Detail

pass object from Java to Oracle procedure

To pass an array to oracle procedure we use ArrayDiscriptor and ARRAY objects. What objects do I have to use to pass an object to a stored procedure?

Show Detail

How to pass java list of objects to oracle stored procedure (Cannot use Spring)

I have a list of POJO's that I want to pass to an Oracle Stored Procedure and then loop through that list in the stored proc and run update statements I've tried using a StructDescriptor but I keep

Show Detail

Passing a list of values (Oracle UserDefinedType) to Oracle stored procedure using Spring Boot JPA

I am searching for a solution for Passing a list of values (Array or List) to an Oracle Stored Procedure using Spring Boot JPA mechanism. Here we actually have an Oracle User Defined Type (UDT) whi...

Show Detail

Pass an Java array to Oracle stored procedure using JdbcTemplate

I am trying to pass an integer array to an Oracle stored procedure. This is my Oracle side: -- Package Declaration TYPE num_array IS TABLE OF NUMBER; PROCEDURE test_arrays(p_array IN num_array); --

Show Detail

Can Oracle DBMS return a Java object from a Java stored procedure call?

Can the Oracle database return a Java object from the return values of a Java stored procedure call? I would like to query the Oracle database with a call to a java stored procedure and receive back

Show Detail

Is it possible to pass a table of rowtype from java to an oracle stored procedure?

I got this type in oracle: TYPE "RequestTable" IS TABLE OF "Requests"%ROWTYPE; I have used this type as an IN(or out) parameter of some stored procedures, like this: create or replace PROCEDURE ...

Show Detail

Pass an array of object to Stored procedure with Oracle ManagedDataAccess.dll

I am upgrading my application to Oracle.ManagedDataAccess. I want to pass an array of objects to a stored procedure. Following is my SP: CREATE OR REPLACE TYPE MATCHING_CRITERIA_LIST IS TABLE OF

Show Detail

how to pass a java object to oracle stored procedure with following details

I have my ORACLE table with structure as desc extraction_log1 Name Null Type ...

Show Detail