Dynamic Table Names and Moving Table

I'm fairly new to PL/SQL and I could use some help. I'm trying to create a cursor and build the tablename based on an input parameter. Currently I'm using DBMS.SQL statements, which I find cumbersome to use. I'd like to find an easier way if possible. Here is an example of what I'd like to do;

Cursor DYNAMIC is
SELECT column1 from I_PARAM||restoftablename
in the above example I_PARAM is an input paramter.

You can try using "EXECUTE IMMEDIATE" statement create a table dynamically.....also do get a second opinion.

*****************
declare
my_table varchar2(400);
begin my_table := 'create table dynamic_table (my_name varchar2(20))';
EXECUTE IMMEDIATE my_table;
end;
*****************

In Oracle DBS 7.3, how can I move a table from one tablespace to another without losing permissions, index's...etc for this table?

Hmmm good question ....i used diff methods but nothing like this in 7x...but still i'll try....
* First i would take an export of that table (say mytable.dmp)
* Open mytable.dmp in note pad.
* Edit the tablespace to the new tablespace name(whichever tablespace u wanna move).
* Save the file
* Try to import the dump file(mytable.dmp) into the new user schema u want to.

I can't gaurentee that this procedure would work but its worth a try on test table first.

No comments:

topics