Re: PL/SQL block, won't accept accept

On Jan 31, 7:24 pm, "Ed Prochak" <edproc..._at_gmail.com> wrote:
> On Jan 29, 11:21 am, "Mitch" <spudtheimpa..._at_gmail.com> wrote:
> []
>
>
>
> > I am currently looking, but if anyone knows of any tricks/best
> > practices to get a user input from within a PL/SQL block (If possible,
> > which it looks like it isnt), it would be appreciated.
>
> > Cheers.
>
> > Mitch
>
> Mitch,
>
> Stop and think for a minute. PL/SQL is a server programming language.
> There is no user to get input from. Yes you compile PL/SQL from the
> SQL*Plus tool, but you do not run it on the client side. PL/SQL
> executes within the server. So there are no tricks to allow you to get
> user input. That is when front end tools like SQL/Plus or application
> languages like Oracle FORMS or interface libraries like ProC are used.
>

When you put it like that, so many of my problems are put into perspective.

> So if your procedure needs inputs, then make them parameters. Then you
> can compile your procedure
> After it compiles cleanly, then in a separate SQL script, you can run
> it. (IOW, split your workaround into two parts)
>
> HTH
>
> ed

Thanks. It does make more sense that way with hindsight. It's weird as whilst I'm learning, I'm being asked to use sqlplus as the front end. I've mentioned it earlier but I am moving onto Java later and will invariably be accessing the database that way eventually. I suppose for now these 'workarounds' are something I have to do. Putting the inputs in as variables is a useful exercise however, as I'm sure it will save me some code refactoring when I do try to integrate java/ oracle.

Thanks for your advice, everyone. It has cleared my head and given me some perspective on the whole thing.

Cheers again
Mitch. Received on Fri Feb 02 2007 - 05:04:24 CST