A problem with UTL_FILE and cursor [message #290008] |
Thu, 27 December 2007 01:54  |
piscean_n
Messages: 36 Registered: December 2007
|
Member |
|
|
LOOP
UTL_FILE.PUT(OutFile, '("' || rec1.option_id || '"),');
END LOOP;
the problem m gettin is at the end again comma is coming like this:
("opd11"),("opd11"),("opd11"),("opd11"),
how can i remove this end comma.
[MERGED by LF; please, do not open a new topic for the same problem]
[Updated on: Fri, 28 December 2007 00:31] by Moderator Report message to a moderator
|
|
|
|
Re: problem in writing utl_file [message #290049 is a reply to message #290010] |
Thu, 27 December 2007 04:34   |
piscean_n
Messages: 36 Registered: December 2007
|
Member |
|
|
UTL_FILE.PUT(OutFile, '|' || NVL(TO_CHAR('"' ||
rec.chg_date || '"'),'') || '|' || NVL(TO_CHAR('"' ||
rec.usage_flux || '"'),'') || '|' || NVL(TO_CHAR('"' ||
rec.service_id || '"' ),''));
suppose no data is there then i want ||
but still i'am getting |""|
what should i write insode this utl_file so i get || only
if no data is there.
[Updated on: Thu, 27 December 2007 05:40] by Moderator Report message to a moderator
|
|
|
|
Re: problem in writing utl_file [message #290056 is a reply to message #290049] |
Thu, 27 December 2007 04:59   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Replace nvl with nvl2. Your utl_file.put_line will be something like this
UTL_FILE.PUT(OutFile, '|' || NVL2(rec.chg_date,TO_CHAR('"' || rec.chg_date || '"'),'') || ...
Search for NVL2 in oracle sql reference manual you will understand what it does. For that matter you might already know how NVL2 works. But your existing syntax for nvl is of no use because the first argument to nvl will never return a null string.
SQL> select nvl(to_char('"'||NULL||'"'),'') from dual;
NVL(TO_CHAR(''||NULL||''),''
----------------------------------------------------------------
""
1* select nvl2(null,to_char('"'||NULL||'"'),'') from dual
SQL> /
NVL2(NULL,TO_CHAR(''||NULL||'
----------------------------------------------------------------
HTH
Regards
Raj
P.S : @Maaher ... You are quick...
[Updated on: Thu, 27 December 2007 05:42] Report message to a moderator
|
|
|
|
|
|
Re: problem in writing utl_file [message #290078 is a reply to message #290076] |
Thu, 27 December 2007 05:54   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Why don't you copy & paste the error whatever you are getting in sqlplus something like this
SQL> select nlv2(null,'a','b') from dual;
select nlv2(null,'a','b') from dual
*
ERROR at line 1:
ORA-00904: "NLV2": invalid identifier
Oracle says nlv2 is invalid identifier because there is nothing called nlv2 defined in the database as an object. So please do the same.
Regards
Raj
|
|
|
|
cursors [message #290171 is a reply to message #290008] |
Thu, 27 December 2007 22:02   |
piscean_n
Messages: 36 Registered: December 2007
|
Member |
|
|
Is there any function to get to know whether this is the last record in cursor or can you tell me other way to know the last record of cursor.
|
|
|
|
|
Re: cursors [message #290182 is a reply to message #290176] |
Thu, 27 December 2007 22:35   |
piscean_n
Messages: 36 Registered: December 2007
|
Member |
|
|
Error is comming:
PLS-00324: cursor attribute may not be applied to non-cursor 'REC1'
FOR rec1 IN (SELECT opt.option_id FROM RQ_SERVICES rs,
TABLE(rs.options) opt WHERE rs.REQUEST_ID = rec.request_id
AND rs.MSISDN = rec.msisdn)
LOOP
UTL_FILE.PUT(OutFile, '("' || rec1.option_id || '"),' );
IF(rec1%NOTFOUND) THEN
UTL_FILE.PUT(OutFile, '("' || rec1.option_id || '")' );
END IF;
END LOOP;
Please tell me how to fetch the last record with this kind of scenario
|
|
|
|
Re: cursors [message #290212 is a reply to message #290186] |
Fri, 28 December 2007 00:17   |
piscean_n
Messages: 36 Registered: December 2007
|
Member |
|
|
If i write like this then my program doesn't end at all
rec1 sys_REFCURSOR;
option_id VARCHAR2(100) ;
OPEN rec1 FOR SELECT opt.option_id FROM RQ_SERVICES rs,
TABLE(rs.options) opt WHERE rs.REQUEST_ID = rec.request_id
AND rs.MSISDN = rec.msisdn;
LOOP
FETCH rec1 INTO option_id;
UTL_FILE.PUT(OutFile, '("' || option_id || '"),' );
IF( rec1%NOTFOUND) THEN
UTL_FILE.PUT(OutFile, '("' || option_id || '")' );
END IF;
END LOOP;
CLOSE rec1;
please tell me how to get the last record.
|
|
|
Re: cursors [message #290215 is a reply to message #290212] |
Fri, 28 December 2007 00:36   |
dhananjay
Messages: 635 Registered: March 2002 Location: Mumbai
|
Senior Member |
|
|
piscean_n it has been said before by other members that there is no concept of first or last recods in an RDBMS.so why bother about that.
LOOP
FETCH rec1 INTO option_id;
Exit when rec1%notfound; --just include the exit statment
UTL_FILE.PUT(OutFile, '("' || option_id || '"),' );
END LOOP;
regards,
|
|
|
Re: cursors [message #290236 is a reply to message #290215] |
Fri, 28 December 2007 02:42   |
piscean_n
Messages: 36 Registered: December 2007
|
Member |
|
|
No i want the last record because
UTL_FILE.PUT(OutFile, '("' || option_id || '"),' );
and if(last record)then
UTL_FILE.PUT(OutFile, '("' || option_id || '")' );
|
|
|
Re: cursors [message #290241 is a reply to message #290236] |
Fri, 28 December 2007 03:04   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
All you have to do is put that line outside the loop. Not only that you have to remove the comma and concatenate it with the beginning of the string. But i leave it you to find how to know whether cursor has processed any records.
HTH
Regards
Raj
[Updated on: Fri, 28 December 2007 03:06] Report message to a moderator
|
|
|
Re: A problem with UTL_FILE and cursor [message #290242 is a reply to message #290008] |
Fri, 28 December 2007 03:06   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
what about changing the code flow a little?
LOOP
FETCH rec1 INTO option_id;
EXIT WHEN rec1%notfound;
IF <first record; eg. from introduced counter> THEN
UTL_FILE.PUT(OutFile, ',' );
END IF;
UTL_FILE.PUT(OutFile, '("' || option_id || '")' );
END LOOP; no need for the last record recognition; just the first one, which is simple (suppose you do not want to include the last record twice, as your pseudocode would do).
|
|
|
|
|