|
|
|
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   |
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 #389151 is a reply to message #389112] |
Fri, 27 February 2009 07:18   |
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 #389442 is a reply to message #389152] |
Mon, 02 March 2009 02:06   |
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
|
|
|
|