topic RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works in Product Forum

OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Mon, 24 Feb 2020 23:48:00 GMT

Hello,
I am running into an issue I haven't experienced before. I have an OLEDB connection where I am running three different sql statements through getting collections. One of these is not pulling any data. If I copy the statement over to my sql developer and run it, it pulls data. It isn't causing any errors or bombing out, just not pulling any data.

Does anyone have any ideas regarding what may be the cause or has anyone ran into this issue before?

Thanks,

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

david.l.morris — Tue, 25 Feb 2020 12:12:00 GMT

My first thought is maybe you need some character escaping. One odd thing I've found is that Blue Prism will perform its own character escaping when you put a query statement inside of a data item rather than putting it directly in the input of an action such as in the OLEDB object. Where does your query string sit in Blue Prism? If you have it hard coded or dynamically built, try putting the static portions into data items to start with and see if it works.

I'm surprised it wouldn't throw an exception though. That's a head scratcher.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Thu, 27 Feb 2020 18:05:00 GMT

Hi Dave,
Thanks for your response. I create the sql from a calculation (as some things are passed through via data items that change depending on the invoice). Then that calculation is stored in a data item and the data item is used as the Value in the collection pull. This method has worked numerous times. The sql that is being created and stored in the data item can be copied and directly pasted to sql developer and it works. No exceptions are being thrown in BP. It just runs and doesn't pull anything........

I can't put the sql directly into the value of the Collection pull because it exceeds the 255 limit....

I'm out of ideas. Been screwing around with it for awhile.

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

vivek.kumar.goel — Fri, 28 Feb 2020 05:45:00 GMT

May be bit naive, but did you check if by mistake you have a semicolon at the end of the statement in the "dataitem" where the SQL is stored? If that's the case, just remove the semicolon 🙂

------------------------------
Vivek Goel
"If you like this post, please press the "Recommend" Button.
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

GopalBhaire — Fri, 28 Feb 2020 06:22:00 GMT

Can you paste the query which is causing the issue, are you using any wildcards?

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Wed, 04 Mar 2020 18:36:00 GMT

Hi Gopal,
Thank you for your response. Here is the sql.

SELECT
o586765.mcc_country AS e1081917,
o586772.mcc_product_line_desc AS e1081924,
o583784.accounting_date AS e1127909,
o583784.bank_location AS e1127923,
o583784.cancelled_date AS e1127935,
o583784.description AS e1127968,
o583784.distribution_description AS e1127972,
o583784.gl_period AS e1127987,
o583784.invoice_line_amount AS invoice_line_amount,
o583784.invoice_number AS invoice_number,
o583784.manually_inserted_due_date AS e1128032,
o583784.payment_date AS payment_date,
o583784.po_number AS po_number,
o583784.segment2 AS fund,
o586765.mcc_country AS country,
o583784.segment5 AS project,
o583784.segment9 AS activity,
o583784.status AS e1128197,
o583784.supplier_site AS e1128207,
o583784.payment_status_flag AS e1833969,
o583784.prepay_amount_remaining AS e1879948,
as1139650_1127987_old AS as1139650_1127987_old
FROM
(
SELECT DISTINCT
v.invoice_id,
v.po_distribution_id,
v.prepay_distribution_id,
v.invoice_distribution_id,
v.gl_period,
v.accounting_date,
v.payment_posted_flag posted_flag,
v.terms_code,
v.invoice_date,
v.invoice_number,
v.source,
v.voucher_num,
v.description,
v.date_received_by_nbc,
v.manually_inserted_due_date,
v.bank_location,
v.grant_po_number,
v.payment_date,
v.check_number,
v.treasury_pay_date,
v.treasury_pay_number,
v.pay_method,
v.pay_group,
v.status,
v.po_number,
v.po_line_type,
v.item_description,
v.buyer_name,
v.invoice_type,
v.invoice_line_number,
v.r12_invoice_line_number,
v.r12_distribution_line_number,
v.vendor_name,
v.vendor_site_code supplier_site,
v.distribution_description,
v.line_type,
v.ussgl_transaction_code,
v.invoice_line_amount,
v.r12_invoice_line_amount,
v.r12_distribution_line_amount,
v.invoice_amount,
v.payment_amount,
v.due_date,
v.cancelled_date,
v.segment1,
v.segment2,
v.segment3,
v.segment4,
v.segment5,
v.segment6,
v.segment7,
v.segment8,
v.segment9,
v.segment10,
v.distribution_account,
v.org_id,
v.org_name,
v.creation_date,
v.last_update_date,
v.created_by,
v.last_updated_by,
v.payment_status_flag,
ap.prepay_amount_remaining
FROM
cus01.xxibc_ap_bot_dist_v v,
xxibc_ap_invoice_dist_v ap
WHERE
v.invoice_distribution_id = ap.invoice_distribution_id
) o583784,
(
SELECT DISTINCT
ffvv.flex_value mcc_country,
ffvv.description mcc_country_desc,
ffvv.enabled_flag enabled_flag,
ffvv.end_date_active end_date
FROM
apps.fnd_flex_values_vl ffvv,
applsys.fnd_flex_value_sets ffvs
WHERE
ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvs.flex_value_set_name = 'MCC_PROD_PROGRAM' --VNickols added to accommodate new MCC LOA
AND ffvv.summary_flag = 'N'
ORDER BY
ffvv.flex_value
) o586765,
(
SELECT DISTINCT
ffvv.flex_value mcc_product_line,
ffvv.description mcc_product_line_desc,
ffvv.enabled_flag enabled_flag,
ffvv.end_date_active end_date
FROM
apps.fnd_flex_values_vl ffvv,
applsys.fnd_flex_value_sets ffvs
WHERE
ffvs.flex_value_set_id = ffvv.flex_value_set_id
AND ffvs.flex_value_set_name = 'MCC_PROD_ACTIVITY' --VNickols added to accommodate new MCC LOA
AND ffvv.summary_flag = 'N'
ORDER BY
ffvv.flex_value
) o586772,
(
SELECT
o1139611.period_name AS as1139650_1127987_old_2,
MAX(o1139611.effective_period_num) AS as1139650_1127987_old
FROM
gl.gl_period_statuses o1139611
WHERE
(
o1139611.application_id = 101
AND o1139611.set_of_books_id = 2
AND o1139611.closing_status IN (
'C',
'O'
)
)
GROUP BY
o1139611.period_name
)
WHERE
(
( o586765.mcc_country = o583784.segment3 )
AND ( o586772.mcc_product_line = o583784.segment9 )
AND ( o583784.gl_period = as1139650_1127987_old_2 (+) )
)
AND ( o586765.mcc_country = 'NER' )
AND ( o583784.accounting_date < '01-MAR-2020' )
AND (
(
o583784.status IS NULL
AND o583784.payment_status_flag = 'Y'
AND o583784.payment_date IS NULL
OR o583784.status <> 'VOIDED'
AND o583784.payment_status_flag = 'Y'
AND o583784.payment_date < '01-MAR-2020'
OR o583784.status = 'VOIDED'
AND o583784.cancelled_date > '01-MAR-2020'
AND o583784.payment_status_flag = 'N'
AND o583784.payment_date < '01-MAR-2020'
)
)
AND ( o583784.segment2 LIKE '%COM%' )
AND o583784.prepay_amount_remaining > 0
ORDER BY
o583784.bank_location ASC,
o583784.payment_date ASC

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Wed, 04 Mar 2020 18:39:00 GMT

