SQL Plus - Revision history

WikiSysop at 05:24, 5 July 2015

2015-07-05T05:24:02Z

← Older revision		Revision as of 05:24, 5 July 2015
Line 1:		Line 1:
-	SQL*Plus is the [[Oracle Database]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer has a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.	+	SQL*Plus is the [[Oracle Database]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, [[SQL Developer]] has a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.

	==Setting Up SQL Plus==		==Setting Up SQL Plus==
Line 24:		Line 24:
	</pre>		</pre>

		+	The difference between long and maxdata (in the above example, 60,000-55,000=5,000) gives the space for the other columns in the rest of the row
	===Changing the Prompt===		===Changing the Prompt===

2015-07-05T05:19:24Z

← Older revision		Revision as of 05:19, 5 July 2015
Line 5:		Line 5:
	SQL Plus has set of options that can be used to tailor the output. You can set these up to allow you to display things on screen or to create batch files.		SQL Plus has set of options that can be used to tailor the output. You can set these up to allow you to display things on screen or to create batch files.

-	===Create a file with only data===	+	===Create a file with only data from your SQL===

	To remove all headings, feedback etc. from the output. If you have just started SQL Plus:		To remove all headings, feedback etc. from the output. If you have just started SQL Plus:

2011-12-11T17:21:19Z

← Older revision		Revision as of 17:21, 11 December 2011
Line 77:		Line 77:
	spool off		spool off
	</pre>		</pre>
		+
		+
		+	Articles about SQL:
		+
		+	* [[SQL Plus]]
		+	* [[Creating Comma Seperated Lists]]
		+	* [[Sampling a Table]]
		+	* [[Tree Queries]]
		+
		+	* [[Example Country Table]]
		+	* [[SQL Developer]]

2011-12-11T16:11:20Z

Set a Spool Filename with Database Name and Timestamp

← Older revision		Revision as of 16:11, 11 December 2011
Line 64:		Line 64:
	===Set a Spool Filename with Database Name and Timestamp===		===Set a Spool Filename with Database Name and Timestamp===

-	Set a SQL*Plus ~~column~~ by a database query and use this as part of the name of the file:	+	Set a SQL*Plus substitution variable by a database query and use this as part of the name of the file:

	<pre>		<pre>

2011-12-11T16:02:23Z

← Older revision		Revision as of 16:02, 11 December 2011
Line 69:		Line 69:
	column colFileSuffix new_value FileSuffix		column colFileSuffix new_value FileSuffix

-	select name \|\| '_' \|\| to_char(sysdate,'~~YYYYMMDD_HH24MISS~~') colFileSuffix	+	select
		+	name\|\|'_'\|\|to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS') colFileSuffix
	from v$database;		from v$database;

2011-12-11T01:13:34Z

← Older revision		Revision as of 01:13, 11 December 2011
Line 1:		Line 1:
-	SQL Plus is the [[Oracle Database]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer is a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.	+	SQL*Plus is the [[Oracle Database]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer has a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.

	==Setting Up SQL Plus==		==Setting Up SQL Plus==
Line 43:		Line 43:

	===Editing Your SQL===		===Editing Your SQL===
-	Use your favourite text editor to edit files - for instance - TextPad - by altering the Sql*Plus system variable:	+	Use your favourite text editor to edit files - for instance - TextPad - by altering the SQL Plus system variable:

	<pre>		<pre>

2011-12-11T01:08:37Z

← Older revision		Revision as of 01:08, 11 December 2011
Line 43:		Line 43:

	===Editing Your SQL===		===Editing Your SQL===
-	Use your favourite text editor to edit files - for instance - TextPad:	+	Use your favourite text editor to edit files - for instance - TextPad - by altering the Sql*Plus system variable:

	<pre>		<pre>
Line 60:		Line 60:
	/* Post 10gR2 client */		/* Post 10gR2 client */
	set serveroutput on size unlimited		set serveroutput on size unlimited
		+	</pre>
		+
		+	===Set a Spool Filename with Database Name and Timestamp===
		+
		+	Set a SQL*Plus column by a database query and use this as part of the name of the file:
		+
		+	<pre>
		+	column colFileSuffix new_value FileSuffix
		+
		+	select name \|\| '_' \|\| to_char(sysdate,'YYYYMMDD_HH24MISS') colFileSuffix
		+	from v$database;
		+
		+	spool ReportFile_&&FileSuffix
		+	...commands...
		+	spool off
	</pre>		</pre>

2011-07-28T11:59:33Z

← Older revision		Revision as of 11:59, 28 July 2011
Line 1:		Line 1:
-	SQL Plus is [[Oracle]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer is a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.	+	SQL Plus is the [[Oracle Database]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer is a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.

	==Setting Up SQL Plus==		==Setting Up SQL Plus==

2011-07-28T11:57:15Z

← Older revision		Revision as of 11:57, 28 July 2011
Line 1:		Line 1:
-	SQL Plus is Oracle's supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer is a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.	+	SQL Plus is [[Oracle]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer is a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man.

	==Setting Up SQL Plus==		==Setting Up SQL Plus==

2011-06-18T22:29:04Z

← Older revision		Revision as of 22:29, 18 June 2011
Line 16:		Line 16:
	===Writing out "LONGs"===		===Writing out "LONGs"===

-	To increase the length of a LONG displayed in SQL Plus:	+	To increase the length of a LONG column displayed in SQL Plus:

	<pre>		<pre>
Line 40:		Line 40:
	<pre>		<pre>
	set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "		set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
		+	</pre>
		+
		+	===Editing Your SQL===
		+	Use your favourite text editor to edit files - for instance - TextPad:
		+
		+	<pre>
		+	define _editor=C:\Progra~1\TextPa~1\Textpad.exe
		+	</pre>
		+
		+	===Show Output from PL/SQL===
		+	To see output from PL/SQL (and see as much as you can):
		+
		+	<pre>
		+	/* Pre 10gR2 client */
		+	set serveroutput on size 1000000
		+	</pre>
		+
		+	<pre>
		+	/* Post 10gR2 client */
		+	set serveroutput on size unlimited
	</pre>		</pre>