Banner
Technical Basics
• A Discussion About Banner
Tables, Fields, Data, etc.
• Presented By
• Barry Stubbs
• Sr. ERP Analyst
• Sam Houston State University
• bstubb[email protected]
• In this presentation, Banner technical staff will gain a
better understanding of Banner Tables, Views, Fields,
Data, etc. This includes How-To’s, ERD’s, SQL queries, and
Discussion, which will expand the attendee’s knowledge
and understanding of foundational database concepts of
the Banner system.
• This presentation is geared towards technical staff
learning to support Banner in their organization.
•
Describe Banner Table/View/Field Naming Conventions
•
Describe Banner Tables Schemas/Owners
•
Provide technical 'How-To's' related to Tables, Views,
Fields, Data, etc.
•
Provide technical resources for further research.
•
Provide an open discussion of topics discussed.
• Banner Table Naming Conventions
• The unique seven- or eight-character names of Banner tables are
assigned according to a set of conventions. Each name is built from the
same four components:
• System identifier (one or two characters).
• Module identifier (one character).
• Object type code (one character).
• Unique identifier (four characters).
Ex. SPRIDEN
SPRIDEN
• System Identifier
• First 1-2 characters of table name.
• Identifies primary system that owns the table.
• A – Advancement
• F – Finance
• G – General
• N – Position Control
• P – HR/Payroll
• R – Financial Aid
• S – Student
• T – Accounts Receivable
• For a full list, refer to the Banner Getting Started Guide.
SPRIDEN
• Module Identifier
• Typically, the 2
nd
character of table name.
• Identifies primary module that owns the table, within the system identified by the
previous character(s).
• A Admissions
• C Catalog
• E Support Services
• F Registration/Fee Assessment
• G General Student
• H Grades/Academic History
• I Faculty Load
• L Local Management
• M CAPP
• O Overall
• P Person
• R Recruiting
• S Schedule
• T Validation Table
• U Utility
• For a full list, refer to the Banner Getting Started Guide.
SPRIDEN
• Object Type Code
• Typically, the 3
rd
character of table name.
• Identifies the type of table.
• B – Base Table
• R – Repeating/Rule Table
• T – Temporary Table
• V – Validation Table or View
• For a full list, refer to the Banner Getting Started Guide.
SPRIDEN - Student Person Repeating IDENtification Table
• Unique Identifier
• Typically, the last 4 characters of table name.
• Uniquely Identifies the table, within the System & Module.
• IDEN – Identification Table
• Examples
• FEED – Feed
• PERS – Person
• TELE – Telephone
• ADDR – Address
• EMPL – Employee
• POSN – Position
• STAT – Status
• GENL – General Ledger
• TRND – Transaction Detail
• Each Table has a Schema or Owner, which basically owns that table and all its
related objects, such as Indexes, Triggers, etc. within the database. Usually the
Schema/Owner corresponds with the System Identifier used in the table name.
• Some Common Owners/Schemas are:
• ALUMNI – Advancement Schema Owner
• BANSECR – Security Schema Owner
• FAISMGR – Financial Aid Schema Owner
• FIMSMGR – Finance Schema Owner
• GENERAL – General Schema Owner
• PAYROLL – Payroll Schema Owner
• POSNCTL – Position Control Schema Owner
• SATURN – Student Schema Owner
• TAISMGR – Accounts Receivable Schema Owner
• TXCNMGR – TCC Schema Owner
• Common Questions About Tables
• What’s the purpose of this table?
• Who owns this table?
• What kind of data does it store?
• What are the fields in this table?
• What do the field names mean?
• How is the table structured?
• What other tables relate to this table?
• What other tables have similar fields?
• What’s the impact of changing data in this table or field?
• What triggers, procedures, packages update this table or field?
• Where do I go from here?
• Other Questions You May Have About A Table?
• What are Some Sources of Table Information
• Ellucian Banner Manuals – Maybe, but I kinda doubt it.
• Google It! – Maybe, but results may be out-of-date or inaccurate.
• Use SQL’s desc tablename – Gonna get you a little info, but…
• Use SQL Developer and navigate to it – That may help some, but not
very easy to navigate to.
• IKEA, Ashley’s, Rooms To Go… - That’s what my wife would tell you.
• ERD’s (Entity Relationship Diagrams), Cross-Ref Charts, Ellucian
Support Search – Now we’re getting somewhere!
• Oracle Data Dictionary Views (Use SQL ) – That’s Cool!
• Ask on the Ellucian Forums! – Not a bad idea!
• Use a combination of some of the above (except IKEA)– Home Run!!!!
• Other Sources of Table Information?
Desc SPRIDEN;
Common Views Related to Tables & Fields
• ALL_TABLES – Lists all tables
• ALL_VIEWS – Lists all views
• ALL_TAB_COLUMNS – Describes the columns of the tables & views
• ALL_TAB_COMMENTS – Displays comments for the tables & views
• ALL_COL_COMMENTS – Displays comments for columns in tables
& views
• ALL_SOURCE – Contains source code for all PL/SQL objects,
including functions, procedures, packages, and package bodies.
• Other views also exist for the Data Dictionary, such as
• ALL_PROCEDURES, ALL_TRIGGERS, ALL_OBJECTS
Other Variants of the Views
• DBA – Views for all objects in the database
• ALL – Views for all objects in the database accessible to user
• USER – Views for all objects owned by the user
-- Details about Tables
select *
from ALL_TABLES
where TABLE_NAME = 'SPRIDEN';
-- List of Tables by Owner
select T.owner,
T.TABLE_NAME,
SUBSTR(C.COMMENTS,1,40) "DESCRIPTION",
T.STATUS,
T.LAST_ANALYZED,
T.NUM_ROWS
from ALL_TABLES T
left outer join ALL_TAB_COMMENTS c
on C.OWNER = T.OWNER
and C.TABLE_NAME = T.TABLE_NAME
where T.OWNER in ('TXCNMGR')
order by T.OWNER, T.TABLE_NAME;
-- List The Fields in a Table
select T1.COLUMN_NAME "FIELD NAME",
SUBSTR(T1.DATA_TYPE||'('||T1.DATA_LENGTH||')', 0, 20) type,
T1.NULLABLE,
T2.COMMENTS
from ALL_TAB_COLUMNS T1
left outer join ALL_COL_COMMENTS T2
on T1.TABLE_NAME = T2.TABLE_NAME
and T1.COLUMN_NAME = T2.COLUMN_NAME
where T1.TABLE_NAME = 'SPRIDEN'
order by COLUMN_ID;
-- List All Tables with Particular Fields
-- and Owned by a Particular Owner
select *
from ALL_TAB_COLUMNS
where COLUMN_NAME like '%PIDM%'
and owner = 'TXCNMGR'
order by OWNER, TABLE_NAME, COLUMN_NAME;
Details about Views
select *
from ALL_VIEWS
where OWNER = 'BANINST1'
order by VIEW_NAME;
-- Locate PL/SQL Code
-- Where PIDM is used
-- In TCC Code
select OWNER, TYPE, NAME, LINE, TEXT
from ALL_SOURCE
where upper(TEXT) like '%PIDM%'
and OWNER = ('TXCNMGR')
order by OWNER, TYPE, NAME, LINE;
• Triggers Owned by TCC
select *
from ALL_TRIGGERS
where owner = 'TXCNMGR'
order by OWNER, TRIGGER_NAME;
• Procedures Owned by TCC
select *
from ALL_PROCEDURES
where owner = 'TXCNMGR'
order by OWNER, OBJECT_NAME;
• All Database Objects Owned by TCC
select *
from ALL_OBJECTS
where owner = 'TXCNMGR'
order by OWNER, OBJECT_NAME,
OBJECT_TYPE;
• Banner General Technical Reference
Manual
• Banner Getting Started Guide
• Banner Transformed: Getting Started
With…
• Ellucian Support – Search for:
• Table or Field Name
• Entity Relationship Diagram
• ERD
• eCommunities – Search or just ask the
question
• Oracle Documentation
• Locate section on Data Dictionary
Views
• YouTube – Yes, you heard me right!
• Search for “Oracle Data Dictionary”
• Google
• Search for Table or Field Name
• Use discretion with the results
• May be old or inaccurate
• Barry Stubbs - bst[email protected]
