Q

Splitting a string, part 1

I simply want to pass in a string parameter to a procedure, something like this:

 01|03|07|11|99|100|231

I need to split the string in the procedure, then select from table a a value based on the values passed in from the string, and then I need to insert that value into table b. I want to do this loop for every value contained in the string ('01' first, then '03', '07', etc.).
I see two different jobs here. You need to:

  1. split the string to get individual values. (From now on, I'll refer to this task as "divide".)
  2. do whatever you need to do with each value. (I'll call this "conquer".)

Your ultimate goal is to conquer, and it sounds like you know how to handle that part; perhaps you already have a procedure that does it for a single value. Don't touch that procedure! For purposes of code maintenance it's best to have this logical unit contained in a separate module.

CREATE OR REPLACE  PROCEDURE  goal
(
    in_text  IN  table_a.id%TYPE
)
--      ***************
--      **  g o a l  **
--      ***************

--  goal inserts yadda yadda yadda ...
--  based on table_a blah blah blah ...

IS
BEGIN
    INSERT INTO table_b (column_2)
    SELECT  column_1
    FROM    table_a
    WHERE   id = in_text;
EXCEPTION
    WHEN  NO_DATA_FOUND
        THEN  ...
END  goal;
/

Since dividing is such a generic, useful task, you'll almost certainly want to isolate it as a separate procedure. If you're using packages, you'll want the two procedures in separate packages, since you'll probably want to give lots of people the ability to divide, but only a few people will have the power to conquer.

Continued on page 2.

This was first published in November 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close