SQLPLUSHow to talk to Oracle server?Slide 3Slide 4What is SQL*PLUSSlide 6Slide 7Slide 8What commands does SQL*PLUS deal with?Slide 10Slide 11Exiting SQL*PlusSlide 13SQL*Plus file commandsSome SQL*Plus file commandsRetrieving a FileUsing Scripts to Create Database TablesSending the Results to a File using spoolExample of using spoolLine-by-line EditingEditing Prior CommandsUsing NotepadSQL*PLUS variablesSlide 24Slide 25bind variablesSlide 27Slide 28Assigning a value to a bind variable with executeUser variableSlide 31Slide 32Slide 33Suppressing old and new valuesSlide 35AcceptSlide 37Output format enhancementFormatting Output in SQL*PlusSimple formatting query resultssetSlide 42Slide 43ErrorsThe role of SQLPLUSSQLPLUSKnow more about the tool you rely on!How to talk to Oracle server?•SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database.Oracle system client server architecture•Oracle server and Oracle clientOracle serverOracle client Oracle client Oracle clientdata managementtransaction controlrecoverysecurityClient software provides interface to manipulate datatools to support development of application.SQL*PLUS is one of the client tools allowing developers to communicate with Oracle server.Oracle is a client-server architecture and SQL*Plus is the client.SQL*Plus•Interface to manipulate Oracle databases•Tool to support the development of application-SQL*Plus as an interfaceWhat is SQL*PLUS•SQL*Plus is a client end interface tool and reporting tool that ships with the Oracle Database Server. •SQL*Plus is a client terminal software allowing users to interact with Oracle server to manipulate data and data structures.•It is a client agent to bridge the user and the server. It is usually used interactively. –Users type in SQL statements in SQL*Plus that send statements to Oracle server, with simple grammar check.–Oracle server then validates and executes the statements on its databases. –The query results are returned to SQL*Plus and displayed to the user.•Besides sending SQL statements to the server, SQL*Plus also saves them into a local buffer and allow users to view and change the statements. The following figure illustrates the process.What commands does SQL*PLUS deal with?•It can take SQL and PL/SQL commands from users in command line mode and send those commands to Oracle server. •Besides bypassing SQL and PL/SQL, SQL*PLUS supports also a set of SQL*PLUS commands to help format query result and preprocess SQL query.•Therefore, when you use SQL*PLUS, you potentially will mingle several set of commands when you type:–SQL commands intended to pass to oracle server.–PL/SQL commands also for oracle server to run–SQL*PLUS commands for pre-processing or post-processing for SQL*PLUS itself-To start SQL*Plus, enter Oracle username and password:–$> sqlplus jason/athena (from a command line operating system such UNIX)–click: Start  Program  Oracle (for Windows - SQL*Plus)–At SUCO, we get access to SQL PLUS through a batch file.Exiting SQL*Plus•Three ways to exit SQL*Plus:–Type exit at the SQL prompt–lick File on the menu bar, and then click Exit–Click the Close button on the program window title bar•Database session ends when SQL*Plus exits•After you login into SQL*Plus, at the SQL prompt, you can begin typing any SQL command. •Upon hitting return (i.e., enter key) the SQL prompt will change to line number prompts. •When you are finished typing a command, type / or RUN to execute the SQL command. •Also, a semicolon at the end of the SQL command will execute the command immediately after hitting return. •In addition to SQL commands, /, and RUN, you can also executes SQL*Plus commands.SQL*Plus file commands•SQL*Plus file command allow you to execute commands (or programs) stored in an external file, input or output data from/to a file, and save SQL commands typed during current session.Some SQL*Plus file commands•SAVE filename. –This allows you to save buffer contents into a file. •SPOOL filename. –This allows you save SQL statements together with their outputs to a file. •GET filename. –This retrieve a file and places it into the buffer.•START filename. –This allows you to execute a batch of SQL statements stored in a file. •@ filename. –This allows you to execute a PL/SQL procedure(s) stored in a file.Retrieving a File•To retrieve SQL command from a file use GET filename or START filename. •GET filename places the file into the buffer. •START filename executes the commands in the file. •The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.Using Scripts to Create Database Tables•One or more SQL commands saved in a text file•Usually have .sql extension•To run from SQL*Plus:–Start full file path (c:\temp\myfile.sql)–@full file path (@c:\temp\myfile.sql)–Extension can be omitted if it is .sqlSending the Results to a Fileusing spool•To send queries and their results to a file for later printing (e.g., turning in a running session for homework assignments), the spool command is used. •On the menu go to FILE/Spool to begin spooling--it will ask for a file name. To quit spooling, go to FILE/Spool and then click on end Spool.•Or you can always issue appropriate SQLPLUS commands to activate the …Example of using spool •spool test.out•select * from employee; •spool offLine-by-line Editing•The previously executed commands (in current SQL*Plus session) are stored in the local buffer. One way to change an SQL statement in the buffer is by using the line editor. The following are a list of line edit commands. –LIST or L--Lists the contents of the buffer –LIST n or L n--Lists the contents of line number n in the buffer and makes the line current –LIST * or L *--Lists the current line –LIST m n--Lists the range from m to n line –Append text or A text--Adds to the end of the current line (e.g., "A ," adds a comma to the end of line –INPUT or I--Adds one or more lines after the current line so you can begin adding the text. –CHANGE /text--Deletes text from the current line –CHANGE /oldtext/newtext--Replaces oldtext with newtext in the current line –DEL -- Deletes the current line •I would not recommend you spend time on this topic, for the best way is to directly use a text editor to edit your script then copy paste or run the script file as a whole directly.Editing Prior