~[wc:commonscripts] ~[wc:admin_header_css] ~[text:psx.html.admin_reports.runreports.start_page] > PowerSchool Customization ~[wc:admin_navigation_css] ~[SetPostValue:tab=sqlDev]

PowerSchool Customization

~[x:insertfile;tabs.html]
What is SQL Developer
Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Databases. SQL Developer offers the ability to explore and query your PowerSchool database.


Installation
Click here to download SQL Developer
  • Accept the license agreement at the top of the page and select the appropriate download for your operating system
  • Windows Users - Select the option with JDK 8 included
  • You will be required to create an Oracle account to complete the download
  • The download will consist of a zipped folder
  • Unzip the folder and place the contents anywhere on your system
Launch SQL Developer by running the executable file from the unzipped folder.

Connecting to Your Database

Click the green Plus symbol in the upper left corner of the screen to create a new database connection.
  • Connection Name - Give your connection a name (usually PowerSchool)
  • Username - psnavigator
  • Password - Enter the psnavigator password
  • Hostname - Enter the IP address of your PowerSchool server (database server)
  • Port - 1521
  • SID - PSPRODDB (may be different for hosted PowerSchool servers)


Hosted Clients
The hosting team will need to provide VPN access and connection credentials.
Click the Test button to see if you are able to connect. You will see one of the following messages.
Successfully Connected
Incorrect Hostname, Port, or SID
Incorrect Username or Password
Be sure to save your connection for future use.
Exploring Your Database Schema
Click the plus symbol next to your database connection to reveal the explorer.

The Oracle PS user is the owner of the PowerSchool tables and views.
To explore the database schema, expand Other Users > PS > Tables.

You will then see a list of all tables in the PowerSchool Database.
Click the plus symbol next to any table to see a list of the fields in that table.
Click any table icon to see the table column definitions in the right pane.
Querying the Database
The right panel includes the SQL Worksheet as well as the Query Results section.

Enter a simple query in the SQL Worksheet, and click the green triangle above the worksheet to execute the query.

Sample Query
SELECT lastfirst
FROM students

The query results will display below the SQL Worksheet.

Note
The SQL Worksheet can contain multiple queries. If the worksheet contains multiple queries, each query must be terminated with a semicolon(;). When the green triangle is clicked to execute a query, SQL Developer will execute the query where the cursor is located at that time.

Exporting Query Results
Query results can be exported to csv files as well as a number of other formats.

To export query results, right click on the results, and select Export.
The Export Wizard will appear. Select csv from the Format dropdown.
Choose a destination, and click the Next button.
Click Finish on the next screen, and a csv file will be saved to your selected location.

Configuration Options
Display Line Numbers
It is useful to display line numbers in the SQL Worksheet.

To enable line numbers, select Preferences from the Tools menu.
Then expand the Code Editor options, and select Line Gutters.
Check the box labeled Show Line Numbers.
Default Date Format
Many users prefer to have dates display in the format MM/DD/YYYY.
This is not the default format for SQL developer.

To modify the default date format, select Preferences form the Tools menu.
Then expand the Database options, and select NLS.
Adjust the date format to MM/DD/YYYY or your preferred format.

Resources for Learning SQL
~[x:insertfile;footer.html] ~[wc:admin_footer_css]