Re: Can you pass a table name into a stored procedure?

On 7 Dec 2005 13:46:09 -0800, "dean" <deanbrown3d_at_yahoo.com> wrote:

>We have a set of tables (all the same structure) called
>EXPAND_ROUTE_001, EXPAND_ROUTE_002, .... to EXPAND_ROUTE_010.
>

This will usually result in an unscalable and non-performant application and kludges like you describe below. Time to throw away this 'design' asap

>I need to write a stored proc that can join to one of these tables
>selected by the user of an application, at runtime. Is there a simple
>way to do this?

Native Dynamic Sql. However your NDS statements are going to be parsed at least once you change the parameter, so you are violating the main advantage of stored procedure

 I cannot seem to be able to pass in the name of the
>table as a parameter, which makes sense I guess, but I would like a
>trick to get this to work.
>
>I know how to do this with a cursor/function combo, but was wondering
>if there is a simple way of doing this.
>

No one other than Native Dynamic SQL: Open cursor for.

Look this up in the PL/SQL documentation and a few months later you'll regret you didn't follow my advice and dumped the application.

>Thanks!
>
>Dean

--
Sybrand Bakker, Senior Oracle DBA