SQL*Plus notes. 

(SQL Command Line) interactive and batch command-line query tool.
Enter and run SQL, PL/SQL, and SQL*Plus commands and statements.

SQL*Plus commands (partial list):
SET SHOW HELP COLUMN SPOOL EXIT DESCRIBE START EXEC [DIS]CONNECT
VARIABLE PRINT
@ & 


Login from cmd:
sqlplus [user[/password[@host]]]

login.sql    startup file

Batch file execution:
sqlplus -S -M "HTML ON"  hr/hr @mybatchfile.sql >makehtmlout.html
-- options: -S for no clutter.  -M for html output  >redirect output to file


--*** What tables are there?
SELECT table_name FROM tabs;     AKA: user_tables

--*** What are the columns (name, type, nullable) of a table?    
DESC mytablename

--*** PK column(s)
SELECT A.table_name,constraint_type,B.column_name,A.constraint_name FROM user_constraints A,user_cons_columns B  WHERE A.constraint_name=B.constraint_name AND constraint_type='P' AND  A.table_name='EVENT';

--*** FK columns and the table and column it references.  
SELECT A.table_name,B.column_name,A.constraint_name,A.constraint_type,A.r_constraint_name,D.table_name,D.column_name FROM user_constraints A,user_cons_columns B,user_constraints C,user_cons_columns D  WHERE (A.r_constraint_name=C.constraint_name AND A.constraint_name=B.constraint_name AND C.constraint_name=D.constraint_name) AND A.constraint_type='R' AND  A.table_name='EMPLOYEES';

--***checks and not nulls constraints on a table:
SELECT constraint_name,search_condition FROM user_constraints  WHERE constraint_type='C' AND table_name='PET';

--*** column default values, if any.   
SELECT cname,defaultval FROM col WHERE defaultval IS NOT NULL AND tname='PET';


--*** all constraints (but no column names or referenced table and column)
SELECT constraint_name,constraint_type,search_condition,index_name,r_constraint_name FROM user_constraints  WHERE table_name='PET';
--note: index_name for primary key and unique; 
        r_constraint_name  for foreign key, is PK's constraint name
        index_type: P primary key, R foreign key, C check/notnull, U unique

-- FK constraints.  what does it reference
SELECT b.constraint_name,b.constraint_type,b.r_constraint_name,a.table_name  FROM user_constraints a,user_constraints b   WHERE a.constraint_name=b.r_constraint_name AND b.table_name='EMPLOYEES';

--SELECT column_name,constraint_name FROM user_cons_columns WHERE table_name='MYTABLE';

-- PK and FK constraints: column names 
SELECT A.constraint_name,B.column_name,constraint_type,r_constraint_name FROM user_constraints A,user_cons_columns B  WHERE A.constraint_name=B.constraint_name AND constraint_type IN('P','R') AND  A.table_name='EMPLOYEES';





