This discussion is archived
5 Replies Latest reply: Aug 1, 2012 11:10 PM by BillyVerreynne RSS

Maximum length of SQL*Plus Command?

Catfive Lander Explorer
Currently Being Moderated
I'm typing a long query into SQL*Plus (Linux, version 9.2.0.1... yes, I know it's ancient).

If I run this query, all works fine:
select 'update myauditing_table set test_details='''||grantee||''' where myaudit_refno=''A.FE'';' from (select case when upper(value) in ('DBO','TRUE')  then (select agg_concat(grantee) from dba_tab_privs where table_name='AUD$') 
else (select 'AUDIT_TRAIL is set to: '||value from dual) end as grantee from v$parameter where upper(name)='AUDIT_TRAIL' order by grantee);

'UPDATEMYAUDITING_TABLESETTEST_DETAILS='''||GRANTEE||'''WHEREMYAUDIT_REFNO=''A.F
--------------------------------------------------------------------------------
update myauditing_table set test_details='AUDIT_TRAIL is set to: DB' where myaud
it_refno='A.FE';
(In other words, is SQL that generates other SQL: if AUDIT_TRAIL is set to something interesting, tell me who has access to the AUD$ table. And it's working fine... the agg_concat function mentioned is simply Tom Kyte's stragg function under another name, as lifted word-for-word from http://www.sqlsnippets.com/en/topic-11591.html)

Now this is what happens if I take out the linebreak after the mention of AUD$, so that the command is submitted as one single line of code:
SQL> select 'update myauditing_table set test_details='''||grantee||''' where myaudit_refno=''A.FE'';' from (select case when upper(value) in ('DBO','TRUE')  then (select agg_concat(grantee) from dba_tab_privs where table_name='AUD$') else (select 'AUDIT_TRAIL is set to: '||value from dual) end as grantee from v$parameter where upper(name)='AUDIT_TRAIL' order by grantee);
SP2-0734: unknown command beginning "s set to: ..." - rest of line ignored.
The error message indicates that the text has been 'split' at the 'AUDIT_TRAIL is set to...' bit, so that "s set to" is being treated as a new command, which is of course syntactically invalid. There is definitely no other change in text between the two versions, apart from the removal of a carriage return before the "else" statement.

Possibly a coincidence, but the first "s" in "s set to" appears at position 258 in the entire text... close to a possible 255 or 256 character limit, perhaps?

The same problem happens whether I run the command as a script (@mysql.sql) or typed in directly into a client SQL*Plus session. Is there some inherent limit to the length of commands that SQL*Plus can process in this version? My code runs fine on 10g and 11g databases/clients, whether it's on one line or two. Anyone know of a bug in 9i regarding this? And if there's a workaround (other than the obvious one of upgrading, of course!)
  • 1. Re: Maximum length of SQL*Plus Command?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    According to the docs, 2500 characters is the limit for SQL*Plus in 9.2

    http://docs.oracle.com/cd/B10501_01/server.920/a90842/apa.htm#634246

    Pl check to see if you have some kind of unprintable character at the location you mention

    HTH
    Srini
  • 2. Re: Maximum length of SQL*Plus Command?
    Catfive Lander Explorer
    Currently Being Moderated
    No funny characters at all. The thing is cut-and-pasted from a plain text editor (Notepad++). It has been re-typed from scratch multiple times. The results are completely replicable, time after time: 9i, breaks as a one-liner, add one carriage return and then it works as a two-liner; 10g and 11g, works fine as a one-liner, even when cut-and-pasted from the identical source as causes 9i to break.

    Thanks for the link to the documentation, though. Hadn't been able to track command maximum length down.
  • 3. Re: Maximum length of SQL*Plus Command?
    rp0428 Guru
    Currently Being Moderated
    >
    No funny characters at all. The thing is cut-and-pasted from a plain text editor (Notepad++). It has been re-typed from scratch multiple times. The results are completely replicable, time after time: 9i, breaks as a one-liner, add one carriage return and then it works as a two-liner; 10g and 11g, works fine as a one-liner, even when cut-and-pasted from the identical source as causes 9i to break.
    >
    This AskTom reply says the limit is about 240 characters.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1546206316314
    >
    As for the line length limit -- yes there is one, and it is about 240 character per line.
    You would use the line continuation character to get around that:
    >
    The specific topic is the COPY command but I believe this would apply to all commands.
  • 4. Re: Maximum length of SQL*Plus Command?
    Catfive Lander Explorer
    Currently Being Moderated
    As mentioned, it's choking at the 258th character, not the 240th. Also, it's a SELECT statement, not a COPY command (one relates to the database, the other is an internal SQL*Plus feature). And someone else also kindly pointed out that SQL*Plus has a command limit length (i.e., database-related commands) of 2500 characters. So I don't think that's the issue.

    I can be a bit more specific about this one now, though. I've a sequence of Solaris boxes running 9.2.0.1 up, and the results are that 9.2.0.1 is affected; and it's a problem for 9.2.0.7:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
    PL/SQL Release 9.2.0.1.0 - Production
    CORE    9.2.0.1.0       Production
    TNS for Solaris: Version 9.2.0.1.0 - Production
    NLSRTL Version 9.2.0.1.0 - Production
    
    SQL> select 'update myauditing_table set test_details='''||grantee||''' where myaudit_refno=''A.FE'';' from (select case when upper(value) in ('DBO','TRUE')  then (select agg_concat(grantee) from dba_tab_privs where table_name='AUD$') else (select 'AUDIT_TRAIL is set to: '||value from dual) end as grantee from v$parameter where upper(name)='AUDIT_TRAIL' order by grantee);
    SP2-0734: unknown command beginning "s set to: ..." - rest of line ignored.
    And...
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
    PL/SQL Release 9.2.0.7.0 - Production
    CORE    9.2.0.7.0       Production
    TNS for Solaris: Version 9.2.0.7.0 - Production
    NLSRTL Version 9.2.0.7.0 - Production
    
    SQL> select 'update myauditing_table set test_details='''||grantee||''' where myaudit_refno=''A.FE'';' from (select case when upper(value) in ('DBO','TRUE')  then (select agg_concat(grantee) from dba_tab_privs where table_name='AUD$') else (select 'AUDIT_TRAIL is set to: '||value from dual) end as grantee from v$parameter where upper(name)='AUDIT_TRAIL' order by grantee);
    SP2-0734: unknown command beginning "s set to: ..." - rest of line ignored.
    But in 10.2.0.1, the problem disappears.

    I am not sure, but it is perhaps related to metalink note 285913.1, since we did get some ORA-00600: internal error code, arguments: [qernsRowP], [1] errors in the alert log with a slightly different version of the above query. Changing the query obviously alters the test, though, so it may be completely unrelated.

    Either way, I think it's definitely a version-specific SQL*Plus bug (though it would be nice if any other 9i owners out there could try it and report back!)

    Anyway, the workaround is simply to submit the command on two separate lines, wherever possible. And of course... to upgrade.
  • 5. Re: Maximum length of SQL*Plus Command?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Works fine on SQL*Plus 9.2.0.1.0 on HP-UX.

    Do not have your agg_concat(..) function, so I substituted it with upper(lower(..)). The statement executed fine from SQL*Plus. No errors.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points