Results 1 to 6 of 6

Thread: oracle output

  1. #1
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23

    Unanswered: oracle output

    I 'm using Pro*C and have the following part in my code

    EXEC SQL SAVEPOINT do_insert;
    ..............................
    .............

    EXEC SQL EXECUTE
    BEGIN
    INSERT INTO OWNER VALUES(:id_idn,:id_nam,:id_fnam,:id_cit);
    COMMIT;
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE(id number' || TO_CHAR(:id_idn) || 'allready exists' );
    ROLLBACK TO do_insert;
    END;
    END-EXEC;
    ...............
    .......

    It works fine without errors but when the exception is raised it doesn't print the message in monitor while rollback works.So i think it's the SET SERVEROUTPUT ON thing tha i must use.But how can i use it inside Pro*c?I 'm on a Solaris system and if i enter sqlplus ,SET SERVEROUTPUT ON and quit the next time that i will enter it will be off.

  2. #2
    Join Date
    Sep 2003
    Posts
    156

    Re: oracle output

    Originally posted by subwrc
    I 'm using Pro*C and have the following part in my code

    EXEC SQL SAVEPOINT do_insert;
    ..............................
    .............

    EXEC SQL EXECUTE
    BEGIN
    INSERT INTO OWNER VALUES(:id_idn,:id_nam,:id_fnam,:id_cit);
    COMMIT;
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE(id number' || TO_CHAR(:id_idn) || 'allready exists' );
    ROLLBACK TO do_insert;
    END;
    END-EXEC;
    ...............
    .......

    It works fine without errors but when the exception is raised it doesn't print the message in monitor while rollback works.So i think it's the SET SERVEROUTPUT ON thing tha i must use.But how can i use it inside Pro*c?I 'm on a Solaris system and if i enter sqlplus ,SET SERVEROUTPUT ON and quit the next time that i will enter it will be off.
    you have a ' missing...

    dbms_output.out_line('id number '||to_char(:id_dn)||'already exists');

    serveroutput is an sql*plus session variable.
    rgs,

    Ghostman

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I use to do this all the time but th recall is fuzzy. Have you tried

    EXEC SQL EXECUTE
    BEGIN

    SET SERVEROUTPUT ON

    INSERT INTO OWNER VALUES(:id_idn,:id_nam,:id_fnam,:id_cit);
    COMMIT;
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE(id number' || TO_CHAR(:id_idn) || 'allready exists' );
    ROLLBACK TO do_insert;
    END;
    END-EXEC;

    Either that or in the script that calls the function (that doesn't seem right but give it a try.

    Once you get it working you might want to add "SIZE #####" to the end of your set command. Otherwise the output will be truncated if it is greater then the internal limit which as I recall is not very big.
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    When i add SET SERVEROUTPUT ON after begin i get this error:

    PLS-S-00103, Encountered the symbol "SERVEROUTPUT" when expecting one of the following:
    transaction

    so it doesn't work

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Check out the doc for "set transaction", maybe you need to "set transaction serveroutput". Sorry I am not more help, I do not have time to test here als.
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    Sep 2003
    Posts
    156
    Originally posted by subwrc
    When i add SET SERVEROUTPUT ON after begin i get this error:

    PLS-S-00103, Encountered the symbol "SERVEROUTPUT" when expecting one of the following:
    transaction

    so it doesn't work
    because serveroutput is an env variable... it is specific to SQL*PLus and should be outside of the BEGIN pl/sql block

    also

    create...

    when others then
    dbms_output.put_line(sqlerrm||' '||sqlcode);

    this will throw oracle error codes that we can look up on metalink
    Last edited by GhostMan; 01-20-04 at 12:35.
    rgs,

    Ghostman

Posting Permissions

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