Error: Data Model definition not found:/Custom/Human Capital Management/FusionSQLtoolTest1/FusionSQLToolDM.xdm

[johnboychan]

Hi Anand,

[1] I am getting the attached error message when I try to run a simple query. oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport for reportAbsolutePath [/Custom/Human Capital Management/FusionSQLtoolTest1/FSTreport.xdo] failed: due to oracle.xdo.server.ReportException: oracle.xdo.servlet.CreateException: oracle.xdo.servlet.CreateException: oracle.xdo.servlet.CreateException: Data Model definition not found:/Custom/Human Capital Management/FusionSQLtoolTest1/FusionSQLToolDM.xdm

John > The above error seems to indicate that I need a BI data model in the Custom folder?

[2] I downloaded the exe and followed the install process.

[3] I entered our dev pod and the user I am connecting with has the 'Human Capital Management Integration Specialist' role.

Am I missing something obvious?

Cheers John

[anandvegaraju]

Hi John,

Thanks for reaching out. Hope you are doing well. Did you click on "Initialize Environment" before running your query? Download the latest/updated exe (same link), click on the "Initialize Environment" button after entering your instance URL, username and password. This should create a data model and report in the path -> Custom/Human Capital Management/FusionSQLtoolTest1. Please let me know if you continue to face this problem.

Thanks and Regards, Anand

On Wed, 9 Sep 2020 at 18:44, johnboychan notifications@github.com wrote:

Hi Anand,

[1] I am getting the attached error message when I try to run a simple query. oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport for reportAbsolutePath [/Custom/Human Capital Management/FusionSQLtoolTest1/FSTreport.xdo] failed: due to oracle.xdo.server.ReportException: oracle.xdo.servlet.CreateException: oracle.xdo.servlet.CreateException: oracle.xdo.servlet.CreateException: Data Model definition not found:/Custom/Human Capital Management/FusionSQLtoolTest1/FusionSQLToolDM.xdm

John > The above error seems to indicate that I need a BI data model in the Custom folder?

[2] I downloaded the exe and followed the install process.

[3] I entered our dev pod and the user I am connecting with has the 'Human Capital Management Integration Specialist' role.

[image: image] https://user-images.githubusercontent.com/11960330/92602557-2f489400-f2a6-11ea-979e-ee3edc6b1f20.png

Am I missing something obvious?

Cheers John

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/anandvegaraju/FusionSQLite/issues/2, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEJQ5A4JNQMPUKJSCZSZNKTSE55VDANCNFSM4RCHBO3A .

[johnboychan]

Hi Anand,

I have tried again and getting closer.

[1] I have downloaded the latest and updated exe and clicked on "Initialize Environment. I can see the data model and report in path: Custom/Human Capital Management/FusionSQLtoolTest1

[2] I get the following error when running the following query.

select * from per_all_assignments_m where person_id = 100000005343733 and rownum < 10;

or

select * from per_all_assignments_m where rownum < 10;

oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport for reportAbsolutePath [/Custom/Human Capital Management/FusionSQLtoolTest1/FSTreport.xdo] failed: due to oracle.xdo.server.ServerException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: SQLInjection Error: Invalid parameter value select * from per_all_assignments_m where rownum < 10;

I have attached screenshots.

Do you know why I would get SQLInjection Error?

Kind Regards John FusionSQLite 20200921.docx

[anandvegaraju]

Hi John,

Can you try again after removing the semicolon?

Thanks and Regards, Anand

On Mon, 21 Sep 2020 at 17:41, johnboychan notifications@github.com wrote:

Hi Anand,

I have tried again and getting closer.

[1] I have downloaded the latest and updated exe and clicked on "Initialize Environment. I can see the data model and report in path: Custom/Human Capital Management/FusionSQLtoolTest1

[2] I get the following error when running the following query.

select * from per_all_assignments_m where person_id = 100000005343733 and rownum < 10;

or

select * from per_all_assignments_m where rownum < 10;

oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport for reportAbsolutePath [/Custom/Human Capital Management/FusionSQLtoolTest1/FSTreport.xdo] failed: due to oracle.xdo.server.ServerException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: SQLInjection Error: Invalid parameter value select * from per_all_assignments_m where rownum < 10;

I have attached screenshots.

Do you know why I would get SQLInjection Error?

Kind Regards John FusionSQLite 20200921.docx https://github.com/anandvegaraju/FusionSQLite/files/5255233/FusionSQLite.20200921.docx

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/anandvegaraju/FusionSQLite/issues/2#issuecomment-696073925, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEJQ5AYNEXULLZCEAXUQF4DSG47IRANCNFSM4RCHBO3A .

[johnboychan]

Hi Anand,

I have tried initializing again and running without semi colon but still get the same error.

This is the cloud version of OBIEE we are using. Oracle Business Intelligence 11.1.1.9

This is the release we are on. Oracle Cloud Application 20C (11.13.20.07.0)

