Forum Stats

  • 3,752,284 Users
  • 2,250,483 Discussions
  • 7,867,783 Comments

Discussions

ora-00902 invalid data type

2

Answers

  • 247890
    247890 Member Posts: 420
    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
    SomeoneElse Member Posts: 14,866 Silver Crown
    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
    Boneist Member Posts: 4,983 Gold Trophy
    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
    Satyaki_De Member Posts: 7,081
    edited Jan 5, 2009 10:32AM
    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
    247890 Member Posts: 420
    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
    Satyaki_De Member Posts: 7,081
    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
    Satyaki_De Member Posts: 7,081
    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
    isotope Member Posts: 748 Bronze Badge
    What's the version of your database ?
    select * from v$version;
    isotope
  • 247890
    247890 Member Posts: 420
    It is 8.1.6. Thanks,
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Oh! Goodness.

    This is almost primitive DB.

    Did you check my solution? Is it working?

    Regards.

    Satyaki De.
This discussion has been closed.