Calling a Stored Function (That Returns an Array of a User-Defined Type) in Oracle Across a Database Link

Calling a stored function (that returns an array of a user-defined type) in oracle across a database link

What you're trying is the correct syntax as far as I know, but in any case it would not work due to the return type being user-defined, as you suspect.

Here's an example with a built-in pipelined function. Calling it locally works, of course:

SELECT * FROM TABLE(dbms_xplan.display_cursor('a',1,'ALL'));

Returns:

SQL_ID: a, child number: 1 cannot be found 

Calling it over a database link:

SELECT * FROM TABLE(dbms_xplan.display_cursor@core('a',1,'ALL'));

fails with this error:

ORA-30626: function/procedure parameters of remote object types are not supported

Possibly you are getting the ORA-904 because the link goes to a specific schema that does not have access to the package. But in any case, this won't work, even if you define an identical type with the same name in your local schema, because they're still not the same type from Oracle's point of view.

You can of course query a view remotely, so if there is a well-defined set of possible parameters, you could create one view for each parameter combination and then query that, e.g.:

CREATE VIEW display_cursor_a_1_all AS
  SELECT * FROM TABLE(dbms_xplan.display_cursor('a',1,'ALL'))
  ;

If the range of possible parameter values is too large, you could create a procedure that creates the needed view dynamically given any set of parameters. Then you have a two-step process every time you want to execute the query:

EXECUTE  package.create_view@remote(parameters)

SELECT * FROM created_view@remote;

You have to then think about whether multiple sessions might call this in parallel and if so how to prevent them from stepping on each other.

How to call function using dblink in oracle?

The function is declared with a quoted identifier:

CREATE OR REPLACE FUNCTION "MC"."Get_REFTYPES"(

You're calling it unquoted, as mc.Get_REFTYPES@READ_MAIN_MCNAV(...). One of the irritations that quoted identifiers cause is that you always have to refer to them with quotes and exactly the same case as the original definition, so you would need to do:

select column_value
from table(mc."Get_REFTYPES"@READ_MAIN_MCNAV(param1, param2, param3)));

You don't need to quote mc because a quoted uppercase identifier behaves like an unquoted one anyway.

However, even with that corrected this won't do what you want. As discussed in this answer you can't call a function that returns a user defined type like RefType_T, and will get "ORA-30626: function/procedure parameters of remote object types are not supported".

How to call oracle stored procedure which include user-defined type in java?

Setting up a link between Oracle SQL objects and java objects manually is not a trivial task. In particular, arrays (or nested tables) of user-defined objects are more complex to pass from java to Oracle than arrays of standard datatypes. In other words, it is easier to call a procedure with signature:

