Re: dynamic Tables

From: <brendan_o'brien_at_wrightexpress.com>
Date: Mon, 20 Jul 1998 20:32:37 GMT
Message-ID: <6p09h5$j5g$1@nnrp1.dejanews.com>

Yes, you can use DBMS_SQL:

create or replace procedure something(table_name in varchar2) is

sql_statement varchar2(1000) default 'select col1, col2, col3 from '; my_cursor integer default null;
ignore integer default null;
x_col1 varchar2(100);
x_col2 {etc.}

BEGIN

	sql_statement := sql_statement || table_name;
	my_cursor := dbms_sql.open_cursor;
	dbms_sql.parse(my_cursor, sql_statement, dbms_sql.native);
	dbms_sql.define_column(my_cursor, 1, x_col1, 100)
	--repeat for subsequent columns

	ignore := dbms_sql.execute(my_cursor);
	while (dbms_sql.fetch_rows(my_cursor) > 0) loop
 		dbms_sql.column_value(my_cursor, 1, x_col1);
		--repeat for subsequent columns

		--do what you will
	end loop;
	dbms_sql.close_cursor(my_cursor);

END; Of course, you'd have to know enough about the possible table names that will come in in order to intelligently build the 'core' query in terms of column names and datatypes, not to mention whatever it is you plan on doing with the retrieved data. Most people use dynamic SQL to conditionally build WHERE and ORDER BY clauses, but there's no reason why you couldn't use it to conditionally build any element of a query.

Good luck.

In article <6ovquq$u9f$1_at_nnrp1.dejanews.com>,   Hard_Core_at_my-dejanews.com wrote:
> Hi, I've just started working with PL/SQl and only have two books from which
> to learn. Neither of the books that I have make any reference to being able
> to have tables sent in as parameters. Is there a way to do this?
>
> eg.
>
> create procedure something (table_in varchar2)
>
> select * from :table_in;
>
> ...
> end;
>
> It doesn't look like DBMS_SQL can do it, and normal PL/SQL can't do it either.
>
> Hope I've given enough Info. Thanks in advance.
>
> Sam
>
> ps. I'm at work and only have access to e-mail. using someone elses computer
> to read ng's. Please respond to the address below.
>
> smullen at avantec dot net
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 20 1998 - 15:32:37 CDT