PL-SQL - ORA-00932: inconsistent datatypes: expected DATE got NUMBER

0
votes

Trying to loop through a sysrefcursor but getting ORA-00932 seems like when looping, the dates, e.g.: 1990/01/01 get operated on as if they were a division ?

set serveroutput on;
DECLARE
    email VARCHAR2(1000);
    webcastEngagement NUMBER(10,1);
    videoEngagement NUMBER(10,1);
    documentEngagement NUMBER(10,1);
    totalEngagement NUMBER(10,1);
    --averageEngagement NUMBER(4,1);
    totalWebcastSeconds NUMBER(10);
    engagementMinutes NUMBER(10, 1);
    last30DaysEM NUMBER(10, 1);
    last60DaysEM NUMBER(10, 1);
    fromDate DATE;
    engagementPrediction NUMBER(10);
    engagementLevel VARCHAR2(6 CHAR);
    totalWebcasts NUMBER(10);
    totalVideos NUMBER(10);
    totalDocuments NUMBER(10);
    totalURLs NUMBER(10);
    firstName VARCHAR2(1000);
    lastName VARCHAR2(1000);
    company VARCHAR2(1000);
    jobTitle VARCHAR2(1000);
    workPhone VARCHAR2(1000);
    clientName VARCHAR2(1000);
    portalEnabled VARCHAR2(5);
    resources NUMBER(10);
    videoProfile NUMBER;
    showInterestCloud VARCHAR2(10);
    attended VARCHAR(1);
    leadIndex NUMBER := 1;
    clientFunnelStages VARCHAR2(4000);
    funnelStage VARCHAR2(100 CHAR);
    partnerref VARCHAR2(4000);
    experienceProfileId NUMBER := 525;
    resp    ON24MASTER.WEBCAST_REPORTS.ResultSetCursor;
BEGIN
    resp := WEBCAST_REPORTS.LEAD_BASIC_INFO('[email protected]',22917);
    LOOP
        FETCH resp into email, webcastEngagement,videoEngagement,documentEngagement, totalEngagement,totalWebcastSeconds,engagementMinutes,last30DaysEM,last60DaysEM, 
                        fromDate,-- also tried to_date(fromDate, 'YYYY-MM-DD')
                        engagementPrediction,engagementLevel,totalWebcasts, totalVideos, totalDocuments, totalURLs, firstName,lastName,company, jobTitle,workPhone,
                        clientName,portalEnabled,resources,videoProfile,showInterestCloud,attended,leadIndex,clientFunnelStages,funnelStage,partnerref,experienceProfileId;

        dbms_output.put_line(email|| ' ---- ' || webcastEngagement|| ' ---- ' ||videoEngagement|| ' ---- ' ||documentEngagement|| ' ---- ' || totalEngagement|| ' ---- ' ||totalWebcastSeconds|| ' ---- ' ||engagementMinutes|| ' ---- ' ||last30DaysEM|| ' ---- ' ||last60DaysEM|| ' ---- ' ||fromDate|| ' ---- ' ||
                        engagementPrediction|| ' ---- ' ||engagementLevel|| ' ---- ' ||totalWebcasts|| ' ---- ' || totalVideos|| ' ---- ' || totalDocuments|| ' ---- ' || totalURLs|| ' ---- ' || firstName|| ' ---- ' ||lastName|| ' ---- ' ||company|| ' ---- ' || jobTitle|| ' ---- ' ||workPhone|| ' ---- ' ||
                        clientName|| ' ---- ' ||portalEnabled|| ' ---- ' ||resources|| ' ---- ' ||videoProfile|| ' ---- ' ||showInterestCloud|| ' ---- ' ||attended|| ' ---- ' ||leadIndex|| ' ---- ' ||clientFunnelStages|| ' ---- ' ||funnelStage|| ' ---- ' ||partnerref|| ' ---- ' ||experienceProfileId);
    exit when resp%notfound;
    END LOOP;
    CLOSE resp;
