Oracle Store Procedure gets unexpected parameter in ODBC

2952936

Hi,

    I created a store procedure as below:

CREATE OR REPLACE PROCEDURE RPT_TEST_PROC (
   rpt_output_cur   IN OUT PKG_RPT_TEST_PROC.rpt_test_proc_out,
   StartDate        IN     DATE DEFAULT NULL,
   EndDate          IN     DATE DEFAULT NULL,
   javaLocale       IN     VARCHAR2 DEFAULT NULL)
IS
   StartDate1   DATE := StartDate;
   EndDate1     DATE := EndDate;
   TXT          VARCHAR2 (2000);
BEGIN
   IF StartDate IS NULL
   THEN
      StartDate1 := SYSDATE;
   END IF;

   IF EndDate IS NULL
   THEN
      EndDate1 := SYSDATE;
   END IF;

   TXT :=
         TO_CHAR (StartDate1, 'DD-MON-YYYY')
      || ' ~ '
      || TO_CHAR (EndDate1, 'DD-MON-YYYY');

   OPEN rpt_output_cur FOR
      SELECT TXT STR_FIELD, EndDate1 - StartDate1 NUM_FIELD FROM DUAL;
EXCEPTION
   WHEN OTHERS
   THEN
      OPEN rpt_output_cur FOR SELECT NULL STR_FIELD, 0 NUM_FIELD FROM DUAL;
END;
/

   And I use Oracle ODBC to fetch its parameter info, however it returns 6 records as below, seems "STR_FIELD" and "NUM_FIELD" should not be a parameter. Could someone answer this question?

   I also tested based on Oracle Native Client and JDBC, both are correct and returns 4 records.

"PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"

<Null>, "I049400", "RPT_TEST_PROC", "RPT_OUTPUT_CUR", 2, -404, "REF CURSOR", <Null>, <Null>, <Null>, <Null>, 2, <Null>, <Null>, -404, , <Null>, 1, <Null>

<Null>, "I049400", "RPT_TEST_PROC", "STR_FIELD", 2, 12, "VARCHAR2", 2000, 2000, <Null>, <Null>, 2, <Null>, <Null>, 12, , 2000, 1, <Null>

<Null>, "I049400", "RPT_TEST_PROC", "NUM_FIELD", 2, 3, "NUMBER", 10, 22, <Null>, 10, 2, <Null>, <Null>, 3, , 22, 2, <Null>

<Null>, "I049400", "RPT_TEST_PROC", "STARTDATE", 1, 93, "DATE", <Null>, <Null>, <Null>, <Null>, 2, <Null>, <Null>, 9, 3, <Null>, 2, <Null>

<Null>, "I049400", "RPT_TEST_PROC", "ENDDATE", 1, 93, "DATE", <Null>, <Null>, <Null>, <Null>, 2, <Null>, <Null>, 9, 3, <Null>, 3, <Null>

<Null>, "I049400", "RPT_TEST_PROC", "JAVALOCALE", 1, 12, "VARCHAR2", 4000, 4000, <Null>, <Null>, 2, <Null>, <Null>, 12, , 4000, 4, <Null>

Thanks

Spring

StarterAman

Would to be possible to return a cursor from function?