Home » SQL & PL/SQL » SQL & PL/SQL » A problem with UTL_FILE and cursor
A problem with UTL_FILE and cursor [message #290008] Thu, 27 December 2007 01:54 Go to next message
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 #290010 is a reply to message #290008] Thu, 27 December 2007 01:59 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

RTRIM

regards,
Re: problem in writing utl_file [message #290049 is a reply to message #290010] Thu, 27 December 2007 04:34 Go to previous messageGo to next message
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 #290055 is a reply to message #290049] Thu, 27 December 2007 04:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Try something like this:
WITH rec AS
  (
    SELECT SYSDATE   chg_date, 12   usage_flux,1 service_id FROM dual UNION ALL
    SELECT NULL      chg_date, NULL usage_flux,2 service_id FROM dual UNION ALL
    SELECT SYSDATE-5 chg_date, 24   usage_flux,3 service_id FROM dual     
  )
SELECT CASE WHEN rec.chg_date   IS NULL 
         THEN '|' 
         ELSE TO_CHAR('"' || rec.chg_date    || '"') || '|'  
       END
    || CASE WHEN rec.usage_flux IS NULL 
         THEN '|' 
         ELSE TO_CHAR('"' || rec.usage_flux  || '"') || '|'  
       END
    || CASE WHEN rec.service_id IS NULL 
         THEN '|' 
         ELSE TO_CHAR('"' || rec.service_id  || '"') || '|'  
       END  x
FROM  rec
/
NVL2 would do just as well...

MHE
Re: problem in writing utl_file [message #290056 is a reply to message #290049] Thu, 27 December 2007 04:59 Go to previous messageGo to next message
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 #290072 is a reply to message #290056] Thu, 27 December 2007 05:36 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
i'am getting error nvl2 should be declared as an identifier.
i'am using 10g.
Re: problem in writing utl_file [message #290074 is a reply to message #290072] Thu, 27 December 2007 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't trust you.
Copy and paste what you did (with line numbers and error message).

Keep your lines in 80 characters.

Regards
Michel
Re: problem in writing utl_file [message #290076 is a reply to message #290074] Thu, 27 December 2007 05:50 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
It is again showing identifer nvl2 must be declared.

does it need any packgage permissions .
Re: problem in writing utl_file [message #290078 is a reply to message #290076] Thu, 27 December 2007 05:54 Go to previous messageGo to next message
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
Re: problem in writing utl_file [message #290104 is a reply to message #290078] Thu, 27 December 2007 07:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
NVL2 cannot be used in PL/SQL. Wink

MHE
cursors [message #290171 is a reply to message #290008] Thu, 27 December 2007 22:02 Go to previous messageGo to next message
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 #290174 is a reply to message #290171] Thu, 27 December 2007 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
use the PREDICT_FUTURE function; which returns TRUE when the last record is present & FALSE all other times.
Re: cursors [message #290176 is a reply to message #290174] Thu, 27 December 2007 22:13 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
I'am not getting how to use this function.
does it works with pl/sql.
Re: cursors [message #290182 is a reply to message #290176] Thu, 27 December 2007 22:35 Go to previous messageGo to next message
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 #290186 is a reply to message #290171] Thu, 27 December 2007 22:42 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
There are SOOOOO many things with the post above.

Which ball in a basket of balls in the "last ball"?

Read the PL/SQL Reference manual to see how valid PL/SQL is done.
It can be found at http://tahiti.oracle.com

Visit http://asktom.oracle.com for many fine coding examples.
Re: cursors [message #290212 is a reply to message #290186] Fri, 28 December 2007 00:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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).
Re: cursors [message #290247 is a reply to message #290182] Fri, 28 December 2007 03:24 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
In cases like this, I tend to use following construction to "solve" the first/last record problem:

DECLARE
  seperator CHAR(1) := NULL ;
BEGIN
  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
    -- First pass seperator is NULL
    UTL_FILE.PUT(OutFile,
        seperator || '("' || rec1.option_id || '")' ) ;

    -- As of now seperator needs to be a ','
    seperator := ',' ;
  END LOOP;
END ;


I'm sure far better solutions are available Wink

But in this case we don't know the complete requirement, so we had to guess a bit
Re: cursors [message #290251 is a reply to message #290247] Fri, 28 December 2007 03:36 Go to previous message
piscean_n
Messages: 36
Registered: December 2007
Member
Thank you so much.
this seperator things works well.
Previous Topic: breaking column opening and production
Next Topic: Problem with ref cursors as out parameter
Goto Forum:
  


Current Time: Mon Dec 05 21:04:34 CST 2016

Total time taken to generate the page: 0.12733 seconds