Home » SQL & PL/SQL » SQL & PL/SQL » Loops and varchar parsing
Loops and varchar parsing [message #39644] Tue, 30 July 2002 16:31 Go to next message
Matthew Iskra
Messages: 16
Registered: April 2002
Location: Sacramento CA USA
Junior Member
I need to take a varchar2(60) field in a table, get individual words then put them into another table.

I figured I would write a PL/SQL program to do it, but I get frustrated by PL/SQL-isms after writing in real programming languages.

The specific example is such:

-- start PL/SQL stored procedure
CREATE OR REPLACE PROCEDURE SB90
IS
r_comments INCIDENT.COMMENTS%TYPE;
CURSOR c_comments IS
SELECT COMMENTS
FROM INCIDENT
WHERE INCIDENT >= TO_DATE('19970701','YYYYMMDD') AND
INCIDENT < TO_DATE('19980701','YYYYMMDD');
BEGIN
FOR r_comments IN c_comments
LOOP
FOR current_pos IN 1 .. LENGTH(r_comments)
LOOP
DBMS_OUTPUT.PUT_LINE( 'do some processing here' );
END LOOP;
END LOOP;
END SB90;
/

-- end PL/SQL

The problem is the length line. The comments field might be null, might have space or spaces, or might have words in it. My original idea was a classic search through character by character, getting the words (any charcter with spaces on each side, or at the start or end of the record field) and inserting them into another table.

Is there a better way? Why is LENGTH() not understanding the r_comments variable (which is a VARCHAR2(60) by the way)?

Thanks in advance.

--Matthew
Re: Loops and varchar parsing [message #39651 is a reply to message #39644] Wed, 31 July 2002 04:31 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
r_comments isn't a variable. You need to specify the column name - in this case, LENGTH(r_comments.comments). Imagine if your query returned multiple columns and you'll understand why this is so.
Previous Topic: LOB morethan 4K error
Next Topic: String search in large table
Goto Forum:
  


Current Time: Fri Mar 29 06:23:45 CDT 2024