Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There are several spots in your code needed attention:</p> <ol> <li>The reason you've got that error is because label <code>&lt;&lt;the_end&gt;&gt;</code> should be placed before the <code>EXCEPTION</code> section.</li> <li>Operator is required after a label. So if you want to jump to the end of a stored procedure and no other actions required <code>NULL</code> operator should be used. </li> </ol> <p>To that end your code should look like this:</p> <pre><code>IF id IS NULL THEN --check if client exists by checking if client_id is there dbms_output.put_line('Please create client first'); GOTO the_end; ELSE INSERT INTO ssjm_workorder VALUES(workorderno,workorderno,company,loggedby ,attention,'Received',today,datedue,id); END IF; &lt;&lt;the_end&gt;&gt; NULL; EXCEPTION WHEN OTHERS THEN raise_application_error(-20999,'An error occured in' || SQLCODE || '-ERROR-' || SQLERRM); END sp_ssjm_newworkorder; </code></pre> <p>By all means try to avoid unconditional branching. Using <code>GOTO</code> operator is very, very not good practice. It kills readability, code like that hard to debug. It will cause you and everybody who will look at that code after you a headache. Moreover if the query</p> <pre><code>SELECT client_id --grab client_id INTO id FROM ssjm_client WHERE ssjm_client.name=company; </code></pre> <p>returns no rows the exception <code>NO_DATA_FOUND</code> will be immediately raised and execution of the code halts. So <code>IF id IS NULL THEN</code> condition will never be evaluated. You may rewrite your code by removing that condition and adding <code>NO_DATA_FOUND</code> exception handler in the <code>EXCEPTION</code> section of your code. And of course as @Rob van Wijk correctly pointed out in the comment </p> <blockquote> <p>but code can be cleaned up further. today variable can be removed and the WHEN OTHERS should definitely be removed. As it is now, it just transforms an error to a longer error message without more detail and most importantly: it disguises the line number where the real error took place.</p> </blockquote> <p>there is no need of <code>today</code> variable, <code>SYSDATE</code> can be used directly in the <code>values</code> clause of the <code>insert</code> statement, and <code>WHEN OTHERS</code> can be removed as well. </p> <pre><code>CREATE OR REPLACE PROCEDURE sp_ssjm_newworkorder ( workorderno IN NUMBER, company IN CHAR, attention IN CHAR, datedue IN DATE, loggedby IN CHAR ) AS id NUMBER; BEGIN SELECT client_id --grab client_id INTO id FROM ssjm_client WHERE ssjm_client.name=company; INSERT INTO ssjm_workorder VALUES(workorderno,workorderno,company,loggedby ,attention,'Received',SYSDATE,datedue,id); EXCEPTION when NO_DATA_FOUND then dbms_output.put_line('Please create client first'); END sp_ssjm_newworkorder; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POPL/SQL : Encountered the symbol "END"
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNick Krasnov
    1. USNick Krasnov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPL/SQL : Encountered the symbol "END"
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COGood answer (+1), but code can be cleaned up further. today variable can be removed and the WHEN OTHERS should definitely be removed. As it is now, it just transforms an error to a longer error message without more detail and most importantly: it disguises the line number where the real error took place.
      singulars
      1. PO
      1. USRob van Wijk
    2. COCould you be kind enough to explain why there is no need for WHEN OTHERS exception. Regards
      singulars
      1. PO
      1. USuser75ponic
    3. CO@Polppan When exception other than `NO_DATA_FOUND` is raised error message will give us a line number where `raise_application_error` function has been called and not the line of the statement that hit the exception. So if we would have several `insert` statements and one of them raised `unique constraint violated` exception we would not know which one. But we can keep `WHEN OTHERS` and use `SQLERRM` combined with `DBMS_UTILITY.FORMAT_ERROR_BACKTRACE` function(10g onward) to get the error message and the line where exception took place in order to log the error.
      singulars
      1. PO
      1. USNick Krasnov
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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