I partially agree with you first and then disagree with you fully!!
Sebastian Thomas, March 02, 2016 - 5:31 am UTC
I totally agree with you Connor McDonald and I too had the same feeling when I had to work on a procedure that had this line.
However, when I saw the actual requirement that is - passing the whole select statement to shell script in a single line so that each select statement can be read inside a loop and process, I found it as a reasonable approach. And it was working perfect till I tried to fine tune (which reduced query execution time from 35 minutes to just 30 seconds) the query and exceeded this 'unwanted' 2500 limit!
I totally agree with you again on developer's responsibility to know the features and limitations of the tools he/she is using but my question is different. When tools such as pl/sql developer, Toad and many other tools support this, why SQL*Plus (it is Oracle's tool only) put a limit like this? And what SQL*Plus did is unacceptable because it removed that line from the procedure (which caused that process taking the previous select statement and producing absolutely wrong result) and created the procedure without any compilation error. Do you agree with this approach? When a tool does this, it is actually insulting the developer who created it and moreover, it is violating Code/Data Integrity.
Hope now you will have sympathy on this one!! :)
March 02, 2016 - 7:10 am UTC
re: "and created the procedure without any compilation error"
Well it did produce an error, an SP2- error. I would hope that any deployment script is checking for those anyway, because you could get them for any myriad of SQL Plus syntax errors in your script.
We'll have to agree to disagree :-) but thanks for adding your thoughts and comments. They are always appreciated.
No Sympath too
John, March 02, 2016 - 8:40 am UTC
The 2499 character limit in sql plus is known, and has been there probably since the utility was first written. It wasn't 'put' there by Oracle, it was just a limitation at the time of writing.
I have to agree with the "no sympathy" response because none is warranted. All changes to all databases should (where relevant) be deployed via scripted sql plus, and nothing else. It's fine to use developer or TOAD to create those scripts, but never ever deploy outside of SQL plus. Having used two different tools for a task, in two different environments, why is the OP surprised at getting two different results?
Before deployment to production, a dry run was not carried out on development, which is poor practice.
When the production instance was modified by the script, the output of the session was neither logged nor reviewed for error-free operation, otherwise the problem would have been noticed. That's just poor practice, which is entirely the responsibility of the users. Sql plus is not to "blame" for doing precisely what it's supposed to.
Bad practice
Jeff, March 03, 2016 - 6:21 pm UTC
Production deployment was done differently (using SQL*plus) than non-production deployment (SQLDeveloper). This is a very bad idea. Things behave differently and cause problems, as you discovered.
Earlier review suggests all deployments should be scripted. Maybe, maybe not. What is indisputable is that deployments should be tested in Non-production environments the same way they are going to be executed in Production environments.
March 04, 2016 - 1:50 am UTC
Agreed.
SQL Dev is a wonderful tool for developers, but if the deployment mechanism is "X", then all scripts should be tested with "X" in Testing, Integration, before ultimately going to Production
Is there any possibility of Negative Sympathy?
Jimbo, March 08, 2016 - 10:31 pm UTC
From your response:
" ... passing the whole select statement to shell script in a single line so that each select statement can be read inside a loop and process, I found it as a reasonable approach. And it was working perfect till I tried to fine tune (which reduced query execution time from 35 minutes to just 30 seconds) the query and exceeded this 'unwanted' 2500 limit!"
Are you actually saying you find a query that is 2500 characters in length is in any way maintainable?
Are you saying that you find working on a system that passes a text string of over 2K characters in length something that is in keeping with professional standards of computer coding?
Back in the old days, we did everything in 80 columns on Hollerith cards and everything worked fine. Not sure why you find the solution you described as desirable, acceptable, or sensible. And sorry, if you don't like the answer, then maybe you should think about the way you asked the question. ;)
March 08, 2016 - 11:49 pm UTC
I always endorse any statement which commences with "Back in the old days" :-)
Phooey on "back in the day"-- Dynamic SQL is OK!
Duke Ganote, March 09, 2016 - 2:28 pm UTC
I've used a similar approach to the original poster: a table containing SQL statements (usually DDL statements, but occasionally anonymous PL/SQL blocks -- most well exceeding 80 characters). The statements had to be dynamic because they're DDL, so a table is a fine place to store the statements. The obvious design alternative would've meant embedding hundreds of dynamic SQL statements in PL/SQL -- rather more difficult to maintain.
The methodology failures are lack of (1) during-deployment error trapping and (2) post-deployment verification. Not using the same tool set for pre-production deployment testing is a specific contributor.
March 10, 2016 - 2:38 am UTC
True.
But if someone is dynamically building SQL statements, one would think it is not that hard to code in the occasionally: ||chr(10)
Case closed - when
A reader, March 09, 2016 - 4:00 pm UTC
The case is closed when during prod deployment
The following error is given - SP2-0027: Input is too long (> 2499 characters) - line ignored'
So why are you complaining against oracle ?
Are you not supposed to verify deployment logs ?
Oracle is not but your deployment, dev, testing process is at fault.
Leave my Oracle alone. Big Mama is upset.