Are You Going to the Noetix View Customization Certification course? Review How to Use SQL*Plus

The Noetix View Customization Certification course uses SQL*Plus in lieu of the more user friendly GUI querying tools.

Are you used to nice GUI database querying tools? While there are non-database “tricks” to obtain metadata about the Noetix schema objects (e.g. examine various spool files), my personal feeling is to just be comfortable with sql statements that expose information from the data dictionary. In all honesty, some of these GUI tools encourage mental lethargy!

Suggestions:

Set-up SQL*Plus in way that enables you to work efficiently. 

-Use a login.sql script to provide some formatting

Here is the contents of my login.sql file:

--******login.sql start******************************

define _editor='C:\Program Files\Vim\vim73\gvim.exe'

set serveroutput on size 1000000
set trimspool on
set long 5000

set linesize 100
set pagesize 9999

column plan_plus_exp format a80
set termout off
SET SQLPROMPT '&_user@&_CONNECT_IDENTIFIER> '
set termout

--******login.sql end********************************

I personally like command mode in lieu of the GUI windows version of SQL*Plus.

With the editor set for Vim (or whatever text editor you want to use).

When you want to edit the SQL*Plus buffer, you would just type, edit

After your editing is complete, save and close your editor.

Next, type "r" or "/" to execute the contents of the buffer.  This is switch from the nice GUI tools, but after working with this for a little while, one can probably have a similar efficiency to a nice GUI tool.

I personally do all my text editing in Vim (or Notepad++).

************************************

Sample Data Dictionary Related Queries (here are more):

Use the dbms_metadata package if they are using the Oracle 9i Client (or later):

select

dbms_metadata.get_ddl('TABLE','N_VIEW_COLUMNS','NOETIX_SYS')

from

dual;

or for a view:

select

dbms_metadata.get_ddl('VIEW','INVG_ITEMS','NOETIX_SYS')

from
dual;

Otherwise use the DESC command to access a description of the columns of a view or table.

e.g.

DESC INVG_ITEMS

For the DDL associated with a view, this works well:

select

text

from
Dba_views
where
view_name = 'INVG_ITEMS'

Here is a query against constraints (a common question when executing DML against the template and non-template tables):  

select

*

from

dba_constraints

where 1=1

and owner = 'NOETIX_SYS'

and table_name like :table_name

;

/* types of constraints
C (check constraint on a table)

P (primary key)

U (unique key)

R (referential integrity)

V (with check option, on a view)

O (with read only, on a view)

*/

That is all.