Forum Stats

  • 3,767,863 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

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,
  • 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.
  • 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.
  • 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.
  • 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
  • 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.
  • 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?
  • 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
  • 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.
  • 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
This discussion has been closed.