| Open REF CURSOR ==> "this feature is not supported in client side programs" [message #392496] |
Wed, 18 March 2009 03:54  |
ramyazhary
Messages: 7
Registered: March 2009
|
Junior Member |
|
|
Hello All,
am builing a form where the user chooses the fields from a certain table which he wishes to print on a file on his computer
so i construct the sql statement ex. "select field1,field2
from my_table"
then i want to use a ref cursor inoder to run this query and then while looping print the output on a file
while doing so i get an error message on this line
OPEN l_cursor FOR TRIM(v_query);
<==this feature is not supported in client side programs
so can anyone help how to overcome this problem .. i stored my pl/sql in a botton and in a function and i always get the same message
below is the script i made:
DECLARE
myCSV text_io.file_type;
v_name char(30);
v_name2 char(30);
v_query varchar2(300);
TYPE ref_cursor IS REF CURSOR;
l_cursor ref_cursor;
BEGIN
myCSV := text_io.fopen('C:\test.xls','w');
select command into v_query
from credit_reports
where rep_id=:REP_id ("Value from forms");
OPEN l_cursor FOR v_query;
LOOP
EXIT WHEN l_cursor%NOTFOUND;
FETCH l_cursor INTO v_name,v_name2;
text_io.put_line(myCSV,v_name||' '||v_name2);
END LOOP;
CLOSE l_cursor;
text_io.fclose(myCSV);
END;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Open REF CURSOR ==> "this feature is not supported in client side programs" [message #395215 is a reply to message #392496] |
Tue, 31 March 2009 13:43  |
ramyazhary
Messages: 7
Registered: March 2009
|
Junior Member |
|
|
Hey David thanks for replying.
if i made a procedure on the database
i won't be able to generate the file on the user's computer.
putting the flag thing won't do me any good
cuz i'll make the user chooose the fields and any conditions he wants to make and if he can't to sum or count or do any math operations, so i guess it won't work..
it would be goood if only if it was just a straight select statement.
the other guy in the other post
Using native dynamic Sql [message #392781]
he made a function, its for any 1 row query only.
any guesses ??
|
|
|
|
|
|
|
|
| Re: Open REF CURSOR ==> "this feature is not supported in client side programs" [message #395677 is a reply to message #392496] |
Thu, 02 April 2009 04:03 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Presumably your database function is using utl_file rather than text_io?
In which case it will put the file on the db server as that's what utl_file does.
If you need this file created on the client machine then you have to use text_io in forms.
Perhaps you'd be better off looking for an alternative to dynamic ref_cursors.
Does your ref cursor always contain the same number/type of columns?
|
|
|
|
|
|
| Re: Open REF CURSOR ==> "this feature is not supported in client side programs" [message #395944 is a reply to message #395788] |
Fri, 03 April 2009 04:21 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
| ramyazhary wrote on Thu, 02 April 2009 16:23 | no, it will always change according to the user's needs
as the user will select the columns that he wants and if he want to do any operations like count or sum
|
Are you trying to write an ad-hoc query tool?
If they want that level of flexibility you need to give them oracle discoverer or something similar.
Trying to do this through forms is wildly impractical.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
