8 Replies Latest reply on Apr 27, 2013 4:12 PM by 750281

    need suggestion

    750281
      hi ,
      i am using 10g database and forms.

      scenario is that i have made a copy of back end procedure for test run and that procedure is calling on button click in the form to show the output. but problem is that
      as it is a copy of procedure. so whenever i change in the original procedure that i have to copy in the test procedure. so is there any copy command or any good suggestion
      to perform this requirement?

      please suggest


      thanks.
        • 1. Re: need suggestion
          Sigh...
          No version information. No platform information.

          Obviously in GUI tools like Sql*developer you can use copy and paste.

          Yes, you need to use some keystrokes. That is the impact of creating a copy of a procedure, which you definitely shouldn't have done.
          Procedures exist to avoid duplicating code. Now you are duplicating code by duplicating procedures. That is simply appalling.

          -----------
          Sybrand Bakker
          Senior Oracle DBA
          1 person found this helpful
          • 2. Re: need suggestion
            750281
            i am duplicating procedure because i have to use different tables but code will be the same. because on press the procedure will execute and then output will be shown from the tables. if i use main code then i will be unable to track that which are test run and which are the required run.
            • 3. Re: need suggestion
              Etbin
              ... if i use main code then i will be unable to track that which are test run and which are the required run.
              Don't you have separate (development, test, (UAT,) production) database environments ?
              You could use synonyms to run the procedure using different tables for your test run if you're able to prevent others running the required run at the same time.

              Regards

              Etbin
              1 person found this helpful
              • 4. Re: need suggestion
                750281
                yaa i have an option of different schema . if you please elaborate your suggestion further will be very helpful.. my problem is that i want to use that procedure for single record run and in different tables but coding will be the same. so if anything require to change in coding then i need to change in both main and test program.

                hope this help


                thanks
                • 5. Re: need suggestion
                  Nitesh.
                  When you have different environments then make use of DB links as creating synonyms for each object will consume time too and advantage of DB links is you can later access other objects too in future ..

                  Regards..
                  • 6. Re: need suggestion
                    rp0428
                    >
                    yaa i have an option of different schema . if you please elaborate your suggestion further will be very helpful.. my problem is that i want to use that procedure for single record run and in different tables but coding will be the same. so if anything require to change in coding then i need to change in both main and test program.
                    >
                    No - not 'different schema'. He said different environment. Same schema, same tables, different database or server. Some standard environments are: production, QA, TEST and DEV.

                    Please explain what you mean by 'single record run' and 'different tables'.
                    • 7. Re: need suggestion
                      Etbin
                      Seems I haven't got it right.
                      Another guess would be: you have a procedure you want to run in say normal and test mode i.e. the same code has to opearte on just different tables.
                      In that case you could simply add a parameter and make the code dynamic (hoping not to degeade performance)
                      procedure procedure_name(p_start_date in date,
                                               p_end_date in date,
                                               p_run_type in varchar2 default 'NORMAL') is
                        sql_text varchar2(4000);
                      begin
                        sql_text := 'select * from ' || decode(p_run_type,'TEST','test_table','normal_table') ||
                                    ' where some_date between :the_start and :the_end';
                      begin
                        execute immediate sql_text using p_start_date,p_end_date;
                      end;
                      Regards

                      Etbin
                      • 8. Re: need suggestion
                        750281
                        i think its not possible to write dynamic code as there are many DML statements and different tables inside the procedure . i think its dynamic code is fine at the initial stage if we want to take data in the cursor at the initial and use inside the program.


                        i think this is not possible .so i have to copy and paste the main program and then need to change the tables name through find and replace all, which i am doing at present.