Re: Dynamic SQL selection question

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 Received on Tue Jun 17 2003 - 18:35:50 CDT