Hi Vivek,
When you have semicolon or problem areas like that it will not run and send you to exceptions. In this case the sql is running properly with no errors, it is just not returning any data. I'm wondering if it can't read down to the levels of select within select within select. If that language that works in sql developer does not work on this side of things. I am unsure on that area.

Thanks,

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Wed, 11 Mar 2020 16:38:00 GMT

Hi Gopal,
I just wanted to check back and see if you had any ideas for me. Still spinning my wheels on this one.

Thanks,

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

GopalBhaire — Thu, 12 Mar 2020 05:28:00 GMT

Sorry Jonathan, I couldn't really figure out what's the issue with the Query if there is or if it is issue with OLEDB not supporting so many select within one query.

Have you tried to check the outcome of all individual select query.

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Thu, 12 Mar 2020 14:36:00 GMT

Hi Gopal,
I tried each query individually and it seemed to be working fine. Any ideas for a workaround here?

I'm thinking I'll open a ticket with BluePrism support.

Thanks for your time.
Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

ChrisNewsom — Thu, 12 Mar 2020 16:43:00 GMT

Have you tried amended the date format? We have experienced issues before as Blue Prism formats dates differently to SQL Developer.

------------------------------
Christopher Newsom
RPA Developer
Leeds Building Society
Europe/London
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Thu, 12 Mar 2020 16:52:00 GMT

Hi Christopher,
I'll give that a shot. Wouldn't that date problem cause the collection to end in exception?

Thanks,
Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

JonathanHolstin — Tue, 31 Mar 2020 14:37:00 GMT

Hi Christopher,
I originally just changed from passing a variable for date to a hard coded '01-MAR-2020'. It turns out after a bunch more trouble shooting it rolled back to the date. Any chance you know what the issue/resolution was on your end for dates?

Thanks!
Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

GopalBhaire — Wed, 01 Apr 2020 04:40:00 GMT

Can you write this as store procedure and then run from BP?
Was BP support of any help?

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

KeithWoof — Mon, 19 Oct 2020 11:15:00 GMT

Hi Jonathan,
Have you got this resolved?
If not try updating the SQL to derive the date from SYSDATE.
Alternatively we use a calculation to replace date parameters in the SQL and format the date to this format 'yyyy-MM-dd HH24:MI:SS'

------------------------------
Keith Woof
RPA Manager
Leeds Building Society
Europe/London
------------------------------

RE: OLEDB - Oracle - Not pulling data - SQL Dev pull works

AndreyKudinov — Thu, 22 Oct 2020 14:04:00 GMT

Try removing comments "--VNickols added to accommodate new MCC LOA" etc, if something is wrong with newlines, that might screw things, but just a wild guess.

In general, if you use multiline queries, store them(parts) in text dataitems, dont just set input to something like:
"select *
from tbl
where xx="&[myvar]
This often looses newlines..
------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------