Connect to OBIEE from Oracle SQL Developer?











up vote
1
down vote

favorite












Is it possible to create a connection to OBIEE from Oracle SQL Developer?



I know it is possible to create an ODBC connection to the Oracle BI server within the Windows ODBC Data Source Administrator, and that other ODBC client tools can use that ODBC connection to run SQL against the BI server, but I don't see any way for SQL Developer to use that ODBC connection.



I'm on OBIEE 12.2.1.4 and SQL Developer 17.3.0.271.



UPDATE



Based on Robin Moffatt's article at https://rmoff.net/2016/03/28/connecting-to-obiee-via-jdbc-with-jisql/, I used jisql (https://www.xigole.com/software/jisql/jisql.jsp) to set up a connection to OBIEE using the JDBC driver that ships with the OBIEE client -- bijdbc.jar, which is located at $ORACLE_HOME/bi/bifoundation/jdbc/.



Following is a screenshot showing how jisql is able to successfully connect to my OBIEE server via the bijdbc.jar driver and run an example SQL statement; the Oracle-specific connection details are outlined in red:



Screenshot 1: OBIEE JDBC connection test using jisql



My next step was to try to implement this same successful JDBC connection in SQL Developer. To that end, I added bijdbc.jar to SQL Developer's third-party JDBC drivers:



Screenshot 2: bijdbc.jar added to SQL Developer third-party JDBC drivers



After adding the JDBC driver, I did not see a new tab in SQL Developer's connection dialog. (By comparison, in the past, when I have added other third-party JDBC drivers, such as the jTDS and MySQL drivers also seen in screenshot 2, new tabs had become available.)



Since there was not a new tab in SQL Developer's connection dialog, I tried setting the Oracle tab's connection type to Advanced and inserting the JDBC connection string as the Custom JDBC URL. Testing that connection leads to a "String index out of range: -1", as seen in this screenshot:



Screenshot 3: Error using custom JDBC URL in SQL Developer



Since that same JDBC connection string works in jisql, I suspect that either I'm doing something wrong, or SQL Developer does not support connecting to OBIEE via that bijdbc driver.










share|improve this question









New contributor




myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • Question: What is your goal? What would you want to achieve with that?
    – Chris
    Nov 5 at 10:01










  • if your BI Server is an oracle database, then yes - but you won't use an odbc source, because SQL Developer is java, it uses jdbc. You would create a jdbc connection in sql developer using the same connection properties defined in your odbc source
    – thatjeffsmith
    Nov 5 at 13:25










  • @Chris: For testing purposes, I would like to be able to run SQL statements directly against the BI server. E.g., select max("Calendar Date") from "Sample Sales Lite"."Time". I know that this can be done within OBIEE's /analytics web app (under Administration > Issue SQL), but I'd prefer using SQL Developer if possible.
    – myfriendedward
    2 days ago










  • @thatjeffsmith: In the ODBC connection, the relevant properties appear to be the server (192.168.223.3) and the port (9514); I have confirmed that this connection works without issue. In SQL Developer, when I try creating a new Oracle connection with hostname = 192.168.223.3 and port = 9514 (with SID and service name left blank), I get "IO Error: Bad packet type".
    – myfriendedward
    yesterday










  • I've never seen that error before, but it's network related I believe
    – thatjeffsmith
    yesterday















up vote
1
down vote

favorite












Is it possible to create a connection to OBIEE from Oracle SQL Developer?



I know it is possible to create an ODBC connection to the Oracle BI server within the Windows ODBC Data Source Administrator, and that other ODBC client tools can use that ODBC connection to run SQL against the BI server, but I don't see any way for SQL Developer to use that ODBC connection.



I'm on OBIEE 12.2.1.4 and SQL Developer 17.3.0.271.



UPDATE



Based on Robin Moffatt's article at https://rmoff.net/2016/03/28/connecting-to-obiee-via-jdbc-with-jisql/, I used jisql (https://www.xigole.com/software/jisql/jisql.jsp) to set up a connection to OBIEE using the JDBC driver that ships with the OBIEE client -- bijdbc.jar, which is located at $ORACLE_HOME/bi/bifoundation/jdbc/.



