0

I am trying to call a Microsoft SQL Server stored procedure that delivers data in table format in Oracle BI Publisher 11g. I want to pass two standard BIP parameters to this procedure call. Is there any work around to call SQL Server procedure? I tried few examples available from the internet. but no luck. 

declare @sql varchar(255)  
set @sql = 'EXEC GET_USER_REPORT ' +'''' + cast(:P_REPORT_START_DATE as varchar) + '''' +','+ '''' +cast(:P_REPORT_END_DATE as varchar) + '''' 
exec (@sql)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Santhosh_ms3
  • 110
  • 2
  • 3
  • 15
  • 1
    What error do you get? You have a mix of SQL server parameters and oracle bind variables there. – Nick.Mc Aug 15 '17 at 01:57
  • "Failed to load XML." - Yes that's how mentioned in the article. – Santhosh_ms3 Aug 15 '17 at 02:33
  • Can you link to the article. – Nick.Mc Aug 15 '17 at 03:03
  • https://stackoverflow.com/questions/27396110/how-do-you-call-a-sql-server-stored-procedure-in-bi-publisher-11g – Santhosh_ms3 Aug 15 '17 at 04:27
  • Do you have any idea of whether "Failed to load XML" is an error from BI Publisher or from SQL Server? Is there any other information? – Nick.Mc Aug 15 '17 at 04:29
  • Looks like its a generic and unhelpful error from BI Publisher. See here on how to get more info on the error. https://community.oracle.com/thread/4047557 When you find more info, please edit your question and all of the info that you've posted in comments as well as whatever additional detail you can find. – Nick.Mc Aug 15 '17 at 04:31
  • Here's another one. This one was a timeout issue. You should be able to work out what the real error is by using the " Data Engine Log button" as posted in my previous comment https://stackoverflow.com/questions/44137775/bi-publisher-failed-to-load-xml-after-trying-to-view-data – Nick.Mc Aug 15 '17 at 04:40
  • I tried the stored_proc call with passing the parameters and it works. exec [sp_name]; {since parameters are optional}. so something with the syntax? sure let me get the data engine logs. – Santhosh_ms3 Aug 15 '17 at 04:41

1 Answers1

1

There are tons of articles which have been written on this subject:

1.) The detailed procedure outlined in an example on the Oracle A-Team blog: http://www.ateam-oracle.com/executing-a-stored-procedure-from-oracle-business-intelligence-cloud-service-bics/

2.) The 11g documentation for exactly this functionality: https://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM149

3.) The 12c documentation for this: https://docs.oracle.com/middleware/12212/bip/BIPDM/GUID-F9F844BA-5831-46E5-B085-44D82258A9A9.htm#BIPDM147

Chris
  • 2,706
  • 2
  • 13
  • 15