Hi, Our DBA’s are unhappy with us (programmers) because we frequently
forget to turn off the Formatter Header before extracting source to be
moved to our production databases. The DBA’s use SQLplus to install,
which chokes when it finds the Formatter Header after the CREATE OR
REPLACE and before PACKAGE… Is there some way we can get it to
generate the Formatter Header comment in a fashion that won’t offend
SQLplus?
You can remove the formatter header totally.
Groetjes,
Wim
On Tue, Sep 7, 2010 at 21:55, Wendy Penfold wrote:
Hi, Our DBA's are unhappy with us (programmers) because we frequently
forget to turn off the Formatter Header before extracting source to be
moved to our production databases. The DBA's use SQLplus to install,
which chokes when it finds the Formatter Header after the CREATE OR
REPLACE and before PACKAGE... Is there some way we can get it to
generate the Formatter Header comment in a fashion that won't offend
SQLplus?I’m trying to figure out why a commented line would cause SQL*Plus to
choke. It would be good to have an example case so we can be sure the script
being created isn’t corrupt.
Any chance they have an ampersand (&) in the comment? SQL*Plus reads &stuff as a
variable even when commented out. Causes me problems with code my users send me
all the time.
Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
“Please consider your environmental responsibility before printing this e-mail”
In which case the SQL Plus command set escape off should fix this – and I
believe there is an option in toad for this
SEE ATTACHMENT
Interesting – may be a sql plus bug, or at least syntax toad supports that
sql plus does not (i.e. we’re smarter) – let me explain:
Run this code under sql plus and it works:
CREATE OR REPLACE
PACKAGE TEST_1
AS
procedure junk;
end;
/
show error
CREATE OR REPLACE
PACKAGE BODY TEST_1
AS
procedure junk
is
begin
null;
end;
end;
/
show error
now add comments and run this code:
CREATE OR REPLACE /* Formatted on 9/8/2010 1:29:35 PM (QP5 v5.142.912.7011) */
PACKAGE TEST_1
AS
procedure junk;
end;
/
show error
CREATE OR REPLACE /* Formatted on 9/8/2010 1:29:35 PM (QP5 v5.142.912.7011) */
PACKAGE BODY TEST_1
AS
procedure junk
is
begin
null;
end;
end;
/
show error
get errors as follows:
SQL> @dummy
Warning: Package created with compilation errors.
SP2-0042: unknown command “end” - rest of line ignored.
Warning: Package created with compilation errors.
Errors for PACKAGE TEST_1:
LINE/COL ERROR
It looks like if you put the comment after the name and before the AS it is OK
also. It looks like SQ*Plus croaks on t having the comment between REPLACE and
PACKAGE;
Weird. It’s reacting to the first semicolon it sees. It’s like the
SQLPlus parser fails to detect that it’s a PL/SQL module being defined,
so it scans to the first semicolon (as if it was a CREATE VIEW or something) and
then sends that off to the database to be compiled. And naturally the database
says that’s not a valid package or package body. Then the SQLPlus parser
continues through the script, and encounters “end;” which also
doesn’t make any sense as a standalone statement.
Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592
This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.
All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”. Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying this e-mail or any attachment.
The information contained in this email may be subject to the export control laws and regulations of the United States, potentially including but not limited to the Export Administration Regulations (“EAR”) and sanctions regulations issued by the U.S. Department of Treasury, Office of Foreign Asset Controls (“OFAC”). As a recipient of this information you are obligated to comply with all applicable U.S. export laws and regulations.