SqlDbx Forum

SqlDbx

You are not logged in.

#1 2015-12-04 11:37:47

ptoniolo
Member

Generate Scripts: "create or replace" for views, not for PL/SQL code

The sql code generated by "Generate Scripts" uses a "create or replace" for views, but not for procedures, functions, triggers, packages and package bodies, where a simple "create" is used instead.

Why is that? In the dictionary views like DBA_SOURCE that part of the code is left to the implementer, and I believe that a "create or replace" is more appropriate here...

Thanks
Pietro

Offline

#2 2015-12-06 20:51:43

sqldbxhelp
Administrator

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

Go to Tools->Options->Scripting/Log and check "User ALTER/REPLACE if possible"

Offline

#3 2015-12-15 06:11:40

ptoniolo
Member

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

The option is already set as you suggest, as you can see: the views are created that way! But this option apparently acts only for views, and not on pl/sql code, but I believe it should do the same for them too.
The version I use is the 4.11 pro.

Offline

#4 2015-12-18 13:37:22

ptoniolo
Member

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

The option is not behaving as expected.
PL/SQL scripts do not have the  "CREATE AND REPLACE"!

Offline

#5 2015-12-18 22:02:29

sqldbxhelp
Administrator

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

I am not able to reproduce any issues you report.
Everything looks to work as expected.
Not sure what you mean by "PL/SQL scripts do not have CREATE AND REPLACE"

Offline

#6 2015-12-19 18:58:43

ptoniolo
Member

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

SqlDbx version 4.11 pro
I am using the 64 bit, no-unicode version
Database Oracle 11g

The option "ALTER/REPLACE if possible" is set

When I use Tools/GenerateScripts the scripts for views starts with "create or replace", but the scripts for PL/SQL code (packages, procedures, functions) use a simple "create".

I was wrong about the triggers: "create or replace" is used for them, as for the views.
But packages, package bodies, procedures and functions, in the scripts are prepended by "create" only.

Offline

#7 2015-12-23 07:34:54

ptoniolo
Member

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

I thought that maybe your Tools/GenerateScripts could use the original "create" or "create or replace" used originally to create the object, but this is not the case...

To reproduce the situation, in an empty schema I executed:

create procedure one as begin null; end;
create or replace procedure two as begin null; end;

The Scripting/Log option "Use ALTER/REPLACE if possible" is checked.

When I use Tools/GenerateScripts to generate a file something.sql, the content of the generated file is:

create procedure one as begin null; end;

create procedure two as begin null; end;

If you are not able to reproduce this situation, maybe there is some other option that affects this specific behavior?

Offline

#8 2015-12-23 14:32:10

sqldbxhelp
Administrator

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

I did not realize that you are talking about Generatate Script dialog and not right click from object list.
You are right it does not work from dialog, but works from Object list. This will be fixed in upcoming release.
Thank you for your help

Offline

#9 2015-12-28 10:21:51

ptoniolo
Member

Re: Generate Scripts: "create or replace" for views, not for PL/SQL code

You are right... I should have specified the "Tools" explicitly in the "Tools/GenerateScripts" in my first post!

Thanks
Pietro

Offline

Board footer

Powered by FluxBB