I simply want to pass in a string parameter to a procedure, something like this:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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:
- split the string to get individual values. (From now on, I'll refer to this task as "divide".)
- 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.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.