Following is a screenshot showing how jisql is able to successfully connect to my OBIEE server via the bijdbc.jar driver and run an example SQL statement; the Oracle-specific connection details are outlined in red:



Screenshot 1: OBIEE JDBC connection test using jisql



My next step was to try to implement this same successful JDBC connection in SQL Developer. To that end, I added bijdbc.jar to SQL Developer's third-party JDBC drivers:



Screenshot 2: bijdbc.jar added to SQL Developer third-party JDBC drivers



After adding the JDBC driver, I did not see a new tab in SQL Developer's connection dialog. (By comparison, in the past, when I have added other third-party JDBC drivers, such as the jTDS and MySQL drivers also seen in screenshot 2, new tabs had become available.)



Since there was not a new tab in SQL Developer's connection dialog, I tried setting the Oracle tab's connection type to Advanced and inserting the JDBC connection string as the Custom JDBC URL. Testing that connection leads to a "String index out of range: -1", as seen in this screenshot:



Screenshot 3: Error using custom JDBC URL in SQL Developer



Since that same JDBC connection string works in jisql, I suspect that either I'm doing something wrong, or SQL Developer does not support connecting to OBIEE via that bijdbc driver.










share|improve this question









New contributor




myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • Question: What is your goal? What would you want to achieve with that?
    – Chris
    Nov 5 at 10:01










  • if your BI Server is an oracle database, then yes - but you won't use an odbc source, because SQL Developer is java, it uses jdbc. You would create a jdbc connection in sql developer using the same connection properties defined in your odbc source
    – thatjeffsmith
    Nov 5 at 13:25










  • @Chris: For testing purposes, I would like to be able to run SQL statements directly against the BI server. E.g., select max("Calendar Date") from "Sample Sales Lite"."Time". I know that this can be done within OBIEE's /analytics web app (under Administration > Issue SQL), but I'd prefer using SQL Developer if possible.
    – myfriendedward
    2 days ago










  • @thatjeffsmith: In the ODBC connection, the relevant properties appear to be the server (192.168.223.3) and the port (9514); I have confirmed that this connection works without issue. In SQL Developer, when I try creating a new Oracle connection with hostname = 192.168.223.3 and port = 9514 (with SID and service name left blank), I get "IO Error: Bad packet type".
    – myfriendedward
    yesterday










  • I've never seen that error before, but it's network related I believe
    – thatjeffsmith
    yesterday













up vote
1
down vote

favorite









up vote
1
down vote

favorite











Is it possible to create a connection to OBIEE from Oracle SQL Developer?



I know it is possible to create an ODBC connection to the Oracle BI server within the Windows ODBC Data Source Administrator, and that other ODBC client tools can use that ODBC connection to run SQL against the BI server, but I don't see any way for SQL Developer to use that ODBC connection.



I'm on OBIEE 12.2.1.4 and SQL Developer 17.3.0.271.



UPDATE



Based on Robin Moffatt's article at https://rmoff.net/2016/03/28/connecting-to-obiee-via-jdbc-with-jisql/, I used jisql (https://www.xigole.com/software/jisql/jisql.jsp) to set up a connection to OBIEE using the JDBC driver that ships with the OBIEE client -- bijdbc.jar, which is located at $ORACLE_HOME/bi/bifoundation/jdbc/.



Following is a screenshot showing how jisql is able to successfully connect to my OBIEE server via the bijdbc.jar driver and run an example SQL statement; the Oracle-specific connection details are outlined in red:



Screenshot 1: OBIEE JDBC connection test using jisql



My next step was to try to implement this same successful JDBC connection in SQL Developer. To that end, I added bijdbc.jar to SQL Developer's third-party JDBC drivers:



Screenshot 2: bijdbc.jar added to SQL Developer third-party JDBC drivers



After adding the JDBC driver, I did not see a new tab in SQL Developer's connection dialog. (By comparison, in the past, when I have added other third-party JDBC drivers, such as the jTDS and MySQL drivers also seen in screenshot 2, new tabs had become available.)



Since there was not a new tab in SQL Developer's connection dialog, I tried setting the Oracle tab's connection type to Advanced and inserting the JDBC connection string as the Custom JDBC URL. Testing that connection leads to a "String index out of range: -1", as seen in this screenshot:



