Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic table and column identifiers?
The name of the package is dbms_sql.
Jens Schauder wrote:
>
> Hi
> The bad news are
> You can't do things like that with sql
>
> The good news are in most environments you can create your sql statement
> dynamical and the information to do so is stored in tables in
> oracle so you can access it.
>
> the tables you are looking for are
> all_tab_columns (You can also replace all by DBA or USER to get
> all the tables you can see
> all the tables in the db
> all the tables of your user
> obviously not everybody has the privileges to select all these tables.
>
> If you want to create your sql statement dynamicaly using PL/SQL
> ... there was a package ... but I forgot the name.
> Maybe somebody else can give a hint?
>
> dd wrote:
> >
> > I want to
> >
> > select count(*)
> > from {all tables that have a column named my_special_column}
> > where {the table's owner}=my_special_user
> >
> > Also, I would like to
> >
> > select {n first columns}
> > from {all tables that have a column named my_special_column}
> > where {the table's owner}=my_special_user
> > and my_special_column='my_special_value'
> >
> > Can somebody please rewrite pseudo-parts into SQL or show me another way to
> > achieve this?
> >
> > Regards Dag
> >
> > STARTADDRESSdpedeATonlineDOTnoENDADDRESS
Received on Fri Oct 29 1999 - 09:13:04 CDT