World of Oracle DBA

Computing Summary of a column With combination of BREAK and COMPUTE command you can do several calculations on SQL reports using sql*plus. Remember the COMPUTE command has no effect without a corresponding BREAK command. Below is the lists of compute functions of Sql*plus and their effects. 1)SUM: Sum of the values in the column. 2)MINIMUM: Minimum value in the column. 3)MAXIMUM: Maximum value in the column. 4)AVG: Average of the values in the column. 5)STD: Standard deviation of the values in the column. 6)VARIANCE: Variance of the values in the column. 7)COUNT: Number of non-null values in the column. 8)NUMBER: Number of rows in the column. Let's look at our data, SQL> select dept_id,name,salary from emp; DEPT_ID NAME SALARY ---------- ------------ ---------- 10 SHAIK 60000 10 ABDUL 22000 10 KHALEEL 40000 20 IBRAHIM 35000 20 RAVI 60000 30 KASARAPU 250...

Computing Summary of a column With combination of BREAK and COMPUTE command you can do several calculations on SQL reports using sql*plus. Remember the COMPUTE command has no effect without a corresponding BREAK command. Below is the lists of compute functions of Sql*plus and their effects. 1)SUM: Sum of the values in the column. 2)MINIMUM: Minimum value in the column. 3)MAXIMUM: Maximum value in the column. 4)AVG: Average of the values in the column. 5)STD: Standard deviation of the values in the column. 6)VARIANCE: Variance of the values in the column. 7)COUNT: Number of non-null values in the column. 8)NUMBER: Number of rows in the column. Let's look at our data, SQL> select dept_id,name,salary from emp; DEPT_ID NAME SALARY ---------- ------------ ---------- 10 SHAIK 60000 10 ABDUL 22000 10 KHALEEL 40000 20 IBRAHIM 35000 20 RAVI 60000 30 KASARAPU 250...

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements. You can control the report by setting the AUTOTRACE system variable. Following is the available AUTOTRACE settings. 1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual. 2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics. 3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan. 4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output. 5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execu...

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements. You can control the report by setting the AUTOTRACE system variable. Following is the available AUTOTRACE settings. 1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual. 2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics. 3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan. 4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output. 5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execu...

Bind variables are variables that are declared inside PL/SQL or a variable in a SQL statement that must be replaced with a valid value. In the SQL*Plus you can also create bind variable. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. Creating Bind Variables In order to create bind variable in Sql*plus you have to use variable keyword. To create a bind variable named bind_var as number datatype use, variable bind_var NUMBER Displaying Bind Variables To display all bind variables in the session just use variable keyword. SQL> variable variable bind_var datatype NUMBER In order to display the value of a particular bind variable use, SQL> print bind_var BIND_VAR ---------- Using Bind Variables To use bind variable first use colon(:) and then give variable name. In order to change values of bind variable you must enter in a PL/SQL block. To change bind varia...

Sometimes you may find difficulties while displaying result on SQL*Plus like a column length displayed as a long length and thus does not result a fine output. In this post I will try to show how we can make good looking result displayed on SQL*Plus. With COLUMN a column can be formatted. For string type data format is specified by A and then length. Like A7 means total column will span to 7 word length. For number data type the format is 99999 where number of 9s decide the length of digits of number data type. Changing Column Headings To change the heading of column a,b and c use commands like below. The vertical bar is used if you want to display the columns in a new line. SQL> COLUMN A HEADING 'FIRST|COLUMN' SQL> COLUMN C HEADING 'THIRD|COLUMN' SQL> COLUMN B HEADING 'SECOND|COLUMN' FORMAT A7 SQL> select a,b,c from test1; FIRST SECOND THIRD COLUMN COLUMN COLUMN ---------- ------- ------ 1 Hi Hi2 Setting the Underline Character...

Bind variables are variables that are declared inside PL/SQL or a variable in a SQL statement that must be replaced with a valid value. In the SQL*Plus you can also create bind variable. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. Creating Bind Variables In order to create bind variable in Sql*plus you have to use variable keyword. To create a bind variable named bind_var as number datatype use, variable bind_var NUMBER Displaying Bind Variables To display all bind variables in the session just use variable keyword. SQL> variable variable bind_var datatype NUMBER In order to display the value of a particular bind variable use, SQL> print bind_var BIND_VAR ---------- Using Bind Variables To use bind variable first use colon(:) and then give variable name. In order to change values of bind variable you must enter in a PL/SQL block. To change bind varia...

The Sql Buffer The Sql buffer stores most recently SQL command or most recently PL/SQL block. SQL*Plus does not store SQL*Plus commands, or the semicolon or slash characters you type to execute a command in the SQL buffer. You can execute the command or block in the SQL buffer using the RUN or /(slash) commands. RUN commands displays the command or block in the buffer and then execute it. /(slash) executes the command or block in the buffer but does not display it. Below is the example. A simple SQL command. SQL> select sysdate from dual; SYSDATE --------- 29-NOV-08 This one (Setting linesize) is SQL*plus command and hence is not stored in SQL buffer. SQL> set linesize 100 Invoking list will show SQL buffer, not SQL*plus command. SQL> list 1* select sysdate from dual Run will execute the command after displaying it. SQL> run 1* select sysdate from dual SYSDATE --------- 29-NOV-08 /(Slash) will not display the command. It will only execute it. SQL> / SYSDATE --------- 2...

Running Sql Scripts To run a sql scripts in Sql*plus use @ or @@ or start command. To run a script named C:\test.sql use, @c:\test or, @@c:\test or, start c:\test In addition, CLEAR BUFFER (CL BUFF): Will clear buffer, i.e delete all lines from the Sql buffer. To insert a line before line 1, enter a zero ("0") and follow the zero with text. 0 SELECT Username Placing comments in Sql Scripts 1)Using Sql*plus REMARK command: If a line starts with REMARK rest words of the line are considered as comment and hence ignored to compile. This is applied to single line only. Example: REMARK This script will generate the salary history REM This script is generated on 11/01/2008 2)Using /*...*/: In order to comment multiple lines start with /* and until it gets */ entire commands inside it are considered as comments. With /*...*/ you can also comment a single line. You must enter a space after the slash-asterisk(/*). Example: /* Column Salary for 9999 Column Name for a30 */ 3)Using --: ...