SQL code [message #428367] |
Wed, 28 October 2009 02:31  |
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 #428381 is a reply to message #428367] |
Wed, 28 October 2009 03:00   |
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   |
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   |
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   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
chandu_imax wrote on Wed, 28 October 2009 10:04I 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   |
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
|
|
|
|
|
|
Re: SQL code [message #428411 is a reply to message #428410] |
Wed, 28 October 2009 04:30   |
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.
|
|
|
|
|