Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Unanswered: use inserted value dynamically at the run time

    Hi,

    I inserted a row in a master table. Primary key is generated using sequence.. Again this newly generated primary key needed to insert in child table as foreign key..This insert queries need to run one after another in a standalone script.

    I have written a script. But is inserting record only in master table.
    Can you please help.?



    DECLARE
    iP_ID parent.p_id%TYPE;

    BEGIN
    INSERT INTO parent
    (P_ID,
    p_relation_ind,
    p_template_defined)
    VALUES
    (p_seq.NEXTVAL,
    3,
    'Y') RETURNING P_ID INTO iP_ID;

    INSERT INTO child
    (chld_ID,
    p_p_id,
    p_is_optional,
    )
    VALUES
    ((chld_seq.NEXTVAL,iP_ID, 1);

    INSERT INTO child
    (chld_ID,
    p_p_id,
    p_is_optional,
    )
    VALUES
    (chld_seq.NEXTVAL,iP_ID, 1);

    END;

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Look at currval method for sequences:

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    INSERT

    take a close look at RETURNING claues
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jul 2013
    Posts
    24
    Thanks.. It is now working.. I used currval to use inserted value again

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    Thanks for the feedback PJS. If you have the time, could you show what you did (reposting and tweaking the code that you supplied above) as it may well help others who search for this sort of topic.

    Cheers

Posting Permissions

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