Oracle- Source Code for Tables and Views cannot find

50 pts.
Tags:
Oracle
Oracle Table
Oracle Tablespaces
Hey, I am working on some coding to find a certain phrase with a number like (SCCS Rev 1.13) in the comments within procedures, tables, views, packages, etc. It works for everything except Tables and Views. This is because i am using Dba_source which does not include the source code for table or views. Does anyone have any ideas of what i cud do or what i could use to search for the phrasse in those too? I included my code below. The first procedure uses the data within a table (Project, Owner, Name) passed from the second procedure below. CREATE OR REPLACE package body REINARD_DEV5.RevNum_pkg as procedure UPDATE_REVNUM(Object_project in varchar2,Object_owner in varchar2, Object_name in varchar2) /*Procedure updates RevNum in Column*/ is Cursor RevNum_cur is select Text from dba_source where owner=Object_owner and name=Object_name and TRIM(upper(text)) like upper('SCCS Rev%'); NumB_old number:=0; NumS_old number:=0; NumB_new number; NumS_new number; Begin For RevNum_rec in RevNum_cur Loop RevNum_rec.text:=REPLACE(REPLACE(TRIM(LTRIM(LTRIM(TRIM(RevNum_rec.text),'SCCS Rev'),'rev')), CHR(10) ), CHR(13) ); /* Filters out text SCCS Rev */ NumB_new := To_number(Substr(RevNum_rec.text,0,instr(RevNum_rec.text,'.')-1)); Begin NumS_new := To_number(Substr(RevNum_rec.text,instr(RevNum_rec.text,'.')+1)); Exception when others then NumS_new := To_number(Substr(RevNum_rec.text,instr(RevNum_rec.text,'.')+1,2)); End; If NumB_new >= NumB_old then If NumS_new >= NumS_old then NumB_old := NumB_new; NumS_old := NumS_new; End if; End if; End loop; If NumB_old =0 and NumS_old = 0 then Update Reinard_dev5.Test Set Revision = ' ' where Table_owner = Object_owner and Table_name = Object_name; else Update Reinard_dev5.Test Set Revision = To_char(NumB_new)||'.'||To_char(NumS_new) where Table_owner = Object_owner and Table_name = Object_name; End if; Exception When others then Update Test Set Revision = 'Error' where Table_owner = Object_owner and Table_name = Object_name; End; procedure Find_RevNum /* Selects all rows in table and inserts into update procedure */ is Cursor RevNum_cur is select * from Test; Begin For RevNum_rec in RevNum_cur Loop Update_RevNum(REVNUM_REC.PROJECT,REVNUM_REC.TABLE_OWNER, REVNUM_REC.TABLE_NAME); End loop; End Find_RevNum;

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can search in tables and views comments by querying the DBA_TAB_COMMENTS view. For columns use DBA_COL_COMMENTS, and <b>I think </b>you can search in comments added in the source query of a view by querying DBA_VIEWS.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following