END;
/

in function body, fromDate DATE does very simple things, it will be assigned similar to: fromDate := SYSDATE - 60

My goal is to output the result set.

What currently works, has horrible output:

variable rc refcursor;

DECLARE
  LEADEMAIL VARCHAR2(200);
  CLIENTID NUMBER;
BEGIN
  LEADEMAIL := '[email protected]';
  CLIENTID := 22921;

  :rc := WEBCAST_REPORTS.LEAD_BASIC_INFO(
    LEADEMAIL => LEADEMAIL,
    CLIENTID => CLIENTID
  );

END;
/

print rc;

This is part of the output:

EMAIL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             CLIENT_ID
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
CLIENT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      CG_ACTIVE                        WEBCAST_ENGAGEMENT VIDEO_ENGAGEMENT DOCUMENT_ENGAGEMENT TOTAL_ENGAGEMENT AVG_WEBCAST_MINUTES  NGAGEMENT_MINUTES TOTAL_WEBCASTS

TOTAL_VIDEOS TOTAL_DOCUMENTS TOTAL_URLS A PREDIC LAST_30D_ENGAGEMENT_MINUTES LAST_60D_ENGAGEMENT_MINUTES ENGAGEMENT_LEVEL                 FIRST_NAME
------------ --------------- ---------- - ------ --------------------------- --------------------------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
JOB_TITLE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WORK_PHONE

COMPANY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          REGISTRATION_SOURCE              H H H H  RESOURCES S
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- - - - - ---------- -
FUNNEL_STAGE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           0               0          0 Y HIGH                            34                          34 LOW                              test                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

EMAIL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             CLIENT_ID
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
CLIENT_NAME

Update

 FUNCTION LEAD_BASIC_INFO(
    leadEmail VARCHAR2,
    clientId NUMBER
  ) RETURN ResultSetCursor IS

 -- rest of the variables
 -- more code 

   OPEN resultSet FOR
      select
        nvl(email, leadEmail) EMAIL,
        clientId CLIENT_ID,
        clientName CLIENT_NAME,
        portalEnabled CG_ACTIVE,
        nvl(webcastEngagement, 0) WEBCAST_ENGAGEMENT,
        nvl(videoEngagement, 0) VIDEO_ENGAGEMENT,
        nvl(documentEngagement, 0) DOCUMENT_ENGAGEMENT,
        nvl(totalEngagement, 0) TOTAL_ENGAGEMENT,
        --nvl(averageEngagement, 0) AVERAGE_ENGAGEMENT,
        case when nvl(totalWebcasts, 0) = 0
          then 0
          else nvl(round((totalWebcastSeconds/totalWebcasts)/60, 1), 0)
        end AVG_WEBCAST_MINUTES,
        nvl(engagementMinutes, 0) ENGAGEMENT_MINUTES,
        nvl(totalWebcasts, 0) TOTAL_WEBCASTS,
        nvl(totalVideos, 0) TOTAL_VIDEOS,
        nvl(totalDocuments, 0) TOTAL_DOCUMENTS,
        nvl(totalURLs, 0) TOTAL_URLS,
        case when (nvl(totalWebcasts, 0) + nvl(totalVideos, 0) + nvl(totalDocuments, 0) + nvl(totalURLs, 0) > 0) then 'Y' else 'N' end ATTENDED,
        case engagementPrediction when 1 then 'LOW' when 2 then 'MEDIUM' when 3 then 'HIGH' else '' end as PREDICTIVE_ENGAGEMENT,
        last30DaysEM as LAST_30D_ENGAGEMENT_MINUTES,
        last60DaysEM as LAST_60D_ENGAGEMENT_MINUTES,
        engagementLevel as ENGAGEMENT_LEVEL,
        nvl(firstName, '') FIRST_NAME,
        nvl(lastName, '') LAST_NAME,
        nvl(jobTitle, '') JOB_TITLE,
        nvl(workPhone, '') WORK_PHONE,
        nvl(company, '') COMPANY,
        nvl(partnerref, '') REGISTRATION_SOURCE,
        case when (length(trim(firstName)) > 0 or length(trim(lastName)) > 0)
          then 'Y'
          else 'N'
        end HAS_NAME,
        case when (length(trim(jobTitle)) > 0)
          then 'Y'
          else 'N'
        end HAS_JOB_TITLE,
        case when (length(trim(workPhone)) > 0)
          then 'Y'
          else 'N'
        end HAS_WORK_PHONE,
        case when (length(trim(company)) > 0)
          then 'Y'
          else 'N'
        end HAS_COMPANY,
        nvl(resources, 0) RESOURCES,
        case when (lower(showInterestCloud) = 'yes')
          then 'Y'
          else 'N'
        end SHOW_BIS,
        funnelStage FUNNEL_STAGE
      from dual;

    RETURN resultSet;
  END LEAD_BASIC_INFO;
