Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
ora-00902 invalid data type
Answers
-
Yes, I tried your solution. It is erroring out near OVER (line 34).
True, it is a very old version. But, I can't help it. I have no control over it. I can execute this function just fine in Oracle 9i and above. But, not in in 8.1.6. I was wondering if there is any other way of achieving this. Thanks, -
Oh! dear,
Over isn't supported in Oracle 8.
Really bad.
So, we have to explore some different ares to achieve the same.
It is working in Oracle 9i.
Regards.
Satyaki De. -
There are all sorts of problems with this because of this version.
I can't even use Oracle SQLDeveloper to connect. Other pl/sql developing tools don't work right either. Even if they do for couple of day, they eventually giveup. -
First you can test this code ->
create type np is table of number; 2 / Type created. SQL> ed Wrote file afiedt.buf 1 Create or Replace Procedure myProc(myArray np) 2 is 3 i number(10); 4 rec emp%rowtype; 5 Begin 6 for i in 1..myArray.count 7 loop 8 select * 9 into rec 10 from emp 11 where empno = myArray(i); 12 dbms_output.put_line('Employee No:'||rec.empno||' Name:'||rec.ename); 13 end loop; 14* End myProc; 15 / Procedure created. SQL> declare 2 v np:=np(6666,7777); 3 begin 4 myProc(v); 5 end; 6 / Employee No:6666 Name:prithwi Employee No:7777 Name:Avik
If collection is working in 8 then you can implement that in your code.
Regards.
Satyaki De. -
user6773 wrote:
It is 8.1.6. Thanks,SQL> SQL> SQL> @ver BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production SQL> SQL> -- SQL> create or replace package types as 2 type cursor_type is ref cursor; 3 end types; 4 / Package created. SQL> SQL> CREATE OR REPLACE FUNCTION get_multi_price 2 ( 3 P_STORE IN VARCHAR2, 4 v_inStr IN VARCHAR2 5 ) 6 RETURN types.cursor_type 7 IS 8 v_cursor types.cursor_type; 9 V_PR_SH_CODE varchar2(1); 10 V_SEQ number; 11 MSG VARCHAR2(200) := NULL; 12 v_sql VARCHAR2(4000) ; 13 v_instr_rp VARCHAR2(200); 14 -- blah, ... 15 BEGIN 16 /* 17 blah, blah, blah, ... 18 */ 19 V_PR_SH_CODE := 'X'; 20 V_SEQ := 1; 21 -- 22 v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39)); 23 24 -- returns multiple rows 25 v_sql := 'SELECT 100*rownum FROM all_objects PRI WHERE ''X'' = ' || '''' || V_PR_SH_CODE || '''' || 26 ' AND ''1'' = ' || '''' || V_SEQ || '''' || 27 ' AND ''p2'' in (' || '''' || v_instr_rp || '''' || 28 ') AND (''X'' NOT IN (''D'', ''SD'')) and rownum <= 3'; 29 -- dbms_output.put_line(v_sql); 30 OPEN v_cursor FOR v_sql; 31 RETURN v_cursor; 32 exception 33 WHEN NO_DATA_FOUND THEN 34 DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.'); 35 WHEN OTHERS THEN 36 MSG := SUBSTR(SQLERRM, 1, 70); 37 DBMS_OUTPUT.PUT_LINE(MSG); 38 END; 39 / Function created. SQL> SQL> -- this doesn't work in SQL*Plus in 8.1.7 SQL> select get_multi_price('0123','p1,p2,p3') as rslt from dual; select get_multi_price('0123','p1,p2,p3') as rslt from dual * ERROR at line 1: ORA-00902: invalid datatype SQL> SQL> -- so use this method in SQL*Plus SQL> var results refcursor SQL> exec :results := get_multi_price('0123','p1,p2,p3'); PL/SQL procedure successfully completed. SQL> print results 100*ROWNUM ---------- 100 200 300 SQL> SQL>
What's the hold up ?
isotope -
Oh! dear.
I was completely overlooked it. It is a function and op is returning in form of ref cursor.
Good point.
Regards.
Satyaki De. -
It works from sqlplus this way. But, we need to call this function from java code. I am not sure if creating a variable and printing it would work from java call. Will it work?
-
user6773 wrote:Of course not, as has been mentioned by SomeoneElse earlier in this thread. You have been asking for a SQL*Plus solution since the get-go:
It works from sqlplus this way. But, we need to call this function from java code. I am not sure if creating a variable and printing it would work from java call. Will it work?user6773 wrote:and all the suggestions over here are for SQL*Plus.
Hello all -
I have a function that returns a ref cursor.
When I test this function using PL/SQL developer, it works just fine and returns ref cursor with a set of price values for a set of products.
However, when I test the same function from SQL*Plus, it errors out.
select get_price('0123','p1,p2,p3') from dual;
Error at line 1:
ORA-00902: invalid datatype
Could you please help me out. Thanks in advance.
This is not a java forum. This is a SQL and PL/SQL forum. If you are expecting someone to post a Java/JDBC program to help you out, then you are barking up the wrong tree.
You may want to check out the appropriate forum for a java solution.
isotope -
Here is another way to print the value from ref cursor without using the print statement and that might be helpful for you in your Java ->
satyaki> satyaki>select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for 32-bit Windows: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production Elapsed: 00:00:01.56 satyaki> satyaki> satyaki>create or replace procedure gen_cur(str in varchar2,rc in out sys_refcursor) 2 is 3 str1 varchar2(500); 4 begin 5 str1 := 'select empno,ename,hiredate,sal 6 from emp 7 where '||str; 8 9 open rc for str1; 10 exception 11 when others then 12 dbms_output.put_line(sqlerrm); 13 end; 14 / Procedure created. Elapsed: 00:00:01.18 satyaki> satyaki> satyaki>declare 2 type a is record 3 ( 4 empno number(4), 5 ename varchar2(30), 6 hiredate date, 7 sal number(10,2) 8 ); 9 rec a; 10 b sys_refcursor; 11 src varchar2(300); 12 begin 13 src:= 'sal between 2000 and 7000'; 14 gen_cur(src,b); 15 loop 16 fetch b into rec; 17 exit when b%notfound; 18 dbms_output.put_line('Employee No:'||rec.empno||' - '|| 19 'Name:'||rec.ename||' - '|| 20 'Hire Date:'||rec.hiredate||' - '|| 21 'Salary:'||rec.sal); 22 end loop; 23 close b; 24 exception 25 when others then 26 dbms_output.put_line(sqlerrm); 27 end; 28 / Employee No:7566 - Name:JONES - Hire Date:02-APR-81 - Salary:2975 Employee No:7698 - Name:BLAKE - Hire Date:01-MAY-81 - Salary:2850 Employee No:7782 - Name:CLARK - Hire Date:09-JUN-81 - Salary:4450 Employee No:7788 - Name:SCOTT - Hire Date:19-APR-87 - Salary:3000 Employee No:7839 - Name:KING - Hire Date:17-NOV-81 - Salary:7000 Employee No:7902 - Name:FORD - Hire Date:03-DEC-81 - Salary:3000 PL/SQL procedure successfully completed. Elapsed: 00:00:01.02
Regards.
Satyaki De. -
Sorry Duplicate post....
Regards.
Satyaki De.
N.B.: Please check my last post in the previous page.
Edited by: Satyaki_De on Jan 6, 2009 12:23 AM
This discussion has been closed.