StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Aggregating functions, er, aggregate. That means that they add numbers or concatenate strings together. The larger the source values the bigger the aggegated product. </p> <p>Without knowing more details it is hard to be certain, but the most likely explanation is that something in your <em>data</em> has changed, with the consequence that your aggregated value is now too big to fit in the target column.</p> <p><strong>edit</strong></p> <blockquote> <p>" I tried the SELECT statement on its own and gives the same error... "</p> </blockquote> <p>Hmmm, which SELECT? Your procedure has got oodles of them.</p> <p>Anyway, the thing you need to do is learn to <em>debug your code</em>. <strong>(1)</strong></p> <p>The best way to debug PL/SQL is with an IDE which supports such endeavours. Both TOAD and PL/SQL Developer do so, as does Oracle's own (free) product SQL Developer. <a href="http://www.oracle.com/technology/obe/11gr2_db_prod/appdev/sqldev/plsql_debug/plsql_debug_otn.htm" rel="nofollow noreferrer" title="OTN White paper">Find out more</a>. </p> <p>Alternatively you can use DBMS_OUTPUT (AKA the Devil's Debugger) and interpolate lots of <code>DBMS_OUTPUT.PUT_LINE()</code> calls to see which statement you're about to execute, and the length of the relevant values.</p> <p>However, given that all your PL/SQL string variables are the maximum SQL column length - <code>varchar2(4000)</code> - I would focus on the code which populates your <code>v_n</code> variable. <code>number(10)</code> is by no means the biggest number it is possible to hold in SQL, so as you are breaching a buffer limit that would seem the most likely candidate. Although, since the error message does mention <em>string</em> buffer I may be selling you a bum steer.</p> <p>The other possibility is that final UPDATE statement. Are all the columns of DEMO sized as <code>varchar2(4000)</code>? If not then you need to look at them. It is good practice to specify variables using the %TYPE syntax:</p> <pre><code> v_a demo.geoc%TYPE; v_b demo.pro%TYPE; </code></pre> <p>Or, to save typing, specify a single record variable:</p> <pre><code>v_demo demo%rowtype; </code></pre> <p>Which you can reference like this:</p> <pre><code>select semic_concat(TXTDESC) into v_demo.geoc From GEOT WHERE ID = j.ID; </code></pre> <p>(Incidentally it possible to use a row level variable in update statements using the <code>UPDATE ... SET ROW =</code> syntax, but I don't think that would be appropriate in your situation.)</p> <p><strong>edit 2</strong></p> <p>Again, <code>NO_DATA_FOUND</code> points to a data issue. Unless our database is read only, we must expect that data will change and we should handle data-related exceptions. If the reason you don't handle <code>NO_DATA_FOUND</code> is because the data should always be there you have a broader problem, possibly a missing or disabled foreign key. Generally it is safer to assume that we will get <code>NO_DATA_FOUND</code>, <code>TOO_MANY_ROWS</code>, etc and include helpful exception handlers to log the relevant details. </p> <hr> <p><strong>footnote (1)</strong> Or learn to develop it Test First using a unit test harness, but this is a stable door, horse gone scenario...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload