Home » SQL & PL/SQL » SQL & PL/SQL » Exporting Function output return type sys_refcursor(Result set) to Excel sheet
Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #388729] Thu, 26 February 2009 00:41 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi ,

I am newbie with files in Oracle.

So could anyone help me with small example of exporting function output return type sys_refcursor(result set) to excel and also help me with steps in scheduling this job everyday.

Thanks in advance

Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #388919 is a reply to message #388729] Thu, 26 February 2009 11:43 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Could anyone help me on this.

Thanks in advance

Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #388922 is a reply to message #388729] Thu, 26 February 2009 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #388931 is a reply to message #388922] Thu, 26 February 2009 12:15 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

Below is the procedure which i have to execute daily by passing parameters as

param1=day(DD)
param2=sysdate-1
param3=sysdate

The result set should be exported to excel sheet at particular location of OS.

create or replace FUNCTION "ESP_GETMYSERVICEREQUESTS_V4"
(
	v_userID NUMBER,
	v_startdate TIMESTAMP,
	v_enddate TIMESTAMP
)return sys_refcursor
AS
v_temp   SYS_REFCURSOR;

BEGIN
OPEN v_temp for SELECT     SR.sr_id, SR.case_id, SR.contact_id, SR.created, SUM(UR.duration) AS duration, CA.custref
FROM        ServiceRequest_ec SR INNER JOIN
                      contacts_ec CO  ON SR.contact_id = CO.contact_id INNER JOIN
                      userresponses_ec your  ON SR.sr_id = UR.sr_id INNER JOIN
                      cases_ec CA  ON SR.case_id = CA.case_id LEFT OUTER JOIN
                      users_ec U  ON CO.user_id = U.user_id
WHERE     U.user_id = v_userID AND
sr.created  >= to_timestamp(v_startdate,'DD-MON-YY HH24:MI:SS') AND
sr.created  <= to_timestamp(v_enddate,'DD-MON-YY HH24:MI:SS') 
GROUP BY SR.sr_id, SR.created, CA.custref, SR.contact_id, SR.case_id
ORDER BY SR.sr_id DESC;
return v_temp;
END;





Thanks in advance
Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #388934 is a reply to message #388729] Thu, 26 February 2009 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Producing a file for Excel is a FAQ.
Please show some resourcefulness & use SEARCH of this forum to see how others have solved this problem.
Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #389112 is a reply to message #388931] Fri, 27 February 2009 05:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm a little unclear on what you're trying to do

Are you
1) trying to write data out to a file that will be imported into Excel
2) Trying to create a file that can be opened directly by Excel
3) Trying to read data into a spreadsheed directly from the database?
Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #389151 is a reply to message #389112] Fri, 27 February 2009 07:18 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Thanks for your reply.

I am trying for the 2 nd option i,e

Trying to create a file that can be opened directly by Excel



SQL> ed
Wrote file afiedt.buf

  1  create or replace function fn1 return sys_refcursor as
  2  cv_1  SYS_REFCURSOR;
  3  begin
  4  open cv_1 for select * from tab;
  5  return cv_1;
  6* end;
SQL> /

Function created.



SQL> DECLARE
  2    fHandler UTL_FILE.FILE_TYPE;
  3    buf      varchar2(32676);
  4   var1 sys_refcursor;
  5  BEGIN
  6   var1:=fn1;
  7    fHandler := UTL_FILE.FOPEN('OUTPUT', 'var.xls', 'w');
  8    UTL_FILE.PUTF(fHandler, var1);
  9    --dbms_output.put_line('DATA FROM FILE: '||var1);
 10    UTL_FILE.FCLOSE(fHandler);
 11  EXCEPTION
 12    WHEN utl_file.invalid_path THEN
 13       raise_application_error(-20000, 'Invalid path. Create directory or set
 UTL_FILE_DIR.');
 14  END;
 15  /
  UTL_FILE.PUTF(fHandler, var1);
  *
ERROR at line 8:
ORA-06550: line 8, column 3:
PLS-00306: wrong number or types of arguments in call to 'PUTF'
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored






Any help really appreciated


Thanks in advance
Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #389152 is a reply to message #389151] Fri, 27 February 2009 07:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look at the OWA_SYLK package from AskTom.
Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #389442 is a reply to message #389152] Mon, 02 March 2009 02:06 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Thanks for your reply.

I am encountered with below error when i tried to excute the procedure having utility 'OWA_SYLK.SHOW' .


SQL> Create or replace procedure hello
  2  as
  3  begin
  4  owa_sylk.show('select * from user_objects where rownum<10');
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE HELLO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: Statement ignored
4/1      PLS-00201: identifier 'OWA_SYLK.SHOW' must be declared
SQL>



Any help really appraciated

Thanks in advance
Re: Exporting Function output return type sys_refcursor(Result set) to Excel sheet [message #389443 is a reply to message #389442] Mon, 02 March 2009 02:10 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to scroll about a page height in the previous link to get the source of the package.

Regards
Michel
Previous Topic: Index on number and varchar (merged)
Next Topic: Time and date question
Goto Forum:
  


Current Time: Sun Dec 04 20:37:37 CST 2016

Total time taken to generate the page: 0.13989 seconds