Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL selection question
76434.1353_at_compuserve.com (Ton) wrote in message news:<4477604.0306170404.50ae7b0d_at_posting.google.com>...
> 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;
you need to run it as straight sql and see what happens. best way to
do this is change your DBMS_SQL.OPEN_CURSOR stuff to a UTL_FILE or a
dbms_output and take a look at the query. then run the select
statement.
btw, use execute immediate. its easier. Received on Tue Jun 17 2003 - 12:02:17 CDT