11/12/2022 0 Comments Sql prompt hints![]() If it’s your job to monitor employee activity, add a call to the script that generates the emp_rpt.lst file every morning you log in to SQL*Plus: -my login.SQL (Structured Query Language) is a standardized programming language that's used to manage relational databases and perform various operations on the data in them. If you want to display SQL results page by page, the following commands could be placed at the top of your login.sql file: SET SERVEROUTPUT ON As you can see, SQL*Plus configures a default environment for you but you can modify these settings to your liking.Ī few simple modifications to this file can make a big difference. The login.sql file is executed every time you log into the utility so it is an ideal place to perform tasks that you’ll know need every session.Īt the SQL*Plus prompt, type in ‘EDIT login.sql’ and this file will open in your editor. This is done by modifying the login.sql file in your working SQL*Plus directory. Save Your PreferencesĪn effective way to increase your productivity with SQL*Plus is to save your preferences for future sessions. When spooling to HTML, you must also be sure to clear this setting with ‘SET MARKUP HTML OFF SPOOL OFF’ at the end of your script. If HTML format is more convenient, use the commands ‘SET MARKUP HTML ON SPOOL ON’ at the top of your script. This script will spool the query, formatted to specification, to the emp_rpt.lst file: Mon Apr 19 page 1ĭepartment ID Employee ID Employee Name Annual Salary SELECT dept_id, emp_id, emp_fname||' '||emp_lname emp_name, emp_salary SPOOL emp_rptĬOLUMN emp_name FORMAT A15 HEADING 'Employee Name'ĬOLUMN emp_salary FORMAT $99,999.00 HEADING 'Annual Salary' You can toggle any of these settings, on and off, with commands in the form of ‘SET ‘ command, which sends query output to a file. ![]() Typing ‘SHOW ALL’ at the SQL*Plus prompt will list these settings for you. When you launch SQL*Plus, there are a number of environment settings to choose from that will affect how the tool behaves. If you need basic instructions on how to launch SQL*Plus and connect to a database, I suggest starting with Oracle’s SQL*Plus Quick Start. You might think of the following sections as a compilation of the most frequently asked questions I’ve fielded over the years from SQL*Plus beginners. This article will provide a number of SQL*Plus tips to increase your productivity. It is not difficult to learn and, chances are, if you are going to spend any amount of time with Oracle, you will use SQL*Plus at some point. If you’ve not worked with SQL*Plus, it is a simple client application that allows you to manage virtually every facet of the database, from ad-hoc querying to complex administrative tasks. ![]() I am approached on a regular basis by developers, testers, and managers, mostly new to Oracle, with a myriad of SQL*Plus questions. However, I know not all Oracle users share my fondness for SQL*Plus. Today, when I work with Oracle, I find myself launching SQL*Plus to do my work, despite having more sophisticated, graphical tools available. I now understand that for many software development tasks, a command-line editor works just fine. I asked the instructor why Oracle didn’t ship with something “better.” He explained that SQL*Plus had been around a long time because many experienced Oracle users prefer to use it, not in spite of its simplicity, but because of it. ![]() I imagined that Oracle would provide some slick, GUI tool for working with its database and was therefore quite disappointed to find such a simple-looking editor. I had previously spent a few years doing mainframe development and knew nothing of Oracle. In this course, we coded all of our examples using SQL*Plus, Oracle’s default tool for accessing the database. Many years ago, I attended my first Oracle training class for SQL and PL/SQL programming. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |