Can Toad do this? (auto save plsql)

Toad for Oracle & DevOps Toolkit
1

I work in a single user environment where I am the only person writing packages and stored procedures.

Is there something in Toad which will auto save each package for me? Ideally, when I compile anything, I wish Toad would take the current version of the package and store it in a table with the time/date, then compile what’s in the editor.

So if I want to see what the package / procedure / function / trigger looked like 5 compilations ago I could query the table.

I do not want to check-out / check-in the source code, I want this to happen in the background.

Is there something in Toad which can do this?

2

Another option which would work is to store the package version on the file system with the date/time stamp as part of the file name.

3

Hi Brian,

Toad has a feature called Team Coding, where packages and stored procedures can be controlled in the database itself, checked in and checked out, but Toad does
not do any versioning internally, just locking. This feature does interface with a variety of popular source control programs if you do want to implement versioning.

Since you are in a single user environment, I would recommend not going the Team Coding route. Another option would be to use source control without Team Coding.
Unlike Team Coding, you would not have to install any database tables to use this feature. You can just setup your source control provider in options > source control and check in/out right from the editor.

Versioning within the DB itself sounds like an interesting idea. I’m not sure if this has been asked for or not, but if you would to submit an enhancement
request for it, I’d recommend doing so in Toad’s Idea Pond, http://toadfororacle.ideascale.com/

Thanks,

Brad

From: brian_1438 [mailto:bounce-brian_1438@toadworld.com]

Sent: Thursday, June 27, 2013 7:39 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Can Toad do this? (auto save plsql)

Can Toad do this? (auto save plsql)

Thread created by brian_1438

I work in a single user environment where I am the only person writing packages and stored procedures.

Is there something in Toad which will auto save each package for me? Ideally, when I compile anything, I want wish Toad would take the current version of the package and store it in a table
with the time/date, then compile what’s in the editor.

So if I want to see what the package / procedure / function / trigger looked like 5 compilations ago I could query the table.

I do not want to check-out / check-in the source code, I want this to happen in the background.

Is there something in Toad which can do this?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

4

Brad’s idea of source code control can be set up to be almost as simple as you said (I think; I haven’t done all of these pieces). One way would be:

Do this one time:

·
Install SubVersion (“SVN”) source code control.

·
(may or may not be necessary, but it’s a good Windows interface to SubVersion) install TortoiseSVN.

·
Set up a local SVN repository on your hard disk (you don’t need a server) and set up a SVN working directory.

Do this one time for each module:

·
Save it (from Toad editor) to a file in the SVN working directory and add it to the SVN repository.

Then, start every editing session by opening that file (rather than opening the package from the database), and then every time you compile the code – or really
every time you want to save a version, which might not be every compile but conversely may be partway through a complex change when the code actually won’t compile – all you do is click the toad “Commit” button in the Toad editor, and SVN saves the current
version.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

5

Thanks Nate… now that you mention this I’ve got a few additional suggestions…

  • I would
    not recommend TortoiseSVN, rather any other command-line SVN client. Our support for Tortoise is very limited.

  • Toad 12 makes setting up a local SVN repository very simple. Just install SVN and then do the rest in Toad. Start in options > source control. After setup,
    use Toad’s main source control toolbar to login (select project button), check in, check out, view differences, etc.

From: nathan.e.schroeder [mailto:bounce-nathaneschroeder@toadworld.com]

Sent: Friday, June 28, 2013 10:02 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Can Toad do this? (auto save plsql)

RE: Can Toad do this? (auto save plsql)

Reply by nathan.e.schroeder

Brad’s idea of source code control can be set up to be almost as simple as you said (I think; I haven’t done all of these pieces). One way would be:

Do this one time:

·
Install SubVersion (“SVN”) source code control.

·
(may or may not be necessary, but it’s a good Windows interface to SubVersion) install TortoiseSVN.

·
Set up a local SVN repository on your hard disk (you don’t need a server) and set up a SVN working directory.

Do this one time for each module:

·
Save it (from Toad editor) to a file in the SVN working directory and add it to the SVN repository.

Then, start every editing session by opening that file (rather than opening the package from the database), and then every time you compile the code – or really
every time you want to save a version, which might not be every compile but conversely may be partway through a complex change when the code actually won’t compile – all you do is click the toad “Commit” button in the Toad editor, and SVN saves the current
version.

Nate Schroeder

US Row Crops IT Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167

314-694-2592

This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled

to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and

all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its

subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.

Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying

this e-mail or any attachment.

The information contained in this email may be subject to the export control laws and regulations of the United States, potentially

including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of

Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all

applicable U.S. export laws and regulations.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

6

I am experimenting with a solution which might be the ticket.

  1. created a table to hold the source code:

create table sys_revision (

revision_date date,

owner varchar2(30),

name varchar2(30),

type varchar2(12),

text varchar2(4000)

)

  1. created 2 triggers:

create or replace trigger revision_trigger_create

before create on myschema.schema

begin

if ora_dict_obj_type in (‘FUNCTION’, ‘PROCEDURE’,‘PACKAGE’, ‘PACKAGE BODY’) then

insert into sys_revision ( revision_date, owner, name, type, text)

select sysdate, owner, name, line, text

from all_source

where owner = ora_dict_obj_owner

and name = ora_dict_obj_name;

end if;

end;

create or replace trigger revision_trigger_alter

before alter on myschema.schema

begin

if ora_dict_obj_type in (‘FUNCTION’, ‘PROCEDURE’,‘PACKAGE’, ‘PACKAGE BODY’) then

insert into sys_revision ( revision_date, owner, name, type, text)

select sysdate, owner, name, line, text

from all_source

where owner = ora_dict_obj_owner

and name = ora_dict_obj_name;

end if;

end;

This is basically working. I just wrote this, so it is not fully tested. The idea seems to hold water. Anytime a package is compiled, created or replaced, the current is saved in the revision table.

This was my goal… I’ll try this out.

7

I changed the code around so that the revision is stored on the file system. Each is time and date stamped.

So far this is working very nicely.

CREATE OR REPLACE DIRECTORYBACKUP_PLSQL_DIR AS ‘D:_pos_backup_plsql’

CREATE OR REPLACE TRIGGER REVISION_TRIGGER_CREATE_EGDB before create on schema
declare
c clob;
fn varchar2(5);
begin

if ora_dict_obj_type in (‘VIEW’, ‘FUNCTION’, ‘PROCEDURE’, ‘PACKAGE’, ‘PACKAGE BODY’) then

if ora_dict_obj_type = ‘FUNCTION’ then
fn := ‘.fnc’;
elsif ora_dict_obj_type = ‘PROCEDURE’ then
fn := ‘.pro’;
elsif ora_dict_obj_type = ‘PACKAGE’ then
fn := ‘.pkg’;
elsif ora_dict_obj_type = ‘PACKAGE BODY’ then
fn := ‘.pkg’;
elsif ora_dict_obj_type = ‘VIEW’ then
fn := ‘.view’;
end if;

c := dbms_metadata.get_ddl( replace(ora_dict_obj_type,’ BODY’,’’), ora_dict_obj_name, ora_dict_obj_owner);
dbms_xslprocessor.clob2file(c, ‘BACKUP_PLSQL_DIR’, lower( ora_dict_obj_owner ||’’ || ora_dict_obj_name) || '’ ||to_char(sysdate,‘yyyymmdd_hhmiss’) || fn );

end if;

end;
/