Code Working in SQLdev but not BI Publisher

User_K63FU

Hello,

The below code is working in SQLdev, and will read in to BI Publisher. However, when I go to view data I am getting a 'SQL or ORA exceptions caused due to invalid sql database package state or some database exceptions' error message. My huynch is it has something to do with the 'extract' statement, but my research has yielded nothing thus far. Please feel free to chime in!

SELECT VEAS.PERSON_CODE,
    VEAS.NAME,
    veas.termination_date,
    '11'||SUBSTR(DDS.DISTRIBUTION_CODE, 31, 9) ORG,
    extract(MONTH from AAL.START_DATE) || '-' || extract(YEAR from AAL.START_DATE) MonthYear,
    Sum(aald.amount)
FROM 		  P2K_HR_VASSIGNMENTS VEAS
INNER JOIN 	P2K_CM_DISTRIBUTIONS DDS ON VEAS.EASD_ID = DDS.EASD_ID
inner join P2K_AT_LEAVE_LINES AAL on AAL.EEM_ID                    = VEAS.EEM_ID
INNER JOIN P2K_AT_LEAVE_LINE_DETAILS AALD ON AAL.ID = AALD.AAL_ID
    INNER JOIN P2K_AT_LEAVE_POLICY_TYPES ALPT ON ALPT.ID = AAL.ALPT_ID
    INNER JOIN P2K_AT_ATTENDANCE_COMPONENTS AAC ON AAC.ID = AALD.AAC_ID
WHERE VEAS.PRIME_ASSIGNMENT = '1'
	AND VEAS.UNIT_CODE NOT IN ('OPS', 'ELECTED')
 and ALPT.LEAVE_TYPE_CODE              = 'SICK POOL JOINT'
	AND VEAS.DES_ID IN (SELECT DES.ID FROM P2K_CM_EMPLOYMENT_STATUSES DES WHERE DES.STATUS_TYPE IN ('L', 'A'))
  AND AAL.START_DATE BETWEEN VEAS.EFFECTIVE AND VEAS.EXPIRY
    AND AAL.START_DATE BETWEEN TO_DATE(:FY_START,'MM/DD/YYYY') AND TO_DATE(:FY_END,'MM/DD/YYYY')
    AND AAL.ACCRUAL_STATUS ='02'
    AND AAC.AC_CODE = 'A300'
 AND VEAS.PERSON_CODE = '1001972'
	GROUP BY 	VEAS.PERSON_CODE,
				VEAS.NAME,
    veas.termination_date,
				'11'||SUBSTR(DDS.DISTRIBUTION_CODE, 31, 9),
    extract(MONTH from AAL.START_DATE) || '-' || extract(YEAR from AAL.START_DATE)
ORDER BY 2, 3, 5

Mallikarjuna Kuppauru-Oracle

Hi @User_K63FU

Are you using option - calling your package using before/after trigger option? -- If yes, better to compile your invalid objects first before calling from BIP Data model.

Regards,

Arjun

Gianni Ceresa

Hi,

If EXTRACT is the one giving you issues, why don't you replace all that piece by a TO_CHAR ? You only seem to want to format a date into month-year, to_char() can do that as you pass the format mask to the function (as long as your date is a datetime, can't remember if to_char() works on intervals...).

Also, it's possible that in SQL Developer you are only retrieving a subset of records, and they just work, but your query would generate an error as well if you were to run it on all the records.

I am getting a 'SQL or ORA exceptions

With the exact error message it could be easier to maybe give a better answer.

User_K63FU

https://community.oracle.com/products/oracleanalytics/discussion/comment/52934#Comment_52934

Thank you for the response, I'll try the 'to_char' suggestion. Unfortunately this is the entirety of the error message:

SQL or ORA exceptions caused due to invalid sql database package state or some database exceptions

Gianni Ceresa

Ah, well, that error message clearly doesn't help you :P

To make sure your query fully works on all the rows in SQL Developers as well, you can try to retrieve all the records of the result set. Or write that query into a new table: if there is an error you will get the details (but it's possible the query seems to work in SQL Developer while the error is there).

User_K63FU

This issue is now resolved.