HELP INDEX     SQL*Plus commands. not very good...  :(
HELP RESERVED  list of SQL and PL/SQL reserved words
  
SHOW ALL    system variables

SET PAGESIZE 200     *** do this!
                     doesn't seem to be able to know the window size :(
SET LINESIZE 100     *** make window as wide as possible, set linesize to
                     that width

SET PAUSE ON|OFF      ON starts with a pause!?! :(  Enter :(  ^C Enter to quit :(
SHOW PAGESIZE
SET HEAD off          no column headings per page, nor at beginning :(
SHOW USER

--comment     but only as line by itself  :(
/* multiline comment
  as in C, Java etc.
  but can not be AFTER the ;  */          :(


Data dictionary
SELECT table_name FROM tabs;     user_tables
SELECT * FROM cat;     user_catalog    tables, views, sequences of user
SELECT * FROM tab;                     tables, views of user
SELECT * FROM user_objects;     obj       all objects of user, incl. Indexes
SELECT * FROM obj WHERE object_type='TABLE' AND object_name='MYTABLE;
-- name of indexes:
SELECT index_name,table_name,uniqueness FROM ind   [WHERE table_name='MYTABLE'] ;
-- column name, type, default, nullable:
SELECT * FROM col WHERE tname='MYTABLE'   [AND cname='MYCOLUMNNAME'];
-- default value of columns:   
SELECT cname,defaultval FROM col WHERE tname='MYTABLE';
SELECT * FROM user_tab_columns;     AKA: cols
SELECT * FROM cols WHERE table_name='MYTABLE' AND column_name='MYCOLUMNNAME';   

-- all users
SELECT username FROM all_users;
--my privileges
SELECT * FROM user_sys_privs;

--my views
SELECT * FROM tab WHERE tabtype='VIEW';
--see the base query that is the view
SELECT view_name,text_length,text FROM user_views WHERE view_name='MYTABLE';

--procedures and functions:
SELECT * FROM user_procedures;
SELECT * FROM obj WHERE object_type='PROCEDURE';
SELECT * FROM obj WHERE object_type='FUNCTION';
SELECT * FROM obj WHERE object_name='HELLO_WORLD';

--triggers
SELECT trigger_name FROM user_triggers;
SELECT * FROM user_triggers WHERE trigger_name='TRG_PRODUCT_REORDER';

--procedures and functions in packages:
SELECT object_name,procedure_name FROM user_procedures;
SELECT object_name,procedure_name FROM user_procedures WHERE object_name='MYPACKAGE';
Desc mypackagename

Desc objectType

DESCRIBE mytable             *** Desc mytable





DUAL dummy table.  one column DUMMY.  one row: X
for calculating, testing:
Select sqrt(2),sysdate,user,userenv('terminal') From dual;


capture everything on screen to file.  cf. typescript
SPOOL outputfilename
...
SPOOL OFF


script file: file of SQL commands:
conventional extension: .sql 
execute it: @myscriptfilename
re-run it: /
variables: &mynumericvariable  '&mytextvariable'
  Upon execution, will be prompted for value.

   SET VERIFY OFF
   script commands
   SET VERIFY ON      to avoid display of previous values of variables  
customized prompts:
  ACCEPT myvariable PROMPT 'Please enter value for widgets order: '

  SET ECHO OFF|ON      commands not shown
  SET TERMOUT OFF|ON   output not shown
  SET SCAN OFF|ON      allows embedded ' and & in strings

Note: interactively don't need Set Scan for embedded '
  insert into pet(name) values('arf''erd');


&variable can be used for table and column name:
Select &col From $table Where &col2=123 Group By &&col Order By &col3;


Loading text file of data into a table:  
Example: vendor.txt into already-created Vendor table
use web interface wizard: Utilities | Data Load/Unload | Load | Load Text File 
  Load To: Existing table
  Upload file (comma separated or tab delimited)    Next
  Schema: HR
  Table name: vendor  
  File:  [browse to it]
  Separator: \t    [for tab delimited]
  First row does not contain column names           Next
  [choose Column Names for each column dropdown box.]
  Load Data
 Repository should indicate 11 rows Succeeded, 0 Failed
Chapter 10 of 2 Day DBA.
Note: product.txt has two nulls indicated by empty...


column formatting: SQL*Plus
Column columnname Format $999,999.99
Column columnname Format A12 Truncate|Wordwrap
Column columnname Heading 'my | heading' Justify Left|Center|Right
Column columnname Format Off


Bind variables:
Variable testvar Number
Variable                      --to list all

begin :testvar:=123; end;      --assign value  use :name
/
begin :testvar:=sqrt(2); end;
/
print testvar
select * from t where colx=:testvar;
--using bind variable instead of different literals preserves identicality of statement thus cached statement will be used





test tables. 
DROP TABLE x CASCADE CONSTRAINTS;
DROP TABLE y CASCADE CONSTRAINTS;

CREATE TABLE x(
 xcode NUMBER(10) NOT NULL,
 xname VARCHAR(12) NOT NULL,
 xdate DATE NOT NULL CHECK(xdate>='1-jan-2000'),
 xnum NUMBER(8) DEFAULT 1234,
 CONSTRAINT x_pk PRIMARY KEY (xcode,xname));

CREATE TABLE y(
 ycode NUMBER(5),
 yname VARCHAR(12) DEFAULT 'hello' NOT NULL UNIQUE,
 yfk NUMBER(10) Check(yfk >0),
 PRIMARY KEY (ycode),
 CONSTRAINT y_fk FOREIGN KEY(yfk,yname) REFERENCES x);