5 Replies Latest reply: May 25, 2012 5:06 AM by Purvesh K RSS

    stored procedure for arhive

    939590
      I created a stored procedure for send data from a source bd to the bd destination with a link between BD
      But it will not work

      CREATE OR REPLACE PROCEDURE archivage ( source IN VARCHAR2, destination IN VARCHAR2)
      BEGIN
      / * - Prepare a cursor to select from the bd_source: * /

      source_cursor: = dbms_sql.open_cursor;
      DBMS_SQL.PARSE (source_cursor,
      'SELECT table_name FROM dba_tables' | | source,DBMS_SQL.NATIVE);
      LOOP i in source_cursor
      /* Or i use this !!!!
      SELECT object_name FROM dba_objects WHERE owner = 'SCOTT' AND object_type = 'TABLE'*/

      DBMS_SQL.DEFINE_COLUMN (source_cursor, i);
      end loop
      ignorer: DBMS_SQL.EXECUTE = (source_cursor);

      / * - Prepare a cursor to insert into the destination db: * /

      destination_cursor: = DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE (destination_cursor,
      'INSERT INTO' | | destination | | DBMS_SQL.NATIVE);

      / * - Fetch a row from the source table and insert it into the destination table: * /
      loop
      if DBMS_SQL.FETCH_ROWS (source_cursor)> 0 ALORS
      - Obtenir des valeurs de colonne de la ligne
      DBMS_SQL.COLUMN_VALUE (source_cursor,i);

      DBMS_SQL.BIND_VARIABLE (destination_cursor,i);


      ignorer: DBMS_SQL.EXECUTE = (destination_cursor);
      ELSE

      EXIT;
      End If;
      END LOOP;

      /*-Close cursor */
      COMMIT;
      DBMS_SQL.CLOSE_CURSOR (source_cursor);
      DBMS_SQL.CLOSE_CURSOR (destination_cursor);
      EXCEPTION
      WHEN OTHERS, THEN
      if
      DBMS_SQL.IS_OPEN (source_cursor)then
      DBMS_SQL.CLOSE_CURSOR (source_cursor);
      End If;
      if DBMS_SQL.IS_OPEN (destination_cursor) then
      DBMS_SQL.CLOSE_CURSOR (destination_cursor);
      End If;
      RAISE;
      END;
        • 1. Re: stored procedure for arhive
          Purvesh K
          936587 wrote:
          I created a stored procedure for send data from a source bd to the bd destination with a link between BD
          But it will not work
          Sounds pretty certain that your code will not work...

          I could not understand your intention of writing this stored procedure. Can you please explain?

          All I could understand was, you are trying to create a Copy of the Objects from the Source to the Target.

          There could be an easier way to do that
          create table target_objects_table as select * from dba_objects where user='SCOTT' and object_type='TABLE';
          We would appreciate if you can elaborate what issue you faced in the below mentioned code!!!


          Regards,
          P.
          • 2. Re: stored procedure for arhive
            939590
            subject is to create a stored procedure for archiving data the source database from archive database using linknetwork betwen two bd
            • 3. Re: stored procedure for arhive
              Igor.M
              find 10 differences ... :(
              CREATE OR REPLACE PROCEDURE archivage (source        IN VARCHAR2,
                                                     destination   IN VARCHAR2)
              IS
                 source_cursor        INTEGER;
                 destination_cursor   INTEGER;
                 ignorer              INTEGER;
              BEGIN
                 /* - Prepare a cursor to select from the bd_source: */
              
                 source_cursor := DBMS_SQL.open_cursor;
                 DBMS_SQL.
                  PARSE (source_cursor,
                         'SELECT table_name FROM dba_tables' || source,
                         DBMS_SQL.NATIVE);
              
                 FOR i IN source_cursor
                 LOOP
                    /* Or i use this !!!!
                    SELECT object_name FROM dba_objects WHERE owner = 'SCOTT' AND object_type = 'TABLE'*/
              
                    DBMS_SQL.DEFINE_COLUMN (source_cursor, i);
                 END LOOP;
              
                 ignorer := DBMS_SQL.EXECUTE (source_cursor);
              
                 /* - Prepare a cursor to insert into the destination db: */
              
                 destination_cursor := DBMS_SQL.OPEN_CURSOR;
                 DBMS_SQL.
                  PARSE (destination_cursor,
                         'INSERT INTO ' || destination,
                         DBMS_SQL.NATIVE);
              
              
                 /* - Fetch a row from the source table and insert it into the destination table: */
                 LOOP
                    IF DBMS_SQL.FETCH_ROWS (source_cursor) > 0
                    THEN
                       --ALORS
                       -- Obtenir des valeurs de colonne de la ligne
                       DBMS_SQL.COLUMN_VALUE (source_cursor, i);
              
              
                       DBMS_SQL.BIND_VARIABLE (destination_cursor, i);
                       ignorer := DBMS_SQL.EXECUTE (destination_cursor);
                    ELSE
                       EXIT;
                    END IF;
                 END LOOP;
              
              
                 /*-Close cursor */
                 COMMIT;
                 DBMS_SQL.CLOSE_CURSOR (source_cursor);
                 DBMS_SQL.CLOSE_CURSOR (destination_cursor);
              EXCEPTION
                 WHEN OTHERS
                 THEN
                    IF DBMS_SQL.IS_OPEN (source_cursor)
                    THEN
                       DBMS_SQL.CLOSE_CURSOR (source_cursor);
                    END IF;
              
                    IF DBMS_SQL.IS_OPEN (destination_cursor)
                    THEN
                       DBMS_SQL.CLOSE_CURSOR (destination_cursor);
                    END IF;
              
                    RAISE;
              END;
              /
              • 4. Re: stored procedure for arhive
                939590
                CREATE OR REPLACE PROCEDURE archivage ( source IN VARCHAR2, destination IN VARCHAR2)
                IS
                source_cursor INTEGER;
                destination_cursor INTEGER ;
                ignorer INTEGER;
                i integer;


                BEGIN

                /* - Préparer un curseur pour sélectionner à partir de la bd_source: */
                source_cursor: = dbms_sql.open_cursor;
                DBMS_SQL.PARSE (source_cursor,
                'SELECT table_name FROM dba_tables' | | source,DBMS_SQL.NATIVE);
                LOOP i in source_cursor
                /* soit j'utilise cette req
                SELECT object_name FROM dba_objects WHERE owner = 'SCOTT' AND object_type = 'TABLE'*/

                DBMS_SQL.DEFINE_COLUMN (source_cursor, i);
                end loop
                ignorer: DBMS_SQL.EXECUTE = (source_cursor);

                /* - Préparer un curseur à insérer dans la bd de destination: */
                destination_cursor: = DBMS_SQL.OPEN_CURSOR;
                DBMS_SQL.PARSE (destination_cursor,
                'INSERT INTO' | | destination,DBMS_SQL.NATIVE);

                /* - Récupère une ligne de la table source et l'insérer dans la table de destination: */
                loop
                if DBMS_SQL.FETCH_ROWS (source_cursor)> 0 ALORS
                - Obtenir des valeurs de colonne de la ligne
                DBMS_SQL.COLUMN_VALUE (source_cursor,i);

                DBMS_SQL.BIND_VARIABLE (destination_cursor,i);


                ignorer: DBMS_SQL.EXECUTE = (destination_cursor);
                ELSE

                EXIT;
                End If;
                END LOOP;

                /*-fermer les curseurs: */
                COMMIT;
                DBMS_SQL.CLOSE_CURSOR (source_cursor);
                DBMS_SQL.CLOSE_CURSOR (destination_cursor);
                EXCEPTION
                WHEN OTHERS, THEN
                if
                DBMS_SQL.IS_OPEN (source_cursor)then
                DBMS_SQL.CLOSE_CURSOR (source_cursor);
                End If;
                if DBMS_SQL.IS_OPEN (destination_cursor) then
                DBMS_SQL.CLOSE_CURSOR (destination_cursor);
                End If;
                RAISE;
                END;

                show error;
                • 5. Re: stored procedure for arhive
                  Purvesh K
                  I would wish you LUCK for what-so-ever you are up to. You have a short and sweet way of accomplishing the task [suggested in an earlier post], but chose to ignore and take an arduous one.

                  I pray to accomplish it well!!!

                  Regards,
                  P.