sql statement

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. . .

Code:

---

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;
/

---

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

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

Quote:

---

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.

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

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

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

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.