Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Very simple PL/SQL question...
That can be done by using Native Dynamic Sql (Open cursor <cursorvariable> for)
Hth,
Sybrand Bakker, Oracle DBA
"Hans Noordhof" <kinetics.delete.or.remove_at_dds.nl> wrote in message
news:9eqga6$7dn9i$1_at_reader02.wxs.nl...
> Much faster?! Also when the statement has to be executed many times, isn't
> it better
> to create a open cursor and determine if the cursor is still open and
> execute
> the cursor by binding the variables.
>
> Hans.
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> schreef in berichtnieuws
> th1eqc4rse6lf8_at_beta-news.demon.nl...
> > That is obsolete with 8i as execute immediate is much faster and more
easy
> > to implement.
> >
> > Regards,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > "Hans Noordhof" <kinetics.delete.or.remove_at_dds.nl> wrote in message
> > news:9eqcet$7c7ba$1_at_reader02.wxs.nl...
> > > 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 - 04:46:04 CDT