1 2 Previous Next 18 Replies Latest reply: Jul 25, 2014 8:34 AM by John Spencer RSS

    Stored Procedures

    969952

      Hi All,

       

      I would like to write a stored Procedure which includes a Merge Statemnt. Here is the requirement.

       

      Step 1:   If new record created,Inserted and then set  CREATE_DT = SYSDATE,

                                                                                                 UPDT_DT      = SYSDATE

                                                                                                 ACT_IND = 'Y'

      Step2 :     If existing record updated the set UPD_DT = SYSDATE

       

      Step 3:  If record removed then set UPDT_DT  = SYSDATE

                                                                   ACT_IND = 'N'

       

      and if there is  an UPDATE , we could also mark the existing record as INACTIVE and create new record and set that to ACTIVE.

       

      Please share your thoughts to write a Stored Proceudre for the above requirement.

       

      Thanks

       

       


        • 1. Re: Stored Procedures
          SomeoneElse

          OK, so you have tried writing a MERGE statement?

           

          You haven't posted your tables and data so we can only guess.

          • 2. Re: Stored Procedures
            969952

            No nothing like that... i would like to know the Skelton hoe can we add Merge statement in SP..

             

            Merge into copy_emp C

              using EMP E

            on (c.empno = e.empno)

            when matched then

              update set c.<>= e.<>

            ...

             

            when not matched then

              insert values (e.empno..)

             

            in my above requirement i have tables called  IMP_DT anbd EXP_DT

             

            Thanks..


            • 3. Re: Stored Procedures
              SomeoneElse

              > and if there is  an UPDATE , we could also mark the existing record as INACTIVE and create new record and set that to ACTIVE.

               

              Sorry, I just saw this in your requirements.  Can't do that in a merge.

              • 4. Re: Stored Procedures
                Frank Kulash

                Hi,

                 

                MERGE can do either an INSERT or an UPDATE.

                You want to do both an INSERT and an UPDATE (if there is an existing row; otherwise just an INSERT).  That is, you may or may not UPDATE a row, but you'll always INSERT a row.  I think the simplest way to do that in a procedure is to

                1. UPDATE the existing row (if any)  If you need to copy any of the data to the new row, capture that data via RETURNING.
                2. INSERT the new row.

                 

                 

                I hope this answers your question.
                If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
                Since yu're asking about a DML statement, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
                Explain, using specific examples, how you get those results from that data.
                Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
                See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

                • 5. Re: Stored Procedures
                  969952

                  we are using Oracle 11.2.0.3

                  • 6. Re: Stored Procedures
                    969952

                    here we have some dataload tables ( around 10). for those dataload tables we need to add

                    CREATE_DT, UPDT_DT , ACT_IND  then we need to follow above tasks. we have to do the merge between DATALOAD_TABLEs and other External tables.

                     

                    CREATE OR REPLACE PROC...

                     

                    MERGE INTO DAT_LOAD tables

                    USING  EXTERNAL_TABLES

                    ..

                    <    END PROC>

                    please advice how can we resolbve the above given task using these tables list..

                    please let me know for more information..


                    • 7. Re: Stored Procedures
                      969952

                      Please let me know if you need mor information.

                       

                      Thanks/.

                      • 8. Re: Stored Procedures
                        Frank Kulash

                        Hi,

                        969952 wrote:

                         

                        Please let me know if you need mor information.

                         

                        Thanks/.

                        Nothing I haven't mentioned already.

                         

                        Post CREATE TABLE statements for all tables involved.  Simplify the problem, if possible.  For example, if you actually have 10 dataload tables, post a problem with only (say) 2; just make it clear that you really need to handle 10.

                        Post INSERT statements to who how the tables look before calling the procedure for the first time.

                        Post some procedure calls.  Show what the changed tables look like after each procedure call is finsihed.

                        • 9. Re: Stored Procedures
                          969952

                          Here is the structure for DATA_LAOD and EMP_EXT tables. Please havea  look.

                           

                          {code}

                          DATA_LOAD tables

                          CREATE TABLE SCOTT.EMPS

                          (

                            ENO                             NUMBER(8),

                            EMP_ID                          NUMBER(9),

                            DOJ                             DATE,

                            LAST_DAY                        DATE,

                            ENAME                           VARCHAR2(20 BYTE),

                            CREATE_DT                       DATE,

                            UPDT_DT                         DATE,

                            ACT_IND                         VARCHAR2(1)

                          );

                          {code}

                           

                          and here is the External table structure.

                           

                          CREATE  table scott.emps_ET

                          (

                          ENO                             NUMBER(8), 

                            EMP_ID                          NUMBER(9),

                            DOJ                             DATE,

                            LAST_DAY                        DATE,

                            ENAME                           VARCHAR2(20 BYTE),

                            CREATE_DT                       DATE,

                            UPDT_DT                         DATE,

                            ACT_IND                        varchar2(1)                     

                           

                          )

                          ORGANIZATION EXTERNAL

                            (  TYPE ORACLE_LOADER

                               DEFAULT DIRECTORY EMP_DIR

                               ACCESS PARAMETERS

                                 ( RECORDS DELIMITED BY NEWLINE SKIP 1

                                  BADFILE CBDW_DIR: 'EMPS.BAD'

                                  LOGFILE CBDW_DIR: 'EMPS.LOG'

                                  DISCARDFILE CBDW_DIR: 'EMPS.DSC'

                                  FIELDS TERMINATED BY '`' MISSING FIELD VALUES ARE NULL            )

                               LOCATION (EMP_DIR:'SProc.out')

                            )

                          REJECT LIMIT 20

                          NOPARALLEL

                          NOMONITORING;

                          {code}

                           

                          so here the requirement as mentioned

                          I would like to write a stored Procedure which includes a Merge Statemnt. Here is the requirement.

                           

                          Step 1:   If new record created,Inserted and then set  CREATE_DT = SYSDATE,

                                                                                                                     UPDT_DT      = SYSDATE

                                                                                                                     ACT_IND = 'Y'

                          Step2 :     If existing record updated the set UPD_DT = SYSDATE

                           

                          Step 3:  If record removed then set UPDT_DT  = SYSDATE

                                                                                       ACT_IND = 'N'

                           

                          and if there is  an UPDATE , we could also mark the existing record as INACTIVE and create new record and set that to ACTIVE.

                           

                           

                           

                          Thanks

                          • 10. Re: Stored Procedures
                            969952

                            I have already created all required External tables. we need to load data from External table to DATA_LOAD tables by using MERGE Concept.. for this we need to write a procedure.. can any one please helo me out..


                            • 11. Re: Stored Procedures
                              969952

                              Hi Frank,

                               

                              I have posted Create table ctructure for both load table and External tables. By using .TXT fine we are going to load the data into External table then by using Store Procedure we are going to load the data into the DATA_LOAD table by using MERGE statement inside the Stored Procedure..

                               

                              So can you share your thoughts as per the given requirement how can we write a Stored Procedure..  let me know if I need to provide more information.

                               

                              Thanks.

                              • 12. Re: Stored Procedures
                                969952

                                Hi All,

                                 

                                hope provided the required information. Please have a ;look and advice.

                                 

                                Thanks

                                • 13. Re: Stored Procedures
                                  969952

                                  as I don't have any insert statements I didn't gave any Insert staements here in my request. So I pasted thw two create table structures for LOAD_DATA and External tables. and am working on Oracle 11.2.0.3.

                                   

                                  so now I have External table. so need to copy the data into load_data table by using MERGE statement which satisfy tne below requirements

                                  Step 1:   If new record created,Inserted and then set  CREATE_DT = SYSDATE,

                                                                                                                             UPDT_DT      = SYSDATE

                                                                                                                             ACT_IND = 'Y'

                                  Step2 :     If existing record updated the set UPD_DT = SYSDATE

                                   

                                  Step 3:  If record removed then set UPDT_DT  = SYSDATE

                                                                                               ACT_IND = 'N'

                                   

                                  please share your thoughts on it. Thank you

                                  • 14. Re: Re: Stored Procedures
                                    John Spencer

                                    The general structure of the statement would be something like:

                                     

                                    merge into table tab

                                    using (select columns from external_table) ext

                                    on (tab.pk = ext.pk)

                                    when matched then

                                       update

                                       set tab.col1 = ext.col1,

                                           tab.col2 = ext.col2,

                                           ...

                                           tab.updt_dt = sysdate

                                    when not matched then

                                       insert (tab.col1, tab.col2, ..., tab.create_dt,

                                               tab.updt_dt, tab.act_ind)

                                       values(ext.col1, ext.col2, ..., sysdate,

                                              sysdate, 'Y');

                                    You would need to run a seperate update statement to update act_ind to N for the rows that were "removed"

                                     

                                    John

                                    1 2 Previous Next