このプロシージャは、テーブルの内容をテキストファイルにエクスポートし、指定した受信者に送信します。
Parameters
Parameter | Description |
---|---|
| Source schema in the CData Virtuality Server; mandatory |
| Source table in the CData Virtuality Server; mandatory |
| Comma-separated list of recipients; mandatory |
| Subject of the email |
| Body text of the email |
| Name of the file in the email. If omitted, name will be created: |
| If not specified, comma( |
| If not specified, double quotes( |
| 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 |
| An |
| 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
);;