SQL+ ANSWER QUESTONS

  • What commands can be executed from SQL*Plus? Answer: You can enter three kinds of commands from the SQL*Plus command prompt:
    • SQL*Plus commands
    • SQL commands
    • PL/SQL blocks
  • What is the basic SQL*Plus commands? Answer:
    DEFINE Declare a variable (short: DEF)
    DESCRIBE Lists the attributes of tables and other objects (short: DESC)
    EDIT Places you in an editor so you can edit a SQL command (short: ED)
    EXIT or QUIT Disconnect from the database and terminate SQL*Plus
    GET Retrieves a SQL file and places it into the SQL buffer
    HOST Issue a operating system command (short: !)
    LIST Displays the last command executed/ command in the SQL buffer (short: L)
    PROMPT Display a text string on the screen.Eg prompt Hello World!!!
    RUN List and Run the command stored in the SQL buffer (short: /)
    SAVE Saves command in the SQL buffer to a file.Eg "save x" will create a script file called x.sql
    SET Modify the SQL*Plus environment eg.SET PAGESIZE 23
    SHOW Show environment settings (short: SHO).Eg SHOW ALL, SHO PAGESIZE etc.
    SPOOL Send output to a file.Eg "spool x" will save STDOUT to a file called x.lst
    START Run a SQL script file (short: @)
  • What is AFIEDT.BUF? Answer: AFIEDT.BUF stands for A FIle EDiting BUFfer.
    AFIEDT.BUF is the SQL*Plus default edit save file.When you issue the command "ed" or "edit" without arguments, the last SQL or PL/SQL command will be saved to a file called AFIEDT.BUF and opened in the default editor.
  • What is the difference between @ and @@? Answer: The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.
    A single @ symbol runs the script relative to the current directory, the double at (@@) runs a command file relative to the directory of the current script
  • What is the difference between & and &&? Answer: "&" is used to create a temporary substitution variable and will prompt you for a value every time it is referenced.
    "&&" is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement.
  • What is the difference between ! and HOST? Answer: Both "!" and "HOST" will execute operating system commands as child processes of SQL*Plus.
    The difference is that "HOST" will perform variable substitution (& and && symbols), whereas "!" will not.
  • How can I trap errors in SQL*Plus? Answer: Use the "WHENEVER OSERROR..." to trap operating system errors and the "WHENEVER SQLERROR..." command to trap SQL and PL/SQL errors.
    Eg:
    SQL> WENEVER OSERROR EXIT 9
    SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
  • Can I prevent users from executing devious commands? Answer: Yes, command authorization is verified against the SYSTEM.PRODUCT_USER_PROFILE table.
    This table is created by the V7PUP.SQL and PUPBLD.SQL scripts.
    Note that this table is not used when someone signs on as user SYSTEM.
    Eg.to disable all users whose names starts with OPS$ from executing the CONNECT command:
    SQL> INSERT INTO SYSTEM.PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'OPS$%', 'CONNECT', NULL, NULL, 'DISABLED', NULL, NULL);
  • How can I disable SQL*Plus formatting? Answer: SET ECHO OFF
    SET NEWPAGE 0
    SET SPACE 0
    SET PAGESIZE 0
    SET FEEDBACK OFF
    SET HEADING OFF



  • 0 comments:

    Feel free to contact the admin for any suggestions and help.