Screenshot 3: Error using custom JDBC URL in SQL Developer



Since that same JDBC connection string works in jisql, I suspect that either I'm doing something wrong, or SQL Developer does not support connecting to OBIEE via that bijdbc driver.










share|improve this question









New contributor




myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











Is it possible to create a connection to OBIEE from Oracle SQL Developer?



I know it is possible to create an ODBC connection to the Oracle BI server within the Windows ODBC Data Source Administrator, and that other ODBC client tools can use that ODBC connection to run SQL against the BI server, but I don't see any way for SQL Developer to use that ODBC connection.



I'm on OBIEE 12.2.1.4 and SQL Developer 17.3.0.271.



UPDATE



Based on Robin Moffatt's article at https://rmoff.net/2016/03/28/connecting-to-obiee-via-jdbc-with-jisql/, I used jisql (https://www.xigole.com/software/jisql/jisql.jsp) to set up a connection to OBIEE using the JDBC driver that ships with the OBIEE client -- bijdbc.jar, which is located at $ORACLE_HOME/bi/bifoundation/jdbc/.



Following is a screenshot showing how jisql is able to successfully connect to my OBIEE server via the bijdbc.jar driver and run an example SQL statement; the Oracle-specific connection details are outlined in red:



Screenshot 1: OBIEE JDBC connection test using jisql



My next step was to try to implement this same successful JDBC connection in SQL Developer. To that end, I added bijdbc.jar to SQL Developer's third-party JDBC drivers:



Screenshot 2: bijdbc.jar added to SQL Developer third-party JDBC drivers



After adding the JDBC driver, I did not see a new tab in SQL Developer's connection dialog. (By comparison, in the past, when I have added other third-party JDBC drivers, such as the jTDS and MySQL drivers also seen in screenshot 2, new tabs had become available.)



Since there was not a new tab in SQL Developer's connection dialog, I tried setting the Oracle tab's connection type to Advanced and inserting the JDBC connection string as the Custom JDBC URL. Testing that connection leads to a "String index out of range: -1", as seen in this screenshot:



Screenshot 3: Error using custom JDBC URL in SQL Developer



Since that same JDBC connection string works in jisql, I suspect that either I'm doing something wrong, or SQL Developer does not support connecting to OBIEE via that bijdbc driver.







oracle oracle-sqldeveloper obiee






share|improve this question









New contributor




myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 18 hours ago





















New contributor




myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked Nov 5 at 2:08









myfriendedward

64




64




New contributor




myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






myfriendedward is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












  • Question: What is your goal? What would you want to achieve with that?
    – Chris
    Nov 5 at 10:01










  • if your BI Server is an oracle database, then yes - but you won't use an odbc source, because SQL Developer is java, it uses jdbc. You would create a jdbc connection in sql developer using the same connection properties defined in your odbc source
    – thatjeffsmith
    Nov 5 at 13:25










  • @Chris: For testing purposes, I would like to be able to run SQL statements directly against the BI server. E.g., select max("Calendar Date") from "Sample Sales Lite"."Time". I know that this can be done within OBIEE's /analytics web app (under Administration > Issue SQL), but I'd prefer using SQL Developer if possible.
    – myfriendedward
    2 days ago










  • @thatjeffsmith: In the ODBC connection, the relevant properties appear to be the server (192.168.223.3) and the port (9514); I have confirmed that this connection works without issue. In SQL Developer, when I try creating a new Oracle connection with hostname = 192.168.223.3 and port = 9514 (with SID and service name left blank), I get "IO Error: Bad packet type".
    – myfriendedward
    yesterday










  • I've never seen that error before, but it's network related I believe
    – thatjeffsmith
    yesterday


















  • Question: What is your goal? What would you want to achieve with that?
    – Chris
    Nov 5 at 10:01










  • if your BI Server is an oracle database, then yes - but you won't use an odbc source, because SQL Developer is java, it uses jdbc. You would create a jdbc connection in sql developer using the same connection properties defined in your odbc source
    – thatjeffsmith
    Nov 5 at 13:25










  • @Chris: For testing purposes, I would like to be able to run SQL statements directly against the BI server. E.g., select max("Calendar Date") from "Sample Sales Lite"."Time". I know that this can be done within OBIEE's /analytics web app (under Administration > Issue SQL), but I'd prefer using SQL Developer if possible.
    – myfriendedward
    2 days ago










  • @thatjeffsmith: In the ODBC connection, the relevant properties appear to be the server (192.168.223.3) and the port (9514); I have confirmed that this connection works without issue. In SQL Developer, when I try creating a new Oracle connection with hostname = 192.168.223.3 and port = 9514 (with SID and service name left blank), I get "IO Error: Bad packet type".
    – myfriendedward
    yesterday










  • I've never seen that error before, but it's network related I believe
    – thatjeffsmith
    yesterday
















