OraFAQ Forum: Client Tools » sqlplus script end of file

Home » Other » Client Tools » sqlplus script end of file (Oracle 11 g2, Windows 2008R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlplus script end of file [message #625590]

Fri, 10 October 2014 13:26

anncao
Messages: 87
Registered: August 2013

Member

We have a lot of sqlplus scripts that spool data to text file.
I have a question about the last few lines that exit the script.

Here is an simplized example of our script:

set heading off
set feedback off
set newpage none
set echo off
set termout off
set verify off
set trimspool on

spool &&1.
Select s.student_number , s.middle_name , s.last_name,s.school_number
from students s;
spool off
/
exit
/

My question is for the last 5 lines, is it a good practice or right way of doing so? I know semicolon means to run that sql statement, what does the first / mean here? and second / mean?

The reason I asked this is because recently we found out one of scripts ends with
something like below:

spool &&1.
Select s.student_number , s.middle_name , s.last_name,s.school_number
from students s;
/
exit
/

It caused the script to run twice, I found out is because the use of ; and / together made it run twice.
Also some script use a ; after exit, then /, is it necessary to use the ;

Thanks

Report message to a moderator

Re: sqlplus script end of file [message #625591 is a reply to message #625590]

Fri, 10 October 2014 13:50

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

My question is for the last 5 lines, is it a good practice or right way of doing so? I know semicolon means to run that sql statement, what does the first / mean here? and second / mean?

They will re-execute the SELECT.

Quote:

Also some script use a ; after exit, then /, is it necessary to use the ;

You don't need ';' to execute SQL*Plus command, only for SQL statements (if you don't use /).
'/' does apply ONLY on SQL or PL/SQL statements.

Report message to a moderator

Re: sqlplus script end of file [message #625592 is a reply to message #625591]

Fri, 10 October 2014 14:48

anncao
Messages: 87
Registered: August 2013

Member

Thanks, but with a spool off in between like below, will the first / run the SQL again? I assume not.
but what its usage here.

The second / I think it means the end of the file, do I really need it.
or what is the best way to end this script?

Thanks

Select s.student_number , s.middle_name , s.last_name,s.school_number
from students s;
spool off
/
exit
/

Report message to a moderator

Re: sqlplus script end of file [message #625593 is a reply to message #625592]

Fri, 10 October 2014 14:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what do you own tests reveal?

Report message to a moderator

Re: sqlplus script end of file [message #625594 is a reply to message #625593]

Fri, 10 October 2014 14:53

anncao
Messages: 87
Registered: August 2013

Member

My own test shows the output has the same number of records in the select statement if I use
below:

Select s.student_number , s.middle_name , s.last_name,s.school_number
from students s;
spool off
/
exit
/

So that means only ran one time. If I put / right after; it will run twice.
But now I just want to know what in above statement the first and second / mean here?

Thanks

[Updated on: Fri, 10 October 2014 14:54]

Report message to a moderator

Re: sqlplus script end of file [message #625595 is a reply to message #625592]

Fri, 10 October 2014 15:04

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Thanks, but with a spool off in between like below, will the first / run the SQL again? I assume not.

Don't assume, test!

Quote:

but what its usage here.

It is a script bug.

Quote:

The second / I think it means the end of the file

No it does mean that.
It means nothing.
Anything after "exit" is even not read.

Report message to a moderator

Re: sqlplus script end of file [message #625596 is a reply to message #625594]

Fri, 10 October 2014 15:05

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

So that means only ran one time. If I put / right after; it will run twice.
But now I just want to know what in above statement the first and second / mean here?

I already answered this but you don't believe me.
If you don"t believe what we say then test and do not post in a forum.

Even better, read the documentation.

Report message to a moderator

Re: sqlplus script end of file [message #625597 is a reply to message #625596]

Fri, 10 October 2014 15:29

anncao
Messages: 87
Registered: August 2013

Member

You didn't answer that clearly.

Report message to a moderator

Re: sqlplus script end of file [message #625598 is a reply to message #625597]

Fri, 10 October 2014 15:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

[oracle@localhost ~]$ cat slash.sql
set term on echo on
spool slash.lis
Select  count(*) from all_objects;
spool off
/
exit 
/
[oracle@localhost ~]$ sqlplus user1/user1 @slash.sql

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 10 13:25:44 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool slash.lis
SQL> Select     count(*) from all_objects;

  COUNT(*)
----------
     73651

SQL> spool off
SQL> /

  COUNT(*)
----------
     73651

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$

Report message to a moderator

Re: sqlplus script end of file [message #625600 is a reply to message #625598]

Fri, 10 October 2014 15:58

anncao
Messages: 87
Registered: August 2013

Member

Thanks, Blackswan,

Report message to a moderator

Re: sqlplus script end of file [message #625602 is a reply to message #625600]

Fri, 10 October 2014 16:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

notice difference from previous post. This time no trailing slash on the last line

[oracle@localhost ~]$ cat slash.sql
set term on echo on
spool slash.lis
Select  count(*) from all_objects;
spool off
/
exit 
[oracle@localhost ~]$ sqlplus user1/user1 @slash.sql

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 10 14:49:28 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool slash.lis
SQL> Select     count(*) from all_objects;

  COUNT(*)
----------
     73651

SQL> spool off
SQL> /

  COUNT(*)
----------
     73651

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$

Report message to a moderator

Re: sqlplus script end of file [message #625603 is a reply to message #625602]

Fri, 10 October 2014 16:56

anncao
Messages: 87
Registered: August 2013

Member

It looks the result is the same, so the last / did nothing, shall we remvoe it?

Thanks

[Updated on: Fri, 10 October 2014 16:57]

Report message to a moderator

Re: sqlplus script end of file [message #625604 is a reply to message #625603]

Fri, 10 October 2014 17:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

WE?
I already did remove it.

What prevent YOU fro doing your own actual testing to see what works & what does not work?

Do you realize that SQL & PL/SQL are two completely different languages?

With plain SQL you don't want both ";" & "/", but with PL/SQL, both are required.

[oracle@localhost ~]$ cat  hello.sql
set term on echo on serveroutput on
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO WORLD!');
END;
/
EXIT
[oracle@localhost ~]$ sqlplus user1/user1 @hello.sql

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 10 15:08:29 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DECLARE
  2  BEGIN
  3  DBMS_OUTPUT.PUT_LINE('HELLO WORLD!');
  4  END;
  5  /
HELLO WORLD!

PL/SQL procedure successfully completed.

SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$

Report message to a moderator

Re: sqlplus script end of file [message #625605 is a reply to message #625604]

Fri, 10 October 2014 17:17

anncao
Messages: 87
Registered: August 2013

Member

Thanks, I did run the test, but without using set term on echo on. So I only verified the records in the output file having duplicates or not to see if it runs once or twice.
I am new to Oracle, so a lot to learn. I know I can read doc and practice this, but it may take me a whole afternoon.
While I am having same time a lot of other tasks to do.
Thought using this forum as a way to get answer quick and also a way to learn.

Thanks

Report message to a moderator

Re: sqlplus script end of file [message #625606 is a reply to message #625605]

Fri, 10 October 2014 17:17

anncao
Messages: 87
Registered: August 2013

Member

Also searched on the web, thought this is helpful too.
From my understanding, all the SQL statement don't need forward slash as they will run automatically at the end of semicolons, including DDL, DML, DCL and TCL statements. For other PL/SQL blocks, including Procedures, Functions, Packages and Triggers, because they are multiple line programs, Oracle need a way to know when to run the block, so we have to write a forward slash at the end of each block to let Oracle run it.

Report message to a moderator

Re: sqlplus script end of file [message #625607 is a reply to message #625606]

Fri, 10 October 2014 17:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>because they are multiple line programs,

Normally Production SQL statements are multiple lines to improve readability.

Report message to a moderator

Previous Topic:	How to find the List of Queries from the History used before a Day
Next Topic:	SQL DATA MODEL IS NOT ENABLE!?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 17:08:02 CST 2025