Calling stored procedure with local custom type @ dblink

Question

Newbie here. I am trying to execute stored procedure located at DBLINK. Here's my code:

DECLARE
  TYPE ARRAY_VARCHAR2 IS TABLE OF VARCHAR2(32767);
  Vparams ARRAY_VARCHAR2 := ARRAY_VARCHAR2('p1', 'p2', 'p3');
  Vvalues ARRAY_VARCHAR2 := ARRAY_VARCHAR2('v1', 'v2', 'v3');
  o_result_clob CLOB;
  o_result VARCHAR2(32767);
  result_cursor SYS_REFCURSOR;

BEGIN
  EXECUTE IMMEDIATE 'CREATE TYPE ARRAY_VARCHAR2 AS TABLE OF VARCHAR2(32767)';
  
  EXECUTE IMMEDIATE '
    DECLARE
      p_result CLOB;
    BEGIN
      PACKAGE.PROCEDURE@DBLINK(
        Icommand=> ''MY_COMMAND'', 
        Iparam_List => :arr,
        Ivalue_List => :arrv,
        O_RESULT => p_result
        );
      :res := p_result;
    END;'
  USING IN Vparams, IN Vvalues, OUT o_result_clob;

  o_result := DBMS_LOB.SUBSTR(o_result_clob, 4000, 1);

  DBMS_OUTPUT.PUT_LINE('Result: ' || o_result);

  CLOSE result_cursor;
END;

And this is what's happening (the error):
ORA-06550: line 12, column 22: PLS-00457: expressions have to be of SQL types ORA-06550: line 1, column 402: PL/SQL: Statement ignored

For someone like me this is so much complex code, it would be a great pleasure if you could assist me, thanks in advance. (At least to give some structural template)

Answer 1

EXECUTE IMMEDIATE can only pass in SQL types, not PL/SQL types. You defined Vparams, Vvalues as type ARRAY_VARCHAR2 but that is a PL/SQL type you defined in the first line. To get EXECUTE IMMEDIATE to work with this, you'd normally need to use a type created with the CREATE TYPE command (not in PL/SQL):

CREATE TYPE ARRAY_VARCHAR2 IS TABLE OF VARCHAR2(4000);

Now it's part of the data dictionary and can be used in SQL statements and in EXECUTE IMMEDIATE. Also, I'm sure you'll notice immediately, in SQL types your varchar2 is limited to 4000, which may be an issue for you. But now you can pass in those Vparams, Vvalues arrays.

However, your situation is further complicated from the fact that you are ultimately needing to pass these to a remote database, and a remote database doesn't know anything about locally defined SQL types.

Solutions:

1. Create a package header on the remote database with a PL/SQL type defined in it. Type your local variables with a reference to those remote packaged types, using the @dblink. Call the procedure directly, not via EXECUTE IMMEDIATE. There's nothing about your call above that would require EXECUTE IMMEDIATE.

2. If you must use EXECUTE IMMEDIATE because you plan to abstract the procedure name or the dblink (you aren't doing this yet in the example code you've given, but perhaps you intend to), then you can create a SQL type locally, pass that into the EXECUTE IMMEDIATE, and within the DECLARE section of the PL/SQL block inside the EXECUTE IMMEDIATE you can declare another variable of the remote package type @dblink, then write some code to loop through the SQL-type input array and build up the new PL/SQL type array with it, then submit that to the procedure.