Re: [newbie] select records, update with procedure

"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 -- Received on Tue Aug 03 2004 - 15:34:26 CEST