Re: [newbie] select records, update with procedure
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 4 Aug 2004 07:15:26 -0700
Message-ID: <2687bb95.0408040615.5399458d_at_posting.google.com>
> > 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" <Mark.Powell_at_eds.com> wrote in message > > Questions:
> > > >"Chris Cowles" <NoSpam_at_For.me> wrote in message
> > > HTH -- Mark D Powell --
Date: 4 Aug 2004 07:15:26 -0700
Message-ID: <2687bb95.0408040615.5399458d_at_posting.google.com>
Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0408030534.15fba75d_at_posting.google.com>...
> "Chris Cowles" <NoSpam_at_For.me> wrote in message news:<xlAPc.302$bi.25_at_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" <Mark.Powell_at_eds.com> wrote in message > > Questions:
> > > >"Chris Cowles" <NoSpam_at_For.me> wrote in message
> news:<E8VOc.2903$U23.2375_at_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 --