Home » SQL & PL/SQL » SQL & PL/SQL » inconsistent datatypes: expected - got -
icon13.gif  inconsistent datatypes: expected - got - [message #268896] Thu, 20 September 2007 01:12 Go to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
hi there,
I need some help with my codes.
Below is my sql code, and when i execute it says "inconsistent datatypes: expected - got -'!!. I am using dynamic sql to simplify the codings...what could be the problem..please assist me.

.....
/*=======================================
             MAIN
=======================================*/ 

BEGIN
      FND_FILE.PUT_NAMES'/oca/ocafsh/tmp/TMPO0430.log','/oca/ocafsh/tmp/TMPO0430.out','/oca/ocafsh/tmp'); 

 CASE

 WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NOT NULL THEN 
 c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
 c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';
 

 WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NULL THEN 
 c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
   c_smt2 := null;
 
 WHEN PRA_BUYER_ID IS NULL AND PRA_VENDOR_ID IS NOT NULL THEN 
   c_smt1 := null;
 c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';

        else
 c_smt1 := null;
 c_smt2 := null;
   
 END CASE; 

        /*SELECT HE.FULL_NAME, SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,'@'))
        INTO T_BUYER_NAME, T_MAIL
        FROM HR_EMPLOYEES HE
        WHERE HE.EMPLOYEE_ID = PRA_BUYER_ID;*/ 


 /*SELECT PV.SEGMENT1
 INTO T_VENDOR_CODE
 FROM PO_VENDORS PV
 WHERE PV.VENDOR_ID = PRA_VENDOR_ID;  
 */

 -- Construct a dynamic SQL to retrieve the vendors details of the buyer 
                 
 c_sql_smt := ' SELECT distinct PHA.Vendor_Id,'||
                        ' HE.FULL_NAME,'||
          ' SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,''@'')),'||
   ' PV.SEGMENT1'||  
                  ' FROM    PO_HEADERS_ALL          PHA,'|| 
                          'PO_LINES_ALL            PLA,'|| 
                          'PO_LINE_LOCATIONS_ALL   PLLA,'||
     'HR_EMPLOYEES            HE,'||
     'PO_VENDORS              PV'||  
                 ' WHERE    PHA.PO_HEADER_ID       = PLA.PO_HEADER_ID'|| 
                  ' AND     PHA.AUTHORIZATION_STATUS= ''APPROVED'''||
                  ' AND     PHA.TYPE_LOOKUP_CODE    IN (''BLANKET'',''STANDARD'')'|| 
                  ' AND     nvl(PHA.CANCEL_FLAG,''N'')= ''N'''|| 
                  ' AND     nvl(PHA.CLOSED_CODE,''OPEN'') = ''OPEN'''||
                  ' AND     PHA.PO_HEADER_ID        = PLA.PO_HEADER_ID'||
                  ' AND     PLA.PO_LINE_ID          = PLLA.PO_LINE_ID'|| 
                  ' AND     PLA.QUANTITY            >  PLLA.QUANTITY_RECEIVED'||    
    '  '||c_smt1||
    '  '||c_smt2; 
     
     
  FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL_STATEMENT = '||C_SQL_SMT); 

    --V_MSG := 'OPEN CSR_TMR';
    OPEN CSR_TMR FOR c_sql_smt; --(PRA_BUYER_ID)
    FND_FILE.PUT_LINE(FND_FILE.LOG,'OPEN C_SQL_SMT'); 
    LOOP 
    --V_MSG := 'FETCH CSR_TMR';
  ----- fetch buyer ID into v_buyer_ID -----
  FETCH CSR_TMR INTO REC_TMR(k);
  FND_FILE.PUT_LINE(FND_FILE.LOG,'FETCH CSR_TMR'); 
  EXIT WHEN CSR_TMR%NOTFOUND;
 
  ----- auto submit request TMPO0080 -----
  FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST1'); 
  REQ_ID := FND_REQUEST.SUBMIT_REQUEST (
    'TIM',
    'TMPO0080',  
    NULL,
    NULL,
    FALSE,
    nvl(PRA_BUYER_ID,''),  
    nvl(PRA_VENDOR_ID,''),
    chr(0),
    '','','','','','','','','','',
           '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','');

  FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST2'); 
  COMMIT;

           /*SELECT PV.SEGMENT1
           INTO T_VENDOR_CODE
           FROM PO_VENDORS PV
           WHERE PV.VENDOR_ID = V_VENDOR_ID; */ 

 IF REQ_ID = 0 THEN
    V_MSG := 'TMPO0080 encounter error, report not submitted';
    RAISE USR_ERROR;
 END IF;

          --- wait for TMPO0080 request finish ---
        T_SUCCESS := FND_CONCURRENT.WAIT_FOR_REQUEST (
                     REQ_ID,
                     2,
                     0,
                     lv_phase,
                     lv_status,
                     lv_dev_phase,
                     lv_dev_status,
                     lv_message );
 
        IF lv_phase = 'Completed' AND lv_status = 'Normal' THEN
    T_SUBJECT := 'Open PO - ' || T_VENDOR_CODE || ' - ' || T_BUYER_NAME;
           REC_REQ_ID(l) := REQ_ID;
           REC_EMAIL(l) := T_MAIL;
    REC_SUBJECT(l) := T_SUBJECT;
           l := l + 1; 
          
    --DBMS_LOCK.SLEEP(20);
           --tim_alrutils.ins_alert_email_subject (REQ_ID, T_MAIL, T_SUBJECT,'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
           --COMMIT;
        END IF; 

 k := k + 1;
 END LOOP;
 --V_MSG := 'CLOSE CSR_TMR';
 FND_FILE.PUT_LINE(FND_FILE.LOG,'CLOSE CSR_TMR'); 
 CLOSE CSR_TMR;

/*===== send email =====*/
FOR IDX IN 0 .. l-1 LOOP
   DBMS_OUTPUT.PUT_LINE(REC_REQ_ID(IDX));
   DBMS_LOCK.SLEEP(30);
  -- tim_alrutils.ins_alert_email_subject (REC_REQ_ID(IDX), REC_EMAIL(IDX), REC_SUBJECT(IDX),'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
   COMMIT;
END LOOP;
  
 EXCEPTION
      WHEN USR_ERROR THEN
    IF C0%ISOPEN THEN
       CLOSE C0;
    END IF;
      WHEN OTHERS THEN
    IF C0%ISOPEN THEN
       CLOSE C0;
    END IF;
 DBMS_OUTPUT.PUT_LINE ('USR_ERROR :' ||V_MSG);
 --- Log output of contents of an error ---
 DBMS_OUTPUT.PUT_LINE ('PROGRAM ID [TMSYS0080]');
 DBMS_OUTPUT.PUT_LINE ('ERROR    :' ||TO_CHAR(SQLCODE));
 DBMS_OUTPUT.PUT_LINE ('ERROR MSG:' || SQLERRM );
 CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
END ;
/

Re: inconsistent datatypes: expected - got - [message #268897 is a reply to message #268896] Thu, 20 September 2007 01:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Start by removing the when others exception.
Then see what line the error occurs

There is a piece of code missing, pra_buyer_id is not defined
Re: inconsistent datatypes: expected - got - [message #268904 is a reply to message #268896] Thu, 20 September 2007 01:29 Go to previous messageGo to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
yes, actually i only posted the past where i had to ammend and where i thought would be causing the probelm...
below is the complete code...please advise!!
/*

SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE

CURSOR C0 (P_BUYER_ID PO.PO_AGENTS.AGENT_ID%TYPE)
IS
 SELECT distinct
                         PHA.Vendor_Id
                 FROM
                         PO_HEADERS_ALL          PHA,
                         PO_LINES_ALL            PLA,                        
                         PO_LINE_LOCATIONS_ALL   PLLA
                 WHERE
    PHA.AGENT_ID            = P_BUYER_ID
   AND     PHA.PO_HEADER_ID        = PLA.PO_HEADER_ID
                 AND     PHA.AUTHORIZATION_STATUS= 'APPROVED'
                 AND     PHA.TYPE_LOOKUP_CODE    In ('BLANKET','STANDARD') 
                 AND     nvl(PHA.CANCEL_FLAG,'N')= 'N'
                 AND     nvl(PHA.CLOSED_CODE,'OPEN') = 'OPEN'
                 AND     PHA.PO_HEADER_ID        = PLA.PO_HEADER_ID
                 AND     PLA.PO_LINE_ID          = PLLA.PO_LINE_ID
                 AND     PLA.QUANTITY            >  PLLA.QUANTITY_RECEIVED
 ;

        TYPE REC_REQ_ID_TYPE IS TABLE OF NUMBER
  INDEX BY BINARY_INTEGER;
        REC_REQ_ID REC_REQ_ID_TYPE;

        TYPE REC_EMAIL_TYPE IS TABLE OF HR_EMPLOYEES.EMAIL_ADDRESS%TYPE
  INDEX BY BINARY_INTEGER;
        REC_EMAIL REC_EMAIL_TYPE;

        TYPE REC_SUBJECT_TYPE IS TABLE OF VARCHAR(500)
  INDEX BY BINARY_INTEGER;
        REC_SUBJECT REC_SUBJECT_TYPE; 

 TYPE TMRCurTyp IS REF CURSOR;

        CSR_TMR TMRCurTyp;

        TYPE TMRRecTyp IS RECORD (

                VENDOR_ID       PO_HEADERS_ALL.VENDOR_ID%TYPE
                        );

        TYPE TMRRec IS TABLE OF TMRRecTyp
                INDEX BY PLS_INTEGER;

        rec_TMR TMRRec;


 V_VENDOR_ID PO.PO_HEADERS_ALL.AGENT_ID%TYPE; 
 PRA_BUYER_ID NUMBER;
 PRA_VENDOR_ID NUMBER;
 PRA_COMMENT1 VARCHAR2(80);
 T_VENDOR_CODE PO_VENDORS.SEGMENT1%TYPE;
 T_BUYER_NAME  HR_EMPLOYEES.FULL_NAME%TYPE;
 T_SUCCESS BOOLEAN;
 T_MAIL  VARCHAR2(100);
 T_SUBJECT VARCHAR2(500);
 lv_phase VARCHAR2(100);
 lv_status VARCHAR2(100);
 lv_dev_phase VARCHAR2(100);
 lv_dev_status VARCHAR2(100);
 lv_message VARCHAR2(500);
        k  PLS_INTEGER DEFAULT 0;
        l  BINARY_INTEGER DEFAULT 0; 
 IDX  NUMBER; 
 c_smt1  VARCHAR2(100);
 c_smt2  VARCHAR2(100);
 c_sql_smt       VARCHAR2(3000);  

 V_MSG  VARCHAR2(241);
 USR_ERROR EXCEPTION;
 CNC_RET  BOOLEAN;
 CNT  NUMBER;
 REQ_ID  NUMBER;  
 ERR_MSG  VARCHAR2(500);
 ERR_CODE NUMBER;

        /*=======================================
  MAIN
  =======================================*/ 

BEGIN
      FND_FILE.PUT_NAMES('/oca/ocafsh/tmp/TMPO0430.log','/oca/ocafsh/tmp/TMPO0430.out','/oca/ocafsh/tmp'); 

 CASE

 WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NOT NULL THEN 
 c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
 c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';
 

 WHEN PRA_BUYER_ID IS NOT NULL AND PRA_VENDOR_ID IS NULL THEN 
 c_smt1 := 'AND PHA.AGENT_ID = PRA_BUYER_ID';
   c_smt2 := null;
 
 WHEN PRA_BUYER_ID IS NULL AND PRA_VENDOR_ID IS NOT NULL THEN 
   c_smt1 := null;
 c_smt2 := 'AND PHA.VENDOR_ID = PRA_VENDOR_ID';

        else
 c_smt1 := null;
 c_smt2 := null;
   
 END CASE; 

        /*SELECT HE.FULL_NAME, SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,'@'))
        INTO T_BUYER_NAME, T_MAIL
        FROM HR_EMPLOYEES HE
        WHERE HE.EMPLOYEE_ID = PRA_BUYER_ID;*/ 

 /*SELECT PV.SEGMENT1
 INTO T_VENDOR_CODE
 FROM PO_VENDORS PV
 WHERE PV.VENDOR_ID = PRA_VENDOR_ID;  
 */

 -- Construct a dynamic SQL to retrieve the vendors details of the buyer 
                 
 c_sql_smt := ' SELECT distinct PHA.Vendor_Id,'||
                        ' HE.FULL_NAME,'||
          ' SUBSTR(HE.EMAIL_ADDRESS,1,INSTR(HE.EMAIL_ADDRESS,''@'')),'||
   ' PV.SEGMENT1'||  
                  ' FROM    PO_HEADERS_ALL          PHA,'|| 
                          'PO_LINES_ALL            PLA,'|| 
                          'PO_LINE_LOCATIONS_ALL   PLLA,'||
     'HR_EMPLOYEES            HE,'||
     'PO_VENDORS              PV'||  
                 ' WHERE    PHA.PO_HEADER_ID       = PLA.PO_HEADER_ID'|| 
                  ' AND     PHA.AUTHORIZATION_STATUS= ''APPROVED'''||
                  ' AND     PHA.TYPE_LOOKUP_CODE    IN (''BLANKET'',''STANDARD'')'|| 
                  ' AND     nvl(PHA.CANCEL_FLAG,''N'')= ''N'''|| 
                  ' AND     nvl(PHA.CLOSED_CODE,''OPEN'') = ''OPEN'''||
                  ' AND     PHA.PO_HEADER_ID        = PLA.PO_HEADER_ID'||
                  ' AND     PLA.PO_LINE_ID          = PLLA.PO_LINE_ID'|| 
                  ' AND     PLA.QUANTITY            >  PLLA.QUANTITY_RECEIVED'||    
    '  '||c_smt1||
    '  '||c_smt2; 
     
     
  FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL_STATEMENT = '||C_SQL_SMT); 

    --V_MSG := 'OPEN CSR_TMR';
    OPEN CSR_TMR FOR c_sql_smt; --(PRA_BUYER_ID)
    FND_FILE.PUT_LINE(FND_FILE.LOG,'OPEN C_SQL_SMT'); 
    LOOP 
    --V_MSG := 'FETCH CSR_TMR';
  ----- fetch buyer ID into v_buyer_ID -----
  FETCH CSR_TMR INTO REC_TMR(k);
  FND_FILE.PUT_LINE(FND_FILE.LOG,'FETCH CSR_TMR'); 
  EXIT WHEN CSR_TMR%NOTFOUND;
 
  ----- auto submit request TMPO0080 -----
  FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST1'); 
  REQ_ID := FND_REQUEST.SUBMIT_REQUEST (
    'TIM',
    'TMPO0080',  
    NULL,
    NULL,
    FALSE,
    nvl(PRA_BUYER_ID,''),  
    nvl(PRA_VENDOR_ID,''),
    chr(0),
    '','','','','','','','','','',
           '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','');

  FND_FILE.PUT_LINE(FND_FILE.LOG,'SUMMIT REQUEST2'); 
  COMMIT;

           /*SELECT PV.SEGMENT1
           INTO T_VENDOR_CODE
           FROM PO_VENDORS PV
           WHERE PV.VENDOR_ID = V_VENDOR_ID; */ 

 IF REQ_ID = 0 THEN
    V_MSG := 'TMPO0080 encounter error, report not submitted';
    RAISE USR_ERROR;
 END IF;

          --- wait for TMPO0080 request finish ---
        T_SUCCESS := FND_CONCURRENT.WAIT_FOR_REQUEST (
                     REQ_ID,
                     2,
                     0,
                     lv_phase,
                     lv_status,
                     lv_dev_phase,
                     lv_dev_status,
                     lv_message );
 
        IF lv_phase = 'Completed' AND lv_status = 'Normal' THEN
    T_SUBJECT := 'Open PO - ' || T_VENDOR_CODE || ' - ' || T_BUYER_NAME;
           REC_REQ_ID(l) := REQ_ID;
           REC_EMAIL(l) := T_MAIL;
    REC_SUBJECT(l) := T_SUBJECT;
           l := l + 1; 
          
    --DBMS_LOCK.SLEEP(20);
           --tim_alrutils.ins_alert_email_subject (REQ_ID, T_MAIL, T_SUBJECT,'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
           --COMMIT;
        END IF; 

 k := k + 1;
 END LOOP;
 --V_MSG := 'CLOSE CSR_TMR';
 FND_FILE.PUT_LINE(FND_FILE.LOG,'CLOSE CSR_TMR'); 
 CLOSE CSR_TMR;

/*===== send email =====*/
FOR IDX IN 0 .. l-1 LOOP
   DBMS_OUTPUT.PUT_LINE(REC_REQ_ID(IDX));
   DBMS_LOCK.SLEEP(30);
  -- tim_alrutils.ins_alert_email_subject (REC_REQ_ID(IDX), REC_EMAIL(IDX), REC_SUBJECT(IDX),'1',PRA_COMMENT1, '', '', ERR_MSG, ERR_CODE);
   COMMIT;
END LOOP;
  
 EXCEPTION
      WHEN USR_ERROR THEN
    IF C0%ISOPEN THEN
       CLOSE C0;
    END IF;
      WHEN OTHERS THEN
    IF C0%ISOPEN THEN
       CLOSE C0;
    END IF;
 DBMS_OUTPUT.PUT_LINE ('USR_ERROR :' ||V_MSG);
 --- Log output of contents of an error ---
 DBMS_OUTPUT.PUT_LINE ('PROGRAM ID [TMSYS0080]');
 DBMS_OUTPUT.PUT_LINE ('ERROR    :' ||TO_CHAR(SQLCODE));
 DBMS_OUTPUT.PUT_LINE ('ERROR MSG:' || SQLERRM );
 CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);
END ;
/

Re: inconsistent datatypes: expected - got - [message #268928 is a reply to message #268904] Thu, 20 September 2007 02:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Remove the WHEN OTHERS - that will let you see the line that the error is happening at.
Re: inconsistent datatypes: expected - got - [message #269005 is a reply to message #268896] Thu, 20 September 2007 07:32 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
kogilaah_k wrote on Thu, 20 September 2007 02:12

FND_FILE.PUT_NAMES'/oca/ocafsh/tmp/TMPO0430.log','/oca/ocafsh/tmp/TMPO0430.out','/oca/ocafsh/tmp'); 



I don't see how that line could possibly work. Syntax error.
Previous Topic: count of all tables in a schema
Next Topic: Is there any Data dictionary table to track a package variable?
Goto Forum:
  


Current Time: Sat Dec 10 07:09:08 CST 2016

Total time taken to generate the page: 0.08816 seconds