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

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. 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
  Received on Thu Nov 02 2000 - 17:21:27 CST