Home » SQL & PL/SQL » SQL & PL/SQL » How to write output of a function returning sys_refcursor to a file
How to write output of a function returning sys_refcursor to a file [message #357113] Tue, 04 November 2008 00:46 Go to next message
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 #357124 is a reply to message #357113] Tue, 04 November 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Second parameter of put_line is string not ref cursor.

Regards
Michel
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 Go to previous messageGo to next message
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 #357128 is a reply to message #357127] Tue, 04 November 2008 01:35 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Don't you see the problem.

A function is supposed to return ref cursor and you are
assigning it to a string.

STRING is NOT EQUAL TO REF CURSOR

Regards,
Rajat
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 Go to previous messageGo to next message
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 #357171 is a reply to message #357128] Tue, 04 November 2008 03:37 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

Could you pls let me know. How to do it?

THanks
Re: How to write output of a function returning sys_refcursor to a file [message #357177 is a reply to message #357171] Tue, 04 November 2008 03:54 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sample code to have you some understanding.

DECLARE
 sql1 sys_refcursor;
 empNo NUMBER;
 empName VARCHAR(100);
BEGIN
  OPEN sql1 FOR 'SELECT empno,ename FROM emp';
  LOOP
   FETCH sql1 INTO empNo,empName;
   EXIT WHEN sql1%NOTFOUND;
   dbms_output.put_line(empNo|| '  '||empName);
   -- Use UTL_FILE Put line function to insert value in file
  END LOOP;
  close sql1;
END;


Regards,
Rajat

[Updated on: Tue, 04 November 2008 03:56]

Report message to a moderator

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 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Error in Writing FIle
Next Topic: text too large for formselectopen
Goto Forum:
  


Current Time: Wed Dec 07 07:09:08 CST 2016

Total time taken to generate the page: 0.08482 seconds