Need help with UTL_FILE (URGENT)

[AJCG]

I have a large update file sent back to me from an external agency, would like this file to update a suppression table very simple... But the file contains " arround all values i.e.

"58153021","9009774"

Without using sed in unix I would like the procedure to strip these out... I have tried REPLACE but maybe I am typing this wrong:

v_person_id := REPLACE(SUBSTR (v_newline, 1, v_1comma - 1),'"','');

Any help would be great!

Adam

 

[jee]

Question: What does this have to do with UTL_FILE??

Second Question: What result are you getting now - are the double quotes still there, do you get an error, or what?

I think what you are doing should work, but you could try to omit the last parameter from the REPLACE function instead of using '' (two single-quotes). I assume your second parameter is single-quote double-quote single-quote.

That is your replace can look like this:

REPLACE (your_string, '"')

Which will just remove the specified character.

Another option, if you after your SUBSTR operation you have a string that looks like "xxxxxx" (first and last chars are double-quotes) is to use the RTRIM and LTRIM functions:
RTRIM (LTRIM (your_string , '"'))

 

[AJCG]

What Has this to do with Utl_file.... I get the feeling thats the question I need answering... I am using utl to update individual clients contact data and I am unsure of how to get rid of the double quotes... I get an

ORA-01858: a non-numeric character was found where a numeric was expected

error when the double quotes are still in place....

P.S. the replace still did not work....

 

[ChrisHunt]

The simple answer is to use LTRIM & RTRIM as jee says (there's also the TRIM() function that trims both ends at once, but it won't let you specify a character other than space to remove - Doh!). So once you've grabbed each bit-between-the-commas you can do this to it:

RTRIM(LTRIM(my_field,'"'),'"')

I have a more over-engineered solution based on a function I've written (as part of a package devoted to CSV strings). Here's the function:

FUNCTION next_term (pstrLine      IN OUT VARCHAR2,
                    pstrSeperator IN     VARCHAR2 := ',') RETURN VARCHAR2 IS
   lnumPos   NUMBER;
   lstrLine  VARCHAR2(32767);
   lstrTerm  VARCHAR2(32767);
   lstrQuote VARCHAR2(1) := NULL;
BEGIN
	  -- Make a copy of the line, removing any leading whitespace
   lstrLine := LTRIM(pstrLine);
   
   -- Check for quoted strings
   lstrQuote := SUBSTR(lstrLine,1,1);
   IF lstrQuote IN ('''','"') THEN
   	 -- Term is a quoted string, remove the starting quote
      lstrLine := SUBSTR(lstrLine,2);

      LOOP
         -- ... look for the closing quote
         lnumPos := INSTR(lstrLine,lstrQuote);
         IF lnumPos > 0 THEN
         	 -- We've found one, but need to check to see if it's
         	 -- a '' or ""...
            IF SUBSTR(lstrLine,lnumPos+1,1) = lstrQuote THEN
            	  -- ... yes it is, add a single quote character to the
            	  -- term and keep looking...
               lstrTerm := lstrTerm||SUBSTR(lstrLine,1,lnumPos);
               lstrLine := SUBSTR(lstrLine,lnumPos+2);
            ELSE
            	  -- It's a proper closing quote. Add the rest of the
            	  -- string to the term
               lstrTerm := lstrTerm||SUBSTR(lstrLine,1,lnumPos-1);
               lstrLine := SUBSTR(lstrLine,lnumPos+1);

               -- Now look for the comma
               lnumPos := INSTR(lstrLine,pstrSeperator);
               IF lnumPos > 0 THEN
                  lstrLine := SUBSTR(lstrLine,lnumPos+LENGTH(pstrSeperator));
               ELSE
               	 -- No comma found, ignore the rest of the line
                  lstrLine := NULL;
               END IF;
               EXIT;
            END IF;
         ELSE
            -- String has an opening quote but not a closing one	
            RAISE VALUE_ERROR;
         END IF;
      END LOOP;

   ELSE
      -- not a quoted string - life's a lot easier!
      
      -- Find the next comma	
      lnumPos := INSTR(lstrLine,pstrSeperator);
      
      IF lnumPos > 0 THEN
      	  -- Found one: Before the comma is the term, after it the
      	  -- rest of the line
         lstrTerm := SUBSTR(lstrLine,1,lnumPos-1);
         lstrLine := SUBSTR(lstrLine,lnumPos+LENGTH(pstrSeperator));
      ELSE
      	  -- No comma, so this is the last term. 
         lstrTerm := lstrLine;
         lstrLine := NULL;
      END IF;
   END IF;
   
   pstrLine := lstrLine;
   RETURN RTRIM(lstrTerm);
END;

You can use it something like this:

BEGIN
   LOOP
      utl_file.get_line(myfile,lstrLine);

      -- With fixed numbers and meanings of fields 
      -- you can do this...

      lstrField1 := next_term(lstrLine);
      lstrField2 := next_term(lstrLine);
      ldatField3 := TO_DATE(next_term(lstrLine));
      lstrField4 := next_term(lstrLine);

      -- With variable numbers of fields, you could
      -- do this...
      WHILE lstrLine IS NOT NULL LOOP;
         lstrField := next_term(lstrLine);
         -- Do something with each field here
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;  -- EOF Reached
END;

This is all probably way over the top for your situation, but some may find it useful.

Note that it's usually frowned upon to have INOUT parameters in a function, but I think it makes the calling code more elegant in this case.

-- Chris Hunt
Extra Connections Ltd

http://www.extracon.com

 

[karluk]

Actually trim allows one to trim any character, not just spaces. For example

select trim('z' from 'zzABCzzz') from dual;