How to write output of a function returning sys_refcursor to a file [message #357113] |
Tue, 04 November 2008 00:46  |
vikram1780
Messages: 222 Registered: January 2007 Location: Bangalore
|
Senior Member |
|
|
Hi,
Below is the code written to write the output of a function which return sys_refcursor into file.
declare
sql1 sys_refcursor;
handle utl_file.file_type;
begin
sql1:=esp_GetProcessFull_v5_new1(2945);
handle:=utl_file.fopen('LOGAREA','test.xml', 'w',32767);
utl_file.put_line(handle,sql1 );
utl_file.fflush(handle);
utl_file.fclose(handle);
exception
when others then
null;
end;
I encountered with below errors.
utl_file.put_line(handle,sql1 );
*
ERROR at line 7:
ORA-06550: line 7, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored
Any help really appreciated.
Thanks
|
|
|
|
Re: How to write output of a function returning sys_refcursor to a file [message #357127 is a reply to message #357124] |
Tue, 04 November 2008 01:28   |
vikram1780
Messages: 222 Registered: January 2007 Location: Bangalore
|
Senior Member |
|
|
Hi,
When I changed to string as below code. I am encountered with an error expression wrong type.
SQL> declare
2 sql1 varchar2(32000);
3 handle utl_file.file_type;
4 begin
5 sql1:=esp_GetProcessFull_v5_new1(2945);
6 handle:=utl_file.fopen('LOGAREA','test.xml', 'w',32767);
7 utl_file.put_line(handle,sql1 );
8 utl_file.fflush(handle);
9 utl_file.fclose(handle);
10 exception
11 when others then
12 null;
13 end;
14 /
sql1:=esp_GetProcessFull_v5_new1(2945);
*
ERROR at line 5:
ORA-06550: line 5, column 7:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
|
|
|
|
Re: How to write output of a function returning sys_refcursor to a file [message #357141 is a reply to message #357128] |
Tue, 04 November 2008 02:40   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think the OP is suffering from an attack of severe optimism, and is hoping that if they write the ref cursor out to a file using Utl_File.Put_Line, then is will automagically write all the data that the ref cursor would return to the file, presumably formatted in the manner of their choice, and with column headings.
I think they may be starting to feel a little disappointed by now.
@vikram1780 : You wil need to write some code to loop, fetch a records from the ref cursor, concatenate the fields that you retrieve into a single string, and output that string using put_line.
Let us know if you need help doing this.
|
|
|
|
|
Re: How to write output of a function returning sys_refcursor to a file [message #357190 is a reply to message #357177] |
Tue, 04 November 2008 04:38  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote: | When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.
|
Regards
Michel
|
|
|