Getting Table Name and Probable Table Owner

Using the Defines Using Select tip, here is a snippet of SQL code that can be used to prompt for and get the table name from the user, as well as the probable default table owner (from the first dba_tables entry for that table name, or, if not found, using the current user's ID) which can be overridden by the user.  The resulting entries are put into the tablename and tableowner variables as upper case values.

set showmode off echo off
set termout on
accept tablename char prompt 'Enter table name: '
set termout off
column tablename new_value tablename
select upper('&tablename') tablename from dual;
define tableowner = 'DUMMY'
column tableowner new_value tableowner
select owner tableowner from dba_tables
     where table_name = upper('&tablename') and rownum = 1;
select username tableowner from user_users
     where '&tableowner' = 'DUMMY' and rownum = 1;
set termout on
accept tableowner2 char prompt 'Enter table owner, if not &tableowner: '
set termout off
select upper(decode('&tableowner2','','&tableowner','&tableowner2'))
     tableowner from dual;

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.