Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service
Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search | Publications | Jobs | Personnel Directory | Links

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;

This Page was Last Updated on 06/04/07

You Are Visitor Number

© 2006 University of Arkansas Division of Agriculture All rights reserved. Last Date Modified 02/09/2008 Webmaster
	University of Arkansas • Division of Agriculture Cooperative Extension Service 2301 South University Avenue Little Rock, Arkansas 72204 • USA Phone (501) 671-2000 • Fax (501) 671-2209	Mission • Disclaimer • EEO • Privacy • FOI