sql statement

[DaPi]

I still think the IN can go and you still need the ; after the last )

Will this work?

 declare
 v_metatags tblworkingcontent.metatags%type;
 v_numerorighe int;
 begin
    delete from tblworkingcontent wc1
       where wc1.metatags=v_metatags
       and (select count(*)
            from tblworkingcontent wc2
            where wc2.idworkingcontent < wc1.idworkingcontent
               and wc2.metatags=v_metatags) <
               (select count(*)
                from tblworkingcontent wc3
                where wc3.metatags=v_metatags-v_numerorighe)
    ;
end;
/

[edli]

no it still doesn't.But I agree about the in clause.
Here are the errors:


ERROR at line 7:
ORA-06550: line 7, column 13:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others
avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date


ORA-06550: line 10, column 45:
PLS-00103: Encountered the symbol "<" when expecting one of the following:
; return returning and or
ORA-06550: line 15, column 1:
PLS-00103: Encountered the symbol "END"

[DaPi]

Hmmm . . think I've seen something like this where the scalar sub-query had to go on a specific side of the > . . . but that was RDB ! ! !

Last try. . .

declare
 v_metatags tblworkingcontent.metatags%type;
 v_numerorighe int;
 v_countr int;
begin

    select count(*) into v_countr
       from tblworkingcontent wc3
       where wc3.metatags=v_metatags-v_numerorighe;

    delete from tblworkingcontent wc1
       where wc1.metatags=v_metatags
       and v_countr > 
           (select count(*)
            from tblworkingcontent wc2
            where wc2.idworkingcontent < wc1.idworkingcontent
            and wc2.metatags=v_metatags);
end;
/

[edli]

Perfect no errors now.
So it's the side of the > that matters?
Thanks a lot.

[chrisrlong]

Now, I haven't even looked at the actual SQL, but the very first response to this problem should have been to put the SQL in a string and run it as dynamic SQL inside the PL/SQL. There are two different SQL engines in 8i, and the one for straight SQL is well ahead of the one for PL/SQL. So anything that works in SQL*Plus but not in PL/SQL simply needs to be converted to dynamic SQL to work.

- Chris

[slimdave]

Originally posted by chrisrlong
Now, I haven't even looked at the actual SQL, but the very first response to this problem should have been to put the SQL in a string and run it as dynamic SQL inside the PL/SQL. There are two different SQL engines in 8i, and the one for straight SQL is well ahead of the one for PL/SQL. So anything that works in SQL*Plus but not in PL/SQL simply needs to be converted to dynamic SQL to work.

- Chris

That's fine as a quick workaround, but there's overhead associated with repeatedly running it as dynamic SQL, no? A better approach is to rewrite it to a form that the PLSQL SQL engine can handle, I would think.

[chrisrlong]

Nope. There is virtually no overhead with Dynamic SQL. I personally find so many advantages to Dynamic SQL that it is pretty much my standard methodology.

- Chris

[slimdave]

So do you use DBMS_SQL, or EXECUTE IMMEDIATE, or both?

[chrisrlong]

NDS (Native Dynamic SQL => EXECUTE IMMEDIATE, OPEN..FOR ) all the way. DBMS_SQL sucks, IMHO. I even created a solution to allow variable inputs for NDS SQL, so I wouldn't have to use DBMS_SQL. I further created another package that wraps DBMS_SQL so that it always does array fetching, which is the only thing that makes it use-able, since it's so bloody slow.

- Chris

[slimdave]

Doesn't NDS open you up to continual re-parsing of the query though? Even when you use bind variables, you are re-parsing every time the statement is executed.