Home » SQL & PL/SQL » SQL & PL/SQL » SQL code (oracle10g, 10.2.0.2.0, AIX52)
SQL code [message #428367] Wed, 28 October 2009 02:31 Go to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Hi,

I have created a sql file with test.sql file name. In that the sql code is as below.

spool test.log;
set termout off;
set echo on;
TIMING START test1;

/*--- ALTER column to NOT NULL ---*/

ALTER TABLE test1
MODIFY
(
col1 NOT NULL,
col2 NOT NULL
);

COMMIT;

TIMING STOP test1;
spool off;



Above code is there in test.sql and when i have run the same with one of the database user, its giving the log as below.

SQL> ALTER TABLE test1
2
SQL> MODIFY
SP2-0042: unknown command "MODIFY" - rest of line ignored.
SQL>
SQL> (
2
SQL> col1 NOT NULL,
SP2-0734: unknown command beginning "col1¿" - rest of line ignored.
SQL>
SQL> col2 NOT NULL
SP2-0734: unknown command beginning "col2¿" - rest of line ignored.
SQL>
SQL> );
SP2-0042: unknown command ")" - rest of line ignored.



please let me know is there any settings in sqlplus that prevents the above issue/error. Thanks in Advance.

Regards,
Chandu
Re: SQL code [message #428372 is a reply to message #428367] Wed, 28 October 2009 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 22 September 2009 14:31
From your previous topic:
Michel Cadot wrote on Mon, 21 September 2009 14:15
...
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
...

Your answer:
chandu_imax wrote on Tue, 22 September 2009 08:14
...
Thank you Michel. I will follow the instructions from now onwards.
...

Conclusion?

Regards
Michel

Re: SQL code [message #428381 is a reply to message #428367] Wed, 28 October 2009 03:00 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I think there are two line breaks in your code that is contained in test.sql file .Please remove it.And please format your code as Michel asked.
Re: SQL code [message #428389 is a reply to message #428367] Wed, 28 October 2009 03:32 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> create table t_2(a varchar2(10), b varchar2(10))
  2  /

Table created.

SQL> set termout off;
SQL> set echo on;
SQL> spool a.txt;

SQL> alter table t_2 modify( a not null, b not null);

Table altered.
SQL>spool off

Why you are issuing commit here?



[Updated on: Wed, 28 October 2009 03:45]

Report message to a moderator

Re: SQL code [message #428398 is a reply to message #428367] Wed, 28 October 2009 04:04 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
I checked it. There is no line break in the SQL code.
Please suggest, is there any settings will be there?
Re: SQL code [message #428399 is a reply to message #428398] Wed, 28 October 2009 04:07 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
chandu_imax wrote on Wed, 28 October 2009 10:04
I checked it. There is no line break in the SQL code.


Oracle seems to disagree with you on that one. And since the code is not formatted, there is no way for us to tell who is right. But my bet is on Oracle.
Re: SQL code [message #428405 is a reply to message #428398] Wed, 28 October 2009 04:16 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
IT works for me
here is the log and the sql file attached
SQL> set termout off;
SQL> set echo on;
SQL> TIMING START test1;
SQL> ALTER TABLE test
  2  MODIFY
  3  (
  4  a not null,
  5  b not null
  6  );

Table altered.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> TIMING STOP test1;
timing for: test1
Elapsed: 00:00:00.04
SQL> spool off;
  • Attachment: sql.sql
    (Size: 0.17KB, Downloaded 754 times)

[Updated on: Wed, 28 October 2009 04:17]

Report message to a moderator

icon5.gif  Re: SQL code [message #428408 is a reply to message #428367] Wed, 28 October 2009 04:25 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Ayush.. thanks for reply. Its even working in my database.
I have sent this script to one of my colleague. In his database
struck up with the mentioned error. So we are in the confusion
that any setting need to be set.
Re: SQL code [message #428409 is a reply to message #428408] Wed, 28 October 2009 04:27 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Could it be a Windows / Unix line break problem on his end?

The "col1¿" also looks suspicious.

[Updated on: Wed, 28 October 2009 04:29]

Report message to a moderator

Re: SQL code [message #428410 is a reply to message #428367] Wed, 28 October 2009 04:29 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Same environment for both. Its Unix (aix52) only.
Re: SQL code [message #428411 is a reply to message #428410] Wed, 28 October 2009 04:30 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
How did he / you "sent" the script? I found that certain mail programs are notorious for breaking code with special "blank" or "hyphen" characters if you copy/paste it.
Re: SQL code [message #428413 is a reply to message #428367] Wed, 28 October 2009 04:33 Go to previous messageGo to next message
chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad
Member
Just did FTP the file from my machine to his machine.
Observed that the file size is also same at both.
Re: SQL code [message #428414 is a reply to message #428410] Wed, 28 October 2009 04:33 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I am using windows so please check the difference between line breaks between win/unix..please try writing the same file with "ed" in oracle and simultaneously with "vi editor" as well

[Updated on: Wed, 28 October 2009 04:35]

Report message to a moderator

Previous Topic: finding sum from inner query
Next Topic: To Delete 25 lacs Records
Goto Forum:
  


Current Time: Sun Feb 09 09:50:23 CST 2025