Print

Generating CSV format from SQL queries, then saving into BLOB column

Posted in Useful Functions.

This is a requirement I came across - a client wanted to generate a number of CSV extracts (one for each department), that were to be updated every week or so. These CSV extracts were to be available to download (from an APEX interface).

I created a package which contained a procedure to loop thorugh a cursor and create an extract for every department (e.g. this could be the list of employees, for example) and then save a record to a database table, with the generated CSV file stored in a BLOB column.


First we need to create the table that will store the CSV file in the BLOB column.
(Also create a sequence & trigger which automatically generates a unique ID number on insert).

CREATE TABLE MY_CSV_EXTRACTS
(
"ID" NUMBER NOT NULL,
"DEPARTMENT" VARCHAR2(4000 BYTE),
"EXTRACT_DATE" DATE,
"EXTRACT" BLOB,
"FILE_NAME" VARCHAR2(100 BYTE),
"MIME_TYPE" VARCHAR2(20 BYTE),
"FILE_SIZE" VARCHAR2(20 BYTE)
)


Then we need to create a package that will have 3 Procedures/Functions:

PROCEDURE save_file_into_blob;
This is the actual procedure that is run (you cpuld kick this off from a DBMS scheduler job) which takes loops through and inserts the BLOB into the table.


FUNCTION get_blob (p_query varchar2) return blob;
This function gets passed a SQL query as a string, then takes this query and converts the results into a .CSV format and saves it into a BLOB which is returned.

PROCEDURE download_my_file (p_file IN NUMBER);
This function gets passed the unique ID of the file record, and allows the user to download the file.
This procedure is called from an APEX page.


Download the package script here:    CSV_EXTRACTS_PKG.txt



To set up the APEX report, and the ability for users to download the files:

1) Create an application item called T_FILE

2) Create an application process called DOWNLOAD_FILE

    Process Point: On Load: Before Header

    Type: PL/SQL Anonymous Block

    Process Text:
    BEGIN
    CSV_EXTRACTS_PKG.download_my_file(:t_file);
    END;
   
    Process Error Message: File cannot be downloaded

    Condition Type: PL/SQL Expression

    Expression 1: :REQUEST IN ('DOWNLOAD_FILE')

3) Then on your page, create a report region as:

    select * from

    MY_CSV_EXTRACTS

4) In the Report Attributes Tab, click on the FILE_NAME column.

5) Scroll down to the Column Link box, and set the values as follows:

    Link Text: #FILE_NAME#

    Target: Page in this Application

    Page: 0

    Request: DOWNLOAD_FILE

    Item 1 Name: T_FILE

    Item 1 Value: #ID#



If you have run your process to extract the data and insert CSV files into the table, you should now be able to run your APEX report, and test the links to the CSV files.



   

Print

Conditionally Show report link column, based on value of another column

Posted in Report Layout.

You can conditionally show a button or link in a column, that is displayed conditionally based on the value of another column.

For example, you could conditionally display a "Deactivate" button for an employee which would then link to a deactivation form.
This link would redirect to Page 60, and pass the employee_id column to the P60_EMPLOYEE_ID item.


SELECT
employee_id,
employee_name,
employee_title,
status,
CASE WHEN status = 'ACTIVE' then
<a href="/f?p=&APP_ID.:60:&SESSION.::&DEBUG.::P60_EMPLOYEE_ID:'||employee_id||'"> </span><br /><span class="Moobi_Code"><img src="#WORKSPACE_IMAGES#deactivate_button.png"></a>'</span><br /><span class="Moobi_Code">ELSE NULL END deactivate_employee,
from
EMPLOYEES


Print

Finding Beggining of Financial Year for a given date

Posted in SQL Syntax.

For a given date we can find the beggining of the financial year that the date falls in:

select
add_months(trunc(add_months(:my_date,-6),'yyyy'),6)
from dual


This can also be used to find the end of a financial year:

select
add_months(trunc(add_months(:my_date,-6),'yyyy'),18)
from dual

About Me

I currently specialise in developing bespoke applications using Oracle APEX.I have worked with APEX since it was known as HTMLDB, but have also dabbled with E-Business Suite, Oracle AS Portal, ECM, Webcenter and Website Design.

I enjoy working with new clients and assisting them develop custom applications to enhance and improve internal business processes.

Find Me

mandy nerd2