このプロシージャは、テーブルの内容をテキストファイルにエクスポートし、指定した受信者に送信します。

Parameters

Parameter

Description

sourceSchema

Source schema in the CData Virtuality Server; mandatory

sourceTable

Source table in the CData 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

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

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

CALL "views.sendCsvExport"(
sourceSchema => 'dwh'
,sourceTable => 'Fetch_list_of_notes'
,Recipients => '[email protected]'
,Subject => 'test send csv'
,Body => 'test body'
,fileName => 'test_send_file.csv'
,delimiter => ','
,quote => '"'
,header => null
,orderBy => null
,encoding => null
);;