DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Inserting data into table from storedprocs

  1. #1
    Join Date
    Apr 2008
    Location
    bangalore
    Posts
    25

    Inserting data into table from storedprocs

    Hi

    Below code is one of the procedure 'AAINSERT_AA' inside a package named 'PORTF_MIGR'....We are calling the procedure to insert the values into a table...could u pls let me know the logic how the data is getting inserted into the table

    DECLARE
    I_KEY VARCHAR2(60);
    I_KEY_SEQ_NR NUMBER;
    I_O_DATE DATE;
    I_C_DATE DATE;

    BEGIN
    I_KEY := NULL;
    I_KEY_SEQ_NR := NULL;
    I_O_DATE := NULL;
    I_C_DATE := NULL;

    PORTF_MIGR.AAINSERT_AA ( I_KEY, I_SEQ_NR, I_O_DATE, I_C_DATE );
    COMMIT;
    END;

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    How about looking at PORTF_MIGR.AAINSERT_AA code?
    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
    Apr 2008
    Location
    bangalore
    Posts
    25
    The same procedure 'AAINSERT_AA' is called again inside to insert the data and are commiting also...

    ---create or replace package PORTF_MIGR --package starts
    ---procedure AAINSERT_AA starts

    DECLARE
    I_KEY VARCHAR2(60);
    I_KEY_SEQ_NR NUMBER;
    I_O_DATE DATE;
    I_C_DATE DATE;

    BEGIN
    I_KEY := NULL;
    I_KEY_SEQ_NR := NULL;
    I_O_DATE := NULL;
    I_C_DATE := NULL;

    PORTF_MIGR.AAINSERT_AA ( I_KEY, I_SEQ_NR, I_O_DATE, I_C_DATE );
    COMMIT;
    END;

    ---procedure AAINSERT_AA ends
    ---package PORTF_MIGR --package ends

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Could you please show us the output of...
    Code:
    select  owner,
            object_name,
            subobject_name
    from    dba_objects
    where   object_name in ('PORTF_MIGR','AAINSERT_AA')
    ;
    select  *
    from    dba_synonyms
    where   synonym_name in ('PORTF_MIGR','AAINSERT_AA')
    or      table_name in ('PORTF_MIGR','AAINSERT_AA')
    ;
    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
    Apr 2008
    Location
    bangalore
    Posts
    25
    hi Pavb,
    output for the 1st query is

    owner object_name sub_object_name
    k port_migr#
    k port_migr#


    output for the second query is nothing..

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I'm really sorry Sadha, first query should query: owner, object_name and object_type columns... not suboject_name - my bad.

    Could you please run the revised version?

    Code:
    select  owner,
            object_name,
            object_type
    from    dba_objects
    where   object_name in ('PORTF_MIGR','AAINSERT_AA')
    ;
    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.

  7. #7
    Join Date
    Apr 2008
    Location
    bangalore
    Posts
    25
    Owner Object_name Object_type

    k port_migr# package_body
    k port_migr# package

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Could you please extract package BODY k.port_migr and take a look at procedure AAINSERT_AA ?
    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.

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