1
plsqloracle11g
Pleas post signature of MY_PACKAGE.FUNCTION('somval') - Lukasz Szozda
What's the type of the column? Does the function do something with the values or just select? - i100
Your fetch command is missing a semicolon, and the loop is missing an exit. - William Robertson
A working test case would help a lot. - William Robertson
If you want help figuring out why your code isn't working, post your actual code. Not something from memory, or that you made up to show what it's like, but your actual copied and pasted code. As you can see, typing something else in can introduce other errors. It's a waste of both our time and yours when you don't provide your actual code. - Ken White

1 Answers

2
votes

Your `fetch into is expecting a date in the 10th column:

FETCH resp into email,  -- 1
  webcastEngagement,    -- 2
  videoEngagement,      -- 3
  documentEngagement,   -- 4
  totalEngagement,      -- 5
  totalWebcastSeconds,  -- 6
  engagementMinutes,    -- 7
  last30DaysEM,         -- 8
  last60DaysEM,         -- 9
  fromDate,             -- 10
  ...

The 10th column in your function's query is a number:

   OPEN resultSet FOR
      select
        nvl(email, leadEmail) EMAIL,                                    -- 1
        clientId CLIENT_ID,                                             -- 2
        clientName CLIENT_NAME,                                         -- 3
        portalEnabled CG_ACTIVE,                                        -- 4
        nvl(webcastEngagement, 0) WEBCAST_ENGAGEMENT,                   -- 5
        nvl(videoEngagement, 0) VIDEO_ENGAGEMENT,                       -- 6
        nvl(documentEngagement, 0) DOCUMENT_ENGAGEMENT,                 -- 7
        nvl(totalEngagement, 0) TOTAL_ENGAGEMENT,                       -- 8
        --nvl(averageEngagement, 0) AVERAGE_ENGAGEMENT,                 
        case when nvl(totalWebcasts, 0) = 0
          then 0
          else nvl(round((totalWebcastSeconds/totalWebcasts)/60, 1), 0)
        end AVG_WEBCAST_MINUTES,                                        -- 9
        nvl(engagementMinutes, 0) ENGAGEMENT_MINUTES,                   -- 10
        ...

The two lists of columns/variables seem to be in completely different orders. For instance, WEBCAST_ENGAGEMENT is the fifth column in your query, but second in your fetch.

Fetch is positional - it doesn't matter what the result set columns are called (or if they are aliased at all), you have to fetch them into the variables in the same order they appear in the query's select list.

You don't actually seem to have any date columns in your query, called fromDate or anything else.

So, you are trying to fetch the number supplied in the query's 10th column, the nvl() that's aliased as ENGAGEMENT_MINUTES, into the date variable fromDate. Hence the error - expected DATE (because that's how fromDate is declared) got NUMBER (because that's what ENGAGEMENT_MINUTES is).