Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Very simple PL/SQL question...
You can also use the dbms_sql package (dynamic sql).
Hans.
Jim Kennedy <kennedy-family_at_home.com> schreef in berichtnieuws
nvRP6.58364$p33.1274455_at_news1.sttls1.wa.home.com...
> If you have 8.i then use execute immediate. Hope you are not doing this
too
> often since it is very expensive to reparse. Better to either just issue
> the sql statement with host variables or to write sql to write sql.
>
> e.g.
> spool myprocs.sql
> select 'create or replace procedure deleteRecord'||table_name||'( p_id in
> number) as begin delete from '||table_name||' where id=p_id; commit;
> end;'||chr(10)||'/' from user_tables order by table_name;
> spool off
> @myprocs.sql
>
> Jim
> "Araxes Tharsis" <teclafp_at_hotmail.com> wrote in message
> news:9eoo2k$1mr$1_at_venus.telepac.pt...
> > Hi,
> > I need to create a PL/SQL procedure that accepts the name of a table and
> > issues an SQL statement to that table. Something like
> >
> > CREATE OR REPLACE PROCEDURE DeleteRecord
> > (
> > p_id NUMBER, p_table_name VARCHAR2
> > )
> > AS
> > BEGIN
> > DELETE FROM p_table_name WHERE id = p_id;
> > COMMIT;
> > END;
> > /
> >
> > How do I say that "p_table_name" is a variable and not the name of a
table?
> > Sorry for this simple question...,
> > Araxes
> >
> >
>
>
Received on Sun May 27 2001 - 03:09:01 CDT