Re: PL/SQL:insert behaves different in procedure and interactive

No. currently I'm doing the topten insert with a cursor in an sp. I select
all rows desc sortet and then loop over the first 100 rows. This is ok but
the second way (the two step method) in PL/SQL is much faster. So I am trieing to
install this in my sp.

Oleg schrieb:
>
> Hi, Carsten,
>
> Question. Does first (slow) query work in stored procedure? If yes, rewrite
> second (and may be third too) query in similar to the first one way: “INSERT
> INTO topten SELECT * FROM (SELECT TO_DATE…)” and that is the solution.

Yes, the first slow one would work in an sp.

> If no, I have no ideas except using cursors to speed up slow query. I love SQL,
> but Oracle advises to use cursors instead of queries as much as possible. :(
>
> It seems you’ve met Oracle’s general problem. The best explanation I’ve
> heard that Oracle has two different SQL machines: for scripts and for
> procedures. Very simple example.
>
> create table t (x int);
>
> update t set x=NVL((select 1 from dual),0);
> /*
> this works fine running from PLSQL, but attempt to create procedure:
> */
> create or replace procedure f is
> begin
> update t set x=NVL((select 1 from dual),0);
> end;
> /
> show errors;
> /*
> fails because of compile time error:"PLS-00103: Encountered the symbol
> "SELECT" when expecting one of the following: ( - + mod not null others :…"
> */
>
> Bye.
>
> Carsten Jacobs wrote:
>
> > Hi,
> >
> > I want to store the 100 most ordered items by a particular customer
> > (120) in octobre.
> > I can do the following:
> >
> > INSERT INTO topten
> > SELECT * FROM
> > (
> > SELECT TO_DATE('2000-10-31 16:00:00','YYYY-MM-DD HH24:MI:SS'),
> > .........
> > I thank everybody who reads until this point. And I appreciate every
> > help because I feel like
> > running agoinst walls.
> >
> > Greetings
> > Carsten
 

-- 

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is 
prohibited. If you received this in error, please contact the sender and
delete the material from any computer.