Re: [newbie] select records, update with procedure

"Chris Cowles" <NoSpam_at_For.me> wrote in message news:<E8VOc.2903$U23.2375_at_bignews5.bellsouth.net>...
> 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

Chris, Oracle normally expects character date strings in the format 'DD-Mon-YY' unless otherwise specified.

You can use SQL via SQLPLUS to generate execute procedure(parms) to perform your updates:

UT1 > select ' execute my_proc('''||fld2||''');'   2 from marktest;

'EXECUTEMY_PROC('''||FLD2||''');'

 execute my_proc('1');
 execute my_proc('2');
 execute my_proc('3');
 execute my_proc('4');
 execute my_proc('5');

HTH -- Mark D Powell -- Received on Mon Aug 02 2004 - 22:01:51 CEST