User Tools

Site Tools


techprivate:powerschool:customfieldsoracle

Custom Fields and Oracle Info

General Oracle stuff

Backups

Here’s the path to the Oracle backups:

On 205.123.190.30

E:\oradata\flash_recovery_area\PSPRODDB\BACKUPSET

Backup documentation can be found here: Oracle Backup and Restore

https://powersource.pearsonschoolsystems.com/d/managing_the_flash_recovery_area

Create a datapump:

c:\oracle\scripts Export.bat PSPRODDB filename.dmp

Importing datapumps:

> sqlplus / as sysdba
SYS@PSPRODDB AS SYSDBA> shutdown immediate
SYS@PSPRODDB AS SYSDBA> startup

c:\oracle\scripts DropSchema.bat PSPRODDB
c:\oracle\scripts Import.bat PSPRODDB [dumpfilename] Y

Note: 'Y' in the import will clear settings, useful if copying data over to the test server.

Note: The sqlplus steps insure that all users/connections are not in use. Seems to be necessary most times, so worth doing every time.

Useful SQL

For all active students:

SELECT * FROM students WHERE enroll_status = 0

Custom Fields

Custom field API:

ps_customfields.getcf('Students',id,'ACT_Composite')

Note:

Unique id of the record, normally the ID column, except for the following:

  • Schools: p_id = schools.school_number
  • StoredGrades: p_id = storedgrades.dcid
  • SPEnrollments: p_id = spenrollments.dcid
  • Virtualtablesdata: p_id = unique_id
  • Virtualtablesdata2: p_id = unique_id
  • Virtualtablesdata3: p_id = unique_id

Basic custom field information is in the fieldstable table. Key fields in this table are:

  • FieldNo: this is the key used in the values table
  • FileNo: indicates which table the custom field is attached to
  • Name: field name

These values are linked to the customtext and customvarchars tables via the KeyNo field which links to students.dcid.

Field Types from PowerSchool

To look up field names and types from PowerSchool itself: http://powerschool.pcschools.us/admin/home.html?ac=structure

techprivate/powerschool/customfieldsoracle.txt · Last modified: 2017/07/25 08:16 by afrink