Cheers John FusionSQLite 20200921_2.docx

[anandvegaraju]

That's strange. I'm able to run it. Can you try passing the select query as a parameter to the data model directly?

[image: image.png]

On Mon, 21 Sep 2020 at 18:05, johnboychan notifications@github.com wrote:

Hi Anand,

I have tried initializing again and running without semi colon but still get the same error.

Cheers John FusionSQLite 20200921_2.docx https://github.com/anandvegaraju/FusionSQLite/files/5255354/FusionSQLite.20200921_2.docx

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/anandvegaraju/FusionSQLite/issues/2#issuecomment-696085133, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEJQ5A6CO3WXIDK7X5NYLX3SG5B77ANCNFSM4RCHBO3A .

[johnboychan]

Hi Anand,

I tried running with the default query 'select from dual' in the datamodel and got the same error. I tried with my original query and got the same error. I tried removing the character e.g

select assignment_number from per_all_assignments_f where rownum < 3 and still get the error.

I think our OBIEE instance may have some setting to stop anything that looks like sql statements from running? incase it looks like sql injection??? I am checking BI documentation.

FusionSQLToolDM_-2.xml.txt FusionSQLite 20200921_3.docx

Cheers John

[anandvegaraju]

Yeah. You're probably right. You can check your BI administrator settings for such protection settings.

On Mon, Sep 21, 2020, 18:54 johnboychan notifications@github.com wrote:

Hi Anand,

I tried running with the default query 'select from dual' in the datamodel and got the same error. I tried with my original query and got the same error. I tried removing the character e.g

select assignment_number from per_all_assignment_f where rownum < 3 and still get the error.

I think our OBIEE instance may have some setting to stop anything that looks like sql statements from running? incase it looks like sql injection??? I am checking BI documentation.

FusionSQLToolDM-2.xml.txt <https://github.com/anandvegaraju/FusionSQLite/files/5255536/FusionSQLToolDM-2.xml.txt> FusionSQLite 20200921_3.docx https://github.com/anandvegaraju/FusionSQLite/files/5255542/FusionSQLite.20200921_3.docx

Cheers John

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/anandvegaraju/FusionSQLite/issues/2#issuecomment-696112078, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEJQ5A527OTKE7VGLPYU7ETSG5HXFANCNFSM4RCHBO3A .

[anandvegaraju]

Will work on a new approach to avoid ref cursor soon. Maybe that would workaround the problem in your case.

On Mon, Sep 21, 2020, 18:59 Anand Vegaraju v.anand.vamsi@gmail.com wrote:

Yeah. You're probably right. You can check your BI administrator settings for such protection settings.

On Mon, Sep 21, 2020, 18:54 johnboychan notifications@github.com wrote:

Hi Anand,

I tried running with the default query 'select from dual' in the datamodel and got the same error. I tried with my original query and got the same error. I tried removing the character e.g

select assignment_number from per_all_assignment_f where rownum < 3 and still get the error.

I think our OBIEE instance may have some setting to stop anything that looks like sql statements from running? incase it looks like sql injection??? I am checking BI documentation.

FusionSQLToolDM-2.xml.txt <https://github.com/anandvegaraju/FusionSQLite/files/5255536/FusionSQLToolDM-2.xml.txt> FusionSQLite 20200921_3.docx https://github.com/anandvegaraju/FusionSQLite/files/5255542/FusionSQLite.20200921_3.docx

Cheers John

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/anandvegaraju/FusionSQLite/issues/2#issuecomment-696112078, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEJQ5A527OTKE7VGLPYU7ETSG5HXFANCNFSM4RCHBO3A .

[johnboychan]

Hi Anand,

I cant find such a setting and was about to raise SR with Oracle as I cannot find the SQLInjection error mentioned in Oracle Cloud Support. If I hard code the sql then it works fine and I can see the details in the FusionSQL UI.

DECLARE type refcursor is REF CURSOR; xdo_cursor refcursor; queryStr varchar2(32767); BEGIN queryStr := 'select assignment_number from per_all_assignments_m where rownum < 3'; OPEN :xdo_cursor FOR queryStr; END;

I am not in a hurry so happy to wait on your new approach

CHeers John

[johnboychan]

Hi Anand,

I will close this issue after it had been highlighted to me the following link https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/r13/wn/common/releases/20C/20C-common-wn.htm#T21584

Look forward to your new solution.

Cheers John

[anandvegaraju]

Hi John,

Can you please try this version and let me know if it works?

Regards, Anand

[masebu]

Works for me on HCM 20D 😀 nice !

[anandvegaraju]

Great! I Will work on optimizing this further.

On Fri, 29 Jan 2021 at 14:29, masebu notifications@github.com wrote:

Works for me on HCM 20D 😀 nice !

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/anandvegaraju/FusionSQLite/issues/2#issuecomment-769673011, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEJQ5AY4K2SO3QJ7HI6HPXTS4J2GLANCNFSM4RCHBO3A .