6 Replies Latest reply on Sep 12, 2000 3:42 PM by 3004

    dbms_output.get_lines() sample code

    3004
      Where can I find working sample Java code that retrieves and displays the array returned by dbms_output.getlines()? I keep getting "wrong number or types of arguments in call to 'GET_LINES' when I attempt to call it. Here is my code:

      try {
      String callProc = "{call dbms_output.get_lines(?,?)}";
      int linecnt;

      CallableStatement cstmt = mConn.prepareCall(callProc);
      OracleCallableStatement ocs = (OracleCallableStatement) cstmt;
      ocs.registerOutParameter(1, OracleTypes.ARRAY,
      "GEIS.CHARARR");
      ocs.registerOutParameter(2, java.sql.Types.INTEGER);
      ocs.setInt(2, 20);
      ocs.executeQuery();
      linecnt = ocs.getInt(2);
      System.out.println("linecnt = " + linecnt);
      cstmt.close();
      }
      catch (SQLException e) { System.out.println(kID + e.getMessage()); }

      GEIS.CHARARR is defined as a table of varchar2(255). dbms_output does not have this type defined.

      I'm using Oracle 8.1.6 JDBC with an Oracle 8.1.6 database and JDK 1.2.2 under Solaris 7.
        • 1. dbms_output.get_lines() sample code
          3004
          Unfortunately, PL/SQL table, which is the type used in the 1st argument to dbms_output.get_lines, isn't supported by JDBC at the moment.
          • 2. dbms_output.get_lines() sample code
            3004
            If that's true, then I need some clarification on the JDBC documentation. Chapter 4 of the "Oracle8i JDBC Developer's Guide and Reference Release 8.1.5" (http://technet.oracle.com/doc/java.815/a64685/oraext7.htm#1040060), would seem to indicate that nested tables are supported by the Oracle JDBC drivers (at least in 8.1.5/8.1.6). Or maybe I'm not understanding the distinction between these types of tables and PL/SQL tables (I haven't done a lot of PL/SQL coding). In order for this to work, the PL/SQL procedure would have to create a database table of this data type in order for my JDBC program to access the values?

            If passing and returning such data types really aren't supported by the Oracle JDBC drivers, what method does Oracle suggest as a workaround? One possibility would be to use JNI to invoke a C function with embedded SQL which would not have the JDBC limitation. However, this is not particularly appealing.
            • 3. dbms_output.get_lines() sample code
              3004
              From the Collections and Records chapter of the PL/SQL documentations, it says, "Items of type TABLE are either index-by tables (Version 2 PL/SQL tables) or nested tables (which extend the functionality of index-by tables)". The current JDBC drivers does support the nested tables (your geis.chararr is one), but it doesn't support index-by tables, which is returned by get_lines. The OCI driver will have some support of index-by tables in the upcoming 8.1.7 release.

              For more details, please take a look at http://technet.oracle.com/doc/server.815/a67842/04_colls.htm#24205

              One work around would be to write a store procedure to wrap the output from get_lines into a nested table.
              null
              • 4. dbms_output.get_lines() sample code
                3004
                The "Supplied Packages Reference" (8.1.5) shows:

                Syntax
                DBMS_OUTPUT.GET_LINES (
                lines OUT CHARARR,
                numlines IN OUT INTEGER);
                CHARARR is a table of VARCHAR2(255).

                It would have been helpful if it stated that CHARARR is an index-by table and that you had to define CHARARR itself. Why doesn't dbms_output define the CHARARR type?

                Is there a known release date for the 8.1.7 driver?
                null
                • 5. dbms_output.get_lines() sample code
                  3004
                  As suggested, I'm trying to convert the index by table returned by get_lines() to a nested table. Here is my code:

                  create or replace type chararr is table of varchar2(255);
                  /

                  create or replace
                  procedure do_get_lines
                  (lines OUT chararr, linecnt IN OUT INTEGER) AS

                  type ib_chararr is table of varchar2(255) index by binary_integer;
                  l_outlines ib_chararr;

                  BEGIN
                  dbms_output.get_lines(l_outlines, linecnt);
                  END;
                  /

                  However, I'm getting "wrong number or types of arguments in call to 'GET_LINES'. I'm passing an index-by for parm and an integer for parm2 which should be correct. What am I missing?
                  • 6. dbms_output.get_lines() sample code
                    3004
                    Ignore my previous post about wrong number or types of parameters, I have fixed the problem...