ora-00902 invalid data type

247890 · Jan 4, 2009 3:59PM

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.

OrionNet · Jan 4, 2009 4:29PM

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 · Jan 4, 2009 4:30PM

variable my_cursor refcursor;

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


print :my_cursor

OrionNet · Jan 4, 2009 4:33PM

Check this link , discussing the same error

844608

Regards

247890 · Jan 4, 2009 10:07PM

Hi,

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

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

247890 · Jan 4, 2009 10:11PM

Hi Tubby,

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

Karthick2003 · Jan 4, 2009 11:13PM

Whats the error you are getting?

247890 · Jan 5, 2009 9:04AM

When I try to test it:

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

Error at line 1:
ORA-00902: invalid datatype

SomeoneElse · Jan 5, 2009 9:14AM

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 · Jan 5, 2009 9:19AM

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

SomeoneElse · Jan 5, 2009 9:35AM

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 · Jan 5, 2009 9:55AM

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 · Jan 5, 2009 9:59AM

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 · Jan 5, 2009 10:00AM

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 · Jan 5, 2009 10:30AM

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 · Jan 5, 2009 10:56AM

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 · Jan 5, 2009 11:06AM

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 · Jan 5, 2009 11:36AM

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 · Jan 5, 2009 11:58AM

What's the version of your database ?

select * from v$version;

isotope

247890 · Jan 5, 2009 12:14PM

It is 8.1.6. Thanks,

Satyaki_De · Jan 5, 2009 12:19PM

Oh! Goodness.

This is almost primitive DB.

Did you check my solution? Is it working?

Regards.

Satyaki De.

247890 · Jan 5, 2009 12:24PM

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 · Jan 5, 2009 12:27PM

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 · Jan 5, 2009 12:34PM

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 · Jan 5, 2009 12:56PM

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 · Jan 5, 2009 1:10PM

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

Forum Stats

Howdy, Stranger!

Quick Links

Categories

ora-00902 invalid data type

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

ora-00902 invalid data type

Answers