I am trying to use a variable in the FROM portion of an SQL statement
in order to process multiple tables through a cursor loop.

Everything works fine through the assignment of the v_office variable,
which has values like aub_poledata, bak_poledata, etc., and reflects
actual table names.

What am I doing incorrectly?

SET SERVEROUTPUT ON

DECLARE
CURSOR cOffice IS
SELECT code, name FROM pl_mapoffice ORDER BY 1,2;
v_rec cOffice%ROWTYPE;
v_office VARCHAR2(12);
v_type VARCHAR2(12);
v_count NUMBER(9);
BEGIN
OPEN cOffice;
FETCH cOffice INTO v_rec;
WHILE cOffice%FOUND
LOOP
FETCH cOffice INTO v_rec;

v_office := v_rec.code || '_poledata';

SELECT
ptype, COUNT(uniqueid) PoleCount
INTO
v_type, v_count
FROM
v_office
WHERE
NVL(dtnttmdt,0) <> 0
AND
NVL(ptype, 'NA') IN ('NA', 'WOOD');

DBMS_OUTPUT.PUT_LINE(v_rec.name || ', ' || v_type || ', ' ||
v_count);

END LOOP;
CLOSE cOffice;
END;
/

Originally posted by Jim C
> I am trying to use a variable in the FROM portion of an SQL statement
> in order to process multiple tables through a cursor loop.
>
> Everything works fine through the assignment of the v_office variable,
> which has values like aub_poledata, bak_poledata, etc., and reflects
> actual table names.
>
> What am I doing incorrectly?
>
> SET SERVEROUTPUT ON
>
> DECLARE
> CURSOR cOffice IS
> SELECT code, name FROM pl_mapoffice ORDER BY 1,2;
> v_rec cOffice%ROWTYPE;
> v_office VARCHAR2(12);
> v_type VARCHAR2(12);
> v_count NUMBER(9);
> BEGIN
> OPEN cOffice;
> FETCH cOffice INTO v_rec;
> WHILE cOffice%FOUND
> LOOP
> FETCH cOffice INTO v_rec;
>
> v_office := v_rec.code || '_poledata';
>
> SELECT
> ptype, COUNT(uniqueid) PoleCount
> INTO
> v_type, v_count
> FROM
> v_office
> WHERE
> NVL(dtnttmdt,0) 0
> AND
> NVL(ptype, 'NA') IN ('NA', 'WOOD');
>
> DBMS_OUTPUT.PUT_LINE(v_rec.name || ', ' || v_type || ',
> ' ||
> v_count);
>
> END LOOP;
> CLOSE cOffice;
> END;
> /


You cannot substitute table name with a variable (v_office) in the from
clause like that. you would have to use dynamic sql. Goto
http://tahiti.oracle.com and search for "EXECUTE IMMEDIATE" or
"DBMS_SQL" package.

Regards
/Rauf Sarwar

--
Posted via http://dbforums.com