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 UpdateProblemTa ble. 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 UpdateProblemTa ble('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
UpdateProblemTa ble(?,?,?)
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
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 UpdateProblemTa ble. 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 UpdateProblemTa ble('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
UpdateProblemTa ble(?,?,?)
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
Comment