Advertisement

08.10.2005 at 05:01AM PDT, ID: 21522015
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
7.6

procedure error due to connection

Asked by sharscho in Oracle Database

Tags:

Have another procedure problem that I don't know how to debug. I comparred the procedure and package body of this DB with another DB of the same application only in a different country and when I found that the bodies were different I did change the bodies to be the same and thought that the error would go away but stayed and is the same;

the error:
Wed Aug 10 13:19:12 2005
Errors in file E:\ORADATA\admin\FAGW\bdump\fagwSNP3.TRC:
ORA-12012: error on auto execute of job 1000
ORA-12008: error in snapshot refresh path
ORA-01004: default username feature not supported; logon denied
ORA-01988: remote os logon is not allowed
OSD-00000: Message 0 not found;  product=RDBMS73; facility=SOSD
ORA-02063: preceding 3 lines from GBS
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 271
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 139
ORA-06512: at "REPSYS.STATICREPLICATION", line 60
ORA-06512: at "REPSYS.PRC$_STATICREPLICATION", line 6
ORA-06512: at line 1

the package body:
CREATE OR REPLACE PACKAGE REPSYS.StaticReplication
IS
   FUNCTION generate_procedure_name( owner IN VARCHAR2
                                   , table_name IN VARCHAR2 )
                                   RETURN VARCHAR2;

   FUNCTION generate_snapshot_name( owner IN VARCHAR2
                                  , table_name IN VARCHAR2 )
                                  RETURN VARCHAR2;

   PROCEDURE replicate( owner IN VARCHAR2
                      , table_name IN VARCHAR2 );

   PROCEDURE generate_master_script( owner IN VARCHAR2
                                  , table_name IN VARCHAR2 );

   PROCEDURE generate_snapshot_script( owner IN VARCHAR2
                                     , table_name IN VARCHAR2 );
END;
/


CREATE OR REPLACE PACKAGE BODY REPSYS.StaticReplication
IS

