How to access database tables in Oracle 1

[123ASP]

hi,
I wonder how can I view all available database in Oracle. I want to be able to identify specific database then view its tables so I can create store procedures to pull data in a reporting format.
I have not worked with Oracle before, but I have a client who gave me access to his plsql which prompt me to provide the following:
user name, password and hosting string

Then what, I guess I need to know more information about the database structure, so what command I should be typing to become familiar with the database, I need to be able to view all available database, view all table in each database, and become familiar with table relation ship so I can create the sql code which will return desired data.

I am sorry if my question is dumm.
Thanks for all your help and support
Al

 

[SantaMufasa]

Al,

When you say, "...I have a client who gave me access to his plsql...", what REALLY did the client GIVE you? To connect from your PC to your client's environment, s/he should have given you 1) a username, 2) a password, and 3) a "Host Service" alias that appears in a connectivity file (named "tnsnames.ora") on your local machine. That file usually appears in your ORACLE_HOME/network/admin directory. (ORACLE_HOME is logical name that probably resolves to something like "C:\Oracle\Ora81".) If you are missing any one of those three components, then your customer didn't really give you access to her/his database.

Additionally, (from a nomenclature perspective) you probably did not try to connect via "plsql" as you suggest; you probably attempted to connect via "SQL*Plus"...two very different Oracle components whose only common features are shared letters in their spellings.

Now, presuming that you do get connected to her/his database, some queries that you can issue to become more familiar with your environment are (from an SQL> prompt):

select * from all_users;
(displays username and other information about all other users [schemas] on the same database).

select table_name from user_tables;
(displays names of tables your login "owns".)

Now, for you last request, to "...become familiar with table relationships so I can create the sql code which will return desired data", perhaps the best way to see relationships (semi-graphically) amongst your tables is to save my script code, below, to a script, which I call "pk_fk.sql", then execute the code from your SQL> prompt as "SQL> pk_fk". The script generates a hierarchical tree-walk of all tables that your login owns, depicting the parent-child (primary key-to-foreign key) relationships within the schema. Here is the code:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set pagesize 500
set feedback off
drop table uc
/
create table uc as
select table_name, constraint_name,
r_constraint_name from user_constraints
where constraint_type in ('P','R')
/
drop table pk_fk
/
create table pk_fk as
select decode(table_name,prior table_name,'(recursive)',table_name) detail,
 prior table_name master
from uc
where prior table_name is not null
connect by prior constraint_name =
r_constraint_name
union
select
table_name detail, '' master
from uc
where not exists
 (select 'x' from user_constraints
 where uc.table_name = table_name
   and constraint_type = 'R')
/
set pagesize 0
select
lpad(' ',(level-1)*3) ||
detail
from pk_fk
where master <> detail or master is null
connect by prior detail = master
start with master is null
/
set echo on
set feedback on
set pagesize 35

REM *** End of PK_FK.sql script ************************

Let us know if these responses help to resolve your needs.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:05 (04Mar04) UTC (aka "GMT" and "Zulu"), 13:05 (04Mar04) Mountain Time)

 

[SantaMufasa]

Al,

I probably should have posted sample results from the PK_FK.sql script on my previous post. Following is sample output from the script, showing the hierarchical relationships that exists amongst the tables for the schema with which I connected to Oracle:

S_IMAGE
   S_PRODUCT
      S_INVENTORY
      S_ITEM
S_LONGTEXT
   S_PRODUCT
      S_INVENTORY
      S_ITEM
S_REGION
   S_CUSTOMER
      S_ORD
         S_ITEM
   S_DEPT
      S_EMP
         (recursive)
         S_CUSTOMER
            S_ORD
               S_ITEM
         S_ORD
            S_ITEM
         S_WAREHOUSE
            S_INVENTORY
   S_WAREHOUSE
      S_INVENTORY
S_TITLE
   S_EMP
      (recursive)
      S_CUSTOMER
         S_ORD
            S_ITEM
      S_ORD
         S_ITEM
      S_WAREHOUSE
         S_INVENTORY

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:09 (04Mar04) UTC (aka "GMT" and "Zulu"), 13:09 (04Mar04) Mountain Time)

 

[123ASP]

Thanks for your help, I ment to say that I was given user name and pssword for Oracle Sql* Plus
once I am logged in , I am able to run some of your sql commands. but for the select table_names from user_tables;
did not get anything. I know that the database name is clientDB and there is a table name called customers.
how can I access the table structure .
select * from clientDb.customers .
thanks again.
al

 

[SantaMufasa]

Al,

If you issue the command: "select table_names from user_tables;" and it displays nothing, then you should issue the SQL*Plus command: "set feedback on". Then reissuing the "Select..." statement should respond with "no rows selected." This means that the account into which you logged has not tables of its own.

Should you wish to see the tables that the "clientDb" schema owns, and assuming that your current login has at least "SELECT" privileges on "clientDb's" tables, then you can issue this variation of the previous command I posted:

select table_name from all_tables where owner = 'CLIENTDB';

My final script, above, works (as currently written) against just the tables within your login schema. You could modify the script to review parent-child relationships for other schemas for which you have SELECT privileges for its tables, but it would take a fair amount of modifications (which I don't have time to do presently). The quickest method to get done what you want is to have your customer either give you access to the owning schema's login (which s/he likely won't want to do) or have her/him connect themselves to the owning schema, run the "PK_FK.sql" script, and forward the results to you.

Let me know if this at least keeps you moving forward.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:01 (04Mar04) UTC (aka "GMT" and "Zulu"), 14:01 (04Mar04) Mountain Time)

 

[123ASP]

Have you used visual studio and created an oracle connection and used Oracle command to create parameter query to pull data into an aspx page. if you know how, I am stuggling in the following:

http://aalhussein.webhostme.com/Q/OracleConnection.html

The above link will explain the problem and thanks for your help
Al