0

I have to insert almost 40 000 rows to a table and every time I try to execute it in SQL developer, I couldn't find any rows in the table it seems like it didn't executed the begin block at all, because i put a msg to be printed in the output and there is nothing after the execution, I have tried to execute the block with only a few insertion statements and it works,

why SQL developer can't handle a lot of insertion statement in PLSQL block?

bellow is my script:

set serveroutput on
set linesize 200
set timing on

whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;

ALTER SESSION SET current_schema = carp;
ALTER SESSION SET nls_length_semantics = char;

DECLARE
    v_rows_count VARCHAR2(10 CHAR);

    PROCEDURE trace (p_message IN VARCHAR2)
    IS
    BEGIN
        DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS') || ' - INFO - ' || p_message);
    END trace;

BEGIN

    dbms_output.ENABLE(1000000);

    TRACE ('=========== Exécution du script: START ===========');

    Insert into my_table (PK_SEQ,CODE) values (1260389,'00AI');
    Insert into my_table (PK_SEQ,CODE) values (1321244,'00AI');

    select COUNT(*) into v_rows_count FROM carp.param_site_groupe_site;
    dbms_output.put_line('Nombre de lignes insérées: ' || v_rows_count);

    TRACE (q'[=========== Exécution du script: FIN ===========]');

    COMMIT;

END;
/

exit;

please note that there is more than 40 000 insert statements inside the begin block not two as shown above I reduce the script size just for readebility porpuse

AbdeAMNR
  • 165
  • 1
  • 5
  • 18
  • I think we need some more information. Do you get any errors? – kara Oct 30 '18 at 13:51
  • that is why I couldn't figure out why!! I don't get any error :( – AbdeAMNR Oct 30 '18 at 13:53
  • 1
    You haven't given us anything to go on. What's the code that isn't executing? Why do you think it isn't reaching the begin block? Please update your question to include some sample data, code, etc – Boneist Oct 30 '18 at 13:56
  • I'm not an expert for Sql Developer - and i don't like it :). You could try to store your script in a text-file and run it with sqlPlus. https://stackoverflow.com/questions/39043872/executing-a-sql-file-in-sql-plus-terminal this should give you an propper error or execute completly. – kara Oct 30 '18 at 13:56
  • @kara SQL Developer would give the same proper error SQL*plus would. and you can run the script in the same fashion – thatjeffsmith Oct 30 '18 at 14:11
  • 1
    insert scripts don't necessarily NEED begin blocks, can you show us the first portion of your script? – thatjeffsmith Oct 30 '18 at 14:12
  • I have run the insert statements without PL/SQL Blocks, and it works fine, and I took more then 5 min just to execute 40 000 statements, however, won't run the begin block if I put 40 000 insert statements in it – AbdeAMNR Oct 30 '18 at 14:57
  • i just ran your script with no issues - how do you mean exactly it won't 'run the begin block' - i'm thinking you mean the begin block runs but you get errors? show us what you see after executing. there will be output in the script output panel – thatjeffsmith Oct 30 '18 at 15:00
  • 1
    You realise that the dbms_output contents are only retrieved once the procedure has completed? I'm assuming that you're seeing no output displayed and assuming that nothing is happening, when in fact it is. – Boneist Oct 30 '18 at 15:25
  • plus in sqldev you need to set serveroutput on to see it even then – thatjeffsmith Oct 30 '18 at 15:41
  • @thatjeffsmith the Script above is perfectly fine, however, the begin block gets ignored with no errors in the output when i add the 40 000 insertion statements – AbdeAMNR Oct 30 '18 at 16:23
  • sql developer doesn't ignore begin blocks – thatjeffsmith Oct 30 '18 at 16:36
  • 1
    @AbdeAMNR - did you, perhaps, run the 40,000 plain inserts in one session, then try to run the block with the same 40,000 inserts in a separate session - maybe just by opening a new worksheet depending on your settings - without committing or rolling back the first lot? It sounds rather like it's just blocking because of pending transaction; then you'd see no output as it's still running (and waiting...)? – Alex Poole Oct 30 '18 at 17:27
  • Those insert sentences were written manually or is an insert from select or is cursor loop? It could be valuable for us to understand how are you doing this. – Giovanni Esposito Oct 30 '18 at 19:11
  • @thatjeffsmith I'm sorry for this question that is not understandable, but I have tried in multiple different machines and different SQL dev versions, and the Begin block always gets ignored every time I put a lot of insertion statements – AbdeAMNR Oct 30 '18 at 22:59
  • @AbdeAMNR please...SHOW US. record a video, or at least post a few screenshots – thatjeffsmith Oct 30 '18 at 23:41
  • @thatjeffsmith could you please try to run the same code with 40 000 insert statements inside the begin block to see what happens, obviously, it is going to take some time to finish the execution but you gonna notice that any statement in the begin block wasn't run – AbdeAMNR Oct 31 '18 at 22:32
  • @AbdeAMNR - the onus is on you to show that there is a problem and to provide enough detail for us to see and understand what is happening and, if necessary, recreate the issue. You still haven't explained exactly what you see - for instance, does the script output window show the 'ScriptRunner task' progress bar, and does it flash up and disappear, or not appear at all, or stay there forever? Do you see output from statements before and after the block? You also haven't said which version of SQL Developer and database you are using. – Alex Poole Nov 01 '18 at 09:17
  • FWIW, I did actually try this yesterday in 18.3/11.2.0.4, with 40,000 insert statements, and the block did run as expected. (It errored after quite a while because I ran out of PGA, but that isn't relevant to what you've said so far). You have also said "obviously, it is going to take some time to finish the execution but you gonna notice that any statement in the begin block wasn't run" which is **not** the same as saying the *block* didn't run, which is all you've said before. Now you're saying it did run, took a long time, and finished OK; but the table doesn't show the inserted rows? – Alex Poole Nov 01 '18 at 09:19
  • @AlexPoole I thought the begin block it didn't run as expected but, there was no record inserted after finishing the execution, that is exactly what happens – AbdeAMNR Nov 01 '18 at 09:37
  • Then [edit your question](https://stackoverflow.com/posts/53065811/edit) to explain what the actual problem is, and show everything that happens and what you see in the script output window. Also, when you reduced the script size for readability, did you also remove an exception handler that's squashing an error and rolling back, maybe? If so, show that too. (I'm wondering if it might actually be better to delete this and start a new question, explaining the real scenario from the start...) – Alex Poole Nov 01 '18 at 09:43

0 Answers0