How to send SQL VAR OBJECT result as excel sheet attachment through email

Question

I have created a Oracle SQL VAR OBJECT to detect long-running jobs. I want to schedule this jobs for weekly run and get the result in a spreadsheet through email. I know how to send SQL VAR OBJECT result through CALL NOTIFICATION object printing the result (few rows) in the email body but I do not know how to get and send the SQL query result (more than 100 rows) into a spreadsheet through email. Can someone help me here.

Answer

You actually have two options.
One with SQL VAR another is with SQL Job.

For the SQL VAR you will need something like this in a SCRI .
You are writing it to a .csv not a real excel.

:SET &SQL_VAR# = 
:SET &AGENT# = 
:SET &LOGIN# = 

:SET &HND# = PREP_PROCESS_VAR(&SQL_VAR#)
:SET &RET# = WRITE_PROCESS(&HND#,"",&AGENT#,&LOGIN#,,,,,";")

Then you can use another job and/or script to convert the .csv to .xlsx

You can use a SQL Job, save the report on the agent, and send the report as an email or start another job to do something with that file.
The actual name of the file is random but can be retrieved as variable. The file is with .txt extension but its a csv. Simple rename with do the work.

In the SQL tab of the job select "Store to file"
In the Post_Process add this (arrange as per your liking)

:SET &HND# = PREP_PROCESS_REPORTLIST()
:PROCESS &HND#
:   SET &RH_TYPE# = GET_PROCESS_LINE(&HND#, 1)
:   SET &START_TIME# = GET_PROCESS_LINE(&HND#, 2)
:   SET &END_TIME# = GET_PROCESS_LINE(&HND#, 3)
:   SET &TITLE# = GET_PROCESS_LINE(&HND#, 4)
:   SET &IS_XML# = GET_PROCESS_LINE(&HND#, 5)
:   SET &FILENAME# = GET_PROCESS_LINE(&HND#, 6)
:   SET &ON_AGENT# = GET_PROCESS_LINE(&HND#, 7)
:   SET &IN_DB# = GET_PROCESS_LINE(&HND#, 8)
:     PRINT "Report type = &RH_TYPE#"
:     PRINT "Start = &START_TIME#"
:     PRINT "End = &END_TIME#"
:     PRINT "Title = &TITLE#"
:     PRINT "XML report? = &IS_XML#"
:     PRINT "File name  = &FILENAME#"
:     PRINT "On the Agent? = &ON_AGENT#"
:     PRINT "In the database? = &IN_DB#"
: IF &FILENAME# = ""
:     PRINT "No external output file"
:   ELSE
:     IF &ON_AGENT# = 1
:       PSET &FT_FILE# = &FILENAME#
:        SET &MAIL# = SEND_MAIL("",,"","","&FT_FILE#",'','')
:        SET &AKT# = ACTIVATE_UC_OBJECT(,,,,,PASS_VALUES,)
:     ENDIF
:  ENDIF
:ENDPROCESS

The LOGIN object must have type MAIL as an entry.
The other job needs to run on the same server as the SQL job and the user needs to have permissions over the temp folder of the SQL agent.

Automic Workload Automation

How to send SQL VAR OBJECT result as excel sheet attachment through email