Setting up SQL*Plus (on Windows) - Oracle

Good blog post on setting up SQL*Plus to give the windows command line screen and make it user friendly.   http://dbaportal.eu/2014/05/20/sqlplusw-exe-the-king-is-dead-long-live-the-king/  

OBIEE – Accessing the RPD

Information is based on getting the OBIEE RPD up and running with the OBIEE Sample App. 

1. Enter Admin Tools: Control Panel\System and Security\Administrative Tools 
2. Set up a Data Source by could clicking “Data Sources (ODBC)”.

3. From the User DSN tab, click Add to create a new data source:

4. Choose Oracle BI Server:

5. Enter the Name for your Database Connection, and the name of the server (or select an existing server name from the drop down)

6. Enter Login ID as weblogic, Password as initial setup and Port as 9703.  Choose Connect to test the connection before finishing.

7. Open up the Administration Client tool for OBIEE8. Select File-Open-Online (or cntrl-l)9. Repository Password = Admin123 User is weblogic and password as initial setup

Agile Excel Template

Thanks to Mitch Lacey and associates for uploading some Agile Sprint templates for use in Excel:  https://www.mitchlacey.com/resources/sprint-excel-templates 
Has saved me trying to build something and saves the hassle of investing in a tool to get up and running quickly.

BI Chart Design

Working in the BI world you see a real mix of ambitious charts and reports tying to impress the audience, but at the end of the day it is always the simpler view that carries the greatest impact.

There are plenty of books and articles to read on design Stephen Few being one of the more accomplished authors. To check your opinions, here is a little game to play to guide you along by Perceptual Edge I would personally recommend the books "Information Dashboard Design" and "Show Me The Numbers", both by Stephen Few.

How many rows in a Clob

If you are not familiar with the ‘WITH’ statement, I am just using it to mock up a table that has a row_id and a clob value – feel free to run this bit (from the select within the WITH clause) in isolation to get the rows with the CR/LF’s.
There are several ways, if you need to extract each row, I expect as separate text you may find working with Option 3 the most productive.

Option 1:
Using LENGTH is just working out how many CHR(13)’s there are

WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
SELECT table_id, LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (13), '')) + 1
FROM clob_tab

Option 2:

WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
select table_id, LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (10), '')) + 1
from clob_tab

Option 3:

If you’ve not used CONNECT BY LEVEL before, it is worth reading and working through the example in the oracle documentation..

WITH clob_tab AS
(SELECT ROWNUM AS table_id, clob_val
FROM (SELECT 'ZOG' || CHR (13) || CHR (10) || 'GIBBENS' AS clob_val
FROM DUAL
UNION ALL
SELECT 'ZOG' || CHR (13) || CHR (10)
|| 'ATE' || CHR (13) || CHR (10)
|| 'ALL' || CHR (13) || CHR (10)
|| 'OF HIS DINNER' AS clob_val
FROM DUAL
)
)
SELECT table_id,
MAX (row_num)
FROM (SELECT table_id,
clob_val,
LEVEL AS row_num
FROM clob_tab
CONNECT BY LEVEL <= LENGTH (clob_val) - LENGTH (REPLACE (clob_val, CHR (10), '')) + 1)
GROUP BY table_id

Oracle - Testing UTL_FILE

CREATE OR REPLACE PROCEDURE
utl_file_test_write (PATH IN VARCHAR2,
filename IN VARCHAR2,
firstline IN VARCHAR2,
secondline IN VARCHAR2
)
AUTHID CURRENT_USER
IS
output_file UTL_FILE.file_type;
v_path all_directories.directory_path%TYPE;
BEGIN
SELECT directory_path
INTO v_path
FROM all_directories
WHERE UPPER (directory_name) = UPPER (PATH) AND ROWNUM = 1;

DBMS_OUTPUT.put_line ('Opening file ' || RTRIM (v_path, '/') || '/' || filename);
output_file := UTL_FILE.fopen (PATH, filename, 'W');

DBMS_OUTPUT.put_line ('Writing the 1st record "' || firstline || '"');
UTL_FILE.put_line (output_file, firstline);

DBMS_OUTPUT.put_line ('Writing the 2nd record "' || secondline || '"');
UTL_FILE.put_line (output_file, secondline);

DBMS_OUTPUT.put_line ('Closing the file');
UTL_FILE.fclose (output_file);

DBMS_OUTPUT.put_line ('Complete!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;

/

Three Peaks Challenge

For some mad reason I thought walking the three giants in the UK within 24 hours would be a good idea!!

Better off visiting the Bingham 3 Peaks Blog - http://bingham3peaks.blogspot.com