Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Debugging scripts

Debugging scripts

From: GreatDayDan <Google_at_GreatDayDan.com>
Date: 13 Feb 2004 11:30:32 -0800
Message-ID: <f91b10ce.0402131130.2f9040eb@posting.google.com> 





Good Morning!



   I have a script that an Oracle DBA type person created.



   Sometimes, when running it, it does not finish but instead errors
out.



   I want to have some debug statements (a la Sql's Print '')sent to
the spool file so I can figure out where the problem is.



   Here is a shortened version of the script (the real one loops for
97 iterations):




PROMPT


PROMPT Create Sequences and Synonyms for finesse TIM database
Create Sequences and Synonyms for finesse TIM database
DECLARE


TYPE t_TableRecord IS RECORD (

  TableName     Varchar2(25),
  KeyField              Varchar2(25),
  Sequence            Char(1));





V_TableRecord t_TableRecord;



TYPE t_SeqTables IS TABLE OF V_TableRecord%Type
  INDEX BY BINARY_INTEGER;


V_Tables t_SeqTables;


V_COUNTER               NUMBER;
V_CursorID              NUMBER;
V_DropString    VARCHAR2(100);
V_CreateString    VARCHAR2(500);
V_Permissions     VARCHAR2(500);
V_Dummy         NUMBER;
V_SelectStatement VARCHAR2(500);
V_StartValue    BINARY_INTEGER;
V_SEQUENCE_NAME VARCHAR2(25);
V_Synonym_Name  VARCHAR2(30);
V_FoundSequence VARCHAR2(3);
V_FoundSynonym  VARCHAR2(3);





BEGIN



V_Tables(1).TableName   :=      'ARC_TBLNOTES';
V_Tables(1).KeyField    :=      'CNTNOTESID';
V_Tables(1).Sequence    :=      'N';





<snip>



v_Tables(97).TableName  := 'tblDLPrevNotes';
V_Tables(97).KeyField   := '';
V_Tables(97).Sequence   := 'N';








FOR v_LoopCounter IN 1..97 LOOP


/***************************** GET LAST COUNTER USED


*******************************






IF V_Tables(V_LoopCounter).Sequence='Y'


THEN


V_CursorID := DBMS_SQL.OPEN_CURSOR;


V_SelectStatement:= 'SELECT MAX(^)

                     FROM TIMADMIN.!';




V_SelectStatement:= REPLACE(V_SelectStatement,'^',V_Tables(V_LoopCounter).KeyField);
V_SelectStatement:= REPLACE(V_SelectStatement,'!',V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_SelectStatement, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(V_CursorID, 1, V_COUNTER);
V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


LOOP



IF DBMS_SQL.FETCH_ROWS(V_CursorID)=0 THEN
  EXIT;



END IF;



END LOOP;



DBMS_SQL.COLUMN_VALUE(V_CursorID, 1, V_COUNTER);
IF V_COUNTER IS NULL THEN V_COUNTER:=0;



END IF;



V_StartValue:=V_COUNTER+1;


DBMS_SQL.CLOSE_CURSOR(V_CursorID);



END IF;


************************  END OF GET LAST COUNTER USED 


****************************




*****************************  IF SEQUENCE EXISTS

**********************************





V_FoundSequence:='No';


IF V_Tables(V_LoopCounter).Sequence='Y'


THEN


V_CursorID := DBMS_SQL.OPEN_CURSOR;


V_SelectStatement:= 'SELECT SEQUENCE_NAME

                     FROM USER_SEQUENCES
                     WHERE SEQUENCE_NAME=:SN';

V_SelectStatement:= REPLACE(V_SelectStatement,'^',V_Tables(V_LoopCounter).KeyField);


V_SelectStatement:= REPLACE(V_SelectStatement,'!',V_Tables(V_LoopCounter).TableName);
V_Sequence_Name:='SEQ';



V_Sequence_Name:=CONCAT(V_Sequence_name,V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_SelectStatement, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(V_CursorID, 1, V_Sequence_Name,25);



DBMS_SQL.BIND_VARIABLE(V_CursorID,':SN',V_Sequence_Name);



V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);



V_FoundSequence:='No';



LOOP



IF DBMS_SQL.FETCH_ROWS(V_CursorID)>0 THEN
  DBMS_SQL.COLUMN_VALUE(V_CursorID, 1, V_Sequence_Name);
  V_FoundSequence:='Yes';


  ELSE



  EXIT;



END IF;



END LOOP;


DBMS_SQL.CLOSE_CURSOR(V_CursorID);



END IF;



****************************  END IF SEQUENCE EXISTS 







IF V_FoundSequence='Yes'


THEN


V_CursorID := DBMS_SQL.OPEN_CURSOR;



V_DropString:= 'DROP SEQUENCE TIMADMIN.SEQ^';
V_DropString:= REPLACE(V_DropString,'^',V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_DropString, DBMS_SQL.V7);



V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


DBMS_SQL.CLOSE_CURSOR(V_CursorID);



END IF;



END IF;





V_CursorID := DBMS_SQL.OPEN_CURSOR;


V_CreateString:=


'CREATE SEQUENCE TIMADMIN.SEQ^



INCREMENT BY 1



START WITH ~



MAXVALUE 9999999999



MINVALUE 1



NOCYCLE



CACHE 20



NOORDER';



V_CreateString:= REPLACE(V_CreateString,'~',TO_CHAR(V_StartValue,'09999'));
V_CreateString:= REPLACE(V_CreateString,'^',V_Tables(V_LoopCounter).TableName);




DBMS_SQL.PARSE(V_CursorID, V_CreateString, DBMS_SQL.V7);
V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


DBMS_SQL.CLOSE_CURSOR(V_CursorID);



V_CursorID :=DBMS_SQL.OPEN_CURSOR;


V_Permissions:=


'GRANT SELECT ON TIMADMIN.SEQ^ TO TIMUSER';

V_Permissions:= REPLACE(V_Permissions,'^',V_Tables(V_LoopCounter).TableName);
DBMS_SQL.PARSE(V_CursorID, V_Permissions, DBMS_SQL.V7);
V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


DBMS_SQL.CLOSE_CURSOR(V_CursorID);



END IF;






/******************************  IF TABLE SYNONYM EXIST 


**********************************/






V_CursorID := DBMS_SQL.OPEN_CURSOR;


V_SelectStatement:= 'SELECT O.NAME, O.OBJ#, O.TYPE#, S.NAME, S.OBJ#

                     FROM SYS.SYN$ S, SYS.OBJ$ O
                     WHERE O.OBJ# = S.OBJ#
                     AND O.TYPE#=5
                     AND O.NAME=:SYN';





V_Synonym_Name:=V_Tables(V_LoopCounter).TableName;



DBMS_SQL.PARSE(V_CursorID, V_SelectStatement, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(V_CursorID, 1, V_Synonym_Name,30);



DBMS_SQL.BIND_VARIABLE(V_CursorID,':SYN',V_Synonym_Name);



V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);



V_FoundSynonym:='No';



LOOP



IF DBMS_SQL.FETCH_ROWS(V_CursorID)>0 THEN
  DBMS_SQL.COLUMN_VALUE(V_CursorID, 1, V_Synonym_Name);
  V_FoundSynonym:='Yes';


  ELSE



  EXIT;



END IF;



END LOOP;


DBMS_SQL.CLOSE_CURSOR(V_CursorID);

/****************************  END IF TABLE SYNONYM EXISTS


********************************/


/****************************  DROP EXISTING TABLE SYNONYM


********************************/





IF V_FoundSynonym='Yes'


THEN


V_CursorID:= DBMS_SQL.OPEN_CURSOR;



V_DropString:= 'DROP PUBLIC SYNONYM ^';


V_DropString:= REPLACE(V_DropString,'^',V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_DropString, DBMS_SQL.V7);



V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


DBMS_SQL.CLOSE_CURSOR(V_CursorID);



END IF;



/***************************  END DROP EXISTING TABLE SYNONYM 


****************************/


/****************************  CREATE THE NEW TABLE SYNONYM 


******************************/






V_CursorID:= DBMS_SQL.OPEN_CURSOR;


V_CreateString:=


'CREATE PUBLIC SYNONYM ^



FOR TIMADMIN.^';


V_CreateString:= REPLACE(V_CreateString,'^',V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_CreateString, DBMS_SQL.V7);
V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


DBMS_SQL.CLOSE_CURSOR(V_CursorID);

/**************************  END CREATE THE NEW TABLE SYNONYM 


*****************************/


/******************************* STARTING SEQUENCE SYNONYMS


***********************************/

/******************************  IF SEQUENCE SYNONYM EXIST 


**********************************/






V_CursorID := DBMS_SQL.OPEN_CURSOR;


V_SelectStatement:= 'SELECT O.NAME, O.OBJ#, O.TYPE#, S.NAME, S.OBJ#

                     FROM SYS.SYN$ S, SYS.OBJ$ O
                     WHERE O.OBJ# = S.OBJ#
                     AND O.TYPE#=5
                     AND O.NAME=:SYN';





V_Synonym_Name:='SEQ';



V_Synonym_Name:=CONCAT(V_Synonym_name,V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_SelectStatement, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(V_CursorID, 1, V_Synonym_Name,30);



DBMS_SQL.BIND_VARIABLE(V_CursorID,':SYN',V_Synonym_Name);



V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);



V_FoundSynonym:='No';



LOOP



IF DBMS_SQL.FETCH_ROWS(V_CursorID)>0 THEN
  DBMS_SQL.COLUMN_VALUE(V_CursorID, 1, V_Synonym_Name);
  V_FoundSynonym:='Yes';


  ELSE



  EXIT;



END IF;



END LOOP;


DBMS_SQL.CLOSE_CURSOR(V_CursorID);

/****************************  END IF SEQUENCE SYNONYM EXISTS


********************************/


/****************************  DROP EXISTING SEQUENCE SYNONYM


********************************/





IF V_FoundSynonym='Yes'


THEN


V_CursorID:= DBMS_SQL.OPEN_CURSOR;



V_DropString:= 'DROP PUBLIC SYNONYM SEQ^';
V_DropString:= REPLACE(V_DropString,'^',V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_DropString, DBMS_SQL.V7);



V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


DBMS_SQL.CLOSE_CURSOR(V_CursorID);



END IF;



/***************************  END DROP EXISTING SEQUENCE SYNONYM 


****************************/


/****************************  CREATE THE NEW SEQUENCE SYNONYM 


******************************/






V_CursorID:= DBMS_SQL.OPEN_CURSOR;


V_CreateString:=


'CREATE PUBLIC SYNONYM SEQ^



FOR TIMADMIN.SEQ^';


V_CreateString:= REPLACE(V_CreateString,'^',V_Tables(V_LoopCounter).TableName);



DBMS_SQL.PARSE(V_CursorID, V_CreateString, DBMS_SQL.V7);
V_Dummy:=DBMS_SQL.EXECUTE(V_CursorID);


DBMS_SQL.CLOSE_CURSOR(V_CursorID);

/**************************  END CREATE THE NEW SEQUENCE SYNONYM 


*****************************/








END LOOP;


END;



/






DECLARE



*


ERROR at line 1:

ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 672






Elapsed: 00:01:10.02


SPOOL OFF


   I want to have it print all the variables and the iteration number.



   How can I do this?



Thanks...Dan'l
Received on Fri Feb 13 2004 - 13:30:32 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US