Package not working Not giving any error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Package not working Not giving any error

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    Package not working Not giving any error

    Hi all
    I have a package that update an error table and set to 'E' if there is an update the dbms.output .put package set the flag to 'E'. The package is not giving any error but the table is not updated. Any idea?
    Thanks

    ********************************************

    v_dbuser VARCHAR2(30);
    v_osuser VARCHAR2(30);
    l_sqlcode VARCHAR2(10);
    l_sqlerrm VARCHAR2(150);

    BEGIN
    v_dbuser := SYS_CONTEXT('userenv','current_user');
    v_osuser := SYS_CONTEXT('userenv','os_user');
    /* Retrieve Oracle return code and message */
    l_sqlcode := SUBSTR(SQLCODE,1,8);
    l_sqlerrm := SUBSTR(SQLERRM,1,150);
    /* Insert record into table that is used for tracking errors */

    transaction_debug('Insert My_error_table....');
    DBMS_OUTPUT.PUT_LINE('ABLAK Start');
    UPDATE mytable SET FACT_EXTRACT='E'
    WHERE TRANSEQNUM = p_transeqnum
    AND TRANSENDTIME = p_transendtime
    AND CUSTOMERID = p_customerid;
    COMMIT;
    Last edited by Ablakios; 09-06-2007 at 03:16 PM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Look at your data.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Pavb
    Could you be more explicit?
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    if update does not work it means no row matches your predicate; look at your data.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    I will double check
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  6. #6
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    PAVB
    I am new to this and I checked and see no difference ..Could please direct me as to what I should cross check?
    Thanks for your help
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    1- Add variable x_rowcount NUMBER; after l_sqlerrm VARCHAR2(150);

    2- Alter your update section to look like...
    Code:
    DBMS_OUTPUT.PUT_LINE('ABLAK Start');
    
    select count(*)
    into   x_rowcount
    from   mytable
    WHERE TRANSEQNUM = p_transeqnum
    AND TRANSENDTIME = p_transendtime
    AND CUSTOMERID = p_customerid;
    DBMS_OUTPUT.PUT_LINE(x_rowcount);
    
    UPDATE mytable SET FACT_EXTRACT='E'
    WHERE TRANSEQNUM = p_transeqnum
    AND TRANSENDTIME = p_transendtime
    AND CUSTOMERID = p_customerid;
    COMMIT;
    3- Compile and Execute your package.

    If you see a zero after the 'ABLAK Start' literal that means no row in your table matches your predicate.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    PAVB,
    Merci Beaucoup Cher ami. I will try it and let you know if it successful or not.
    Thanks
    A
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

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