(TABLE OF NUMBER, TABLE OF NUMBER, TABLE OF NUMBER)`

than a procedure whose signature is:

(TABLE OF (NUMBER, NUMBER, NUMBER))   <- your case

You can write a wrapper around your procedure to transform the second case into the first case.

---

That being said, it is by far not impossible to map your procedure. The following example is largely inspired by a post by Tom Kyte. Tom describes how to map a TABLE OF NUMBER using oracle.sql.ARRAY. In your case we will also have to use oracle.sql.STRUCT to map the IDS SQL object.

You may also want to browse the Oracle JDBC doc, in particular the chapter Working with Oracle Object Types.

First is a setup similar to yours:

SQL> CREATE OR REPLACE TYPE IDS AS OBJECT ( id1 NUMBER, id2 NUMBER, id3 NUMBER );
  2  /
Type created

SQL> CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS;
  2  /
Type created

SQL> CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS
  2  BEGIN
  3     FOR i IN 1 .. p_ids.COUNT LOOP
  4        dbms_output.put_line(p_ids(i).id1
  5                             || ',' || p_ids(i).id2
  6                             || ',' || p_ids(i).id3);
  7     END LOOP;
  8  END getInfo;
  9  /     
Procedure created

This is the java procedure:

SQL> CREATE OR REPLACE
  2  AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  import oracle.sql.*;
  7  import oracle.jdbc.driver.*;
  8  
  9  public class ArrayDemo {
 10  
 11     public static void passArray() throws SQLException {
 12  
 13        Connection conn =
 14           new OracleDriver().defaultConnection();
 15  
 16  
 17        StructDescriptor itemDescriptor =
 18           StructDescriptor.createDescriptor("IDS",conn);
 19  
 20        Object[] itemAtributes = new Object[] {new Integer(1),
 21                                               new Integer(2),
 22                                               new Integer(3)};
 23        STRUCT itemObject1 = new STRUCT(itemDescriptor,conn,itemAtributes);
 24  
 25        itemAtributes = new Object[] {new Integer(4),
 26                                      new Integer(5),
 27                                      new Integer(6)};
 28        STRUCT itemObject2 = new STRUCT(itemDescriptor,conn,itemAtributes);
 29  
 30        STRUCT[] idsArray = {itemObject1,itemObject2};
 31  
 32        ArrayDescriptor descriptor =
 33           ArrayDescriptor.createDescriptor( "IDS_TABLE", conn );
 34  
 35        ARRAY array_to_pass =
 36           new ARRAY( descriptor, conn, idsArray );
 37  
 38        OraclePreparedStatement ps =
 39           (OraclePreparedStatement)conn.prepareStatement
 40           ( "begin getInfo(:x); end;" );
 41  
 42        ps.setARRAY( 1, array_to_pass );
 43        ps.execute();
 44  
 45     }
 46  }
 47  /
Java created

Let's call it:

SQL> CREATE OR REPLACE
  2  PROCEDURE show_java_calling_plsql
  3  AS LANGUAGE JAVA
  4  NAME 'ArrayDemo.passArray()';
  5  /
Procedure created

SQL> exec show_java_calling_plsql ;
1,2,3
4,5,6

PL/SQL procedure successfully completed

Retrieving user-defined record from procedure PLSQL

You have defined a record type in the package spec. That means you can use it in other programs, just like you can call the procedure.

But what you've done is declare a second record type in your anonymous block. They look the same to you, because they've got the same names and structure. But to the compiler they're different, because they are different things. The procedure wants the record type defined in the package.

So the fix is quite simple:

DECLARE
  v_rec college_package.class_type;
BEGIN
   college_package.get_class_infor(:class_id, v_rec);
   DBMS_OUTPUT.PUT_LINE('** Class id:     ' || v_rec.class_id);
   DBMS_OUTPUT.PUT_LINE('** Instructor:   ' || v_rec.i_first || ' ' || v_rec.i_last);
   DBMS_OUTPUT.PUT_LINE('** Course Title: ' || v_rec.course);
   DBMS_OUTPUT.PUT_LINE('** Department:   ' || v_rec.dept);
END;

Oracle sql types over dblink

I've had similar problems in the past. Then I came to the conclusion that fundamentally Oracle's db links are "broken" for anything but simple SQL types (especially UDT's, CLOBS may have problems, XMLType may as well). If you can get the OID solution working then good luck to you.

The solution I resorted to was to use a Java Stored procedure, instead of the DB Link.

Characteristics of the Java Stored Procedure:

1. Can return a "rich set of types", just about all of the complex types (UDT's, tables/arrays/varrays) see Oracle online documentation for details. Oracle does a much better job of marshalling complex (or rich) types from java, than from a DBLink.
2. Stored Java can acquire the "default connection" (runs in the same session as the SQL connection to the db - no authentication issues).
3. Stored Java calls the PL/SQL proc on the remote DB, and the java JDBC layer does the marshaling from the remote DB.
4. Stored Java packages up the result and returns the results to the SQL or PL/SQL layer.

It's a bit of work, but if you have a bit of java, you should be able to "cut and paste" a solution together from the Oracle documentation and sample.

I hope this helps.

PHP - PL/SQL: How to read array (as OUT parameter from Oracle procedure) into PHP

What you have in oracle is not a just an array, it is a array of records.... So a standard array in PHP is not going to be able to handle it.

As per the below question here on Stack Overflow you need to tell PHP what the Type is going to look like

PHP: Binding variable to table type output parameter

So use the below (substituting your type and schema)

$table_output = oci_new_collection($conn,'some_table_type','schema');

The other question also has a link to a good resource for finding more information about this.

As pointed out by @MT0 you will have to change the way that you are defining the types as well. You can iether change it to object as suggested or leave it as record, but the major change will be moving the declaration outside of you package.

PHP will not be able to see them if they are only defined in the package.

Passing an array of data as an input parameter to an Oracle procedure

This is one way to do it:

SQL> set serveroutput on
SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
  2  /

Type created

SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS
  2  BEGIN
  3    FOR i IN 1..t_in.count LOOP
  4      dbms_output.put_line(t_in(i));
  5    END LOOP;
  6  END;
  7  /

Procedure created

SQL> DECLARE
  2    v_t MyType;
  3  BEGIN
  4    v_t := MyType();
  5    v_t.EXTEND(10);
  6    v_t(1) := 'this is a test';
  7    v_t(2) := 'A second test line';
  8    testing(v_t);
  9  END;
 10  /

this is a test
A second test line

To expand on my comment to @dcp's answer, here's how you could implement the solution proposed there if you wanted to use an associative array:

SQL> CREATE OR REPLACE PACKAGE p IS
  2    TYPE p_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  3  
  4    PROCEDURE pp (inp p_type);
  5  END p;
  6  /

Package created
SQL> CREATE OR REPLACE PACKAGE BODY p IS
  2    PROCEDURE pp (inp p_type) IS
  3    BEGIN
  4      FOR i IN 1..inp.count LOOP
  5        dbms_output.put_line(inp(i));
  6      END LOOP;
  7    END pp;
  8  END p;
  9  /

Package body created
SQL> DECLARE
  2    v_t p.p_type;
  3  BEGIN
  4    v_t(1) := 'this is a test of p';
  5    v_t(2) := 'A second test line for p';
  6    p.pp(v_t);
  7  END;
  8  /

this is a test of p
A second test line for p

PL/SQL procedure successfully completed

SQL> 

This trades creating a standalone Oracle TYPE (which cannot be an associative array) with requiring the definition of a package that can be seen by all in order that the TYPE it defines there can be used by all.

---

Related Topics