Please see example PL/SQL procedure below. This is an example of a procedure written, which opens a cursor, and fetches XML content from a view on an Oracle database. This was talked about in the previous blog post i.e. using XMLDB to create an XML document within Oracle. The Procedure essentially loops through the cursor,…
There are times when you might need to transform the content of a table, into XML output. There are many ways to do this, using tools such as ODI and OWB. However there are some in-built functions within the Oracle RDBMS which allow you to generate complex XML documents, within SQL. Known as XMLDB this means…
So the previous blog post configures and tests the ODBC configuration. Next steps would be to configure the Oracle HS Services to use the installed ODBC driver. First steps login the the Oracle RDBMS host. cd $ORACLE_HOME/hs/admin Create a new configuration file <init>DSNNAME.ora i.e. initSQLSERVERHLC.ora # This is a sample agent init file that contains the…
The procedure outlined below will allow you to configure a Linux machine, to connect to a Microsoft SQL Server machine using an ODBC driver. The main goal here was to ensure the BCP ( Bulk Copy ) command can be called from a client machine loading data using the bulk insert command into the SQL…
We recently engaged Sirocco Systems to design and implement a cloud based data warehouse solution, using the AWS platform for infrastructure and database services. The initial aim was twofold: first to reduce the operational costs of running and maintaining bare metal infrastructure in-house, and second to find a database platform that would be cost and…
Often useful to have the ability to split a csv/large text file into chunks. Found this useful during a recent project, where we needed to pass in a value i.e. number of lines per file, and split into many files. This kind of script can be used by external programs i.e. ODI/OWB as a post…
Its often useful to have the ability when customizing an OBIEE environment, to have access to the corporate LDAP/Active Directory server. A good example is a client may wish to drive a prompt / default selection on a dashboard page, based on a users office location. Often an employees location is recorded and kept up…
This post highlights the suggested changes required to enable users of OBIEE prior to 11.1.1.7.140527 release to use Internet Explorer 11 ( IE 11 ), which is not officially supported by Oracle. This test was carried out using OBIEE release 11.1.1.6.12. IE Developer Tools Following of deployment of IE11 the IE11 Developer tools report an…
There is often a need to restrict what a user is able to see within underlying tables within an Oracle Database. This can be achieved many was via views, and logic within them. However Oracle has offered row level security for many years, since around v8. Below is a simple example of how to apply…
When using 12c database, you may have internal container databases. In order to manage them i.e. tablespaces etc, you need to configure each container database’s em console. You can do this via logging into the container database as sysdba i.e. sqlplus sys/password@instance as sysdba Then issue :- exec dbms_xdb_config.sethttpsport(5501); This will configure the container database…