Sql Plus How_to's
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Sql Plus How_to's as PDF for free.
More details
- Words: 10,468
- Pages: 52
How to Rollback an Uncommitted DML in SQL*Plus Goal In SQL*Plus, by default the DML (data manipulation language) transaction is committed to the database upon exit. Is there a way to rollback the transaction upon exit or prior to exiting the SQL*Plus session if the commit has not been explicitly defined? Solution From a SQL*Plus standpoint, there are 1 of 2 options: OPTION 1: 1. Use the following command prior to issuing any DML: SQL> set transaction use rollback segment test1; ... where test1 = a name you wish to rollback to should you want to reverse the changes back to this point 2. Before exiting, issue the rollback command: SQL> rollback; OPTION 2: 1. In the glogin.sql located in the $ORACLE_HOME/sqlplus/admin directory, add the below: Set transaction use rollback segment &rollback_segment; 2. When entering SQL*Plus, you will be prompted for a name -Enter value for rollback_segment: test1 NOTE: If you do not want to be prompted, then set a static name in place of the &rollback_segment in step 1, like: set termout off set transaction use rollback segment test1; set termout on 3. Then, if you wish to reverse the DML that has not been committed, then before exiting issue the rollback command: SQL> rollback;
How to Delete Line Feeds and Carriage Returns from Varchar2 Data? Goal How to delete carriage returns and line feeds contained within the varchar2 field data? Solution You can perform the following nested replace sql statement using the replace command to delete the linefeeds and the carriage returns. SELECT REPLACE (REPLACE (column name, CHR (13), ‘’), CHR (10), '') FROM tablename;
How to Setup Date Format or Specific Variables in iSQL*Plus 9i? Goal How to set NLS_DATE_FORMAT in iSQL* plus 9.2? Solution A- For a session you can user command: alter session set NLS_DATE_FORMAT... B- To set it permanently: 1- Modify the $ORACLE_HOME/sqlplus/admin/isqplus.conf file to add -initial-env for NLS_LANG and NLS_DATE_FORMAT as follow: FastCgiServer /emea/rdbms/32bit/app/oracle/product/9.2.0/bin/isqlplus -initial-env SHLIB_PATH -initial-env LD_LIBRARY_PATH -initial-env ORACLE_HOME -initial-env ORACLE_SID -initial-env TNS_ADMIN -initial-env NLS_LANG -initial-env NLS_NCHAR -initial-env NLS_LANG -initial-env NLS_DATE_FORMAT -initial-env iSQLPlusNumberOfThreads=20 -initial-env iSQLPlusTimeOutInterval=30 -initial-env iSQLPlusLogLevel=off -initial-env iSQLPlusAllowUserEntMap=none -idle-timeout 3600 2- Set ENV variable at UNIX level NLS_LANG and NLS_DATE_FORMAT. 3- Restart apache. 4- Start sqlplus and the Date format will match NLS_DATE_FORMAT value.
What Is the Command that Replaces ''Spool'' in iSQL*Plus? Goal Spool command is no longer a valid command in iSQL*Plus. What is the new command to replace it? Solution iSQL*Plus works in a different way to generate the output to file. The file output is html instead of text. Review iSQL*Plus -> Help -> The iSQL*Plus User Interface -> Output. It states: File: when the contents of the input area are executed, the resulting output is saved to a file. You are
prompted to enter the name of the file. As the output is in HTML format, it is useful to give the saved output file a .htm or .html extension. Steps to generate the output to file: 1) Invoke iSQL*Plus: http://host:port/isqlplus 2) In the User Interface, select output=file. 2) Select 'Execute' button. 3) Save the file. This is an html file. 4) Open the html file using your browser.
Commands Not Supported in iSQL*Plus Goal This article lists SQL*Plus commands not supported in the iSQL*Plus user interface. Attempting to use any of the following unsupported commands or command options raises an SP2-0850 error message. Solution The following commands have no context in iSQL*Plus and have not been implemented. ACCEPT
PASSWORD
CLEAR SCREEN
PAUSE
The following SET command variables have no context in iSQL*Plus and have not been implemented. SET EDITFILE
SET SQLBLANKLINES
SET TAB
SET FLUSH
SET SQLCONTINUE
SET TERMOUT
SET NEWPAGE
SET SQLNUMBER
SET TIME
SET PAUSE
SET SQLPREFIX
SET TRIMOUT
SET SHIFTINOUT
SET SQLPROMPT
SET TRIMSPOOL
SET SHOWMODE
SET SUFFIX
The following commands have security issues on the middle tier and have not been implemented. GET
SPOOL
HOST
STORE
The following commands are SQL buffer editing commands which are not relevant in iSQL*Plus and have not been implemented. APPEND
DEL
INPUT
CHANGE
EDIT
SAVE
List/Table of General SQL*Plus 9.2 Limits Goal The purpose of this article is to provide a list of general SQL*Plus limits. The limits shown are valid for most operating systems. Solution Item
Limit
filename length
system dependent
username length
30 bytes
user variable name length
30 bytes
user variable value length
240 characters
command-line length
2500 characters
length of a LONG value entered through SQL*Plus LINESIZE value LINESIZE
system dependent
LONGCHUNKSIZE value
system dependent
output line size
system dependent
line size after variable substitution
3,000 characters (internal only)
number of characters in a COMPUTE command label
500 characters
number of lines per SQL command
500 (assuming 80 characters per line
maximum PAGESIZE
50,000 lines
total row width
60,000 characters for VMS; otherwise, 32,767 characters
maximum ARRAYSIZE
5000 rows
maximum number of nested scripts
20 for VMS, CMS, Unix; otherwise, 5
maximum page number
99,999
maximum PL/SQL error message size
2K
maximum ACCEPT character string length
240 Bytes
maximum number of DEFINE variables
2048
How to Get US Eastern Time No Matter of the Time Zone Location? Goal The goal of this document is to provide one way of getting US Eastern Time No Matter of the Time Zone Location. Solution
If Oracle 9i or later is used then use the following sql statement: SELECT CURRENT_TIMESTAMP AT TIME ZONE '-5:00' AS eastern_time FROM dual; That gives you a TIMESTAMP. If a regular DATE is needed then CAST it as shown below: SELECT CAST (CURRENT_TIMESTAMP AT TIME ZONE '-5:00' AS DATE) AS eastern_time FROM dual;
How to Retrieve the Select Statement Used to Create a View? Problem Description How can you see the SELECT statement used to create a view? When you try selecting the TEXT field from USER_VIEWS, you are not able to see the entire SELECT statement. Problem Solution In SQL*Plus, you must first execute SET LONG n, where n is an integer value greater than or equal to the value of the TEXT_LENGTH field in USER_VIEWS. For example: SQL> set long 999 SQL> select text from user_views where view_name like 'MYVIEW'; You should now be able to see a complete SELECT statement used to create MYVIEW.
How to Restrict User Access to Database Instances in iSQL*Plus? Goal The article is intended to provide information about how to restrict user database instance access from iSQL*Plus. You may want to limit the databases instances that users can access in iSQL*Plus to a restricted list. When restricted database access has been enabled, a dropdown list of available databases is displayed in place of the Connection Identifier text field on the Login screen. This enables greater security for iSQL*Plus Servers in hosted environments. Solution Connection identifiers are listed in the order defined in iSQLPlusConnectIdList. Edit the $ORACLE_HOME/oc4j/j2ee/oc4j-applications/applications/isqlplus/isqlplus/WEB-INF/web.xml
file to restrict database access to iSQL*Plus users. Change the following entry to include a new paramvalue element which contains the list of databases to which you want to restrict access. For example, to only have ora10g and ora9i instance available: NOTE: A period is added to the text for readability. Do not include in the web.xml . .<param-name>iSQLPlusConnectIdList .<param-value>ora10g;ora9i .<description>The database(s) to which iSQL*Plus users are restricted. The list should contain the Oracle SIDs or SERVICE_NAMEs, separated by a semicolon (;). If there are no entries, database access is not restricted through iSQL*Plus. . Entries in the param-value element should be identical to the alias for SERVICE_NAMEs or SIDs set in your $ORACLE_HOME/network/admin/tnsnames.ora file. Connection identifiers are listed in the order defined in iSQLPlusConnectIdList. For Oracle 9i release 2 make the following changes in $ORACLE_HOME/sqlplus/admin/isqlplus.conf file Change the following line FastCgiServer \bin\isqlplus -port 8228 -initial-env iSQLPlusNumberOfThreads=20 -initial-env iSQLPlusTimeOutInterval=2 -initial-env iSQLPlusLogLevel=off -initial-env iSQLPlusAllowUserEntMap=none -idle-timeout 3600 To FastCgiServer \bin\isqlplus -port 8228 -initial-env iSQLPlusNumberOfThreads=20 -initial-env iSQLPlusTimeOutInterval=2 -initial-env iSQLPlusLogLevel=off -initial-env iSQLPlusAllowUserEntMap=none -initial-env iSQLPlusConnectIdList=Ora9iDB,Ora10gDB -idletimeout 3600 Restart iSQL*Plus for your changes to take effect. Connection identifiers are case insensitive, and each connection identifier listed in the argument should be identical to an alias in the tnsnames.ora file. If no connection identifier is given, or if the one given does not match an entry in iSQLPlusConnectIdList, the database connection is refused and the following error occurs: SP2-0884: Connection to database database_name is not allowed
How to Output Two Records from a Single Record Depending on the Column Value? Goal
One of the column in a table has three valid values e.g. column value (name: cell) is 1, 2 and 3. The value 3 indicates that the record is both 1 and 2. When you select the records along with other values, you will get all the three values (1, 2 and 3) in select statement. Now in the output value = 1 or 2 the single is ok. But for value = 3, you want to create two records that display values 1 and 2 (value for cell). Actual output with standard select is as follows: ID CELL ----- -------721 1 722 2 723 3 and the required output is as follows : ID CELL ----- -------721 1 722 2 723 1 723 2 How to split and resolve this in SQL? Fix The following SQL statement to achieves the desired output: select id, decode(cell, 3, 1, cell) cell from union all select id, 2 from where cell = 3
How to Exit with a Rollback from SQL*Plus Goal How to exit sqlplus with a rollback? Fix To prevent SQL*Plus from automatically committing all changes, such as INSERT, UPDATE, or DELETE command, you can use the 'ROLLBACK' or 'EXIT ROLLBACK' command before exiting. The SQL*Plus User Guide and Reference states the following: EXIT Purpose Terminates SQL*Plus and returns control to the operating system.
Syntax {EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK] By default the exit is with commit. To rollback you must do: exit rollback
How to Generate Numbers 0 to N from a Query without PL/SQL? Goal How to generate numbers 0 to N from a query without any PL/SQL Fix select rownum-1 from ( select 1 from dual group by cube (1,1,1,1,1,1)) where rownum<&maxlimit+2 The logic behind this query is that the CUBE is generating 2^n rows where n is the number of arguments to the CUBE function. In this case the inner subquery generates 2^6 rows i.e. 64 rows which means you can generate numbers up to 63. In case you want to generate numbers beyond this range, then you need to add one more argument to the CUBE which would increase it to 2^7 rows i.e. 128 rows. select rownum-1 from ( select 1 from dual group by cube (1,1,1,1,1,1,1)) where rownum<&maxlimit+2 The range can be increased by increasing the number of arguments to the CUBE function.
How to Hide the Password Running a Script as a Different User? Goal How to hide a password to execute a script? For example, connected as user2 you need to execute a script querying objects from Scott's schema like select * from dept. Fix 1. Create hide_pswd.sql script using hide option. It will display the prompt "Password: ", place the reply in a char variable named hide_password, and suppress the display: accept hide_password char prompt 'Password: ' hide connect scott/&&hide_password@aliasdb / select * from dept
/ 2. This will prompt for user password and will show it in hide mode (* instead of password): SQL> start hide_pswd.sql SQL> accept hide_password char prompt 'Enter password: ' hide Enter password: ***** SQL> connect scott/&&hide_password@aliasdb Connected. SQL> / DEPTNO DNAME LOC ---------- -------------- ------------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
How to Generate A Delimiter in Spool Output (Excel CSV file)? Goal How to generate a delimiter in spool output (Excel .csv files)? Fix In order to generate a delimited file output, you need to concatenate columns using the desired delimiter i.e. comma. Example: select empno|| ','||ename||'&'||mgr from X; Other option is using: SQL> set colsep ',' SQL> spool c:\testexcel.csv SQL> select * from emp; Change some of the default SQL*Plus parameters, that will be garbage for Excel: feedback=off newpage=none termout=off header=off If some columns are empty, be aware to include the delimiter, too: nvl(to_char(col2),',')
How to Select N-th Highest Value from a Table? Goal This document provides the SQL statement which can be used to find the N-th highest or lowest value from a table column of NUMBER datatype. Also, provides the N-th highest or lowest DISTINCT value of a table column. For example: - select the 5th highest salary record from the EMP table. - select the 5th highest salary DISTINCT value from the EMP table. - select the 5th lowest salary record from the EMP table. - select the 5th lowest salary DISTINCT value from the EMP table. Fix Given the following data: SQL>SELECT SAL FROM EMP ORDER BY SAL DESC; SAL ---------5000 3000 3000 2975 2850 <--- Top 5th RECORD 2450 <--- Top 5th DISTINCT VALUE 1600 1500 1300 <--- Bottom 5th DISTINCT VALUE 1250 <--- Bottom 5th RECORD 1250 1100 950 800 1. The general syntax to retrieve the TOP N-th record is : 1.A. Select MIN(column_name) from (select column_name from table_name order by column_name desc) where rownum<= N; For example, the following can be used to find the 5th highest record from the EMP table: SELECT MIN(sal) from (select sal from emp order by sal desc) where rownum <= 5; MIN(SAL)
---------2850 1. B. A method without using MIN () to find the top 5-th record is: SELECT distinct (temp.sal) FROM EMP temp WHERE 5= (SELECT COUNT (tmp.sal) FROM EMP tmp WHERE temp.sal<=tmp.sal); SAL ---------2850 2. The general syntax to retrieve the TOP N-th DISTINCT VALUE is : 2.A. Select MIN(column_name) from (select distinct column_name from table_name order by column_name desc) where rownum<= N; For example, the following can be used to find the 5th highest distinct salary from the EMP table: Select MIN(sal) from (select distinct sal from emp order by sal desc) where rownum <= 5; MIN(SAL) ---------2450 2. B. A method without using MIN () to find the top N-th DISTINCT VALUE is: SELECT DISTINCT (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT (DISTINCT (tmp.sal)) FROM EMP tmp WHERE temp.sal<=tmp.sal); SAL ---------2450 3. The general syntax to retrieve the N-th RECORD from the BOTTOM is as follows: 3.A. Select MAX(column_name) from (select column_name from table_name order by column_name ) where rownum<= N; For example, the following can be used to find the 5th lowest RECORD from the EMP table: SELECT MAX(sal) from (select sal from emp order by sal) where rownum <= 5; MAX(SAL) ---------1250 3.B. A method without using MAX () to find the bottom N-th RECORD is: SELECT distinct (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT (tmp.sal) FROM EMP tmp WHERE temp.sal>=tmp.sal); SAL ---------1250 4. The general syntax to retrieve the N-th DISTINCT VALUE from the BOTTOM is as follows: 4.A. Select MAX(column_name) from (select distinct column_name from table_name order by column_name ) where rownum<= N; For example, the following can be used to find the 5th lowest DISTINCT value from the EMP table: Select MAX(sal) from (select distinct sal from emp order by sal) where rownum <= 5; MAX(SAL) ---------1300 4.B. A method without using MAX() to find the lowest N-th DISTINCT VALUE is:
SELECT DISTINCT (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT (DISTINCT (tmp.sal)) FROM EMP tmp WHERE temp.sal>=tmp.sal); SAL ---------1300 HOW TO MAKE THE SELECT STATEMENTS ABOVE MORE DYNAMIC? In examples 1.A., 2.A., 3.A., and 4.A., use the following modification to the query: From … where rownum <= 5; To … where rownum <= &N; In examples 1.B., 2.B., 3.B., and 4.B., use the following modification to the query: From … WHERE 5 = (SELECT COUNT… To …WHERE &N = (SELECT COUNT… This change will cause the following prompt for the user: Enter value for n: <user puts in value>
How to Stop 'Connected' Message when Running SQLPlus in Silent Mode? Goal When you start sqlplus in silent mode, you still get the connected message. Example: sqlplus -S /nolog conn / Connected. How to stop the "Connected." from appearing? Fix 1. Do not use the "/nolog" option. This is causing 2 connections. It is the second connection that is echoed. Instead start sqlplus as: sqlplus -s / 2. If you need the /nolog, then use grep to exclude the Connected line as follows: sqlplus -S /nolog | grep -v Connected
How to use New Predefined Variables to Set SQLPROMPT to Display the User, Date and/or Privileges? Goal To explain the new predefined variables introduced in 10g and how they can be used to make the SQL*PLUS Prompt display more informative. These variables are available only in 10g. Fix You can now use substitution variables in the SQL*Plus command-line prompt to display, for example, the database and server you are connected to, or other information available through a substitution variable you choose. There are four new predefined variables: Predefined Variables: _DATE, _PRIVILEGE, _USER, _CONNECT_IDENTIFIER _DATE contains the current date or a user defined fixed string. _PRIVILEGE contains the privilege level of the current connect. This will be either AS SYSDBA, AS SYSOPER or blank to indicate a normal connection. _USER contains the username as supplied by the user to make the current connection. This is the same as the output from the SHOW USER command. _CONNECT_IDENTIFIER contains the connection identifier information. These variables can be accessed like any other substitution variable. For example, they could be used in TTITLE, in '&' substitution variables, They can used in your SQL*Plus command line prompt by using the SET SQLPROMPT command. For example, to make your prompt always show your username (_USER), the @ symbol, and then your connection identifier (_CONNECT_IDENTIFIER) during your session, enter:
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " You can view the predefined variable definitions in the same way as you view other DEFINE definitions, using the DEFINE command with no arguments, or with the specific argument you wish to display, for example: Using DEFINE to display these variables: SQL> DEFINE DEFINE _DATE = "16-MAR-04" (CHAR) DEFINE _CONNECT_IDENTIFIER = "isc101" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
----or to display on one variable: SQL> DEFINE _PRIVILEGE DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) Uses UNDEFINE to remove variable definitions. How to use these variables with SQLPROMPT? SET SQLP [ROMPT] {SQL> | text} Note: SET SQLPROMPT is not supported in iSQL*Plus Variable substitution occurs each time SQLPROMPT is SET. If SQLPROMPT is included in glogin.sql, then substitution variables in SQLPROMPT are refreshed with each login or connect. You need the 'Select Any Table' privilege to successfully run the following example scripts. 1. To change your SQL*Plus prompt to display your connection identifier, enter: SQL> SET SQLPROMPT "_CONNECT_IDENTIFIER > " isc101 > 2. To set the SQL*Plus command prompt to show the current user, enter SQL> SET SQLPROMPT "_USER > " SCOTT > 3. To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter: SQL> SET SQLPROMPT "_DATE _USER _PRIVILEGE> " 17-MAR-04 SCOTT > 4. To change your SQL*Plus prompt to display a variable you have defined, enter: SQL> DEFINE Instance = Prod SQL> SET SQLPROMPT Instance> " Prod> 5. Since text in nested quotes is not parsed for substitution, to have a SQL*Plus prompt of your username, followed by "@", and then your connection identifier, enter: SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " SCOTT@isc101 > At present there is no predefined variable to display the database you are connected to.You can use the notes referenced below for the same.
How To Interpret The Time Format • • • •
goal: How To Interpret The Time Format fact: Oracle Server - Enterprise Edition 7 fact: Oracle Server - Enterprise Edition 8 fact: MS Windows NT
•
fact: SqlPlus
Fix: SQL> set timing on After the SQL statement is executed the time needed for processing the statement is printed into the next line. Interpretation: Elapsed: 00:00:00.32 ^ ^ ^ ^ | | | | | | | milliseconds | | seconds | minutes hours SPECIAL NOTE: The seconds field will represents the TOTAL TIME in seconds. Abstract: ELAPSED TIME SHOWS INCORRECTLY FOR DBMS_LOCK.SLEEP VALUES GREATER THAN 1 MINUTE The seconds overflow (60 seconds = 1 minute) will not reset the seconds counter so it's possible that the seconds field gets more than 2 digits (>60)! Example:
Elapsed: 01:05:3909.32
To find the actual seconds, the Hours and Minutes must be removed from the total. Above example: hours: 1 minutes: 5 seconds: 3909.35 - (1*3600) - (5*60) = 9.32 Converts to: hours: 1 minutes: 5 seconds: 9 milliseconds: 32
How to Display a Master Detail Relationship in One Row? PURPOSE How to display columns as rows? This is similar to other line-column transposition that may be in use. But here we use outer join to achieve this. SCOPE & APPLICATION
Developers and DBAs (For reporting) SQL> create table parent (ChildNo varchar2(5), parent varchar2(20)); insert insert insert insert insert insert
into into into into into into
parent parent parent parent parent parent
values values values values values values
('E0001', ('E0001', ('E0002', ('E0002', ('E0003', ('E0004',
'FATHER-JOHN'); 'MOTHER-NANCY'); 'FATHER-BLAKE'); 'MOTHER-SALLY'); 'FATHER-WILLIAM'); 'MOTHER-JULIE');
SQL> select * from parent; CHILD ----E0001 E0001 E0002 E0002 E0003 E0004
PARENT -------------------FATHER-JOHN MOTHER-NANCY FATHER-BLAKE MOTHER-SALLY FATHER-WILLIAM MOTHER-JULIE
SQL> select distinct c.childno, a.father, b.mother from 2 (select childno, parent father from parent where parent like 'FATHER%') a, 3 (select childno, parent mother from parent where parent like 'MOTHER%') b, 4 parent c 5 where c.childno=a.childno(+) 6* and c.childno=b.childno(+) CHILD ----E0001 E0002 E0003 E0004
FATHER -------------------FATHER-JOHN FATHER-BLAKE FATHER-WILLIAM
MOTHER -------------------MOTHER-NANCY MOTHER-SALLY MOTHER-JULIE
How To Calculate The Number of Days betweeen Two Dates Excluding Weekends? • •
Goal: How to calculate the number of days betweeen two dates excluding weekends fact: Embedded SQL
Fix: A table called DATETABLE has two columns BEGDATE and ENDDATE:-
rem Calculates the difference between two dates and gives you a number excluding weekends. rem This part calculates the difference in whole weeks and then subtracts out the weekends. rem rem (trunc(enddate,'D') - trunc(begdate+6,'D')) rem - (((trunc(enddate,'D') - trunc(begdate+6,'D'))/7)*2) rem rem This part calculates the number of work days in the end week. rem rem + decode(to_number(to_char(enddate,'D')),3,1,4,2,5,3,6,4,7,5,0) rem rem This part calculates the number of work days in the start week rem minus Sunday start day workaround. rem rem + decode(to_number(to_char(begdate,'D')),2,5,3,4,4,3,5,2,6,1,0) rem days eg. select (trunc(enddate,'D') - trunc(begdate+6,'D')) - (((trunc(enddate,'D') - trunc(begdate+6,'D'))/7)*2) + decode(to_number(to_char(enddate,'D')),3,1,4,2,5,3,6,4,7,5,0) + decode(to_number(to_char(begdate,'D')),2,5,3,4,4,3,5,2,6,1,0) days from datetable /
Setting the SQL*Plus Prompt to Show Instance Name (SID) How to set the SQL*Plus prompt to show your SID. set termout off col x new_value y select rtrim(instance,chr(0)) x from v$thread; set sqlprompt '&y SQL> ' set termout on Remember this is NOT dynamic. You need to reset it when you change instances, i.e. when you use the connect command to logon to another instance. To successfully run the above script, you need the Select Any Table privilege. NOTE: from version 9.2 this syntax can be used to SET SQLPROMPT "&_CONNECT_IDENTIFIER>"
How to Insert a Carriage Return in the Middle of a Select Statement
Goal Trying to do a long select that will ultimately result in 3 lines of output separated by carriage returns. For Example: Select 'D',val1, val2, '^M', 'F',val1, val3,'^M','A',val1,val4,'^M' from value_table. Pipes will be used instead of comma delimiters and the output will need to be looked like this: D|val1|val2|valx| F|val1|val3|valx| A|val1|val4|valx| Fix Basically, carriage return --> CHR(10) but has to be used with the concatenate symbol '||'. TESTCASE 3 below is probably the closest to what you are looking for: For Example: SQL> ed Wrote file afiedt.buf 1 select empno||','||ename||','||chr(10)||job 2* from emp SQL> / TESTCASE DETAILS: TESTCASE 1: SQL> ed Wrote file afiedt.buf 1 select empno||chr(10)||ename||chr(10) 2* from emp SQL> / EMPNO||CHR(10)||ENAME||CHR(10)||JOB -----------------------------------------7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN EMPNO||CHR(10)||ENAME||CHR(10)||JOB ------------------------------------------
7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE EMPNO||CHR(10)||ENAME||CHR(10)||JOB -----------------------------------------MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST ... etc TESTCASE 2: ========== SQL> ed Wrote file a 1 select empno||','||ename||','||job||chr(10) 2* from emp SQL> / EMPNO||','||ENAME||','||JOB||CHR(10) -------------------------------------------------------7369,SMITH,CLERK 7499,ALLEN,SALESMAN 7521,WARD,SALESMAN 7566,JONES,MANAGER 7654,MARTIN,SALESMAN 7698,BLAKE,MANAGER 7782,CLARK,MANAGER 7788,SCOTT,ANALYST 7839,KING,PRESIDENT 7844,TURNER,SALESMAN 7876,ADAMS,CLERK EMPNO||','||ENAME||','||JOB||CHR(10) -------------------------------------------------------7900,JAMES,CLERK
7902,FORD,ANALYST 7934,MILLER,CLERK 14 rows selected. TESTCASE 3: Closest to the format -- D|val1|val2|valx| -- would be something like this: SQL> ed Wrote file afiedt.buf 1 select empno||','||ename||','||chr(10)||job 2* from emp SQL> / EMPNO||','||ENAME||','||CHR(10)||JOB ---------------------------------------------------------7369,SMITH, CLERK 7499,ALLEN, SALESMAN 7521,WARD, SALESMAN 7566,JONES, MANAGER ... etc
How to show the values of a column in a row for both previous/current record Goal How To show the values of a column in a row for both previous/current record ? Suppose a customer wants to show the 1) Profit of the company in the current quarter and Last quarter in same row. . 2) Profit of the company in the current quarter and Next quarter in same row. . Fix Let us assume customer has the following table structure. SQL> desc QR_Profit Name Null? Type ----------------------------------------- -------- -------------------
YRQR DATE PROFIT NUMBER SQL> select * from QR_Profit; YRQR PROFIT --------- ---------31-DEC-02 10 31-MAR-03 11 30-JUN-03 12 30-SEP-03 12 Following query will show the " profit for current quarter and Last quarter in same row " SQL> select yrqr , profit,lag(profit,1,0) over (order by yrqr) Last_Quarter from QR_Profit; ; YRQR PROFIT LAST_QUARTER --------- ---------- -----------31-DEC-02 10 0 31-MAR-03 11 10 30-JUN-03 12 11 30-SEP-03 12 12 Following query will show "Profit in the current quarter and Next quarter in same row. ". SQL> select yrqr, profit,lead(profit,1,0) over (order by yrqr) Next_Quarter from QR_Profit; YRQR PROFIT NEXT_QUARTER --------- ---------- -----------31-DEC-02 10 11 31-MAR-03 11 12 30-JUN-03 12 12 30-SEP-03 12 0
How to Create a Matrix Report in SQLPLUS Goal When you query any table in relational database, the datas are represented as row format. That means each record in the table is represented in row. But to analyze the data the above format would not be enough , there you need data in 3-dimensional format i.e. to represent in rectangular format ( popularly know as matrix format ) Where you will have a row attribute, column attribute and cell which shows the values for the corresponding row and column attribute .
Eg. Col1 Col2 Col3 Col4 Row1 Row2 Row3 Row4 How to achieve the same in sqlplus without using any other tools? Note :- The same output format can be easily designed thru Oracle Reports writer . Fix Here I am showing an example shows the no.of people joined in a department on year basis. The query is based on Scott.EMP table. SQL> select empno,deptno,hiredate from emp; EMPNO DEPTNO HIREDATE ---------- ---------- --------7369 20 17-DEC-80 7499 30 20-FEB-81 7521 30 22-FEB-81 7566 20 02-APR-81 7654 30 28-SEP-81 7698 30 01-MAY-81 7782 10 09-JUN-81 7788 20 19-APR-87 7839 10 17-NOV-81 7844 30 08-SEP-81 7876 20 23-MAY-87 EMPNO DEPTNO HIREDATE ---------- ---------- --------7900 30 03-DEC-81 7902 20 03-DEC-81 7934 10 23-JAN-82 14 rows selected. Now the same query represented in matrix format . select deptno, sum(decode(to_char(hiredate,'RRRR'),'1980',1,0)) sum(decode(to_char(hiredate,'RRRR'),'1981',1,0)) sum(decode(to_char(hiredate,'RRRR'),'1982',1,0)) sum(decode(to_char(hiredate,'RRRR'),'1987',1,0)) from emp group by deptno
"1980", "1981", "1982", "1987"
DEPTNO 1980 1981 1982 1987 ---------- --------- ---------- ---------- ---------10 0 2 1 0 20 1 2 0 2 30 0 6 0 0 Which shows the no. of employee joined to departments in the above years. Note: - The only drawback here is the column can not be added automatically. That means for each column and corresponding cell values to display, then the column need to be defined in query.
How to Check a Particular Format for Any Field thru SQL Goal How to validate the data saved in a column ? Let us assume a table CUSTOMER with lot of records saved. We want to check the column CUSTID with a particular format like 99 .So that we can update all records of the table.column in the same format. Fix Let us take an example for table Customer SQL> desc customer Name Null? Type ----------------------------------------- -------CUSTID VARCHAR2(10) SQL> select * from customer; CUSTID ---------IN-CTC-001 IN-BNG-001 IN-BNG-12 IN-MUM-01 We need to check if any of these records keeps the format 'XX-XXX-999'' .Write the query to get the result SQL> SELECT custid FROM customer where TRANSLATE(CUSTID, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX-') ='XX-XXX-999'; CUSTID ---------IN-CTC-001 IN-BNG-001
View the Source Code Of A Procedure Goal Is it possible to provide a Grant such that User can view only the source code of Procedure but not execute it. Fix A Grant option would allow the user to execute the Procedure. To allow the User to just view the source code of the Procedure, try the following: 1) Create a view like: create view view_name as select text from all_source where type='PROCEDURE' and name='PROCEDURE-NAME'; 2) Grant select on the View created above. This way, the user can do a select of the View, and see the source code of the Procedure.
How to Find or Delete Duplicate Rows in a Table PROBLEM DESCRIPTION: How do you find or delete duplicate rows in a table? SOLUTION DESCRIPTION: The following SELECT statement will find and display all duplicate rows in a table, except the row with the maximum ROWID. The example uses the dept table: SELECT * FROM dept a WHERE ROWID <>(SELECT MAX(ROWID) FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc); The following statement will delete all duplicate rows in a table, except the row with the maximum ROWID: DELETE FROM dept a WHERE ROWID <> (SELECT MAX (ROWID) FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc);
Alternatively: DELETE FROM dept a WHERE 1 < (SELECT COUNT (deptno) FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc); EXPLANATION Using the pseudo column ROWID is the fastest way to access a row. ROWID represents a unique storage identification number for a single row in a table (Note: Two rows on different tables but stored in the same cluster may have the same rowid value). Duplicate rows which contain only null columns, however, will not be identified by either of the two methods above. In such a case, simply: DELETE FROM dept WHERE deptno IS NULL AND dname IS NULL AND loc IS NULL;
How do I create a flat ASCII file without rows wrapping? PROBLEM DESCRIPTION: You want to create a flat file with the output from a SQL query. When you execute the query, the rows are wrapped around. How can you prevent this wrap-around? SOLUTION DESCRIPTION: To store the SQL query results in a file, you need to enter the SPOOL command in SQL*Plus: SQL> SPOOL file_name If you do not follow the filename with a period and an extension, SPOOL adds a default file extension to the filename to identify it as an output file. The default varies with the host operating system. On most hosts it is LST or LIS. To create a flat file in SQL*Plus, you first must enter the following: SET commands in SQL*Plus: SET SET SET SET
NEWPAGE O SPACE 0 LINESIZE 80 PAGESIZE 0
SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF After entering these commands, use the SPOOL command as above to create the flat file. (If the line size is too small, you may set it to a larger number. If you do this, you may need to use another command 'SET BUFFER xxxx' where xxxx is the same size as the line size).
How do I create an output file with commas between the columns? You will need to produce a .SQL file for the table so that the table output will have commas between the columns.
How do I calculate an average date with time component? Problem Explanation: You want to calculate the difference between two average timestamps. Solution Description: Use a format mask without characters. If a table (x) has a date field (a) with two rows 01-jan-96 01:10 01-jan-96 01:20 SQL> select avg( to_number( to_char(a,'MMDDYYHHMI') ) ) from x; AVG(TO_NUMBER(TO_CHAR(A,'MMDDYYHHMI'))) --------------------------------------101960115 The average time of 1:10 and 1:20 gives 1:15 for the hours, date is the same. Solution Explanation: 1. The following SELECT gives you the seconds past midnight, then displays in
hh:mm:ss
select to_char(sysdate, 'sssss'), to_char(sysdate, 'hh:mm:ss') from dual; 2. The following SELECT will convert the seconds past midnight back to display as hh:mm:ss select substr(to_char(sysdate, 'sssss'),1,8), substr(to_char(sysdate, 'hh:mi:ss'),1,8), substr(trunc(to_char(sysdate, 'sssss')/3600), 1, 10), substr(trunc(mod(to_char(sysdate, 'sssss'),3600)/60), 1, 10),
substr(mod(mod(to_char(sysdate, 'sssss'),3600),60), 1, 10) from dual;
How to insert & character or special character into Database using SQL*Plus. • •
goal: How to insert & character or special character into Database using SQL*Plus. fact: Oracle Server - Enterprise Edition
Fix: Solution 1: If you are not using substitution variables (&1 &2 &3 etc.) you can do a "SET DEFINE OFF" or "SET SCAN OFF" to turn off the definition of the ampersand as a substitution variable. Example: SQL> SET DEFINE OFF SQL> INSERT INTO VALUES ('AT&T'); / 1 row created Solution 2: If you are using substitution variables, set the substitution character to one which will not be encountered when inserting data. Example: SQL> SET DEFINE % SQL> INSERT INTO VALUES ('AT&T') / 1 row created. Solution 3: If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON, then SQL*Plus will understand the special character following the escape symbol is to be treated as a regular character. Example: SQL>set escape on SQL>show escape escape "\" (hex 5c) SQL> INSERT INTO temp_table VALUES ('select * from emp where ename = \& 1'); 1 row created.
How to embed Single Quote in a string
Problem Description: How do you embed single quotes ( ' ) into a character string? How do you concatenate a quote in SQL? How do you place quotes around a character string in a SQL query? How do you store an apostrophe into a character variable in PL/SQL? Problem Explanation: Example 1 When you issue either of the following SELECT statements: SQL> SELECT ' FROM dual; SQL> SELECT ''' FROM dual; the following error occurs: ORA-0xxx:
quoted string not properly terminated
Example 2 When you issue the following SELECT statement: SQL> SELECT ''character string in quotes'' FROM dual; the following error occurs: ORA-00923:
FROM keyword not found where expected
CONCATENATE CHR(39) TO CREATE LITERAL SINGLE QUOTE Solution Description: To create a single quote, concatenate CHR(39) to the string. Example 1 --------SQL> SELECT 'test' || CHR(39) || 'case' result 2> FROM dual; RESULT --------test'case Example 2 --------SQL> SELECT CHR(39) FROM dual; C '
Solution Explanation: To return the ASCII value of the single quote ( ' ): SQL> SELECT ASCII('''') FROM dual; ASCII('''') ----------39 USE 2 SINGLE QUOTES TO CREATE 1 SINGLE QUOTE Solution Description: Keep the following two rules in mind: 1. Enclose every character string in single quotes. The single quote is a string delimiter. 2. Inside a string literal, use two consecutive single quotes to create a literal single quote. Example 1 --------6 single quotes: 'test' || '''''' || 'case' ---> test''case 8 single quotes: 'test' || '''''''' || 'case' ---> test'''case You can also implement the above in the following way: 'test''case' 'test''''case'
---> test'case ---> test''case
Hence: a. To create a single quote, concatenate 4 single quotes: '''' The two single quotes in the middle define the single quote. The outside single quotes are the single quotes that must surround a string. Example 2 --------SQL> SELECT '''' FROM dual; ' ' Example 3 --------SQL> SELECT 'test' || '''' || 'case' result 2> FROM dual;
RESULT --------test'case b. To place single quotes around a character string, enclose the character string within 3 single quotes: ''' At the start of a character string: the first single quote defines the start of the character string; It is one of the two single quotes that surround the string. The second and third single quotes define the literal single quote. At the end of the character string: the first and second single quotes define the literal single quote. The third single quote closes the character string; it is the other single quote that surrounds the string. Example 4 --------SQL> SELECT '''character string in quotes''' result 2> FROM dual; RESULT ---------------------------'character string in quotes' Solution Explanation: More examples: Example 5 --------SRW.DO_SQL('SELECT DECODE(dname, ''NONE'', NULL, ''A'') FROM dept WHERE deptno = 10'); As a result, this is the SELECT statement sent to the database: SELECT DECODE(dname, 'NONE', NULL, 'A') FROM dept WHERE deptno = 10 Example 6 --------DECLARE a VARCHAR2(200); q CHAR(1) := ''''; BEGIN a := '''this is a ' || q || 'quoted'' string' || q; END; String "a" stores: 'this is a 'quoted' string'
How to do a case insensitive search using the 'LIKE' operator?
Problem Description: How can a user perform a case insensitive match using the LIKE comparison operator? Problem Explanation: The field in a table may be entered in different cases, for example, 'MacDonald' and MACDONALD' how can a single SQL query using the LIKE operator find all occurrences of the name MacDonald. Solution Description: You can use the UPPER () function to perform a case insensitive match as in the following condition; For example, to find an employee name in a string beginning with the letters 'SM' SQL > select * from emp where UPPER (ename) like 'SM%' ; Solution Explanation: The UPPER function converts the ename field results in the searched column to upper case. For example the strings 'smith' and 'Smith' and 'SMITH' will all become 'SMITH'.
How to Create Column Delimited Flat File Problem Description: How can SQL*Plus be used to create a delimited flat file? Problem Explanation: When moving data between different software products, it is sometimes necessary to use a "flat" file ( an operating system file with no escape characters, headings, or extra characters embedded ). CONCATENATE COLUMN SEPARATOR IN QUERY AND SPOOL Solution Description: Concatenate the desired column separator into the query. For example: To delineate column text with TABs: ----------------------------------SQL> select DEPTNO || CHR(9) || DNAME FROM DEPT; DEPTNO||CHR(9)||DNAME ------------------------------------------------------10 ACCOUNTING 20 RESEARCH
30 40
SALES OPERATIONS
The CHR() SQL character function is used to embed the TAB in the query text. To delineate column text with COMMAs: ------------------------------------SQL> select DEPTNO || ',' || DNAME FROM DEPT; DEPTNO||','||DNAME ------------------------------------------------------10,ACCOUNTING 20,RESEARCH 30,SALES 40,OPERATIONS To send your query results to a file, use the following SQL*Plus SET commands before running your query: SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SPOOL file_name < your query> SPOOL OFF SET TERMOUT ON Solution Explanation: SQL*Plus versions 3.0.x and 3.1.x do not have a SET command to specify a column separator to delineate text between columns. NOTE: The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to a maximum result of 255 characters. If either string is VARCHAR2, the result has datatype VARCHAR2 and is limited to a maximum result of 2000 characters. Solution Description: Use the SQL*Plus 3.2.x COLSEP SET command. For example:
To send your query results to a comma delimited flat file, use the following SQL*Plus commands: SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET COLSEP ',' SPOOL file_name < your query> SPOOL OFF SET TERMOUT ON Solution Explanation: SQL*Plus 3.2.x has the COLSEP SET command. Previous versions of SQL*Plus did not have COLSEP. This solution will send the query results to a file, with the text printed between the SELECTed columns as specified by the SET COLSEP command. If the COLSEP variable contains blanks or punctuation characters, you must enclose the text with single quotes.
Method to create .SQL file with comma-delimited output. SOLUTION DESCRIPTION: The SQL script below when executed will produce a second .SQL file for the table so that the listed table output will have comma's between the columns. REM ********************************************************************* ***** REM COMMA.SQL REM -----REM Generate SELECT statements using Catalog tables REM REM Usage: @COMMA tablename outputfilename REM REM tablename: Any table name (standard SQL wildcards accepted, e.g. % for all) REM outputfilename: Target SQL file name (no extension, .SQL REM extension supplied automatically REM ********************************************************************* *
SET SET SET SET SET
FEEDBACK OFF VERIFY OFF TERMOUT OFF PAGES 999 HEADING OFF
COL tabname NOPRINT COL seqno NOPRINT COL seq2 NOPRINT SELECT table_name tabname , 1 seqno , 0 seq2 , 'SELECT ' txt FROM user_tables WHERE table_name LIKE UPPER('&1') UNION SELECT table_name tabname , 2 seqno , column_id seq2 , DECODE(column_id ,1,'' ,'||'',''||')||column_name txt FROM user_tab_columns WHERE table_name LIKE UPPER('&1') and exists ( select 1 from user_tables where user_tables.table_name = user_tab_columns.table_name ) UNION SELECT table_name , 3 seqno , 0 seq2 , 'FROM '||table_name||';' txt FROM user_tables WHERE table_name LIKE UPPER('&1') ORDER BY 1,2,3 SPOOL &2..sql // SPOOL OFF SET SET SET SET
FEEDBACK ON VERIFY ON TERMOUT ON HEADING ON
How to Query top "N" rows ordered by a column Problem Description: What SQL SELECT statement do you use to retrieve the top "n" rows ordered by a particular "column"? Problem Explanation: Solution Description: To return exactly "n" rows ordered by a column, create a query similar to the following: SELECT * FROM table A WHERE n >= (SELECT COUNT(*) FROM table B WHERE B.column >= A.column) ORDER BY column DESC; where "n" is the number of rows to be retrieved and ordered, "table" is the name of the table and "column" is the name of the column. The first SELECT statement controls the number of rows, while the nested, second SELECT statement controls the condition. The query returns the first "n" ordered rows. If n=10 and Rows 8-12 share the same column value, this only return Rows 9 and 10. The above SELECT statement does not take duplicate column values into consideration. Solution Explanation: To return any row whose column value is one of the top "n" ordered column values, create a query similar to the following: SELECT * FROM table A WHERE n >= (SELECT COUNT (DISTINCT column) FROM column B WHERE B.column >= A.column) ORDER BY column DESC;
Specifying DISTINCT return all rows whose column (e.g. sal) value falls into one of the top "n" values. Hence, the query returns more than "n" rows when several rows share the same column value.
How do you convert a character field into a date format without losing the century data? Problem Description:
When you convert a character field into a date field, the century information is lost. How do you convert a character field into date format without losing the century data? Solution Description: To retain the four digit century information, you must convert the date field back into a character string. For example the query is: SQL> select TO_CHAR (HIREDATE, 'DDMONYYYY') from EMP Where HIREDATE = TO_DATE ('17NOV1981', 'DDMONYYYY');
The results of the query which show the full four character century are: TO_CHAR (HIREDATE,'DDMONYYYY') 17NOV1981
How do I set the SQL*Plus search path for SQL scripts? Answer Set SQLPATH environment variable. Add all the directories you want SQL*Plus to search for the SQL scripts, each separated by a semicolon. Example: SQLPATH = C:\ORANT\DBS;C:\APPS\SCRIPTS;C:\MYSCRIPTS; In Windows environment, if you are using 32-bit SQL*Plus then set this variable in the Registry, or if you are using 16-bit SQL*Plus then set this variable in the file Oracle.ini
How do I get the system date and time over DBLINK? You cannot use SYSDATE or USER, to get the SYSDATE, USER over DBLINK due to a known restriction with these functions. For this, you can use the following workaround: Step-1: On the remote machine, populate a table with the SYSDATE, USER, values using a stored procedure on the remote machine. CREATE TABLE db_table (mydate DATE); CREATE OR REPLACE PROCEDURE db_date as mydate date; BEGIN DELETE FROM db_table; SELECT SYSDATE INTO mydate FROM DUAL;
INSERT INTO db_table VALUES (mydate); END; Step2:
On local machine, select the data from the table which has been populated using the stored procedure. EXECUTE db_date@dblink SELECT mydate FROM db_table@dblink;
How do I execute SQL scripts in batch? You can execute any SQL script using the SQL*Plus command in a batch file. The format of the command is as follows: SQL*PlusExecutable USERNAME/PASSWORD@CONNECT_STRING Sqlscript
For Example: Plus31 scott/tiger@orcl @c: \script.sql
will execute the script script.sql on 'c' drive. Suppose the contents of script.sql is as follows: SELECT * FROM emp; host dir/p EXIT;
Executing the above example command will display the emp records in the SQL*Plus window and would list the directory in a command window. The 'exit' command in the last line will close the SQL*Plus session.
How do I display the time component of a date column? To retrieve the time information, use the TO_CHAR function with a format mask. Examples: SELECT To_Char(datecolumn, 'DDMONYY HH24:MI:SS') FROM mytable; :global.hi := To_Char(:time1, 'DDMONYY HH:MI:SS'); The data returns in the following format: 01SEP94 15:01:01
How do you skip a page in a SQL*Plus report? You can skip a page in SQL*Plus reports by making use of the following statement:
BREAK ON X SKIP PAGE; COMPUTE SUM OF Y ON X; SELECT fld1,fld2 FROM tab1,tab2 WHERE x=y GROUP BY fld1,fld2 ORDER BY fld1;
Is there a way to document my SQL command and file? You can do this in two ways : 1. SQL comment delimiters (/*........*/) EX: SQL> select ename /*this is a comment delimiter example*/ from /*This is another example*/ emp; 2. REMARK Begins a remark in a command file. Must appear at the beginning of a line and ends at the end of the line. A line cannot contain both a remark and a command. For example: get rem.sql 1 Rem This is an example of how REM is used. 2 Column sal format $99,999.99 heading 3 'Monthly|salary' 4 Compute sum od sal on deptno 5 Rem This sql script will total the salaries 6 Rem of all employees in each department 7 Select deptno, ename, sal from emp 8 order by deptno;
How do I include the current date and time in a SQL*Plus report heading? The following SQL*Plus command file segment shows how to put the current date into a variable and then include that variable in the title. To get the time, just change the mask in the to_char function to include that as well. COLUMN SYSDATE new_value today SELECT To_Char(SYSDATE,'mm/dd/yy') "sysdate" FROM DUAL / TTITLE left today center 'MY HEADING OR TITLE' skip 2
The use of "new_value" shown here applies in general to any value selected into a column specified in the COLUMN xxx NEW_VALUE command. It can be used to display various data in the title of a report.
How To Trim Lines When Spooling To remove trailing blanks at the end of each line enter the following statement on the command prompt before the spool command: SQL> set trimspool on
How to insert ‘&’ character or special character into Database using SQL*Plus Solution 1: If you are not using substitution variables (&1 &2 &3 etc.) you can do a "SET DEFINE OFF" or "SET SCAN OFF” to turn off the definition of the ampersand as a substitution variable. Example: SQL> SET DEFINE OFF SQL> INSERT INTO VALUES ('AT&T'); / 1 row created
Solution 2: If you are using substitution variables, set the substitution character to one which will not be encountered when inserting data. Example: SQL> SET DEFINE % SQL> INSERT INTO VALUES ('AT&T') / 1 row created.
Solution 3: If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON, then SQL*Plus will understand the special character following the escape symbol is to be treated as a regular character. Example: SQL>set escape on SQL>show escape escape "\" (hex 5c) SQL> INSERT INTO temp_table VALUES ('select * from emp where ename = \& 1'); 1 row created.
How to Insert Special Characters Into Database First pay attention the database you use is able to deal with the special characters you want to insert. That means you have to check if the special character is included in your database characterset.
Here just an example how to insert these special characters: Assume you want to insert the following sample string, consisting of several single characters, which are concated to one string. -Ob?''!#*)EBK+`#a"^?#c\QH'
To be able to do this, mask each single ' (single quote) with just one preceeding '(single quote).If just two ''(two single quotes) signs have to be inserted use four '''' (four single quotes): The following will work: SQL>insert into test VALUES ('-Ob?''''!#*)EBK+`#a"^?#c\QH'''); 1 row created. SQL> select * from test; A --------------------------Ob?''!#*)EBK+`#a"^?#c\QH'
Be aware of the characters (' at the beginning and the characters ') at the end of the string you want to insert.
How To Create a Flat File In SQL*Plus Without Showing Statement or ''Spool Off'' Create a script, e.g. report.sql, with the following contents: set NEWPAGE 0 set SPACE 0 set LINESIZE 80 set PAGESIZE 0 set ECHO OFF set FEEDBACK OFF set HEADING OFF spool report.txt select sysdate from dual; spool off
<--- your SQL statement here
Run the script from SQL*Plus: SQL> @report.sql
How to SELECT Columns as Rows Using SQL Example:
Using a UNION and VIEW should do the transformation like: create table p ( product varchar(16), attr1
varchar(16),
attr2 insert into p values ( insert into p values ( insert into p values ( commit;
varchar(16), 'product_1', 'product_2', 'product_3',
attr3 varchar(16) ); 'attr_11', 'att_12', 'attr_13'); 'attr_21', 'att_22', 'attr_23'); 'attr_31', 'att_32', 'attr_33');
create view vv as select * from (select product , attr1 from p union (select product , attr2 from p union (select product , attr3 from p ); Output: SQL> select * from vv; PRODUCT ---------------product_1 product_1 product_1 product_2 product_2 product_2 product_3 product_3 product_3
ATTR1 ---------------att_12 attr_11 attr_13 att_22 attr_21 attr_23 att_32 attr_31 attr_33
How to Change the Displayed Font in SQL*Plus (GUI) To Change the Windows GUI Font: 1. Select Run from the the Start menu and then enter regedit in the Open field and Click OK to start the registry Editor. 2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. Note:If you have more than one Oracle installation, you must select the HOME entry associated with the Windows GUI you want to change. HOME0 is the registry entry for an Oracle installation. A subsequent Oracle installation will have the registry entry HOME1 and the next HOME2 and so on. Changes only affect a SQL*Plus Windows GUI started from the associated Oracle installation, so you can use different settings for each Oracle installation. 3. Click New String Value in the Edit menu. A new string value, with the default name, NewValue #1 is created at the bottom of the right pane of the Registry Editor. The default name of the new string value is selected ready for you to replace with the name you want. 4. Enter SQLPLUS_FONT as the name of the new font face string value.If you miskey the name or inadvertently enter it in mixed or lower case, you can edit the name by selecting Rename from the Edit menu. 5. Click Modify from the Edit menu or press Enter again to display the Edit String dialog.
6. Enter the font name you want to use, such as Courier New, in the Value Data: field. SQL*Plus will use the new font the next time you start the SQL*Plus Windows GUI.
Modify Password and Suppressing Typed Password in SQLPLUS Use sqlplus functionality passw just enter on sqlplus prompt: SQLPLUS> passw
and new password wil not be echoed.
How to Select and Display a Certain Number of Records Problem Description Customer needs to select from a table and display only a limited number of records. Example would be select all Smiths from address table, but only display the first 10 or what ever. Solution Description Several solutions are available, from very simple to slightly complex Solution 1 (simple) SQL> select empno from emp where empno like '%7%' and rownum <= 10; EMPNO 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 10 rows selected.
Solution 2 SQL> l 1 declare 2 v_counter number(3):=+0; 3 v_empno emp.empno%type; 4 cursor emp_cursor is 5 select empno 6 from emp 7 where empno like '7%'; 8 begin 9 open emp_cursor;
10 loop 11 exit when emp_cursor%notfound; 12 fetch emp_cursor into v_empno; 13 v_counter := v_counter +1; 14 exit when emp_cursor%notfound or v_counter > 10; 15 dbms_output.put_line(v_empno ||' ' || v_counter); 16 end loop; 17 close emp_cursor; 18* end; SQL> / 7369 1 7499 2 7521 3 7566 4 7654 5 7698 6 7782 7 7788 8 7839 9 7844 10 PL/SQL procedure successfully completed.
Solution 3 set serveroutput on accept select_info prompt 'please enter select: ' accept where_info prompt 'please enter where: ' declare v_counter number(3):=+0; v_&select_info emp.&select_info%type; cursor emp_cursor is select &select_info from emp where &select_info like &where_info; begin open emp_cursor; loop exit when emp_cursor%notfound; fetch emp_cursor into v_&select_info; v_counter := v_counter +1; exit when emp_cursor%notfound or v_counter > 10; dbms_output.put_line(v_&select_info ||' ' || v_counter); end loop; close emp_cursor; end; /
How to Number Rows After They Have been Sorted
Problem description SQL*Plus users sometimes want to have rows numbered in a sorted order. This can't be achieved using the ROWNUM pseudo column, since the ordering is done after the values for ROWNUM had been assigned to rows as they were selected in random order. This is illustrated in the following example. The first selection shows the unsorted selection of ename and rownum from emp, and the second selection illustrates how ROWNUM is useless as a numbering device for ordered selections because the values of ROWNUM are assigned before the ordering is performed. SQL> select ename, rownum from emp; ENAME ROWNUM ALLEN 1 JONES 2 BLAKE 3 CLARK 4 KING 5 ADAMS 6 JAMES 7 FORD 8 SQL> select ename, rownum from emp order by ename; ENAME ROWNUM ADAMS 6 ALLEN 1 BLAKE 3 CLARK 4 FORD 8 JAMES 7 JONES 2 KING 5
Solution Explanation The following select statement achieves the desired ordering/numbering: SQL> select A.ename, count(*) position 2 from emp A, emp B 3 where A.ename > B.ename 4 or A.ename = B.ename and A.empno >= B.empno 5 group by A.empno, A.ename 6 order by A.ename, A.empno; ENAME POSITION
ADAMS 1 ALLEN 2 BLAKE 3 CLARK 4 FORD 5 JAMES 6 JONES 7 KING 8
This method works by counting the number of records that a particular record is superior than (or equal to) in alphabetical order of employee name. The empno column acts as a unique key to discern between records in case identical names occur in the table. This would not be an efficient method against tables with large numbers of rows. Another alternative is to create an index on the order column, and include a meaningless where clause to force use of the index. SQL> create index sort on emp (ename); Index created. SQL> select ename, rownum from emp where ename > ' '; ENAME ROWNUM ADAMS 1 ALLEN 2 BLAKE 3 CLARK 4 FORD 5 JAMES 6 JONES 7 KING 8
The where clause in the query forces SQL*Plus to use the index created on the ename column, and since the index is used, the rows are returned in ascending order. Since this method depends on the inherent order of the index, it cannot be used to return rows numbered in descending order. The last method presented takes advantage of a feature of the database kernel optimizer. SQL> select rownum, ename 2 from emp , dual 3 where emp.ename = dual.dummy (+); ROWNUM ENAME 1 ADAMS 2 ALLEN 3 BLAKE 4 CLARK 5 FORD 6 JAMES 7 JONES
8 KING
The optimizer evaluates the outer join in this example by using a sort/merge join, which results in the desired sorted order.
Missing Label when Computing is Done on the First Column PROBLEM: Your label is missing when doing computes of the first column on a report break. In other words,
suppose you do the following in SQL*Plus: SQL> break on report SQL> compute count of deptno on report SQL> select * from dept; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 4
Notice the lack of the 'count' label. This happens in report breaks where the computation is performed on the first column. Normally, the compute labels for a report break always appear in the first column. For example, if I had performed the count computation on the second column, DNAME, I would have gotten: SQL> clear computes Computes cleared SQL> compute count of dname on report SQL> select * from dept; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON count 4
In this case, no computation is performed on the first column so SQL*Plus is free to place the "count" label in the first column. RESOLUTION: The following approach was proposed: use a "dummy" first column to hold the compute label when
you want a compute of the first column on a report break. Here's how it works with our original example:
SQL> clear computes computes cleared SQL> clear breaks breaks cleared SQL> break on report SQL> compute count of deptno on report SQL> col summary format a7 SQL> select ' ' summary, dept.* from dept; SUMMARY DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON count 4
Thus, we create a dummy left most column named SUMMARY (or whatever is appropriate) to hold the "count" label. Notice that as the first element of the SELECT list we specify the constant expression ' ' (a blank string) and give it a column alias of SUMMARY.
How to exit multiple connection attempts to DB from SQL*Plus when first connection attempt fails. Purpose The purpose of this article is to demonstrate how to make only one connection attempt to the database and then exit so SQL*Plus does not bring up the username prompt again. This technique can then be used in scripts or batch files to automatically handle failing SQL*Plus connections e.g. In backup scripts or batch jobs. SQL*Plus example from Unix Prompt sqlplus /nolog <<EOF whenever sqlerror exit connect scott/tiger@notthere EOF
SQL*Plus example for Windows
Create a SQL Script connect.sql as follows: whenever sqlerror exit connect scott/tiger@notthere
From DOS Prompt, Start this script as follows:
sqlplus /nolog @connect.sql
How to restrict SQL commands using PRODUCT_USER_PROFILE It is possible to disable the following commands: ALTER, AUDIT, CREATE, DELETE, DROP, GRANT, INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, UPDATE, VALIDATE.
Example To disable the SELECT statement for SCOTT: 1. Insert in the table PRODUCT_USER_PROFILE the next row: SQL> insert into product_user_profile values ('SQL*Plus', 'SCOTT', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
2. When the user SCOTT tries to execute the command SELECT he will obtain the error: SP2-0544: Invalid command: SELECT.
How to Get a Heading After Set Pagesize 0 When setting PAGESIZE to 0 then the column headings will be suppressed. Use the following example to generate a heading: SQL-script (t1.sql) set pagesize 0 set feedback off select 'Number Name' from dual; select '---------- -------' from dual; select empno, ename from emp / SQL> @t1 => Result: Number Name ---------- ------7369 SMITH 7499 ALLEN
7521 WARD 7566 JONES
How to Include a New Line Character in an Insert Statement from SQL*Plus Concatenate the ASCII value CHR (10) in between the data where the carriage return is required. E.G. INSERT INTO VALUES ('HELLO'||CHR (10) ||'HOW ARE YOU')
How to Delete Line Feeds and Carriage Returns from Varchar2 Data? Goal How to delete carriage returns and line feeds contained within the varchar2 field data? Solution You can perform the following nested replace sql statement using the replace command to delete the linefeeds and the carriage returns. SELECT REPLACE (REPLACE (column name, CHR (13), ‘’), CHR (10), '') FROM tablename;
How to Setup Date Format or Specific Variables in iSQL*Plus 9i? Goal How to set NLS_DATE_FORMAT in iSQL* plus 9.2? Solution A- For a session you can user command: alter session set NLS_DATE_FORMAT... B- To set it permanently: 1- Modify the $ORACLE_HOME/sqlplus/admin/isqplus.conf file to add -initial-env for NLS_LANG and NLS_DATE_FORMAT as follow:
What Is the Command that Replaces ''Spool'' in iSQL*Plus? Goal Spool command is no longer a valid command in iSQL*Plus. What is the new command to replace it? Solution iSQL*Plus works in a different way to generate the output to file. The file output is html instead of text. Review iSQL*Plus -> Help -> The iSQL*Plus User Interface -> Output. It states: File: when the contents of the input area are executed, the resulting output is saved to a file. You are
prompted to enter the name of the file. As the output is in HTML format, it is useful to give the saved output file a .htm or .html extension. Steps to generate the output to file: 1) Invoke iSQL*Plus: http://host:port/isqlplus 2) In the User Interface, select output=file. 2) Select 'Execute' button. 3) Save the file. This is an html file. 4) Open the html file using your browser.
Commands Not Supported in iSQL*Plus Goal This article lists SQL*Plus commands not supported in the iSQL*Plus user interface. Attempting to use any of the following unsupported commands or command options raises an SP2-0850 error message. Solution The following commands have no context in iSQL*Plus and have not been implemented. ACCEPT
PASSWORD
CLEAR SCREEN
PAUSE
The following SET command variables have no context in iSQL*Plus and have not been implemented. SET EDITFILE
SET SQLBLANKLINES
SET TAB
SET FLUSH
SET SQLCONTINUE
SET TERMOUT
SET NEWPAGE
SET SQLNUMBER
SET TIME
SET PAUSE
SET SQLPREFIX
SET TRIMOUT
SET SHIFTINOUT
SET SQLPROMPT
SET TRIMSPOOL
SET SHOWMODE
SET SUFFIX
The following commands have security issues on the middle tier and have not been implemented. GET
SPOOL
HOST
STORE
The following commands are SQL buffer editing commands which are not relevant in iSQL*Plus and have not been implemented. APPEND
DEL
INPUT
CHANGE
EDIT
SAVE
List/Table of General SQL*Plus 9.2 Limits Goal The purpose of this article is to provide a list of general SQL*Plus limits. The limits shown are valid for most operating systems. Solution Item
Limit
filename length
system dependent
username length
30 bytes
user variable name length
30 bytes
user variable value length
240 characters
command-line length
2500 characters
length of a LONG value entered through SQL*Plus LINESIZE value LINESIZE
system dependent
LONGCHUNKSIZE value
system dependent
output line size
system dependent
line size after variable substitution
3,000 characters (internal only)
number of characters in a COMPUTE command label
500 characters
number of lines per SQL command
500 (assuming 80 characters per line
maximum PAGESIZE
50,000 lines
total row width
60,000 characters for VMS; otherwise, 32,767 characters
maximum ARRAYSIZE
5000 rows
maximum number of nested scripts
20 for VMS, CMS, Unix; otherwise, 5
maximum page number
99,999
maximum PL/SQL error message size
2K
maximum ACCEPT character string length
240 Bytes
maximum number of DEFINE variables
2048
How to Get US Eastern Time No Matter of the Time Zone Location? Goal The goal of this document is to provide one way of getting US Eastern Time No Matter of the Time Zone Location. Solution
If Oracle 9i or later is used then use the following sql statement: SELECT CURRENT_TIMESTAMP AT TIME ZONE '-5:00' AS eastern_time FROM dual; That gives you a TIMESTAMP. If a regular DATE is needed then CAST it as shown below: SELECT CAST (CURRENT_TIMESTAMP AT TIME ZONE '-5:00' AS DATE) AS eastern_time FROM dual;
How to Retrieve the Select Statement Used to Create a View? Problem Description How can you see the SELECT statement used to create a view? When you try selecting the TEXT field from USER_VIEWS, you are not able to see the entire SELECT statement. Problem Solution In SQL*Plus, you must first execute SET LONG n, where n is an integer value greater than or equal to the value of the TEXT_LENGTH field in USER_VIEWS. For example: SQL> set long 999 SQL> select text from user_views where view_name like 'MYVIEW'; You should now be able to see a complete SELECT statement used to create MYVIEW.
How to Restrict User Access to Database Instances in iSQL*Plus? Goal The article is intended to provide information about how to restrict user database instance access from iSQL*Plus. You may want to limit the databases instances that users can access in iSQL*Plus to a restricted list. When restricted database access has been enabled, a dropdown list of available databases is displayed in place of the Connection Identifier text field on the Login screen. This enables greater security for iSQL*Plus Servers in hosted environments. Solution Connection identifiers are listed in the order defined in iSQLPlusConnectIdList. Edit the $ORACLE_HOME/oc4j/j2ee/oc4j-applications/applications/isqlplus/isqlplus/WEB-INF/web.xml
file to restrict database access to iSQL*Plus users. Change the following entry to include a new paramvalue element which contains the list of databases to which you want to restrict access. For example, to only have ora10g and ora9i instance available: NOTE: A period is added to the text for readability. Do not include in the web.xml .
How to Output Two Records from a Single Record Depending on the Column Value? Goal
One of the column in a table has three valid values e.g. column value (name: cell) is 1, 2 and 3. The value 3 indicates that the record is both 1 and 2. When you select the records along with other values, you will get all the three values (1, 2 and 3) in select statement. Now in the output value = 1 or 2 the single is ok. But for value = 3, you want to create two records that display values 1 and 2 (value for cell). Actual output with standard select is as follows: ID CELL ----- -------721 1 722 2 723 3 and the required output is as follows : ID CELL ----- -------721 1 722 2 723 1 723 2 How to split and resolve this in SQL? Fix The following SQL statement to achieves the desired output: select id, decode(cell, 3, 1, cell) cell from
How to Exit with a Rollback from SQL*Plus Goal How to exit sqlplus with a rollback? Fix To prevent SQL*Plus from automatically committing all changes, such as INSERT, UPDATE, or DELETE command, you can use the 'ROLLBACK' or 'EXIT ROLLBACK' command before exiting. The SQL*Plus User Guide and Reference states the following: EXIT Purpose Terminates SQL*Plus and returns control to the operating system.
Syntax {EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK] By default the exit is with commit. To rollback you must do: exit rollback
How to Generate Numbers 0 to N from a Query without PL/SQL? Goal How to generate numbers 0 to N from a query without any PL/SQL Fix select rownum-1 from ( select 1 from dual group by cube (1,1,1,1,1,1)) where rownum<&maxlimit+2 The logic behind this query is that the CUBE is generating 2^n rows where n is the number of arguments to the CUBE function. In this case the inner subquery generates 2^6 rows i.e. 64 rows which means you can generate numbers up to 63. In case you want to generate numbers beyond this range, then you need to add one more argument to the CUBE which would increase it to 2^7 rows i.e. 128 rows. select rownum-1 from ( select 1 from dual group by cube (1,1,1,1,1,1,1)) where rownum<&maxlimit+2 The range can be increased by increasing the number of arguments to the CUBE function.
How to Hide the Password Running a Script as a Different User? Goal How to hide a password to execute a script? For example, connected as user2 you need to execute a script querying objects from Scott's schema like select * from dept. Fix 1. Create hide_pswd.sql script using hide option. It will display the prompt "Password: ", place the reply in a char variable named hide_password, and suppress the display: accept hide_password char prompt 'Password: ' hide connect scott/&&hide_password@aliasdb / select * from dept
/ 2. This will prompt for user password and will show it in hide mode (* instead of password): SQL> start hide_pswd.sql SQL> accept hide_password char prompt 'Enter password: ' hide Enter password: ***** SQL> connect scott/&&hide_password@aliasdb Connected. SQL> / DEPTNO DNAME LOC ---------- -------------- ------------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
How to Generate A Delimiter in Spool Output (Excel CSV file)? Goal How to generate a delimiter in spool output (Excel .csv files)? Fix In order to generate a delimited file output, you need to concatenate columns using the desired delimiter i.e. comma. Example: select empno|| ','||ename||'&'||mgr from X; Other option is using: SQL> set colsep ',' SQL> spool c:\testexcel.csv SQL> select * from emp; Change some of the default SQL*Plus parameters, that will be garbage for Excel: feedback=off newpage=none termout=off header=off If some columns are empty, be aware to include the delimiter, too: nvl(to_char(col2),',')
How to Select N-th Highest Value from a Table? Goal This document provides the SQL statement which can be used to find the N-th highest or lowest value from a table column of NUMBER datatype. Also, provides the N-th highest or lowest DISTINCT value of a table column. For example: - select the 5th highest salary record from the EMP table. - select the 5th highest salary DISTINCT value from the EMP table. - select the 5th lowest salary record from the EMP table. - select the 5th lowest salary DISTINCT value from the EMP table. Fix Given the following data: SQL>SELECT SAL FROM EMP ORDER BY SAL DESC; SAL ---------5000 3000 3000 2975 2850 <--- Top 5th RECORD 2450 <--- Top 5th DISTINCT VALUE 1600 1500 1300 <--- Bottom 5th DISTINCT VALUE 1250 <--- Bottom 5th RECORD 1250 1100 950 800 1. The general syntax to retrieve the TOP N-th record is : 1.A. Select MIN(column_name) from (select column_name from table_name order by column_name desc) where rownum<= N; For example, the following can be used to find the 5th highest record from the EMP table: SELECT MIN(sal) from (select sal from emp order by sal desc) where rownum <= 5; MIN(SAL)
---------2850 1. B. A method without using MIN () to find the top 5-th record is: SELECT distinct (temp.sal) FROM EMP temp WHERE 5= (SELECT COUNT (tmp.sal) FROM EMP tmp WHERE temp.sal<=tmp.sal); SAL ---------2850 2. The general syntax to retrieve the TOP N-th DISTINCT VALUE is : 2.A. Select MIN(column_name) from (select distinct column_name from table_name order by column_name desc) where rownum<= N; For example, the following can be used to find the 5th highest distinct salary from the EMP table: Select MIN(sal) from (select distinct sal from emp order by sal desc) where rownum <= 5; MIN(SAL) ---------2450 2. B. A method without using MIN () to find the top N-th DISTINCT VALUE is: SELECT DISTINCT (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT (DISTINCT (tmp.sal)) FROM EMP tmp WHERE temp.sal<=tmp.sal); SAL ---------2450 3. The general syntax to retrieve the N-th RECORD from the BOTTOM is as follows: 3.A. Select MAX(column_name) from (select column_name from table_name order by column_name ) where rownum<= N; For example, the following can be used to find the 5th lowest RECORD from the EMP table: SELECT MAX(sal) from (select sal from emp order by sal) where rownum <= 5; MAX(SAL) ---------1250 3.B. A method without using MAX () to find the bottom N-th RECORD is: SELECT distinct (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT (tmp.sal) FROM EMP tmp WHERE temp.sal>=tmp.sal); SAL ---------1250 4. The general syntax to retrieve the N-th DISTINCT VALUE from the BOTTOM is as follows: 4.A. Select MAX(column_name) from (select distinct column_name from table_name order by column_name ) where rownum<= N; For example, the following can be used to find the 5th lowest DISTINCT value from the EMP table: Select MAX(sal) from (select distinct sal from emp order by sal) where rownum <= 5; MAX(SAL) ---------1300 4.B. A method without using MAX() to find the lowest N-th DISTINCT VALUE is:
SELECT DISTINCT (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT (DISTINCT (tmp.sal)) FROM EMP tmp WHERE temp.sal>=tmp.sal); SAL ---------1300 HOW TO MAKE THE SELECT STATEMENTS ABOVE MORE DYNAMIC? In examples 1.A., 2.A., 3.A., and 4.A., use the following modification to the query: From … where rownum <= 5; To … where rownum <= &N; In examples 1.B., 2.B., 3.B., and 4.B., use the following modification to the query: From … WHERE 5 = (SELECT COUNT… To …WHERE &N = (SELECT COUNT… This change will cause the following prompt for the user: Enter value for n: <user puts in value>
How to Stop 'Connected' Message when Running SQLPlus in Silent Mode? Goal When you start sqlplus in silent mode, you still get the connected message. Example: sqlplus -S /nolog conn / Connected. How to stop the "Connected." from appearing? Fix 1. Do not use the "/nolog" option. This is causing 2 connections. It is the second connection that is echoed. Instead start sqlplus as: sqlplus -s / 2. If you need the /nolog, then use grep to exclude the Connected line as follows: sqlplus -S /nolog | grep -v Connected
How to use New Predefined Variables to Set SQLPROMPT to Display the User, Date and/or Privileges? Goal To explain the new predefined variables introduced in 10g and how they can be used to make the SQL*PLUS Prompt display more informative. These variables are available only in 10g. Fix You can now use substitution variables in the SQL*Plus command-line prompt to display, for example, the database and server you are connected to, or other information available through a substitution variable you choose. There are four new predefined variables: Predefined Variables: _DATE, _PRIVILEGE, _USER, _CONNECT_IDENTIFIER _DATE contains the current date or a user defined fixed string. _PRIVILEGE contains the privilege level of the current connect. This will be either AS SYSDBA, AS SYSOPER or blank to indicate a normal connection. _USER contains the username as supplied by the user to make the current connection. This is the same as the output from the SHOW USER command. _CONNECT_IDENTIFIER contains the connection identifier information. These variables can be accessed like any other substitution variable. For example, they could be used in TTITLE, in '&' substitution variables, They can used in your SQL*Plus command line prompt by using the SET SQLPROMPT command. For example, to make your prompt always show your username (_USER), the @ symbol, and then your connection identifier (_CONNECT_IDENTIFIER) during your session, enter:
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " You can view the predefined variable definitions in the same way as you view other DEFINE definitions, using the DEFINE command with no arguments, or with the specific argument you wish to display, for example: Using DEFINE to display these variables: SQL> DEFINE DEFINE _DATE = "16-MAR-04" (CHAR) DEFINE _CONNECT_IDENTIFIER = "isc101" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
----or to display on one variable: SQL> DEFINE _PRIVILEGE DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) Uses UNDEFINE to remove variable definitions. How to use these variables with SQLPROMPT? SET SQLP [ROMPT] {SQL> | text} Note: SET SQLPROMPT is not supported in iSQL*Plus Variable substitution occurs each time SQLPROMPT is SET. If SQLPROMPT is included in glogin.sql, then substitution variables in SQLPROMPT are refreshed with each login or connect. You need the 'Select Any Table' privilege to successfully run the following example scripts. 1. To change your SQL*Plus prompt to display your connection identifier, enter: SQL> SET SQLPROMPT "_CONNECT_IDENTIFIER > " isc101 > 2. To set the SQL*Plus command prompt to show the current user, enter SQL> SET SQLPROMPT "_USER > " SCOTT > 3. To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter: SQL> SET SQLPROMPT "_DATE _USER _PRIVILEGE> " 17-MAR-04 SCOTT > 4. To change your SQL*Plus prompt to display a variable you have defined, enter: SQL> DEFINE Instance = Prod SQL> SET SQLPROMPT Instance> " Prod> 5. Since text in nested quotes is not parsed for substitution, to have a SQL*Plus prompt of your username, followed by "@", and then your connection identifier, enter: SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " SCOTT@isc101 > At present there is no predefined variable to display the database you are connected to.You can use the notes referenced below for the same.
How To Interpret The Time Format • • • •
goal: How To Interpret The Time Format fact: Oracle Server - Enterprise Edition 7 fact: Oracle Server - Enterprise Edition 8 fact: MS Windows NT
•
fact: SqlPlus
Fix: SQL> set timing on After the SQL statement is executed the time needed for processing the statement is printed into the next line. Interpretation: Elapsed: 00:00:00.32 ^ ^ ^ ^ | | | | | | | milliseconds | | seconds | minutes hours SPECIAL NOTE: The seconds field will represents the TOTAL TIME in seconds. Abstract: ELAPSED TIME SHOWS INCORRECTLY FOR DBMS_LOCK.SLEEP VALUES GREATER THAN 1 MINUTE The seconds overflow (60 seconds = 1 minute) will not reset the seconds counter so it's possible that the seconds field gets more than 2 digits (>60)! Example:
Elapsed: 01:05:3909.32
To find the actual seconds, the Hours and Minutes must be removed from the total. Above example: hours: 1 minutes: 5 seconds: 3909.35 - (1*3600) - (5*60) = 9.32 Converts to: hours: 1 minutes: 5 seconds: 9 milliseconds: 32
How to Display a Master Detail Relationship in One Row? PURPOSE How to display columns as rows? This is similar to other line-column transposition that may be in use. But here we use outer join to achieve this. SCOPE & APPLICATION
Developers and DBAs (For reporting) SQL> create table parent (ChildNo varchar2(5), parent varchar2(20)); insert insert insert insert insert insert
into into into into into into
parent parent parent parent parent parent
values values values values values values
('E0001', ('E0001', ('E0002', ('E0002', ('E0003', ('E0004',
'FATHER-JOHN'); 'MOTHER-NANCY'); 'FATHER-BLAKE'); 'MOTHER-SALLY'); 'FATHER-WILLIAM'); 'MOTHER-JULIE');
SQL> select * from parent; CHILD ----E0001 E0001 E0002 E0002 E0003 E0004
PARENT -------------------FATHER-JOHN MOTHER-NANCY FATHER-BLAKE MOTHER-SALLY FATHER-WILLIAM MOTHER-JULIE
SQL> select distinct c.childno, a.father, b.mother from 2 (select childno, parent father from parent where parent like 'FATHER%') a, 3 (select childno, parent mother from parent where parent like 'MOTHER%') b, 4 parent c 5 where c.childno=a.childno(+) 6* and c.childno=b.childno(+) CHILD ----E0001 E0002 E0003 E0004
FATHER -------------------FATHER-JOHN FATHER-BLAKE FATHER-WILLIAM
MOTHER -------------------MOTHER-NANCY MOTHER-SALLY MOTHER-JULIE
How To Calculate The Number of Days betweeen Two Dates Excluding Weekends? • •
Goal: How to calculate the number of days betweeen two dates excluding weekends fact: Embedded SQL
Fix: A table called DATETABLE has two columns BEGDATE and ENDDATE:-
rem Calculates the difference between two dates and gives you a number excluding weekends. rem This part calculates the difference in whole weeks and then subtracts out the weekends. rem rem (trunc(enddate,'D') - trunc(begdate+6,'D')) rem - (((trunc(enddate,'D') - trunc(begdate+6,'D'))/7)*2) rem rem This part calculates the number of work days in the end week. rem rem + decode(to_number(to_char(enddate,'D')),3,1,4,2,5,3,6,4,7,5,0) rem rem This part calculates the number of work days in the start week rem minus Sunday start day workaround. rem rem + decode(to_number(to_char(begdate,'D')),2,5,3,4,4,3,5,2,6,1,0) rem days eg. select (trunc(enddate,'D') - trunc(begdate+6,'D')) - (((trunc(enddate,'D') - trunc(begdate+6,'D'))/7)*2) + decode(to_number(to_char(enddate,'D')),3,1,4,2,5,3,6,4,7,5,0) + decode(to_number(to_char(begdate,'D')),2,5,3,4,4,3,5,2,6,1,0) days from datetable /
Setting the SQL*Plus Prompt to Show Instance Name (SID) How to set the SQL*Plus prompt to show your SID. set termout off col x new_value y select rtrim(instance,chr(0)) x from v$thread; set sqlprompt '&y SQL> ' set termout on Remember this is NOT dynamic. You need to reset it when you change instances, i.e. when you use the connect command to logon to another instance. To successfully run the above script, you need the Select Any Table privilege. NOTE: from version 9.2 this syntax can be used to SET SQLPROMPT "&_CONNECT_IDENTIFIER>"
How to Insert a Carriage Return in the Middle of a Select Statement
Goal Trying to do a long select that will ultimately result in 3 lines of output separated by carriage returns. For Example: Select 'D',val1, val2, '^M', 'F',val1, val3,'^M','A',val1,val4,'^M' from value_table. Pipes will be used instead of comma delimiters and the output will need to be looked like this: D|val1|val2|valx| F|val1|val3|valx| A|val1|val4|valx| Fix Basically, carriage return --> CHR(10) but has to be used with the concatenate symbol '||'. TESTCASE 3 below is probably the closest to what you are looking for: For Example: SQL> ed Wrote file afiedt.buf 1 select empno||','||ename||','||chr(10)||job 2* from emp SQL> / TESTCASE DETAILS: TESTCASE 1: SQL> ed Wrote file afiedt.buf 1 select empno||chr(10)||ename||chr(10) 2* from emp SQL> / EMPNO||CHR(10)||ENAME||CHR(10)||JOB -----------------------------------------7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN EMPNO||CHR(10)||ENAME||CHR(10)||JOB ------------------------------------------
7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE EMPNO||CHR(10)||ENAME||CHR(10)||JOB -----------------------------------------MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST ... etc TESTCASE 2: ========== SQL> ed Wrote file a 1 select empno||','||ename||','||job||chr(10) 2* from emp SQL> / EMPNO||','||ENAME||','||JOB||CHR(10) -------------------------------------------------------7369,SMITH,CLERK 7499,ALLEN,SALESMAN 7521,WARD,SALESMAN 7566,JONES,MANAGER 7654,MARTIN,SALESMAN 7698,BLAKE,MANAGER 7782,CLARK,MANAGER 7788,SCOTT,ANALYST 7839,KING,PRESIDENT 7844,TURNER,SALESMAN 7876,ADAMS,CLERK EMPNO||','||ENAME||','||JOB||CHR(10) -------------------------------------------------------7900,JAMES,CLERK
7902,FORD,ANALYST 7934,MILLER,CLERK 14 rows selected. TESTCASE 3: Closest to the format -- D|val1|val2|valx| -- would be something like this: SQL> ed Wrote file afiedt.buf 1 select empno||','||ename||','||chr(10)||job 2* from emp SQL> / EMPNO||','||ENAME||','||CHR(10)||JOB ---------------------------------------------------------7369,SMITH, CLERK 7499,ALLEN, SALESMAN 7521,WARD, SALESMAN 7566,JONES, MANAGER ... etc
How to show the values of a column in a row for both previous/current record Goal How To show the values of a column in a row for both previous/current record ? Suppose a customer wants to show the 1) Profit of the company in the current quarter and Last quarter in same row. . 2) Profit of the company in the current quarter and Next quarter in same row. . Fix Let us assume customer has the following table structure. SQL> desc QR_Profit Name Null? Type ----------------------------------------- -------- -------------------
YRQR DATE PROFIT NUMBER SQL> select * from QR_Profit; YRQR PROFIT --------- ---------31-DEC-02 10 31-MAR-03 11 30-JUN-03 12 30-SEP-03 12 Following query will show the " profit for current quarter and Last quarter in same row " SQL> select yrqr , profit,lag(profit,1,0) over (order by yrqr) Last_Quarter from QR_Profit; ; YRQR PROFIT LAST_QUARTER --------- ---------- -----------31-DEC-02 10 0 31-MAR-03 11 10 30-JUN-03 12 11 30-SEP-03 12 12 Following query will show "Profit in the current quarter and Next quarter in same row. ". SQL> select yrqr, profit,lead(profit,1,0) over (order by yrqr) Next_Quarter from QR_Profit; YRQR PROFIT NEXT_QUARTER --------- ---------- -----------31-DEC-02 10 11 31-MAR-03 11 12 30-JUN-03 12 12 30-SEP-03 12 0
How to Create a Matrix Report in SQLPLUS Goal When you query any table in relational database, the datas are represented as row format. That means each record in the table is represented in row. But to analyze the data the above format would not be enough , there you need data in 3-dimensional format i.e. to represent in rectangular format ( popularly know as matrix format ) Where you will have a row attribute, column attribute and cell which shows the values for the corresponding row and column attribute .
Eg. Col1 Col2 Col3 Col4 Row1 Row2 Row3 Row4 How to achieve the same in sqlplus without using any other tools? Note :- The same output format can be easily designed thru Oracle Reports writer . Fix Here I am showing an example shows the no.of people joined in a department on year basis. The query is based on Scott.EMP table. SQL> select empno,deptno,hiredate from emp; EMPNO DEPTNO HIREDATE ---------- ---------- --------7369 20 17-DEC-80 7499 30 20-FEB-81 7521 30 22-FEB-81 7566 20 02-APR-81 7654 30 28-SEP-81 7698 30 01-MAY-81 7782 10 09-JUN-81 7788 20 19-APR-87 7839 10 17-NOV-81 7844 30 08-SEP-81 7876 20 23-MAY-87 EMPNO DEPTNO HIREDATE ---------- ---------- --------7900 30 03-DEC-81 7902 20 03-DEC-81 7934 10 23-JAN-82 14 rows selected. Now the same query represented in matrix format . select deptno, sum(decode(to_char(hiredate,'RRRR'),'1980',1,0)) sum(decode(to_char(hiredate,'RRRR'),'1981',1,0)) sum(decode(to_char(hiredate,'RRRR'),'1982',1,0)) sum(decode(to_char(hiredate,'RRRR'),'1987',1,0)) from emp group by deptno
"1980", "1981", "1982", "1987"
DEPTNO 1980 1981 1982 1987 ---------- --------- ---------- ---------- ---------10 0 2 1 0 20 1 2 0 2 30 0 6 0 0 Which shows the no. of employee joined to departments in the above years. Note: - The only drawback here is the column can not be added automatically. That means for each column and corresponding cell values to display, then the column need to be defined in query.
How to Check a Particular Format for Any Field thru SQL Goal How to validate the data saved in a column ? Let us assume a table CUSTOMER with lot of records saved. We want to check the column CUSTID with a particular format like 99 .So that we can update all records of the table.column in the same format. Fix Let us take an example for table Customer SQL> desc customer Name Null? Type ----------------------------------------- -------CUSTID VARCHAR2(10) SQL> select * from customer; CUSTID ---------IN-CTC-001 IN-BNG-001 IN-BNG-12 IN-MUM-01 We need to check if any of these records keeps the format 'XX-XXX-999'' .Write the query to get the result SQL> SELECT custid FROM customer where TRANSLATE(CUSTID, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX-') ='XX-XXX-999'; CUSTID ---------IN-CTC-001 IN-BNG-001
View the Source Code Of A Procedure Goal Is it possible to provide a Grant such that User can view only the source code of Procedure but not execute it. Fix A Grant option would allow the user to execute the Procedure. To allow the User to just view the source code of the Procedure, try the following: 1) Create a view like: create view view_name as select text from all_source where type='PROCEDURE' and name='PROCEDURE-NAME'; 2) Grant select on the View created above. This way, the user can do a select of the View, and see the source code of the Procedure.
How to Find or Delete Duplicate Rows in a Table PROBLEM DESCRIPTION: How do you find or delete duplicate rows in a table? SOLUTION DESCRIPTION: The following SELECT statement will find and display all duplicate rows in a table, except the row with the maximum ROWID. The example uses the dept table: SELECT * FROM dept a WHERE ROWID <>(SELECT MAX(ROWID) FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc); The following statement will delete all duplicate rows in a table, except the row with the maximum ROWID: DELETE FROM dept a WHERE ROWID <> (SELECT MAX (ROWID) FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc);
Alternatively: DELETE FROM dept a WHERE 1 < (SELECT COUNT (deptno) FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc); EXPLANATION Using the pseudo column ROWID is the fastest way to access a row. ROWID represents a unique storage identification number for a single row in a table (Note: Two rows on different tables but stored in the same cluster may have the same rowid value). Duplicate rows which contain only null columns, however, will not be identified by either of the two methods above. In such a case, simply: DELETE FROM dept WHERE deptno IS NULL AND dname IS NULL AND loc IS NULL;
How do I create a flat ASCII file without rows wrapping? PROBLEM DESCRIPTION: You want to create a flat file with the output from a SQL query. When you execute the query, the rows are wrapped around. How can you prevent this wrap-around? SOLUTION DESCRIPTION: To store the SQL query results in a file, you need to enter the SPOOL command in SQL*Plus: SQL> SPOOL file_name If you do not follow the filename with a period and an extension, SPOOL adds a default file extension to the filename to identify it as an output file. The default varies with the host operating system. On most hosts it is LST or LIS. To create a flat file in SQL*Plus, you first must enter the following: SET commands in SQL*Plus: SET SET SET SET
NEWPAGE O SPACE 0 LINESIZE 80 PAGESIZE 0
SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF After entering these commands, use the SPOOL command as above to create the flat file. (If the line size is too small, you may set it to a larger number. If you do this, you may need to use another command 'SET BUFFER xxxx' where xxxx is the same size as the line size).
How do I create an output file with commas between the columns? You will need to produce a .SQL file for the table so that the table output will have commas between the columns.
How do I calculate an average date with time component? Problem Explanation: You want to calculate the difference between two average timestamps. Solution Description: Use a format mask without characters. If a table (x) has a date field (a) with two rows 01-jan-96 01:10 01-jan-96 01:20 SQL> select avg( to_number( to_char(a,'MMDDYYHHMI') ) ) from x; AVG(TO_NUMBER(TO_CHAR(A,'MMDDYYHHMI'))) --------------------------------------101960115 The average time of 1:10 and 1:20 gives 1:15 for the hours, date is the same. Solution Explanation: 1. The following SELECT gives you the seconds past midnight, then displays in
hh:mm:ss
select to_char(sysdate, 'sssss'), to_char(sysdate, 'hh:mm:ss') from dual; 2. The following SELECT will convert the seconds past midnight back to display as hh:mm:ss select substr(to_char(sysdate, 'sssss'),1,8), substr(to_char(sysdate, 'hh:mi:ss'),1,8), substr(trunc(to_char(sysdate, 'sssss')/3600), 1, 10), substr(trunc(mod(to_char(sysdate, 'sssss'),3600)/60), 1, 10),
substr(mod(mod(to_char(sysdate, 'sssss'),3600),60), 1, 10) from dual;
How to insert & character or special character into Database using SQL*Plus. • •
goal: How to insert & character or special character into Database using SQL*Plus. fact: Oracle Server - Enterprise Edition
Fix: Solution 1: If you are not using substitution variables (&1 &2 &3 etc.) you can do a "SET DEFINE OFF" or "SET SCAN OFF" to turn off the definition of the ampersand as a substitution variable. Example: SQL> SET DEFINE OFF SQL> INSERT INTO
How to embed Single Quote in a string
Problem Description: How do you embed single quotes ( ' ) into a character string? How do you concatenate a quote in SQL? How do you place quotes around a character string in a SQL query? How do you store an apostrophe into a character variable in PL/SQL? Problem Explanation: Example 1 When you issue either of the following SELECT statements: SQL> SELECT ' FROM dual; SQL> SELECT ''' FROM dual; the following error occurs: ORA-0xxx:
quoted string not properly terminated
Example 2 When you issue the following SELECT statement: SQL> SELECT ''character string in quotes'' FROM dual; the following error occurs: ORA-00923:
FROM keyword not found where expected
CONCATENATE CHR(39) TO CREATE LITERAL SINGLE QUOTE Solution Description: To create a single quote, concatenate CHR(39) to the string. Example 1 --------SQL> SELECT 'test' || CHR(39) || 'case' result 2> FROM dual; RESULT --------test'case Example 2 --------SQL> SELECT CHR(39) FROM dual; C '
Solution Explanation: To return the ASCII value of the single quote ( ' ): SQL> SELECT ASCII('''') FROM dual; ASCII('''') ----------39 USE 2 SINGLE QUOTES TO CREATE 1 SINGLE QUOTE Solution Description: Keep the following two rules in mind: 1. Enclose every character string in single quotes. The single quote is a string delimiter. 2. Inside a string literal, use two consecutive single quotes to create a literal single quote. Example 1 --------6 single quotes: 'test' || '''''' || 'case' ---> test''case 8 single quotes: 'test' || '''''''' || 'case' ---> test'''case You can also implement the above in the following way: 'test''case' 'test''''case'
---> test'case ---> test''case
Hence: a. To create a single quote, concatenate 4 single quotes: '''' The two single quotes in the middle define the single quote. The outside single quotes are the single quotes that must surround a string. Example 2 --------SQL> SELECT '''' FROM dual; ' ' Example 3 --------SQL> SELECT 'test' || '''' || 'case' result 2> FROM dual;
RESULT --------test'case b. To place single quotes around a character string, enclose the character string within 3 single quotes: ''' At the start of a character string: the first single quote defines the start of the character string; It is one of the two single quotes that surround the string. The second and third single quotes define the literal single quote. At the end of the character string: the first and second single quotes define the literal single quote. The third single quote closes the character string; it is the other single quote that surrounds the string. Example 4 --------SQL> SELECT '''character string in quotes''' result 2> FROM dual; RESULT ---------------------------'character string in quotes' Solution Explanation: More examples: Example 5 --------SRW.DO_SQL('SELECT DECODE(dname, ''NONE'', NULL, ''A'') FROM dept WHERE deptno = 10'); As a result, this is the SELECT statement sent to the database: SELECT DECODE(dname, 'NONE', NULL, 'A') FROM dept WHERE deptno = 10 Example 6 --------DECLARE a VARCHAR2(200); q CHAR(1) := ''''; BEGIN a := '''this is a ' || q || 'quoted'' string' || q; END; String "a" stores: 'this is a 'quoted' string'
How to do a case insensitive search using the 'LIKE' operator?
Problem Description: How can a user perform a case insensitive match using the LIKE comparison operator? Problem Explanation: The field in a table may be entered in different cases, for example, 'MacDonald' and MACDONALD' how can a single SQL query using the LIKE operator find all occurrences of the name MacDonald. Solution Description: You can use the UPPER () function to perform a case insensitive match as in the following condition; For example, to find an employee name in a string beginning with the letters 'SM' SQL > select * from emp where UPPER (ename) like 'SM%' ; Solution Explanation: The UPPER function converts the ename field results in the searched column to upper case. For example the strings 'smith' and 'Smith' and 'SMITH' will all become 'SMITH'.
How to Create Column Delimited Flat File Problem Description: How can SQL*Plus be used to create a delimited flat file? Problem Explanation: When moving data between different software products, it is sometimes necessary to use a "flat" file ( an operating system file with no escape characters, headings, or extra characters embedded ). CONCATENATE COLUMN SEPARATOR IN QUERY AND SPOOL Solution Description: Concatenate the desired column separator into the query. For example: To delineate column text with TABs: ----------------------------------SQL> select DEPTNO || CHR(9) || DNAME FROM DEPT; DEPTNO||CHR(9)||DNAME ------------------------------------------------------10 ACCOUNTING 20 RESEARCH
30 40
SALES OPERATIONS
The CHR() SQL character function is used to embed the TAB in the query text. To delineate column text with COMMAs: ------------------------------------SQL> select DEPTNO || ',' || DNAME FROM DEPT; DEPTNO||','||DNAME ------------------------------------------------------10,ACCOUNTING 20,RESEARCH 30,SALES 40,OPERATIONS To send your query results to a file, use the following SQL*Plus SET commands before running your query: SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SPOOL file_name < your query> SPOOL OFF SET TERMOUT ON Solution Explanation: SQL*Plus versions 3.0.x and 3.1.x do not have a SET command to specify a column separator to delineate text between columns. NOTE: The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to a maximum result of 255 characters. If either string is VARCHAR2, the result has datatype VARCHAR2 and is limited to a maximum result of 2000 characters. Solution Description: Use the SQL*Plus 3.2.x COLSEP SET command. For example:
To send your query results to a comma delimited flat file, use the following SQL*Plus commands: SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET COLSEP ',' SPOOL file_name < your query> SPOOL OFF SET TERMOUT ON Solution Explanation: SQL*Plus 3.2.x has the COLSEP SET command. Previous versions of SQL*Plus did not have COLSEP. This solution will send the query results to a file, with the text printed between the SELECTed columns as specified by the SET COLSEP command. If the COLSEP variable contains blanks or punctuation characters, you must enclose the text with single quotes.
Method to create .SQL file with comma-delimited output. SOLUTION DESCRIPTION: The SQL script below when executed will produce a second .SQL file for the table so that the listed table output will have comma's between the columns. REM ********************************************************************* ***** REM COMMA.SQL REM -----REM Generate SELECT statements using Catalog tables REM REM Usage: @COMMA tablename outputfilename REM REM tablename: Any table name (standard SQL wildcards accepted, e.g. % for all) REM outputfilename: Target SQL file name (no extension, .SQL REM extension supplied automatically REM ********************************************************************* *
SET SET SET SET SET
FEEDBACK OFF VERIFY OFF TERMOUT OFF PAGES 999 HEADING OFF
COL tabname NOPRINT COL seqno NOPRINT COL seq2 NOPRINT SELECT table_name tabname , 1 seqno , 0 seq2 , 'SELECT ' txt FROM user_tables WHERE table_name LIKE UPPER('&1') UNION SELECT table_name tabname , 2 seqno , column_id seq2 , DECODE(column_id ,1,'' ,'||'',''||')||column_name txt FROM user_tab_columns WHERE table_name LIKE UPPER('&1') and exists ( select 1 from user_tables where user_tables.table_name = user_tab_columns.table_name ) UNION SELECT table_name , 3 seqno , 0 seq2 , 'FROM '||table_name||';' txt FROM user_tables WHERE table_name LIKE UPPER('&1') ORDER BY 1,2,3 SPOOL &2..sql // SPOOL OFF SET SET SET SET
FEEDBACK ON VERIFY ON TERMOUT ON HEADING ON
How to Query top "N" rows ordered by a column Problem Description: What SQL SELECT statement do you use to retrieve the top "n" rows ordered by a particular "column"? Problem Explanation: Solution Description: To return exactly "n" rows ordered by a column, create a query similar to the following: SELECT * FROM table A WHERE n >= (SELECT COUNT(*) FROM table B WHERE B.column >= A.column) ORDER BY column DESC; where "n" is the number of rows to be retrieved and ordered, "table" is the name of the table and "column" is the name of the column. The first SELECT statement controls the number of rows, while the nested, second SELECT statement controls the condition. The query returns the first "n" ordered rows. If n=10 and Rows 8-12 share the same column value, this only return Rows 9 and 10. The above SELECT statement does not take duplicate column values into consideration. Solution Explanation: To return any row whose column value is one of the top "n" ordered column values, create a query similar to the following: SELECT * FROM table A WHERE n >= (SELECT COUNT (DISTINCT column) FROM column B WHERE B.column >= A.column) ORDER BY column DESC;
Specifying DISTINCT return all rows whose column (e.g. sal) value falls into one of the top "n" values. Hence, the query returns more than "n" rows when several rows share the same column value.
How do you convert a character field into a date format without losing the century data? Problem Description:
When you convert a character field into a date field, the century information is lost. How do you convert a character field into date format without losing the century data? Solution Description: To retain the four digit century information, you must convert the date field back into a character string. For example the query is: SQL> select TO_CHAR (HIREDATE, 'DDMONYYYY') from EMP Where HIREDATE = TO_DATE ('17NOV1981', 'DDMONYYYY');
The results of the query which show the full four character century are: TO_CHAR (HIREDATE,'DDMONYYYY') 17NOV1981
How do I set the SQL*Plus search path for SQL scripts? Answer Set SQLPATH environment variable. Add all the directories you want SQL*Plus to search for the SQL scripts, each separated by a semicolon. Example: SQLPATH = C:\ORANT\DBS;C:\APPS\SCRIPTS;C:\MYSCRIPTS; In Windows environment, if you are using 32-bit SQL*Plus then set this variable in the Registry, or if you are using 16-bit SQL*Plus then set this variable in the file Oracle.ini
How do I get the system date and time over DBLINK? You cannot use SYSDATE or USER, to get the SYSDATE, USER over DBLINK due to a known restriction with these functions. For this, you can use the following workaround: Step-1: On the remote machine, populate a table with the SYSDATE, USER, values using a stored procedure on the remote machine. CREATE TABLE db_table (mydate DATE); CREATE OR REPLACE PROCEDURE db_date as mydate date; BEGIN DELETE FROM db_table; SELECT SYSDATE INTO mydate FROM DUAL;
INSERT INTO db_table VALUES (mydate); END; Step2:
On local machine, select the data from the table which has been populated using the stored procedure. EXECUTE db_date@dblink SELECT mydate FROM db_table@dblink;
How do I execute SQL scripts in batch? You can execute any SQL script using the SQL*Plus command in a batch file. The format of the command is as follows: SQL*PlusExecutable USERNAME/PASSWORD@CONNECT_STRING Sqlscript
For Example: Plus31 scott/tiger@orcl @c: \script.sql
will execute the script script.sql on 'c' drive. Suppose the contents of script.sql is as follows: SELECT * FROM emp; host dir/p EXIT;
Executing the above example command will display the emp records in the SQL*Plus window and would list the directory in a command window. The 'exit' command in the last line will close the SQL*Plus session.
How do I display the time component of a date column? To retrieve the time information, use the TO_CHAR function with a format mask. Examples: SELECT To_Char(datecolumn, 'DDMONYY HH24:MI:SS') FROM mytable; :global.hi := To_Char(:time1, 'DDMONYY HH:MI:SS'); The data returns in the following format: 01SEP94 15:01:01
How do you skip a page in a SQL*Plus report? You can skip a page in SQL*Plus reports by making use of the following statement:
BREAK ON X SKIP PAGE; COMPUTE SUM OF Y ON X; SELECT fld1,fld2 FROM tab1,tab2 WHERE x=y GROUP BY fld1,fld2 ORDER BY fld1;
Is there a way to document my SQL command and file? You can do this in two ways : 1. SQL comment delimiters (/*........*/) EX: SQL> select ename /*this is a comment delimiter example*/ from /*This is another example*/ emp; 2. REMARK Begins a remark in a command file. Must appear at the beginning of a line and ends at the end of the line. A line cannot contain both a remark and a command. For example: get rem.sql 1 Rem This is an example of how REM is used. 2 Column sal format $99,999.99 heading 3 'Monthly|salary' 4 Compute sum od sal on deptno 5 Rem This sql script will total the salaries 6 Rem of all employees in each department 7 Select deptno, ename, sal from emp 8 order by deptno;
How do I include the current date and time in a SQL*Plus report heading? The following SQL*Plus command file segment shows how to put the current date into a variable and then include that variable in the title. To get the time, just change the mask in the to_char function to include that as well. COLUMN SYSDATE new_value today SELECT To_Char(SYSDATE,'mm/dd/yy') "sysdate" FROM DUAL / TTITLE left today center 'MY HEADING OR TITLE' skip 2
The use of "new_value" shown here applies in general to any value selected into a column specified in the COLUMN xxx NEW_VALUE command. It can be used to display various data in the title of a report.
How To Trim Lines When Spooling To remove trailing blanks at the end of each line enter the following statement on the command prompt before the spool command: SQL> set trimspool on
How to insert ‘&’ character or special character into Database using SQL*Plus Solution 1: If you are not using substitution variables (&1 &2 &3 etc.) you can do a "SET DEFINE OFF" or "SET SCAN OFF” to turn off the definition of the ampersand as a substitution variable. Example: SQL> SET DEFINE OFF SQL> INSERT INTO
Solution 2: If you are using substitution variables, set the substitution character to one which will not be encountered when inserting data. Example: SQL> SET DEFINE % SQL> INSERT INTO
Solution 3: If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON, then SQL*Plus will understand the special character following the escape symbol is to be treated as a regular character. Example: SQL>set escape on SQL>show escape escape "\" (hex 5c) SQL> INSERT INTO temp_table VALUES ('select * from emp where ename = \& 1'); 1 row created.
How to Insert Special Characters Into Database First pay attention the database you use is able to deal with the special characters you want to insert. That means you have to check if the special character is included in your database characterset.
Here just an example how to insert these special characters: Assume you want to insert the following sample string, consisting of several single characters, which are concated to one string. -Ob?''!#*)EBK+`#a"^?#c\QH'
To be able to do this, mask each single ' (single quote) with just one preceeding '(single quote).If just two ''(two single quotes) signs have to be inserted use four '''' (four single quotes): The following will work: SQL>insert into test VALUES ('-Ob?''''!#*)EBK+`#a"^?#c\QH'''); 1 row created. SQL> select * from test; A --------------------------Ob?''!#*)EBK+`#a"^?#c\QH'
Be aware of the characters (' at the beginning and the characters ') at the end of the string you want to insert.
How To Create a Flat File In SQL*Plus Without Showing Statement or ''Spool Off'' Create a script, e.g. report.sql, with the following contents: set NEWPAGE 0 set SPACE 0 set LINESIZE 80 set PAGESIZE 0 set ECHO OFF set FEEDBACK OFF set HEADING OFF spool report.txt select sysdate from dual; spool off
<--- your SQL statement here
Run the script from SQL*Plus: SQL> @report.sql
How to SELECT Columns as Rows Using SQL Example:
Using a UNION and VIEW should do the transformation like: create table p ( product varchar(16), attr1
varchar(16),
attr2 insert into p values ( insert into p values ( insert into p values ( commit;
varchar(16), 'product_1', 'product_2', 'product_3',
attr3 varchar(16) ); 'attr_11', 'att_12', 'attr_13'); 'attr_21', 'att_22', 'attr_23'); 'attr_31', 'att_32', 'attr_33');
create view vv as select * from (select product , attr1 from p union (select product , attr2 from p union (select product , attr3 from p ); Output: SQL> select * from vv; PRODUCT ---------------product_1 product_1 product_1 product_2 product_2 product_2 product_3 product_3 product_3
ATTR1 ---------------att_12 attr_11 attr_13 att_22 attr_21 attr_23 att_32 attr_31 attr_33
How to Change the Displayed Font in SQL*Plus (GUI) To Change the Windows GUI Font: 1. Select Run from the the Start menu and then enter regedit in the Open field and Click OK to start the registry Editor. 2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. Note:If you have more than one Oracle installation, you must select the HOME entry associated with the Windows GUI you want to change. HOME0 is the registry entry for an Oracle installation. A subsequent Oracle installation will have the registry entry HOME1 and the next HOME2 and so on. Changes only affect a SQL*Plus Windows GUI started from the associated Oracle installation, so you can use different settings for each Oracle installation. 3. Click New String Value in the Edit menu. A new string value, with the default name, NewValue #1 is created at the bottom of the right pane of the Registry Editor. The default name of the new string value is selected ready for you to replace with the name you want. 4. Enter SQLPLUS_FONT as the name of the new font face string value.If you miskey the name or inadvertently enter it in mixed or lower case, you can edit the name by selecting Rename from the Edit menu. 5. Click Modify from the Edit menu or press Enter again to display the Edit String dialog.
6. Enter the font name you want to use, such as Courier New, in the Value Data: field. SQL*Plus will use the new font the next time you start the SQL*Plus Windows GUI.
Modify Password and Suppressing Typed Password in SQLPLUS Use sqlplus functionality passw just enter on sqlplus prompt: SQLPLUS> passw
and new password wil not be echoed.
How to Select and Display a Certain Number of Records Problem Description Customer needs to select from a table and display only a limited number of records. Example would be select all Smiths from address table, but only display the first 10 or what ever. Solution Description Several solutions are available, from very simple to slightly complex Solution 1 (simple) SQL> select empno from emp where empno like '%7%' and rownum <= 10; EMPNO 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 10 rows selected.
Solution 2 SQL> l 1 declare 2 v_counter number(3):=+0; 3 v_empno emp.empno%type; 4 cursor emp_cursor is 5 select empno 6 from emp 7 where empno like '7%'; 8 begin 9 open emp_cursor;
10 loop 11 exit when emp_cursor%notfound; 12 fetch emp_cursor into v_empno; 13 v_counter := v_counter +1; 14 exit when emp_cursor%notfound or v_counter > 10; 15 dbms_output.put_line(v_empno ||' ' || v_counter); 16 end loop; 17 close emp_cursor; 18* end; SQL> / 7369 1 7499 2 7521 3 7566 4 7654 5 7698 6 7782 7 7788 8 7839 9 7844 10 PL/SQL procedure successfully completed.
Solution 3 set serveroutput on accept select_info prompt 'please enter select: ' accept where_info prompt 'please enter where: ' declare v_counter number(3):=+0; v_&select_info emp.&select_info%type; cursor emp_cursor is select &select_info from emp where &select_info like &where_info; begin open emp_cursor; loop exit when emp_cursor%notfound; fetch emp_cursor into v_&select_info; v_counter := v_counter +1; exit when emp_cursor%notfound or v_counter > 10; dbms_output.put_line(v_&select_info ||' ' || v_counter); end loop; close emp_cursor; end; /
How to Number Rows After They Have been Sorted
Problem description SQL*Plus users sometimes want to have rows numbered in a sorted order. This can't be achieved using the ROWNUM pseudo column, since the ordering is done after the values for ROWNUM had been assigned to rows as they were selected in random order. This is illustrated in the following example. The first selection shows the unsorted selection of ename and rownum from emp, and the second selection illustrates how ROWNUM is useless as a numbering device for ordered selections because the values of ROWNUM are assigned before the ordering is performed. SQL> select ename, rownum from emp; ENAME ROWNUM ALLEN 1 JONES 2 BLAKE 3 CLARK 4 KING 5 ADAMS 6 JAMES 7 FORD 8 SQL> select ename, rownum from emp order by ename; ENAME ROWNUM ADAMS 6 ALLEN 1 BLAKE 3 CLARK 4 FORD 8 JAMES 7 JONES 2 KING 5
Solution Explanation The following select statement achieves the desired ordering/numbering: SQL> select A.ename, count(*) position 2 from emp A, emp B 3 where A.ename > B.ename 4 or A.ename = B.ename and A.empno >= B.empno 5 group by A.empno, A.ename 6 order by A.ename, A.empno; ENAME POSITION
ADAMS 1 ALLEN 2 BLAKE 3 CLARK 4 FORD 5 JAMES 6 JONES 7 KING 8
This method works by counting the number of records that a particular record is superior than (or equal to) in alphabetical order of employee name. The empno column acts as a unique key to discern between records in case identical names occur in the table. This would not be an efficient method against tables with large numbers of rows. Another alternative is to create an index on the order column, and include a meaningless where clause to force use of the index. SQL> create index sort on emp (ename); Index created. SQL> select ename, rownum from emp where ename > ' '; ENAME ROWNUM ADAMS 1 ALLEN 2 BLAKE 3 CLARK 4 FORD 5 JAMES 6 JONES 7 KING 8
The where clause in the query forces SQL*Plus to use the index created on the ename column, and since the index is used, the rows are returned in ascending order. Since this method depends on the inherent order of the index, it cannot be used to return rows numbered in descending order. The last method presented takes advantage of a feature of the database kernel optimizer. SQL> select rownum, ename 2 from emp , dual 3 where emp.ename = dual.dummy (+); ROWNUM ENAME 1 ADAMS 2 ALLEN 3 BLAKE 4 CLARK 5 FORD 6 JAMES 7 JONES
8 KING
The optimizer evaluates the outer join in this example by using a sort/merge join, which results in the desired sorted order.
Missing Label when Computing is Done on the First Column PROBLEM: Your label is missing when doing computes of the first column on a report break. In other words,
suppose you do the following in SQL*Plus: SQL> break on report SQL> compute count of deptno on report SQL> select * from dept; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 4
Notice the lack of the 'count' label. This happens in report breaks where the computation is performed on the first column. Normally, the compute labels for a report break always appear in the first column. For example, if I had performed the count computation on the second column, DNAME, I would have gotten: SQL> clear computes Computes cleared SQL> compute count of dname on report SQL> select * from dept; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON count 4
In this case, no computation is performed on the first column so SQL*Plus is free to place the "count" label in the first column. RESOLUTION: The following approach was proposed: use a "dummy" first column to hold the compute label when
you want a compute of the first column on a report break. Here's how it works with our original example:
SQL> clear computes computes cleared SQL> clear breaks breaks cleared SQL> break on report SQL> compute count of deptno on report SQL> col summary format a7 SQL> select ' ' summary, dept.* from dept; SUMMARY DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON count 4
Thus, we create a dummy left most column named SUMMARY (or whatever is appropriate) to hold the "count" label. Notice that as the first element of the SELECT list we specify the constant expression ' ' (a blank string) and give it a column alias of SUMMARY.
How to exit multiple connection attempts to DB from SQL*Plus when first connection attempt fails. Purpose The purpose of this article is to demonstrate how to make only one connection attempt to the database and then exit so SQL*Plus does not bring up the username prompt again. This technique can then be used in scripts or batch files to automatically handle failing SQL*Plus connections e.g. In backup scripts or batch jobs. SQL*Plus example from Unix Prompt sqlplus /nolog <<EOF whenever sqlerror exit connect scott/tiger@notthere EOF
SQL*Plus example for Windows
Create a SQL Script connect.sql as follows: whenever sqlerror exit connect scott/tiger@notthere
From DOS Prompt, Start this script as follows:
sqlplus /nolog @connect.sql
How to restrict SQL commands using PRODUCT_USER_PROFILE It is possible to disable the following commands: ALTER, AUDIT, CREATE, DELETE, DROP, GRANT, INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, UPDATE, VALIDATE.
Example To disable the SELECT statement for SCOTT: 1. Insert in the table PRODUCT_USER_PROFILE the next row: SQL> insert into product_user_profile values ('SQL*Plus', 'SCOTT', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
2. When the user SCOTT tries to execute the command SELECT he will obtain the error: SP2-0544: Invalid command: SELECT.
How to Get a Heading After Set Pagesize 0 When setting PAGESIZE to 0 then the column headings will be suppressed. Use the following example to generate a heading: SQL-script (t1.sql) set pagesize 0 set feedback off select 'Number Name' from dual; select '---------- -------' from dual; select empno, ename from emp / SQL> @t1 => Result: Number Name ---------- ------7369 SMITH 7499 ALLEN
7521 WARD 7566 JONES
How to Include a New Line Character in an Insert Statement from SQL*Plus Concatenate the ASCII value CHR (10) in between the data where the carriage return is required. E.G. INSERT INTO
