Forum Stats

  • 3,781,517 Users
  • 2,254,525 Discussions
  • 7,879,742 Comments

Discussions

Categories

ora-00902 invalid data type

13»

Answers

  • 247890
    247890 Member Posts: 420
    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,
    · Share on TwitterShare on Facebook
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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.
    · Share on TwitterShare on Facebook
  • 247890
    247890 Member Posts: 420
    :(

    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.
    · Share on TwitterShare on Facebook
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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.
    · Share on TwitterShare on Facebook
  • isotope
    isotope Member Posts: 748 Bronze Badge
    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
    · Share on TwitterShare on Facebook
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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.
    · Share on TwitterShare on Facebook
  • 247890
    247890 Member Posts: 420
    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?
    · Share on TwitterShare on Facebook
  • isotope
    isotope Member Posts: 748 Bronze Badge
    edited Jan 5, 2009 1:30PM
    user6773 wrote:
    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?
    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:
    user6773 wrote:
    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.
    and all the suggestions over here are for SQL*Plus.

    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
    · Share on TwitterShare on Facebook
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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.
    · Share on TwitterShare on Facebook
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    edited Jan 5, 2009 1:53PM
    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
    · Share on TwitterShare on Facebook
This discussion has been closed.