Re: need to use variable in FROM clause

As you are using 8i you can solve the problem also with native dynamic sql. Search your online-doc - there are numerous examples.

DECLARE
    TYPE EmpCurTyp IS REF CURSOR;
    c EmpCurTyp;
    emp_rec emp%ROWTYPE;
    stmt_str VARCHAR2(200);
    e_job emp.job%TYPE;

BEGIN
   stmt_str := 'SELECT * FROM emp WHERE job = :1';

• in a multi-row query OPEN c FOR stmt_str USING 'MANAGER'; LOOP FETCH c INTO emp_rec; EXIT WHEN c%NOTFOUND; END LOOP; CLOSE c;
• in a single-row query EXECUTE IMMEDIATE stmt_str INTO emp_rec USING 'PRESIDENT';

END;
/

Klaus

<tandym_at_hotmail.com> schrieb im Newsbeitrag news:8ogrda$n55$1_at_nnrp1.deja.com...
> I want to be able to pass a table name to a procedure and have it
> execute a generic select statement. I have had problems doing this
> (PLS-00201) and have a vague notion that I will need to user DBMS_SQL
> to do it. PLease help - all examples I have been able to find don't
> show how to use a variable in a FROM clause. My code is below. I am
> using Oracle 8.1.5 and PL/SQL 8.1.5 on an NT box.
>
> ------------------------------------------------------------------------
> --PROC_EVENT_DET_OTHERS
> --Is Called from PROC_EVENT_DETAIL_PARENT for details of tables that do
> --not require joins to other tables
> --Accepts request number and table name
> --Passes Back CURSOR
> ------------------------------------------------------------------------
> procedure proc_event_det_others(v_table IN varchar2,
> v_requestnr IN NUMBER,
> c_event_detail IN OUT audit_cur_typ) IS
>
>
> BEGIN
>
> OPEN c_event_detail FOR
>
> SELECT *
> FROM V_TABLE
> WHERE requestnr = V_REQUESTNR;
>
> END;
> -------------------
>
> Thanks,
>
> sonya
> please respond email as well, if possible
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Aug 29 2000 - 14:44:15 CDT