sendCsvExport

You are looking at an older version of the documentation. The latest version is found here.

This procedure exports the contents of a table into a text file and sends it to specified recipients.

Parameters

Parameter	Description
`sourceSchema`	Source schema in the Data Virtuality Server; mandatory
`sourceTable`	Source table in the Data Virtuality Server; mandatory
`Recipients`	Comma-separated list of recipients; mandatory
`Subject`	Subject of the email
`Body`	Body text of the email
`fileName`	Name of the file in the email. If omitted, name will be created: `sourceSchema_sourceTable.csv`
`delimiter`	If not specified, comma(`,`) is used by default
`quote`	If not specified, double quotes(`"`) are used by default
`header`	If specified, the result contains the header row as the first line; the header line will be present even if there are no rows in a group
`orderBy`	An ORDER BY clause to sort the output
`encoding`	Encoding for the created file; the default is the system's default encoding

Usage

SQL

CALL "UTILS.sendCsvExport" (
     sourceSchema => 'string_sourceSchema'
    ,sourceTable => 'string_sourceTable'
    ,Recipients => 'string_Recipients'
    ,Subject => 'string_Subject'
    ,Body => 'clob_Body'
    ,fileName => 'string_fileName'
    ,delimiter => 'string_delimiter'
    ,quote => 'string_quote'
    ,header => boolean_header
    ,orderBy => 'string_orderBy'
    ,encoding => 'string_encoding'
);;

Definition

SQL

CREATE VIRTUAL PROCEDURE sendCsvExport
    (
        IN sourceSchema string NOT NULL OPTIONS (ANNOTATION 'The source schema in DataVirtuality Server.')
        , IN sourceTable string  NOT NULL OPTIONS (ANNOTATION 'The source table in DataVirtuality Server.')
        , IN Recipients string NOT NULL OPTIONS (ANNOTATION 'Comma separated list of recipients.')
        , IN Subject string OPTIONS (ANNOTATION 'Subject of the email')
        , IN Body clob OPTIONS (ANNOTATION 'Body text of the email')
        , IN fileName string OPTIONS (ANNOTATION 'The name of the file in the email. If omitted, name will be created: sourceSchema_sourceTable.csv.')
        , IN delimiter string OPTIONS (ANNOTATION 'When DELIMITER is not specified, by default comma(,) is used as delimiter.')
        , IN quote string OPTIONS (ANNOTATION 'Double quotes(") is the default quote character. Use QUOTE to specify a different value. All values will be quoted.')
        , IN header boolean OPTIONS (ANNOTATION 'If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group.')
        , IN orderBy string OPTIONS (ANNOTATION 'An ORDER BY-clause may be used to sort the output.')
        , IN encoding string OPTIONS (ANNOTATION 'Encoding for the created file. Default is the systems default encoding.')
    )
    OPTIONS (ANNOTATION 'Exports content of a table into a textfile.')
    AS
    BEGIN
        DECLARE string variables.source_schema = LCASE(REPLACE(sourceSchema, '"', ''));
        DECLARE string variables.source_table = LCASE(REPLACE(sourceTable, '"', ''));
        DECLARE string variables.target_file = fileName;
        INSERT into #__LOCAL__csvdata
        SELECT result FROM (
        CALL csvExport(
                   sourceSchema => sourceSchema
                ,  sourceTable => sourceTable
                ,  delimiter => delimiter
                ,  quote => quote
                ,  header => header
                ,  orderBy => orderBy
                ,  encoding => encoding
                ,  returnBlob => true
        ))e;
        CALL "UTILS.sendMail"(
            "Recipients" => Recipients,
            "Subject" => Subject,
            "Body" => Body,
            "AttachmentNames" => ARRAY(variables.target_file),
            "Attachments" => ARRAY((select result from  #__LOCAL__csvdata)),
            "AttachmentMimeTypes" => ARRAY('text/csv')
        ); 
END;

Example

SQL

 CALL "views.sendCsvExport"(
     sourceSchema => 'dwh'
    ,sourceTable => 'Fetch_list_of_notes'
    ,Recipients => 'user@example.com'
    ,Subject => 'test send csv'
    ,Body => 'test body'
    ,fileName => 'test_send_file.csv'
    ,delimiter => ','
    ,quote => '"'
    ,header => null
    ,orderBy => null
    ,encoding => null
);;