Esteban Rincon
Reputation: 2110
PL-SQL - ORA-00932: inconsistent datatypes: expected DATE got NUMBER
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;
Upvotes: 0
Views: 681
Answers (1)
Alex Poole
Reputation: 191570
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).
Upvotes: 2
Related Questions
- What is this error message? ORA-00932: inconsistent datatypes: expected DATE got NUMBER
- "ORA-01861: literal does not match format string" Error in PL/SQL
- SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s"
- Inconsistent Datatypes: Expected DATE got NUMBER
- Not able to convert into date in oracle
- ORA-00932: inconsistent datatypes: expected CHAR got NUMBER while adding 1 to a date
- SQL Oracle Date issue: ORA-01722: invalid number
- Error(38,1): PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
- ORA-00932: inconsistent datatypes: expected TIMESTAMP got TIMESTAMP
- ORA-01861: literal does not match format string 'date datatype error'