ora-00902 invalid data type

OrionNet

Hello,
Can you post your get_price procedure/function?

Try using anonymous pl/sql block simple example,

CREATE OR REPLACE FUNCTION getpmnorgrow (empid emp.id%TYPE)
   RETURN emp%ROWTYPE --
IS
   result   emp%ROWTYPE;
BEGIN
   SELECT *
   INTO result
   FROM emp
   WHERE id = empid;

   RETURN result;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      raise_application_error (-20100, 'error');
      RETURN NULL;
END;
/

DECLARE
   retval   emp%ROWTYPE;
   empid    NUMBER;
BEGIN
   empid    := 1;

   retval   := getpmnorgrow (empid);
   DBMS_OUTPUT.put_line ('employee name=' || retval.name);
   COMMIT;
END;

Regards

Tubby

variable my_cursor refcursor;
exec :my_cursor := get_price('0123','p1,p2,p3');

print :my_cursor

OrionNet

Check this link , discussing the same error

844608

Regards

247890

Hi,

Thanks for your response.
-------------------------------------------------------------------------

Edited by: user6773 on Jan 5, 2009 6:05 AM

247890

Hi Tubby,

I get an error msg when I try this. Not sure ..

Karthick2003

Whats the error you are getting?

247890

When I try to test it:

select get_price('0123','p1,p2,p3') from dual;

Error at line 1:
ORA-00902: invalid datatype

SomeoneElse

select get_price('0123','p1,p2,p3') from dual;

Why do you have p1,p2,p3 in quote marks?

Is your function expecting a character string here? Or should they be separate parameters?

247890

Yes, it is expecting a string of values for the second parameter (set of products).

SomeoneElse

I have no idea what your function does, but try this:

SQL> var results refcursor
SQL> exec :results := get_price('0123','p1,p2,p3');
SQL> print results

247890

Thanks for your response. That works. But, I am not sure if this is useful when this function is called by a java application.

SomeoneElse

But, I am not sure if this is useful when this function is called by a java application.

Of course not. You asked why this didn't work in SQL Plus, so this is a SQL Plus solution.

If you're using Java and having trouble, you can post another question.

Boneist

It would be really, really helpful if you could post your function, as has already been requested!

I could ask you "My car doesn't work start, unless I'm parked on a hill, why not?" but I bet you'd have trouble pinpointing the exact problem that my car has got...

Satyaki_De

I have plenty of questions.

Question:

Why don't you post your actual script here?

a. Your Company Restrict You.
b. You don't want to post.
c. You don't understand what our volunteers have repeatedly ask
d. None of the above.

Now,

Solution - In case of ->

a. Then post one dummy solution which also facing the same error prone scenario and ask your requirement clearly.

b. Then hard luck. Our help will be up to this due to your response.

c. Our volunteers are asking repeatedly to post the actual code here. And, again as i've already explained in point (a) - if you have any problem to post then follow solution a.

d. Then try to read oracle documentation and specifically read how collection and ref cursor works and then try to implement that by yourself.

I hope i've covered almost all the points for you.

Regards.

Satyaki De.

247890

wow!

Sorry, it took me a little time to check this. I am not sure if my company objects to this. So, I have posted this code earlier and then, I removed it again.

I didn't mean to ask a random question and expect answer. After reading few similar threads on this forum, I thought this question is more independent of what code does. Sorry for any inconvenience caused...
-------------------------
create or replace package types as
type cursor_type is ref cursor;
end types;

CREATE OR REPLACE FUNCTION GET_multi_PRICE(P_STORE IN VARCHAR2,
--P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
v_inStr IN VARCHAR2
)
RETURN types.cursor_type IS
v_cursor types.cursor_type;
V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
V_SEQ RPPRODPRICE.SEQNUM%TYPE;
v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
MSG VARCHAR2(200) := NULL;
v_sql VARCHAR2(4000) ;
v_instr_rp VARCHAR2(200);
-- 1. Check to see if there is a SALE PRICE for the selected product
--=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
--======= Check to ensure that the price sheet has been deployed ('D')
--======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
--======= If multiple records found, select the one with the most recent effective date.
BEGIN
SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
FROM RPPRICESTORE STR, RPPRICESHEET PS
WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
AND STR.SEQNUM = PS.SEQNUM
AND PS.STATUS = 'D'
AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
AND (STR.EXPDATE = '99999999' OR
TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10))
AND STR.STORE = P_STORE
AND STR.EFFECTIVEDATE =
(SELECT MAX(S.EFFECTIVEDATE)
FROM RPPRICESTORE S
WHERE S.STORE = P_STORE
AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
AND (S.EXPDATE = '99999999' OR
TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)));
--=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));

IF V_PR_SH_TYPE = 'A' THEN
IF V_ADV_TYPE = 'E' THEN

v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI
WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
' AND PRI.PRODCODE in (' || '''' || v_instr_rp || '''' ||
') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
dbms_output.put_line(v_sql);

OPEN v_cursor FOR v_sql;
RETURN v_cursor;
-- ADV Rule based
END IF;

END IF;
exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
WHEN OTHERS THEN
MSG := SUBSTR(SQLERRM, 1, 70);
DBMS_OUTPUT.PUT_LINE(MSG);
END;
/

Satyaki_De

I'm posting your query in a formatted manner so that everyone can easily understand your coding ->

create or replace package types as
  type cursor_type is ref cursor;
end types;

CREATE OR REPLACE FUNCTION GET_multi_PRICE
 (
   P_STORE IN VARCHAR2,
   --P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
   v_inStr IN VARCHAR2
 )
