Forum Stats

  • 3,782,598 Users
  • 2,254,670 Discussions
  • 7,880,132 Comments

Discussions

ORA-06550/PLS-00382:help me

tobari
tobari Member Posts: 9


Where do you think there is a problem?


the following error when you run the java → get_ArraySTR.

ORA-06550: Line1、Row22:
PLS-00382: Incorrect type of the expression.
ORA-06550: Line1、Row7:
PL/SQL: Statement ignored

Oracle 11.2.0.3.0 JDBC 4.0 compiled with JDK6 on Thu_Jul_11_15:43:23_PDT_2013

#Default Connection Properties Resource

#Thu Aug 07 17:39:16 JST 2014


★stack trace
PreparedStatementUtil.java:86
AbstractFunctionCall.java:136

★java source

List<String> result =
    jdbcManager
        .callBySql(
            String.class,
            "{? = call get_ArraySTR}")
        .getResultList();

★stored procedure source

CREATE OR REPLACE package test_pkg is
TYPE vArraySTR IS VARRAY(2) OF VARCHAR2(10);
function get_ArraySTR return vArraySTR;
end test_pkg;
/

CREATE OR REPLACE package BODY test_pkg is
function get_ArraySTR return vArraySTR is
vName10_List vArraySTR;
begin
vname10_list := vArraySTR();
vname10_list.extend(2);
vName10_List(1):='123';
vName10_List(2):='456';
return vName10_List;
end get_ArraySTR;

end test_pkg;
/

Tagged:

Best Answer

Answers

  • Unknown
    Accepted Answer
    TYPE vArraySTR IS VARRAY(2) OF VARCHAR2(10);

    You can NOT use PL/SQL types outside of PL/SQL.

    Create a SQL type for the code to return.

  • tobari
    tobari Member Posts: 9

    Or would not be able to return multiple result sets?

  • Or would not be able to return multiple result sets?

    What does that have to do with the question you ask and the code you posted?

    Besides, Oracle 11g doesn't support multiple result sets.

    Tell us what PROBLEM you are trying to solve.

  • tobari
    tobari Member Posts: 9
    edited Aug 10, 2014 8:01PM

    >What does that have to do with the question you ask and the code you posted?


    When you check the operation of the java source, stored procedure that returns multiple result sets are required.


    >You can NOT use PL/SQL types outside of PL/SQL.


    I was able by the above advice, you create a stored procedure that returns multiple result sets.



    CREATE OR REPLACE package test_pkg is
    TYPE ref_cursor IS REF CURSOR;
    function get_ResultList return ref_cursor;
    end test_pkg;
    /

    create or replace package body test_pkg is
    function get_ResultList return ref_cursor is
    test_cursor ref_cursor;
    begin
    open test_cursor for select * from test;
    return test_cursor;
    end get_ResultList;

    end test_pkg;
    /

  • I was able by the above advice, you create a stored procedure that returns multiple result sets.
    
    CREATE OR REPLACE package test_pkg is
    TYPE ref_cursor IS REF CURSOR;

    No - that procedure does NOT return multiple result sets. It returns a single weakly-typed REF CURSOR.

    Your thread question and problem was because you were trying to return a PL/SQL type and use it outside of PL/SQL

    CREATE OR REPLACE package test_pkg is
    TYPE vArraySTR IS VARRAY(2) OF VARCHAR2(10);
    

    You can NOT do that if that code is called from Java.

    A REF CURSOR is designed by Oracle so that it CAN BE called from clients. That is the method that SHOULD be used to return result sets to clients.

    But your code does NOT return multiple result sets. Because a REF CURSOR is weakly typed it can be used to return data from DIFFERENT data sources each time it is called.

    I discuss that and provide sample code in this thread:

    https://forums.oracle.com/forums/thread.jspa?messageID=10976360&#10976360

    You can use stored procedures with OUT parameters to return multiple REF CURSORs.

    And Oracle 12c DOES support multiple and implicit result sets similar to what Sql Server supports. Tom Kyte discusses that in this article

    The Tom Kyte Blog: 12c - Implicit Result Sets...

This discussion has been closed.