Home » SQL & PL/SQL » SQL & PL/SQL » Tracking PL/SQL Code Changes
Tracking PL/SQL Code Changes [message #228817] Wed, 04 April 2007 05:41 Go to next message
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
Re: Tracking PL/SQL Code Changes [message #228849 is a reply to message #228817] Wed, 04 April 2007 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Tracking PL/SQL Code Changes [message #228859 is a reply to message #228849] Wed, 04 April 2007 07:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Tracking PL/SQL Code Changes [message #228880 is a reply to message #228865] Wed, 04 April 2007 08:20 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Tracking PL/SQL Code Changes [message #229022 is a reply to message #229018] Thu, 05 April 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Tracking PL/SQL Code Changes [message #229052 is a reply to message #229022] Thu, 05 April 2007 03:39 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Tracking PL/SQL Code Changes [message #229054 is a reply to message #229052] Thu, 05 April 2007 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Tracking PL/SQL Code Changes [message #229126 is a reply to message #229054] Thu, 05 April 2007 08:33 Go to previous messageGo to next message
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
Re: Tracking PL/SQL Code Changes [message #229130 is a reply to message #229126] Thu, 05 April 2007 08:56 Go to previous messageGo to next message
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.
Re: Tracking PL/SQL Code Changes [message #229134 is a reply to message #229130] Thu, 05 April 2007 09:10 Go to previous messageGo to next message
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./fa/2317/0/

[Mod-edit: Replaced >200kB .bmp with <10 kB .png]
  • Attachment: Error.PNG
    (Size: 9.11KB, Downloaded 1056 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 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://technology.amis.nl/blog/?p=846
Re: Tracking PL/SQL Code Changes [message #229454 is a reply to message #229172] Sun, 08 April 2007 04:51 Go to previous messageGo to next message
janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis
Member
Thanks andrew....
I'm going thru the document....


janet
Re: Tracking PL/SQL Code Changes [message #229565 is a reply to message #229454] Mon, 09 April 2007 02:35 Go to previous messageGo to next message
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..
Re: Tracking PL/SQL Code Changes [message #229604 is a reply to message #229565] Mon, 09 April 2007 06:34 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Tracking PL/SQL Code Changes [message #229680 is a reply to message #229604] Mon, 09 April 2007 13:01 Go to previous messageGo to next message
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.
Re: Tracking PL/SQL Code Changes [message #229979 is a reply to message #229680] Tue, 10 April 2007 08:19 Go to previous messageGo to next message
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
Re: Tracking PL/SQL Code Changes [message #230016 is a reply to message #229979] Tue, 10 April 2007 09:55 Go to previous message
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.
Previous Topic: ORA-00984: column not allowed here ??
Next Topic: Temp table
Goto Forum:
  


Current Time: Mon Dec 05 09:11:45 CST 2016

Total time taken to generate the page: 0.05782 seconds