DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: dbms_output.put_line doesn't always print

  1. #1
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144

    dbms_output.put_line doesn't always print

    PHP Code:
    SQLset serverout on
    SQL
    r
      1  
    declare
      
    2                 read_only_ts    exception;
      
    3                 pragma          exception_init read_only_ts, -1642 );
      
    4                 sql_stmt        varchar2(250);
      
    5                 ErrorMsg        varchar2(200) :=SQLERRM;
      
    6              begin
      7  dbms_output
    .put_line('print line 1 ');
      
    8                 sql_stmt:= 'alter tablespace d2_claim begin backup';
      
    9                 execute immediate sql_stmt;
     
    10                 ErrorMsg := SQLERRM;
     
    11  dbms_output.put_line('print line 2 ');
     
    12              exception
     13                 when read_only_ts then null
    ;
     
    14                 when others then
     15                     insert into admin
    .backup_error
     16                         
    (backup_datetablespace_nameerror_code)
     
    17                         values
     18                        
    sysdate'$Tablespace'ErrorMsg );
     
    19                   commit;
     
    20*             end;
    print 
    line 1
     
    PL
    /SQL procedure successfully completed.
     
    SQL


    anyone know why i'm getting
    "print line 1" but not getting "print line 2"??

    if i comment out "execute immediate" line, both lines print

      1  declare
      2                 read_only_ts    exception;
      3                 pragma          exception_init ( read_only_ts, -1642 );
      4                 sql_stmt        varchar2(250);
      5                 ErrorMsg        varchar2(200) :=SQLERRM;
      6              begin
      7  dbms_output.put_line('
    print line 1 ');
      8                 sql_stmt:= '
    alter tablespace d2_claim begin backup';
      9  --               execute immediate sql_stmt;
     10                 ErrorMsg := SQLERRM;
     11  dbms_output.put_line('
    print line 2 ');
     12              exception
     13                 when read_only_ts then null;
     14                 when others then
     15                     insert into admin.backup_error
     16                         (backup_date, tablespace_name, error_code)
     17                         values
     18                        ( sysdate, '
    $Tablespace', ErrorMsg );
     19                   commit;
     20*             end;
    SQL> /
    print line 1
    print line 2 
    PL/SQL procedure successfully completed.



    tia,
    d.
    Last edited by tamilselvan; 12-17-2004 at 04:57 PM.

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    That means after "execute immediate" it's going to the exception unit
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  3. #3
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    man, that was dumb of me. thanks.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by DBAtrix
    man, that was dumb of me. thanks.
    Took me a while to learn to put BEGIN..EXCEPTION..END around blocks that had a probability of failing as well.
    Jeff Hunter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width