[newbie] select records, update with procedure

I use an application that uses Oracle 8.1.7. All functions of the application are completed with calls to stored procedures. A data entry error occurred that caused thousands of records to be created with a consistent error in a single field. I can identify those easily records with a select statement. I'd *really* rather not have to change them all manually.

I do have access to run a simple update query to correct only the field in question, but that won't trigger other events like insert records into application transaction logs, print update notices, etc. All of those are accomplished by the procedure I'll call UpdateProblemTable. The parameters of the procedure correspond to all fields in the table affected.

I've successfully used some other procedures by constructing a script using an Access (gack!) select query, then exporting that to a text file. It's not very sophisticated but it is accurate and works fine, except when one of the parameters is a date. It's also a rather primitive way to go about this, I'm sure.

When the procedure call fails, the error message says something to the effect of missing a parenthesis. Since it only happens when I try to pass a date as a parameter, I'm guessing a bad date format causes it.

What I'm sending is: Call UpdateProblemTable('field1' (text), field2 (number), 7/31/2003 23:45:00) .

Questions:

1. How should I format this date field?
2. Is there a simpler way to accomplish this? From browsing this group, I see references to 'select into procedure'. If I could do something like that, this would be a piece of cake. If so, I'm guessing it would be something like:

Select 'correct', field2, field3 from ProblemTable where field1='wrong' into UpdateProblemTable(?,?,?)

Am I way off base? If not, the field I'm updating is a text field, so the date issue is moot. (Note: I do not have rights to create tables or anything like that. For the purposes of this issue, I'm pretty much limited to select, update, and call procedure.)

I have other uses for a similar call to another procedure, but need to provide a specific date as a parameter in that case. So, for that, I do need to know how to provide a date as a parameter.

Thanks for in advance for your time and advice.

Chris Received on Sat Jul 31 2004 - 17:35:28 CDT