Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: help needed for this problem ?

    Create a sequence starting at 1000 and incrementing by one. This sequence is used to
    generate transaction numbers for the entries into the wgb_transaction table.

    a. Reads in the transactions from wgb_in_transactions and inserts each one with an
    automatically generated transaction number and date into the wgb_transaction
    table
    b. Updates the wgb_account and wgb_assets tables as appropriate to maintain
    balanced books (assume valid data).

    This is the question


    DECLARE

    CURSOR c_returns IS
    SELECT customer_number,account_type,transaction_amount,tr ansaction_type
    FROM wgb_in_transactions;
    g_date DATE;

    BEGIN

    CREATE SEQUENCE g_transaction
    START WITH 1
    INCREMENT BY 1000;

    FOR r_returns IN c_returns LOOP
    BEGIN
    SELECT customer_number,account_type,transaction_amount,tr ansaction_type
    INTO g_customer_number, g_account_type,g_transaction_amount,g_transaction_ type
    FROM wgb_in_transactions;

    INSERT INTO wgb_transaction
    VALUES
    (g_transaction.nextVal, r_returns.g_customer_number, r_returns.g_account_type, r_returns.g_transaction_amount,r_returns.g_transac tion_type,
    r_returns.account_type,g_date);
    COMMIT;
    END LOOP;
    DROP SEQUENCE g_tansaction;
    END;
    /

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Do not create (and drop) a sequence (or a table, or ...) within PL/SQL (not that it is not possible - if you are interested in the subject, research use of dynamic SQL and EXECUTE IMMEDIATE). Create a sequence BEFORE running your PL/SQL block.

    If you've created a cursor and created a FOR loop, then there's really no need to repeat the same SELECT statement once again. "r_returns" already contains everything you need, so just use those values in the INSERT statement.

    Do not commit in the loop.

Posting Permissions

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