Re: Dynamic SQL selection question

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:qBNHa.104111$x67.4397929_at_twister.socal.rr.com...
> Ton wrote:
> >
> > Can someone explain me why the following statement is ignoring the "
> > ANAL_A0 LIKE 'ABNA%'" condition. It returns also records which do not
> > comply with this selection criterion.
> > Thanks
> >
> > DECLARE retval INTEGER; CURSOR obj_cur IS SELECT DISTINCT
> > 'INSERT INTO C_SPIN (SUN_DB, AMOUNT, TRANS_DATE) SELECT
> > SSRFACC.SUN_DB, SUM(AMOUNT), TRANS_DATE FROM
> > SSRFACC,SALFLDG'||SUN_DB||' WHERE SSRFACC.ACCNT_CODE =
> > SALFLDG'||SUN_DB||'.ACCNT_CODE AND SSRFACC.SUN_DB ='''||SUN_DB||''' '
> > CMDSQL FROM SSRFACC WHERE SUN_DB LIKE 'B%' AND ACCNT_TYPE <> 'P' AND
> > ANAL_A0 LIKE 'ABNA%' ;
> > drop_cursor INTEGER;
> > out_str VARCHAR2(1000);
> > BEGIN FOR obj_rec IN obj_cur LOOP drop_cursor :=
> > DBMS_SQL.OPEN_CURSOR; out_str := obj_rec.cmdsql; DBMS_SQL.PARSE
> > (drop_cursor, out_str, DBMS_SQL.NATIVE);
> > retval := dbms_sql.EXECUTE(drop_cursor);
> > DBMS_SQL.CLOSE_CURSOR (drop_cursor);
> > END LOOP;
> > END;
>
> Are you implying that the query behaves differently if you just execute
> it in sql*plus? If that's the case, take a look in V$SQLAREA while this
> statement is running to confirm that the clause actually made it to the
> database. You're sure there aren't any triggers interfering with what
> you think you're seeing, right? If you can't even get the select part
> to work as expected in sql*plus then you'll need to log a TAR (must be a
> bug).
>
> Out of curiosity, what are some of the ANAL_AO values that make it
> through? Do all of them make it through?
>
>
> Richard
>

no im not implying that. Im simply stating that its easier to figure out what is going on in SQLPLUS than with dynamic sql. I always take my dynamic SQL and run it in sql plus if im having a problem. You can then manipulate the SQL directly to figure out what is going on. Far more efficient and saves alot of time. Received on Tue Jun 17 2003 - 19:05:54 CDT