Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 533 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.2K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 402 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Oracle Store Procedure gets unexpected parameter in ODBC
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
Answers
-
Would to be possible to return a cursor from function?