Question: What is your goal? What would you want to achieve with that?
– Chris
Nov 5 at 10:01




Question: What is your goal? What would you want to achieve with that?
– Chris
Nov 5 at 10:01












if your BI Server is an oracle database, then yes - but you won't use an odbc source, because SQL Developer is java, it uses jdbc. You would create a jdbc connection in sql developer using the same connection properties defined in your odbc source
– thatjeffsmith
Nov 5 at 13:25




if your BI Server is an oracle database, then yes - but you won't use an odbc source, because SQL Developer is java, it uses jdbc. You would create a jdbc connection in sql developer using the same connection properties defined in your odbc source
– thatjeffsmith
Nov 5 at 13:25












@Chris: For testing purposes, I would like to be able to run SQL statements directly against the BI server. E.g., select max("Calendar Date") from "Sample Sales Lite"."Time". I know that this can be done within OBIEE's /analytics web app (under Administration > Issue SQL), but I'd prefer using SQL Developer if possible.
– myfriendedward
2 days ago




@Chris: For testing purposes, I would like to be able to run SQL statements directly against the BI server. E.g., select max("Calendar Date") from "Sample Sales Lite"."Time". I know that this can be done within OBIEE's /analytics web app (under Administration > Issue SQL), but I'd prefer using SQL Developer if possible.
– myfriendedward
2 days ago












@thatjeffsmith: In the ODBC connection, the relevant properties appear to be the server (192.168.223.3) and the port (9514); I have confirmed that this connection works without issue. In SQL Developer, when I try creating a new Oracle connection with hostname = 192.168.223.3 and port = 9514 (with SID and service name left blank), I get "IO Error: Bad packet type".
– myfriendedward
yesterday




@thatjeffsmith: In the ODBC connection, the relevant properties appear to be the server (192.168.223.3) and the port (9514); I have confirmed that this connection works without issue. In SQL Developer, when I try creating a new Oracle connection with hostname = 192.168.223.3 and port = 9514 (with SID and service name left blank), I get "IO Error: Bad packet type".
– myfriendedward
yesterday












I've never seen that error before, but it's network related I believe
– thatjeffsmith
yesterday




I've never seen that error before, but it's network related I believe
– thatjeffsmith
yesterday












1 Answer
1






active

oldest

votes

















up vote
0
down vote













Ok, fair enough. Have a look at what Andrew did to make the ODBC drivers usable in Excel - it's basically down to manipulating things in Windows:
https://www.rittmanmead.com/blog/2017/02/working-with-obiee-data-in-excel-using-odbc/






share|improve this answer





















  • Thanks for this, Chris. Andrew's article provides a great overview of how to set up an ODBC connection to OBIEE and get it working in tools like Excel that can utilize ODBC connections, and his link to Robin Moffatt's article taught me about the existence of OBIEE's bijdbc.jar JDBC driver and how to use it in jisql (which I have documented in the update to my question). However, I'm still not finding a way within SQL Developer to get a working connection to OBIEE.
    – myfriendedward
    18 hours ago











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});






myfriendedward is a new contributor. Be nice, and check out our Code of Conduct.










 

draft saved


draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147434%2fconnect-to-obiee-from-oracle-sql-developer%23new-answer', 'question_page');
}
);

Post as a guest
































1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
0
down vote













Ok, fair enough. Have a look at what Andrew did to make the ODBC drivers usable in Excel - it's basically down to manipulating things in Windows:
https://www.rittmanmead.com/blog/2017/02/working-with-obiee-data-in-excel-using-odbc/






