• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

SqlPlus script fails to create objects

New to Oracle and SqlPlus, I need to create a release script for an exising set of files for tables/views/functions/etc.  I'm finding that unlike Toad, when SqlPlus processes these files, the objects are not created if the file does not end with "/".  I can ask that all the files be corrected, but I thought there might be some SqlPlus option that handles this.  Is there any way around this in SqlPlus?
0
jkavx
Asked:
jkavx
1 Solution
 
OP_ZaharinCommented:
- apart from using '/' you can put semicolon ';'at the end of each statement in your script to execute each of the sql statements.

- eg:
select * from o_table1;
create table table2 as select * from table1;
0
 
jkavxAuthor Commented:
These are ddl scripts.  One script per object - table, view, function, procedure.  The problem files end with ";" but without a final line with "/", the SqlPlus commands, although successful, don't actually create the object.
0
 
OP_ZaharinCommented:
- i've done my test on the following script without having to use slash '/':
- my script contain:
CREATE TABLE "TEST"."TEST2"
   (      "LEGACYKEY" VARCHAR2(100),
      "CODE" VARCHAR2(10),
      "CODE2" VARCHAR2(10),
      "CODE3" VARCHAR2(10)
   );

- and i execute it this way:
SQL> @c:\temp\test2.sql;

Table created.

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
OP_ZaharinCommented:
- kindly note also that if you are executing pl/sq block, you need to use slash '/' as mention in the Oracle doc:

"You must include a semicolon at the end of each SQL command and a slash (/) on a line by itself after each PL/SQL block in the file. You can include multiple SQL commands and PL/SQL blocks in a script"
http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch6.htm
0
 
tangchunfengCommented:
$ cat a.sql
select count(*) from dual
/
exit

$ sqlplus scott/tiger @a.sql


  COUNT(*)
----------
         1

This works.
0
 
slightwv (䄆 Netminder) Commented:
Check for blank lines.

The semi-colon (';') MUST be at the end of the command immediately on the next line.

The slash ('/') tells sqlplus to run whatever comment is current in the buffer.  With only one command per script a blank line after the command will store the script in the command buffer and a '/' will execute it later.

I don't like this idea since each script should be able to execute by itself but since you asked it there was a way around it:  If you have some master script that calls them individually you can always add the '/' in the master script after the individual ones.

master.sql
-------
@file1
/

@file2
/

etc...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now