cancel
Showing results for 
Search instead for 
Did you mean: 

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

JonathanHolstin
Level 5

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
------------------------------
15 REPLIES 15

david.l.morris
Level 15
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
------------------------------

Dave Morris, 3Ci at Southern Company

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
------------------------------

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.
------------------------------

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

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

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------