share|improve this answer





















  • Thanks for this, Chris. Andrew's article provides a great overview of how to set up an ODBC connection to OBIEE and get it working in tools like Excel that can utilize ODBC connections, and his link to Robin Moffatt's article taught me about the existence of OBIEE's bijdbc.jar JDBC driver and how to use it in jisql (which I have documented in the update to my question). However, I'm still not finding a way within SQL Developer to get a working connection to OBIEE.
    – myfriendedward
    18 hours ago















up vote
0
down vote













Ok, fair enough. Have a look at what Andrew did to make the ODBC drivers usable in Excel - it's basically down to manipulating things in Windows:
https://www.rittmanmead.com/blog/2017/02/working-with-obiee-data-in-excel-using-odbc/






share|improve this answer





















  • Thanks for this, Chris. Andrew's article provides a great overview of how to set up an ODBC connection to OBIEE and get it working in tools like Excel that can utilize ODBC connections, and his link to Robin Moffatt's article taught me about the existence of OBIEE's bijdbc.jar JDBC driver and how to use it in jisql (which I have documented in the update to my question). However, I'm still not finding a way within SQL Developer to get a working connection to OBIEE.
    – myfriendedward
    18 hours ago













up vote
0
down vote










up vote
0
down vote









Ok, fair enough. Have a look at what Andrew did to make the ODBC drivers usable in Excel - it's basically down to manipulating things in Windows:
https://www.rittmanmead.com/blog/2017/02/working-with-obiee-data-in-excel-using-odbc/






share|improve this answer












Ok, fair enough. Have a look at what Andrew did to make the ODBC drivers usable in Excel - it's basically down to manipulating things in Windows:
https://www.rittmanmead.com/blog/2017/02/working-with-obiee-data-in-excel-using-odbc/







share|improve this answer












share|improve this answer



share|improve this answer










answered 2 days ago









Chris

1,2872712




1,2872712












  • Thanks for this, Chris. Andrew's article provides a great overview of how to set up an ODBC connection to OBIEE and get it working in tools like Excel that can utilize ODBC connections, and his link to Robin Moffatt's article taught me about the existence of OBIEE's bijdbc.jar JDBC driver and how to use it in jisql (which I have documented in the update to my question). However, I'm still not finding a way within SQL Developer to get a working connection to OBIEE.
    – myfriendedward
    18 hours ago


















  • Thanks for this, Chris. Andrew's article provides a great overview of how to set up an ODBC connection to OBIEE and get it working in tools like Excel that can utilize ODBC connections, and his link to Robin Moffatt's article taught me about the existence of OBIEE's bijdbc.jar JDBC driver and how to use it in jisql (which I have documented in the update to my question). However, I'm still not finding a way within SQL Developer to get a working connection to OBIEE.
    – myfriendedward
    18 hours ago
















Thanks for this, Chris. Andrew's article provides a great overview of how to set up an ODBC connection to OBIEE and get it working in tools like Excel that can utilize ODBC connections, and his link to Robin Moffatt's article taught me about the existence of OBIEE's bijdbc.jar JDBC driver and how to use it in jisql (which I have documented in the update to my question). However, I'm still not finding a way within SQL Developer to get a working connection to OBIEE.
– myfriendedward
18 hours ago




Thanks for this, Chris. Andrew's article provides a great overview of how to set up an ODBC connection to OBIEE and get it working in tools like Excel that can utilize ODBC connections, and his link to Robin Moffatt's article taught me about the existence of OBIEE's bijdbc.jar JDBC driver and how to use it in jisql (which I have documented in the update to my question). However, I'm still not finding a way within SQL Developer to get a working connection to OBIEE.
– myfriendedward
18 hours ago










myfriendedward is a new contributor. Be nice, and check out our Code of Conduct.










 

draft saved


draft discarded


















myfriendedward is a new contributor. Be nice, and check out our Code of Conduct.













myfriendedward is a new contributor. Be nice, and check out our Code of Conduct.












myfriendedward is a new contributor. Be nice, and check out our Code of Conduct.















 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147434%2fconnect-to-obiee-from-oracle-sql-developer%23new-answer', 'question_page');
}
);

Post as a guest




















































































這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()