OraFAQ Forum: SQL & PL/SQL » Tracking PL/SQL Code Changes

Home » SQL & PL/SQL » SQL & PL/SQL » Tracking PL/SQL Code Changes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tracking PL/SQL Code Changes [message #228817]

Wed, 04 April 2007 05:41

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Hi All,

Can anyone please advice me how to track the PL/SQL Code changes.

I tried with DDL Trigger.

But it is not working.

The Steps i tried are

Created a before Create Trigger and stored the codes in one table.

Created an after create trigger and stored in another table.

and tried to store the difference in another table using MINUS Operator.

Can anyone please help me whether the method i followed is the correct one or not.

I just want to store the PL/SQL Code type(Procedure/Function/Package) and the line number the text that has changed.

PLease guide me.

Regards
Janet

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #228849 is a reply to message #228817]

Wed, 04 April 2007 06:57

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I used something like that and it works.
Just put your code and we'll tell you what is wrong.

Regards
Michel

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #228859 is a reply to message #228849]

Wed, 04 April 2007 07:32

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Thanks for the reply

Code posted not correct so deleted.....
Code is posted again......

I'm trying to pupulate one table with the code before changing and comparing the same with the new code inside the After DDL Trigger.

Is this the correct way?

Janet

[Updated on: Wed, 04 April 2007 08:25]

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #228865 is a reply to message #228859]

Wed, 04 April 2007 08:01

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is not what you are doing.
Your first trigger records in LOG_TEMP the current source. That's OK although you don't record the object name, so you can have some issues if several sessions modify another function at the same time.
I don't really understand what you do with v$locked_object in your Log_Tracking procedure.
I don't really understand why your hard coded 'SCOTT' in the same procedure instead of using ora_login_user as in the before trigger.
As you hard coded your function name in the Log_Tracking procedure, it will not work for any other one.
I don't see where you make a comparison.

Regards
Michel

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #228880 is a reply to message #228865]

Wed, 04 April 2007 08:20

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Code Updated

Before Create Trigger

CREATE OR REPLACE TRIGGER LOGGINGB4_PLSQL
BEFORE CREATE
ON SCOTT.SCHEMA
DECLARE
BEGIN
IF ora_dict_obj_type='FUNCTION' THEN
-- INSERT INTO TEST123 VALUES('MODIFYING FUCNTION '||ora_dict_obj_name);
INSERT INTO LOG_TEMP SELECT ora_login_user,SYSDATE, line,text FROM USER_SOURCE WHERE NAME=ora_dict_obj_name ORDER BY line ASC;
END IF;
END TEST;
/

After DDL Trigger

CREATE OR REPLACE TRIGGER SCOTT.LOGGING_AFTERPLSQL
AFTER DDL
ON SCOTT.SCHEMA
DECLARE
BEGIN
IF ora_dict_obj_type='FUNCTION' THEN
INSERT INTO LOG_TEMPAFTER SELECT ORA_LOGIN_USER, SYSDATE, LINE, TEXT FROM USER_SOURCE WHERE NAME=ORA_DICT_OBJ_NAME;
INSERT INTO TRACKING_PLSQL SELECT ORA_LOGIN_USER,SYSDATE,LINE,TEXT FROM LOG_TEMP MINUS SELECT ORA_LOGIN_USER,SYSDATE,LINE,TEXT FROM LOG_TEMPAFTER;
END IF;
END;
/
/

I have one sample function

Function
CREATE OR REPLACE FUNCTION Ret_Sal(EMPN IN NUMBER) RETURN VARCHAR2 IS
vsal NUMBER;
BEGIN
SELECT sal INTO vsal FROM EMPLOYEE WHERE empno=EMPN;
RETURN vsal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'PLEASE CHECK THE EMP NUMBER';
END Ret_Sal;
/

After Recompiling these with some changes like changing number to int. contents of the two tables log_temp and log_tempafter are same.

Janet

[Updated on: Wed, 04 April 2007 08:23]

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229018 is a reply to message #228880]

Thu, 05 April 2007 01:24

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Hi Michel,

Can you please tell me the method u followed for tracking PL/SQL code changes.

Regards
Janet

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229022 is a reply to message #229018]

Thu, 05 April 2007 01:35

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sorry Janet, I forgot you.

We don't try to keep only changes (which are more than just a minus, you also have to "union all" the opposite minus), we record the whole code each time it is modified (that is your first step), knowing that the current version is in %_source views.

If we need to know the changes between 2 versions or 2 dates we just have to query the log table (and maybe %_source).
If you record only changes, it is much more complex to know the differences between 2 versions if they are not 2 adjacent ones.

Regards
Michel

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229052 is a reply to message #229022]

Thu, 05 April 2007 03:39

Frank
Messages: 7901
Registered: March 2000

Senior Member

If you just stick to decent development rules, you'd store your code in a source code repository.
You can use that to track changes.

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229054 is a reply to message #229052]

Thu, 05 April 2007 03:49

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I agree with you Frank but often (too often) developers ask for DBA to make that for them as they are not able to follow their owb changes.

Moreover, it is always fine to have some weapons when application team try to affirm that what is in the database is not what they deliver or have not been installed at the right date or...

Regards
Michel

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229126 is a reply to message #229054]

Thu, 05 April 2007 08:33

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Hi Michel,

Thanks for your suggestions.
Currently i'm working on that.

I want to know whether is it possible to integrate VSS with Toad or SQL Developer.

Janet

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229130 is a reply to message #229126]

Thu, 05 April 2007 08:56

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

TOAD will integrate very nicely with VSS, allowing you to checkout and checkin from the Procedure Editor.

Sql*Developer doesn't support any integration as far as I know.

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229134 is a reply to message #229130]

Thu, 05 April 2007 09:10

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

I tested the integration of VSS with Toad.
Installed Server Side Objects and Enabled the Check-in/Check-out options.

But while going to Team Coding ==> Team Coding Status ==> Settings i'm geeting a information message sayinfg the connection is read only.

Attached is the screen shot of the error.

Janet /forum/fa/2317/0/

/forum/fa/2317/0/

[Mod-edit: Replaced >200kB .bmp with <10 kB .png]

Attachment: Error.PNG
(Size: 9.11KB, Downloaded 1709 times)

[Updated on: Thu, 05 April 2007 11:09] by Moderator

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229172 is a reply to message #229134]

Thu, 05 April 2007 11:34

andrew again
Messages: 2577
Registered: March 2000

Senior Member

http://technology.amis.nl/blog/?p=846

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229454 is a reply to message #229172]

Sun, 08 April 2007 04:51

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Thanks andrew....
I'm going thru the document....

janet

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229565 is a reply to message #229454]

Mon, 09 April 2007 02:35

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Hi Andrew,

i had gone thru the article.
It is not working...

While creating the package body getting errors.

GET_ERRORS a function is present in package body but bot in package specification.

Janet..

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229604 is a reply to message #229565]

Mon, 09 April 2007 06:34

Frank
Messages: 7901
Registered: March 2000

Senior Member

Please show us the actual error. Not some interpretation of it. So, copy-paste the outcome of a query on user_errors.

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229680 is a reply to message #229604]

Mon, 09 April 2007 13:01

andrew again
Messages: 2577
Registered: March 2000

Senior Member

I haven't installed the packages. GET_ERRORS code is on the web page, but not in the ZIP file.

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #229979 is a reply to message #229680]

Tue, 10 April 2007 08:19

janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis

Member

Hi Andrew,

The GET_ERRORS is not only on web page but also ion the zip file.
Attached is the zip file.

Janet

Attachment: plsql_archiver.zip
(Size: 6.27KB, Downloaded 630 times)

Report message to a moderator

Re: Tracking PL/SQL Code Changes [message #230016 is a reply to message #229979]

Tue, 10 April 2007 09:55

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Hmmm... I must have missed it. Try basic things like compiling all the specs first, then the bodies. If it still won't work - try posting further questions on that page for the author to answer himself. Don't forget to mention the version of Oracle you are using.

Report message to a moderator

Previous Topic:	ORA-00984: column not allowed here ??
Next Topic:	Temp table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 20 10:51:31 CDT 2025