Re: DYNAMIC SQL SELECTION QUESTION

ITAPORT06 wrote:
[...]
>
> Example:
> Row of File A: field SUN_DB = "ZZZ" so File A has to be joined
> with Table: "B_ZZZ" by the fields SUN_DB & ACCOUNT_NR
>
> Row of File A: field SUN_DB= "YYY" so File A has to be joined
> with Table: "B_YYY" by the fields SUN_DB & ACCOUNT_NR
>
> There are +- 700 SUN_DB codes and so 700 TABLES TO JOIN WITH
> IN SHORT I need to run +-700 joines between Table A and variable Table
> Name B named: SALFLDG'||SUN_DB||'
>

Hello ITAPORT06,
maybe it's worth to rethink your database design. What about putting all 700 tables into one adding a column sun_db so that you can join on this column? This would eliminate the need for dynamic SQL altogether.

> Do I make myself clear?
>
> I use the following script but some say it is far too complex for I
> try to achieve. For me this script is working fine but it is ignoring
> the following conditions "AND ACCNT_TYPE <> 'P' AND ANAL_A0 LIKE
> 'ABNA%'; "
> Can anyone explain me why?
>
> DECLARE retval INTEGER; CURSOR obj_cur IS
>
> SELECT DISTINCT 'INSERT INTO C_SPIN (SUN_DB, ACCNT_CODE, TRANS_DATE,
> AMOUNT, ANAL_A0, ANAL_T0)
>
> SELECT SSRFACC.SUN_DB,SRFACC.ACCNT_CODE,TRANS_DATE,
> AMOUNT,SSRFACC.ANAL_A0, ANAL_T0 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;
>

I don't see why the where condition is not executed but there is something that you can improve:
I would put the dbms_sql.open_cursor and dbms_sql.close_cursor out of the loop. A SQL cursor can be used for more than one SQL. You can reuse it just by parsing a new SQL.
Maybe it would help to output the SQLs using dbms_output to see what's wrong.
Eventually, you might have a look at the execute immediate feature of Oracle 8i and up. This is a bit easier to handle and according to the docs it is more performant.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de