Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: [JAVA CODE] stored proc to insert and then delete the data from another table /CODE

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default [JAVA CODE] stored proc to insert and then delete the data from another table /CODE

    create or replace
    PACKAGE BODY KEY_GPL_LCP_ARCHIVE AS

    procedure LCP_ARCHIVE_PROC(CHECKID_VAR in number, STATUS_VAR out VARCHAR2) AS
    STATUSID_VAR number;
    ACTIONFID_VAR number;
    PAYMNTFID_VAR number;
    CORACTACCTFID_VAR number;
    ACCTNAME_VAR varchar2(100);
    ACCTNO_VAR varchar2(50);
    AMOUNT_VAR number;
    FEEAMT_VAR number;
    PAYEE_VAR varchar2(32);
    ADDRESS1_VAR varchar2(32);
    ADDRESS2_VAR varchar2(32);
    ADDRESS3_VAR varchar2(32);
    CITY_VAR varchar2(50);
    STATE_VAR varchar2(20);
    ZIP_VAR varchar2(11);
    DEPOSITDT_VAR timestamp(6);
    CLOSEDDT_VAR timestamp(6);
    COMMENTS_VAR varchar2(500);
    ADDEDBY_VAR varchar2(50);
    ADDEDDT_VAR timestamp(6);
    PROCESS_VAR varchar2(3);
    CONTROLNO_VAR varchar2(50);
    MESSAGE_VAR varchar2(100);
    NUMSHARES_VAR number;
    ADMIN_VAR varchar2(50);
    NEWACCT_VAR varchar2(50);
    BRO_VAR varchar2(50);
    FEES_VAR varchar2(1);
    PMTPROC_VAR varchar2(15);
    ARCHIVE_VAR varchar2(3);
    ESCHSTATE_VAR varchar2(3);
    ESCHMONTHS_VAR number;
    SUCCACCNO_VAR varchar2(50);


    begin
    is_rec_inserted := FALSE;
    STATUS_VAR :='OK';

    select STATUSID
    ,ACTIONFID
    ,PAYMNTFID
    ,CORACTACCTFID
    ,ACCTNAME
    ,ACCTNO
    ,AMOUNT
    ,FEEAMT
    ,PAYEE
    ,ADDRESS1
    ,ADDRESS2
    ,ADDRESS3
    ,CITY
    ,STATE
    ,ZIP
    ,DEPOSITDT
    ,CLOSEDDT
    ,COMMENTS
    ,ADDEDBY
    ,ADDEDDT
    ,PROCESS
    ,CONTROLNO
    ,MESSAGE
    ,NUMSHARES
    ,ADMIN
    ,NEWACCT
    ,BRO
    ,FEES
    ,PMTPROC
    ,ARCHIVE
    ,ESCHSTATE
    ,ESCHMONTHS
    ,SUCCACCNO
    into STATUSID_VAR
    ,ACTIONFID_VAR
    ,PAYMNTFID_VAR
    ,CORACTACCTFID_VAR
    ,ACCTNAME_VAR
    ,ACCTNO_VAR
    ,AMOUNT_VAR
    ,FEEAMT_VAR
    ,PAYEE_VAR
    ,ADDRESS1_VAR
    ,ADDRESS2_VAR
    ,ADDRESS3_VAR
    ,CITY_VAR
    ,STATE_VAR
    ,ZIP_VAR
    ,DEPOSITDT_VAR
    ,CLOSEDDT_VAR
    ,COMMENTS_VAR
    ,ADDEDBY_VAR
    ,ADDEDDT_VAR
    ,PROCESS_VAR
    ,CONTROLNO_VAR
    ,MESSAGE_VAR
    ,NUMSHARES_VAR
    ,ADMIN_VAR
    ,NEWACCT_VAR
    ,BRO_VAR
    ,FEES_VAR
    ,PMTPROC_VAR
    ,ARCHIVE_VAR
    ,ESCHSTATE_VAR
    ,ESCHMONTHS_VAR
    ,SUCCACCNO_VAR
    from lc_checks
    where CHECKID = CHECKID_VAR;


    insert into lc_checks_arc values (CHECKID_VAR
    ,STATUSID_VAR
    ,ACTIONFID_VAR
    ,PAYMNTFID_VAR
    ,CORACTACCTFID_VAR
    ,ACCTNAME_VAR
    ,ACCTNO_VAR
    ,AMOUNT_VAR
    ,FEEAMT_VAR
    ,PAYEE_VAR
    ,ADDRESS1_VAR
    ,ADDRESS2_VAR
    ,ADDRESS3_VAR
    ,CITY_VAR
    ,STATE_VAR
    ,ZIP_VAR
    ,DEPOSITDT_VAR
    ,CLOSEDDT_VAR
    ,COMMENTS_VAR
    ,ADDEDBY_VAR
    ,ADDEDDT_VAR
    ,PROCESS_VAR
    ,CONTROLNO_VAR
    ,MESSAGE_VAR
    ,NUMSHARES_VAR
    ,ADMIN_VAR
    ,NEWACCT_VAR
    ,BRO_VAR
    ,FEES_VAR
    ,PMTPROC_VAR
    ,ARCHIVE_VAR
    ,ESCHSTATE_VAR
    ,ESCHMONTHS_VAR
    ,SUCCACCNO_VAR);

    is_rec_inserted := TRUE;

    delete from lc_checks where CHECKID = CHECKID_VAR;

    exception
    when OTHERS then
    begin
    if is_rec_inserted then
    STATUS_VAR :='t';
    else
    STATUS_VAR :='f';
    DBMS_OUTPUT.PUT_LINE('Caught raised exception NO_DATA_FOUND');
    end if;
    is_rec_inserted := FALSE;
    end

    END LCP_ARCHIVE_PROC;
    END KEY_GPL_LCP_ARCHIVE;

    ------
    Error(165,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier> The symbol ";" was substituted for "END" to continue.

    ----

    public int updateArchive( String checkIdVal) throws ApplicationException {

    System.out.println("the updateArchive method begins ************* ");
    System.out.println("the value of checkid in the archive check is"+checkIdVal);


    // GPLUserDataSource dataSource = null;
    // Connection connection = null;
    CallableStatement callableStatement = null;
    String status = null;
    int updatecount = 0;
    try{
    // TODO remove sysout and use logger to log these message.
    // System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ Scheduled History batch process started @ " + new Date());
    dataSource = new GPLUserDataSource("gpl", "oracle");
    conn = dataSource.getConnection();
    // long batchId = new Date().getTime();
    String query = "CALL " + GPLUtil.getDBOwner(dataSource.getApp(), dataSource.getDsname())
    + ".key_gpl_lcp_archive.lcp_archive_proc(?, ?)";
    callableStatement = conn.prepareCall(query);
    callableStatement.registerOutParameter(2, Types.VARCHAR);
    callableStatement.setString(1, "" + checkIdVal);
    System.out.println("the callable statement query is "+query);
    System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ Callig SQL checkIdVal : [" + checkIdVal +"]");
    callableStatement.executeQuery();
    status = callableStatement.getString(2);
    System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ SQL batch [" + checkIdVal + "] finished with status : " + status);
    System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ Finished Scheduled java batch : " + status);
    updatecount++;

    } catch (SQLException exception) {
    //result = 0;
    exception.printStackTrace();
    throw new ApplicationException(exception.getLocalizedMessage (),exception);
    } catch (ConnectionException exception) {
    // result=0;
    exception.printStackTrace();
    throw new ApplicationException(exception.getLocalizedMessage (),exception);
    } catch (NamingException exception) {
    // result = 0;
    exception.printStackTrace();
    throw new ApplicationException(exception.getLocalizedMessage (),exception);
    }finally{
    try {
    conn.close();
    stmt.close();
    } catch (SQLException e) {
    // result = 0;
    e.printStackTrace();
    }
    }

    return updatecount;
    }


  2. #2
    Super Moderator curmudgeon's Avatar
    Join Date
    Aug 2012
    Posts
    1,130
    My Mood
    Cynical
    Thanks
    64
    Thanked 139 Times in 134 Posts

    Default Re: [JAVA CODE] stored proc to insert and then delete the data from another table /CO

    Ahem,... do you have a question?

  3. #3
    Super Moderator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,225
    Thanks
    176
    Thanked 817 Times in 760 Posts
    Blog Entries
    5

    Default Re: [JAVA CODE] stored proc to insert and then delete the data from another table /CO

    And how is this different from your other thread?
    Stored proc to fetch row from 1 table and insert into another and delete from the 1st
    If you wish to receive help, I recommend formulating a question that can actually be answered. See the link in my signature entitled "Getting Help"
    Last edited by copeg; September 3rd, 2012 at 09:50 AM.

  4. #4
    Junior Member
    Join Date
    Aug 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: [JAVA CODE] stored proc to insert and then delete the data from another table /CO

    Hi
    it is error from procedure .
    After is_rec_inserted := FALSE;
    there is a end statement replace it as end;(i.e., put ; after end statement)
    it removes this error

Similar Threads

  1. Replies: 2
    Last Post: August 31st, 2012, 12:36 PM
  2. How to Delete selected table data from DB???? HELP
    By lanepulcini in forum JDBC & Databases
    Replies: 0
    Last Post: February 21st, 2012, 06:07 PM
  3. Replies: 2
    Last Post: June 15th, 2011, 03:49 PM
  4. Replies: 1
    Last Post: June 11th, 2011, 05:39 AM
  5. issue in executing Stored Proc
    By Bhawesh Kurmi in forum JDBC & Databases
    Replies: 1
    Last Post: December 8th, 2010, 09:05 AM