[newbie] select records, update with procedure

[Chris Cowles]

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

[Mark D Powell]

"Chris Cowles" <No****@For.mewrote in message news:<E8*****************@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');

You would want to set pagesize 0, set feedback off, set trimspool on
etc... to keep the execute script file clean

HTH -- Mark D Powell --

[Chris Cowles]

Mark -

Thanks for the time spent responding.

I found to_date() discussed in other threads. Those explained sufficiently
what my formatting problems were.

It sounds like the select 'execute....' may be the most efficient method. I
can work on generating the select statement without the 'execute
my_procedure(..' part to get the field syntax right, then add the execute
statement.

If a field already contains a date/time, can I just pass it, unformatted? Or
do I have to format the text output back through to_date, before passing it
to the execute statement?
"Mark D Powell" <Ma*********@eds.comwrote in message Questions:

"Chris Cowles" <No****@For.mewrote in message

news:<E8*****************@bignews5.bellsouth.net>. ..

[truncated]
1. How should I format this date field?

2. Is there a simpler way to accomplish this?
[truncated]

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');

You would want to set pagesize 0, set feedback off, set trimspool on
etc... to keep the execute script file clean

HTH -- Mark D Powell --

[Mark D Powell]

"Chris Cowles" <No****@For.mewrote in message news:<xl*************@bignews1.bellsouth.net>...

Mark -

Thanks for the time spent responding.

I found to_date() discussed in other threads. Those explained sufficiently
what my formatting problems were.

It sounds like the select 'execute....' may be the most efficient method. I
can work on generating the select statement without the 'execute
my_procedure(..' part to get the field syntax right, then add the execute
statement.

If a field already contains a date/time, can I just pass it, unformatted? Or
do I have to format the text output back through to_date, before passing it
to the execute statement?
"Mark D Powell" <Ma*********@eds.comwrote in message Questions:

>"Chris Cowles" <No****@For.mewrote in message

news:<E8*****************@bignews5.bellsouth.net>. ..

>
[truncated]
1. How should I format this date field?
>
2. Is there a simpler way to accomplish this?
[truncated]
>

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');

You would want to set pagesize 0, set feedback off, set trimspool on
etc... to keep the execute script file clean

HTH -- Mark D Powell --

Chris, it would depend on how the procedure was written. If it was
written to be called with a date then you would need create a valid
date variable using to_char('date','format'). If the procedure
expects a character string that it will convert to a date then you
pass the expected character string format.

HTH -- Mark D Powell --

[Mark D Powell]

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

"Chris Cowles" <No****@For.mewrote in message news:<xl*************@bignews1.bellsouth.net>...

Mark -

Thanks for the time spent responding.

I found to_date() discussed in other threads. Those explained sufficiently
what my formatting problems were.

It sounds like the select 'execute....' may be the most efficient method. I
can work on generating the select statement without the 'execute
my_procedure(..' part to get the field syntax right, then add the execute
statement.

If a field already contains a date/time, can I just pass it, unformatted? Or
do I have to format the text output back through to_date, before passing it
to the execute statement?
"Mark D Powell" <Ma*********@eds.comwrote in message Questions:

"Chris Cowles" <No****@For.mewrote in message

news:<E8*****************@bignews5.bellsouth.net>. ..

[truncated]
1. How should I format this date field?

2. Is there a simpler way to accomplish this?
[truncated]

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');
>
You would want to set pagesize 0, set feedback off, set trimspool on
etc... to keep the execute script file clean
>
HTH -- Mark D Powell --

Chris, it would depend on how the procedure was written. If it was
written to be called with a date then you would need create a valid
date variable using to_char('date','format'). If the procedure
expects a character string that it will convert to a date then you
pass the expected character string format.

HTH -- Mark D Powell --

Whoops. Make that to_date('date','format') to conver the character string to date.

-- Mark --

[Chris Cowles]

Thought so. Thanks for the clarification.

"Mark D Powell" <Ma*********@eds.comwrote in message

Chris, it would depend on how the procedure was written. If it was
written to be called with a date then you would need create a valid
date variable using to_char('date','format'). If the procedure
expects a character string that it will convert to a date then you
pass the expected character string format.

HTH -- Mark D Powell --

Whoops. Make that to_date('date','format') to conver the character string

to date.

>
-- Mark --