/* naming conventions */
-------------------------------------------------------------
FUNCTION generate_procedure_name( owner IN VARCHAR2
                                , table_name IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
   RETURN owner || '.rsp$_' || table_name;
END;
-------------------------------------------------------------
FUNCTION generate_snapshot_name( owner IN VARCHAR2
                               , table_name IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
   RETURN owner || '.sn_' || table_name;
END; -- end generate_snapshot_name
-------------------------------------------------------------
FUNCTION generate_snapshot_table( owner IN VARCHAR2
                                , table_name IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
   RETURN owner || rpad( '.snap$_sn_' || table_name, 27) ; -- this length should stay 27!!!
END; -- end generate_snapshot_table
-------------------------------------------------------------
FUNCTION generate_snapshot_index( owner IN VARCHAR2
                                , table_name IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
   RETURN owner || '.i_sn_' || table_name;
END; -- end generate_snapshot_index
-------------------------------------------------------------

/* all procedures */
PROCEDURE replicate( owner IN VARCHAR2
                   , table_name IN varchar2)
IS
   cursor_handle NUMBER(38);
   updated_rows  NUMBER(38);
   l_proc_name   VARCHAR2(500);

   CURSOR replication_status_cur ( proc_name varchar2 )
   IS
      SELECT execution_date
        FROM replication_status
       WHERE procedure_name = proc_name
         FOR update ;

   replication_status_rec replication_status_cur%ROWTYPE;
BEGIN
   l_proc_name := generate_procedure_name(owner, table_name);

   -- First refresh the snapshot
   DBMS_OUTPUT.PUT_LINE('-- PDX start snapshot refresh ' ||to_char(sysdate,'HH24:MI:SS') );
   DBMS_SNAPSHOT.REFRESH(generate_snapshot_name(owner, table_name),'?');

   -- Then update the new master table
   DBMS_OUTPUT.PUT_LINE('-- PDX start update table' ||to_char(sysdate,'HH24:MI:SS') );
   OPEN replication_status_cur (l_proc_name);
   FETCH replication_status_cur INTO replication_status_rec;
   IF replication_status_cur%FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE('-- PDX exec date = ' ||
                to_char(replication_status_rec.execution_date, 'DD-MON-YYYY HH24:MI:SS') );
      cursor_handle := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cursor_handle,
        'BEGIN ' ||
           l_proc_name || '( to_date( ''' ||
            to_char(replication_status_rec.execution_date, 'DD-MON-YYYY HH24:MI:SS') ||
              ''', ''DD-MON-YYYY HH24:MI:SS'') ); ' ||
        'END;',
      DBMS_SQL.V7);

      updated_rows := DBMS_SQL.EXECUTE(cursor_handle);
      DBMS_SQL.CLOSE_CURSOR(cursor_handle);

      UPDATE replication_status
         SET execution_date = sysdate
       WHERE current of replication_status_cur ;
   ELSE
      DBMS_OUTPUT.PUT_LINE('-- PDX no exec date' );
      cursor_handle := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cursor_handle,
        'BEGIN ' ||
           l_proc_name || '(NULL); ' ||
        'END;',
      DBMS_SQL.V7);

      updated_rows := DBMS_SQL.EXECUTE(cursor_handle);
      DBMS_SQL.CLOSE_CURSOR(cursor_handle);

      INSERT INTO replication_status
           ( procedure_name
           , execution_date
           )
      VALUES
           ( l_proc_name
           , sysdate
           ) ;

   END IF;
   CLOSE replication_status_cur;
   DBMS_OUTPUT.PUT_LINE('-- PDX end update table' ||to_char(sysdate,'HH24:MI:SS') );

   COMMIT;
end; -- end replicate



--
-- all the procedures to create the replication scripts
--

PROCEDURE generate_master_script( owner IN VARCHAR2
                               , table_name IN VARCHAR2 )
IS
   file_handle UTL_FILE.file_type;
BEGIN
   DBMS_OUTPUT.PUT_LINE('REM' );
   DBMS_OUTPUT.PUT_LINE('PROMPT Creating Snapshot Logs for ' || owner || '.' || table_name );

   DBMS_OUTPUT.PUT_LINE('REM' );
   DBMS_OUTPUT.PUT_LINE('REM '||TO_CHAR(sysdate,'DD/MM/YYYY HH24:MI') );
   DBMS_OUTPUT.PUT_LINE('REM' );

   DBMS_OUTPUT.PUT_LINE('set termout off');
   DBMS_OUTPUT.PUT_LINE('DROP SNAPSHOT LOG ON ' ||
                    owner || '.' || table_name  );
   DBMS_OUTPUT.PUT_LINE('/');
   DBMS_OUTPUT.PUT_LINE('set termout on');

   DBMS_OUTPUT.PUT_LINE('CREATE SNAPSHOT LOG ON ' ||
                    owner || '.' || table_name  );
   DBMS_OUTPUT.PUT_LINE('/');
END;



--declaration of a package internal procedure defined later
PROCEDURE generate_upd_ins( owner VARCHAR2
                          , table_name VARCHAR2
);


/* pdx */
PROCEDURE generate_snapshot_script( owner IN VARCHAR2
                                  , table_name IN VARCHAR2 )
IS
BEGIN
   -- first generate the snapshot
   DBMS_OUTPUT.PUT_LINE('REM' );
   DBMS_OUTPUT.PUT_LINE('PROMPT Creating Snapshot and Procedure for ' || owner || '.' || table_name );

   DBMS_OUTPUT.PUT_LINE('REM' );
   DBMS_OUTPUT.PUT_LINE('REM '||TO_CHAR(sysdate,'DD/MM/YYYY HH24:MI') );
   DBMS_OUTPUT.PUT_LINE('REM' );

   DBMS_OUTPUT.PUT_LINE('-- The snapshot  ');
   DBMS_OUTPUT.PUT_LINE('set termout off');
   DBMS_OUTPUT.PUT_LINE('DROP SNAPSHOT ' ||
                         generate_snapshot_name(owner, table_name) );
   DBMS_OUTPUT.PUT_LINE('/');
   DBMS_OUTPUT.PUT_LINE('set termout on');

   DBMS_OUTPUT.PUT_LINE('CREATE SNAPSHOT ' ||
                         generate_snapshot_name(owner, table_name));
   DBMS_OUTPUT.PUT_LINE('REFRESH FAST');
   DBMS_OUTPUT.PUT_LINE('AS');
   DBMS_OUTPUT.PUT_LINE('SELECT TAB.*, sysdate modification_date');
   DBMS_OUTPUT.PUT_LINE('  FROM ' || owner || '.' || table_name ||  '@&&db_link TAB' );
   DBMS_OUTPUT.PUT_LINE('/');

   DBMS_OUTPUT.PUT_LINE('-- The snapshot index  ');
   DBMS_OUTPUT.PUT_LINE('CREATE INDEX ' ||
                         generate_snapshot_index(owner, table_name));
   DBMS_OUTPUT.PUT_LINE('  ON '||
                         generate_snapshot_table(owner, table_name));
   DBMS_OUTPUT.PUT_LINE('  ( modification_date );' );

   -- then generate the procedure
   DBMS_OUTPUT.PUT_LINE('-- The procedure  ');
   DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE PROCEDURE ' ||
                        generate_procedure_name(owner, table_name) ||
                        '(p_execution_date IN DATE)'   );
   DBMS_OUTPUT.PUT_LINE('IS');

   -- extra cursor for update
   DBMS_OUTPUT.PUT_LINE('--');
   DBMS_OUTPUT.PUT_LINE('-- cursor for updates');
   DBMS_OUTPUT.PUT_LINE('  cursor cur_update is');
   DBMS_OUTPUT.PUT_LINE('  select t.* from ' ||
                           generate_snapshot_name(owner, table_name) || ' t' );
   DBMS_OUTPUT.PUT_LINE('  where t.modification_date > p_execution_date;');
   DBMS_OUTPUT.PUT_LINE('--');

   DBMS_OUTPUT.PUT_LINE('BEGIN');

   generate_upd_ins(owner, table_name );

   DBMS_OUTPUT.PUT_LINE('END;');
   DBMS_OUTPUT.PUT_LINE('/');

END;


/* pdx */
PROCEDURE generate_upd_ins( owner VARCHAR2
                          , table_name VARCHAR2
)
IS
   CURSOR non_primary_key_fields( p_owner VARCHAR2
                                , p_table_name VARCHAR2 )
   IS
      SELECT column_name
        FROM all_tab_columns COLS
       WHERE COLS.owner = p_owner
         AND COLS.table_name = p_table_name
       MINUS
      SELECT column_name
        FROM all_constraints CONS
           , all_cons_columns CONSCOL
        WHERE CONS.owner = p_owner
          AND CONS.table_name = p_table_name
          AND CONSCOL.owner = CONS.owner
          AND CONSCOL.table_name = CONS.table_name
          AND CONSCOL.constraint_name = CONS.constraint_name
          AND CONS.constraint_type = 'P' ;

   CURSOR primary_key_fields( p_owner VARCHAR2
                            , p_table_name VARCHAR2 )
   IS
      SELECT column_name
        FROM all_constraints CONS
           , all_cons_columns CONSCOL
        WHERE CONS.owner = p_owner
          AND CONS.table_name = p_table_name
          AND CONSCOL.owner = CONS.owner
          AND CONSCOL.table_name = CONS.table_name
          AND CONSCOL.constraint_name = CONS.constraint_name
          AND CONS.constraint_type = 'P'
        ORDER BY column_name;

   CURSOR all_fields( p_owner VARCHAR2
                            , p_table_name VARCHAR2 )
   IS
      SELECT column_name
        FROM all_tab_columns COLS
       WHERE COLS.owner = p_owner
         AND COLS.table_name = p_table_name
        ORDER BY column_name;

   column_counter NUMBER;

BEGIN

-- fast refresh? ...
   DBMS_OUTPUT.PUT_LINE('   IF p_execution_date IS NOT NULL');
   DBMS_OUTPUT.PUT_LINE('   THEN ');

   DBMS_OUTPUT.PUT_LINE('      -- ');
   DBMS_OUTPUT.PUT_LINE('  DBMS_OUTPUT.PUT_LINE(''      -- pdx delete '');' );
   DBMS_OUTPUT.PUT_LINE('      -- delete the rows in the child table that do not exist');
   DBMS_OUTPUT.PUT_LINE('DELETE FROM '|| owner || '.' || table_name || ' TARGET');
   DBMS_OUTPUT.PUT_LINE(' WHERE NOT EXISTS ( SELECT ''dummy'' ');
   DBMS_OUTPUT.PUT_LINE('       FROM '|| generate_snapshot_name(owner, table_name)||' SOURCE');
   column_counter := 1;
   FOR column in primary_key_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE('            WHERE SOURCE.' || column.column_name || ' = TARGET.' || column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('            AND   SOURCE.' || column.column_name || ' = TARGET.' || column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('           ); ');



   -- the update  should come before the insert !!!
   DBMS_OUTPUT.PUT_LINE('      -- ');
   DBMS_OUTPUT.PUT_LINE('  DBMS_OUTPUT.PUT_LINE(''      -- pdx update '');' );
   DBMS_OUTPUT.PUT_LINE('      -- then update the existing rows');

   DBMS_OUTPUT.PUT_LINE('      FOR R IN CUR_UPDATE LOOP');

   DBMS_OUTPUT.PUT_LINE('      UPDATE ' || owner || '.' || table_name || ' TARGET SET');

   column_counter := 1;
   FOR column in non_primary_key_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE( '     ' || column.column_name ||' = R.'|| column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('    , ' || column.column_name ||' = R.'|| column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;

   column_counter := 1;
   FOR column in primary_key_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE('            WHERE TARGET.' || column.column_name || ' = R.' || column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('            AND   TARGET.' || column.column_name || ' = R.' || column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('      ;');
   DBMS_OUTPUT.PUT_LINE('      end loop;');


   -- the insert should come after the update !!
   DBMS_OUTPUT.PUT_LINE('      -- ');
   DBMS_OUTPUT.PUT_LINE('  DBMS_OUTPUT.PUT_LINE(''      -- pdx insert '');' );
   DBMS_OUTPUT.PUT_LINE('      -- insert the new rows ');
   DBMS_OUTPUT.PUT_LINE('      insert into ' || owner || '.' || table_name);
   column_counter := 1;
   FOR column in all_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE('       ( ' || column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('       , ' || column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('      )');
   column_counter := 1;
   FOR column in all_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE('       SELECT ' || column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('       , ' || column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('   FROM ' || generate_snapshot_name(owner, table_name) );
   DBMS_OUTPUT.PUT_LINE('  MINUS ');
   column_counter := 1;
   FOR column in all_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE('       SELECT ' || column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('       , ' || column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('   FROM ' || owner || '.' || table_name || ';' );

--   DBMS_OUTPUT.PUT_LINE('      end loop;');
   DBMS_OUTPUT.PUT_LINE('      -- ');

-- p_execution_date is NULL !! ==> full replace of the table
   DBMS_OUTPUT.PUT_LINE('ELSE');

   DBMS_OUTPUT.PUT_LINE('      -- ');
   DBMS_OUTPUT.PUT_LINE('  DBMS_OUTPUT.PUT_LINE(''      -- pdx delete full '');' );
   DBMS_OUTPUT.PUT_LINE('      -- delete the rows in the child table');
   DBMS_OUTPUT.PUT_LINE('DELETE FROM '|| owner || '.' || table_name || ';');

   DBMS_OUTPUT.PUT_LINE('      -- ');
   DBMS_OUTPUT.PUT_LINE('  DBMS_OUTPUT.PUT_LINE(''      -- pdx insert full '');' );
   DBMS_OUTPUT.PUT_LINE('      -- insert the new rows ');
   DBMS_OUTPUT.PUT_LINE('      insert into ' || owner || '.' || table_name);
   column_counter := 1;
   FOR column in all_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE('       ( ' || column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('       , ' || column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('      )');
   column_counter := 1;
   FOR column in all_fields(UPPER(owner), UPPER(table_name))
   LOOP
      IF column_counter = 1
      THEN
         DBMS_OUTPUT.PUT_LINE('       SELECT ' || column.column_name );
      ELSE
         DBMS_OUTPUT.PUT_LINE('       , ' || column.column_name );
      END IF;
      column_counter := column_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('   FROM ' || generate_snapshot_name(owner, table_name) );
   DBMS_OUTPUT.PUT_LINE(';');

   DBMS_OUTPUT.PUT_LINE('END IF;');

END;

END;  --end of the package body
/
Start Free Trial
 
Keywords: procedure error due to connection
Title
1 DBMS_STATS procedure
2 DBMS_STAT
3 dbms_job schedule
4 VARCHAR2 or CHAR, does it matter?
5 passing VARCHAR2 as parameter in …
6 dbms_lob.writeappend(...)
 
Loading Advertisement...
 
[+][-]08.10.2005 at 05:04AM PDT, ID: 14640894

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.10.2005 at 05:23AM PDT, ID: 14641026

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.10.2005 at 01:00PM PDT, ID: 14645903

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]08.11.2005 at 02:17AM PDT, ID: 14649889

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Oracle Database
Tags: all_fields
Sign Up Now!
Solution Provided By: syntheticbyte
Participating Experts: 3
Solution Grade: A
 
 
[+][-]08.12.2005 at 04:52AM PDT, ID: 14659702

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]08.12.2005 at 06:14AM PDT, ID: 14660264

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02.17.2006 at 11:40PM PST, ID: 15987615

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]02.22.2006 at 05:15AM PST, ID: 16018296

Experts Exchange has a courteous staff of administrators who help members get the most out of the website by means of administrative comments like this one.

Start your 7-day free trial to view this Administrative Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32