RETURN types.cursor_type 
IS
    v_cursor types.cursor_type;
    V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
    V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
    V_SEQ RPPRODPRICE.SEQNUM%TYPE;
    v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
    V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
    V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
    V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
    v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
    V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
    V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
    V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
    V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
    V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    MSG VARCHAR2(200) := NULL;
    v_sql VARCHAR2(4000) ;
    v_instr_rp VARCHAR2(200);
-- 1. Check to see if there is a SALE PRICE for the selected product
--=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
--======= Check to ensure that the price sheet has been deployed ('D')
--======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
--======= If multiple records found, select the one with the most recent effective date.
BEGIN
  SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
  INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
  FROM RPPRICESTORE STR, RPPRICESHEET PS
  WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
  AND STR.SEQNUM = PS.SEQNUM
  AND PS.STATUS = 'D'
  AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
  AND (
         STR.EXPDATE = '99999999' 
         OR TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
      )
  AND STR.STORE = P_STORE
  AND STR.EFFECTIVEDATE =(
                            SELECT MAX(S.EFFECTIVEDATE)
                            FROM RPPRICESTORE S
                            WHERE S.STORE = P_STORE
                            AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
                            AND (
                                    S.EXPDATE = '99999999' 
                                    OR TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
                                )
                         );
  --=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
  v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));

  IF V_PR_SH_TYPE = 'A' THEN
    IF V_ADV_TYPE = 'E' THEN
      v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
               ' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
               ' AND PRI.PRODCODE in (' || '''' || v_instr_rp || '''' ||
               ') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
      dbms_output.put_line(v_sql);
      
      OPEN v_cursor FOR v_sql;
      
      RETURN v_cursor;
    -- ADV Rule based
    END IF;
  END IF;
exception
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
  WHEN OTHERS THEN
    MSG := SUBSTR(SQLERRM, 1, 70);
    DBMS_OUTPUT.PUT_LINE(MSG);
END;

N.B.: This is your post - i just format it.

Regards.

Satyaki De.

Satyaki_De

I'm not so sure whether your previous solution is working or not.

But, i've made some modification in your code and this might work for you ->

CREATE OR REPLACE FUNCTION GET_multi_PRICE
 (
   P_STORE IN VARCHAR2,
   --P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
   v_inStr IN VARCHAR2
 )
RETURN types.cursor_type 
IS
    v_cursor types.cursor_type;
    V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
    V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
    V_SEQ RPPRODPRICE.SEQNUM%TYPE;
    v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
    V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
    V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
    V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
    v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
    V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
    V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
    V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
    V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
    V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    MSG VARCHAR2(200) := NULL;
    v_sql VARCHAR2(4000) ;
    v_instr_rp VARCHAR2(200);
    
    cursor c1
    is
      SELECT  chr(39)||
              SUBSTR(v_inStr,                    
                      INSTR(v_inStr,',',1,LEVEL) + 1,                    
                      NVL(
                          LEAD(INSTR(v_inStr,',',1,LEVEL)) OVER (ORDER BY INSTR(v_inStr,',',1,LEVEL)) - 1 , 
                          LENGTH(v_inStr) + 1 
                        ) - INSTR(v_inStr,',',1,LEVEL)                   
                    )||
              chr(39) ils       
      FROM ee,DUAL 
      CONNECT BY LEVEL <= LENGTH(v_inStr) - LENGTH(REPLACE(v_inStr,',')) + 1;
      
    r1 c1%rowtype;
    
    v_instr_p  varchar2(2000);
-- 1. Check to see if there is a SALE PRICE for the selected product
--=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
--======= Check to ensure that the price sheet has been deployed ('D')
--======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
--======= If multiple records found, select the one with the most recent effective date.
BEGIN
  SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
  INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
  FROM RPPRICESTORE STR, RPPRICESHEET PS
  WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
  AND STR.SEQNUM = PS.SEQNUM
  AND PS.STATUS = 'D'
  AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
  AND (
         STR.EXPDATE = '99999999' 
         OR TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
      )
  AND STR.STORE = P_STORE
  AND STR.EFFECTIVEDATE =(
                            SELECT MAX(S.EFFECTIVEDATE)
                            FROM RPPRICESTORE S
                            WHERE S.STORE = P_STORE
                            AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
                            AND (
                                    S.EXPDATE = '99999999' 
                                    OR TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
                                )
                         );
  --=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
  v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));
  
  ---New Code for splitting the string
  
  for r1 in c1
  loop
    v_instr_p := v_instr_p || r1.ils ||',';
  end loop;
  
  v_instr_p := rtrim(v_instr_p,',')
  
  ---End Of the New Script

  IF V_PR_SH_TYPE = 'A' THEN
    IF V_ADV_TYPE = 'E' THEN
      v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
               ' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
               ' AND PRI.PRODCODE in (' || '''' || v_instr_p || '''' ||
               ') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
      dbms_output.put_line(v_sql);
      
      OPEN v_cursor FOR v_sql;
      
      RETURN v_cursor;
    -- ADV Rule based
    END IF;
  END IF;
exception
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
  WHEN OTHERS THEN
    MSG := SUBSTR(SQLERRM, 1, 70);
    DBMS_OUTPUT.PUT_LINE(MSG);
END;
/

And, now you should pass as you are trying to pass the value previously and see the result.

N.B.: Not Tested...

Regards.

Satyaki De.

isotope

What's the version of your database ?

select * from v$version;

isotope

247890

It is 8.1.6. Thanks,

Satyaki_De

Oh! Goodness.

This is almost primitive DB.

Did you check my solution? Is it working?

Regards.

Satyaki De.

247890

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

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

:(

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

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

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

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

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

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

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

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

SQL & PL/SQL

ora-00902 invalid data type

Comments

Post Details