Import Designer 2000 Web PLSQLs into Designer 9i

[Raz]

Hi there,
Firstly, I am total dodo when it comes to Designer and i have just
started out scratching the surface of this 'my where did i land' apps!

Someone decided to change a till now less used Client application
coded in Designer 2000 and.... here i am!

So here goes..
i have a Web application generated entirely through Designer 2000
toolset. Its working in the production environment without any
glitches. Currently no Designer repository is maintained on the
production sever or anywhere else and all Web PL/SQL code is available
only as database packages.

Now I need to enhance this app. I am using Designer 9iDs and need to
import all the packages and procedures created for the web application
(using Designer 2000) into the Designer 9i repository.

Can this be done at all? All guides point to creating/connecting to a
repository and then creating new Web pages using web server generator.
But i need to modify an existing Web PL/SQL page. Do i have to
re-write the entire thing as the code is not maintained in any
repository OR is there any way i can extract these stored packages
from the database onto the newly created repository and then start
modifying from there?

PS: I am not sure if the above write up is making any sense to you!

Thanks for any help
Cheers
Raz

[jhking]

Sorry, Raz, you're screwed. There is no reverse engineering tool for
web pl/sql packages. Given that Oracle has declared Designer a "mature"
tool that won't be receiving any new updates I wouldn't start into
learning how to build Designer web pl/sql applications.
Raz wrote: Hi there, Firstly, I am total dodo when it comes to Designer and i have just started out scratching the surface of this 'my where did i land' apps! Someone decided to change a till now less used Client application coded in Designer 2000 and.... here i am! So here goes.. i have a Web application generated entirely through Designer 2000 toolset. Its working in the production environment without any glitches. Currently no Designer repository is maintained on the production sever or anywhere else and all Web PL/SQL code is available only as database packages. Now I need to enhance this app. I am using Designer 9iDs and need to import all the packages and procedures created for the web application (using Designer 2000) into the Designer 9i repository. Can this be done at all? All guides point to creating/connecting to a repository and then creating new Web pages using web server generator. But i need to modify an existing Web PL/SQL page. Do i have to re-write the entire thing as the code is not maintained in any repository OR is there any way i can extract these stored packages from the database onto the newly created repository and then start modifying from there? PS: I am not sure if the above write up is making any sense to you! Thanks for any help Cheers Raz

[Frank van Bortel]

Jason King wrote:
Sorry, Raz, you're screwed. There is no reverse engineering tool for web pl/sql packages. Given that Oracle has declared Designer a "mature" tool that won't be receiving any new updates I wouldn't start into learning how to build Designer web pl/sql applications. Raz wrote: Hi there, Firstly, I am total dodo when it comes to Designer and i have just started out scratching the surface of this 'my where did i land' apps! Someone decided to change a till now less used Client application coded in Designer 2000 and.... here i am! So here goes.. i have a Web application generated entirely through Designer 2000 toolset. Its working in the production environment without any glitches. Currently no Designer repository is maintained on the production sever or anywhere else and all Web PL/SQL code is available only as database packages. Now I need to enhance this app. I am using Designer 9iDs and need to import all the packages and procedures created for the web application (using Designer 2000) into the Designer 9i repository. Can this be done at all? All guides point to creating/connecting to a repository and then creating new Web pages using web server generator. But i need to modify an existing Web PL/SQL page. Do i have to re-write the entire thing as the code is not maintained in any repository OR is there any way i can extract these stored packages from the database onto the newly created repository and then start modifying from there? PS: I am not sure if the above write up is making any sense to you! Thanks for any help Cheers Raz

Nonsense, you can reverse engineer any PL/SQL in designer....

It's just a pity you will not have any relations, code reusability,
etc, what could have been inside the previous repository.
Don't you have a backup, or application export lying around
somewhere?
Would be a tremendous help.

If not - you're faced with the problem of distinguishing
Designer added (transactional/referential) code from the actual
application code.
Not a nice task, as one of the long standing
bugs has been the size of Designer generated packages and procedures
being too large for SQLplus to execute...
--

Regards,
Frank van Bortel

[jhking]

Frank van Bortel wrote: Jason King wrote:
Nonsense, you can reverse engineer any PL/SQL in designer.... It's just a pity you will not have any relations, code reusability, etc, what could have been inside the previous repository. Don't you have a backup, or application export lying around somewhere? Would be a tremendous help. If not - you're faced with the problem of distinguishing Designer added (transactional/referential) code from the actual application code. Not a nice task, as one of the long standing bugs has been the size of Designer generated packages and procedures being too large for SQLplus to execute...

Assuming we have mod$ mod$comp and mod$js$comp for a simple
one-component web pl/sql module you can indeed reverse engineer the 3
packages back, but they're just pl/sql code. There is no automated way
to turn them back into a designer module and module component and use
Designer to change, say, query criteria and regenerate. I believe that
was the OPs question.
For Developer modules I gather there is some ability to reverse engineer
them back into modules, but as I've never worked on Developer modules I
don't know how well that works.

[Frank van Bortel]

jhking wrote:
Frank van Bortel wrote: Jason King wrote: Nonsense, you can reverse engineer any PL/SQL in designer.... It's just a pity you will not have any relations, code reusability, etc, what could have been inside the previous repository. Don't you have a backup, or application export lying around somewhere? Would be a tremendous help. If not - you're faced with the problem of distinguishing Designer added (transactional/referential) code from the actual application code. Not a nice task, as one of the long standing bugs has been the size of Designer generated packages and procedures being too large for SQLplus to execute... Assuming we have mod$ mod$comp and mod$js$comp for a simple one-component web pl/sql module you can indeed reverse engineer the 3 packages back, but they're just pl/sql code. There is no automated way to turn them back into a designer module and module component and use Designer to change, say, query criteria and regenerate. I believe that was the OPs question. For Developer modules I gather there is some ability to reverse engineer them back into modules, but as I've never worked on Developer modules I don't know how well that works.

As I said.
And there is no mention of developer modules
in the OP's post, just revere engineering.
Which indeed cannot be done, and engineering from the
'raw' pl/sql code is a pita.
As I tried to make clear
--

Regards,
Frank van Bortel

[Raz]

Jeez, this indeed is a mess!

There is no trace of the earlier repository anywhere :(
Also we are planning to move this application onto entirely new
machines and will be creating the database afresh in which case i
guess we have only the folliwng options.
1. Create a new repository and create the application from scratch
using Designer 9i. This will take a considerable amount of work
assuming we have sufficient knowledge of the screens and database.
Thankfully the application as such is not humongous.
2. Compile all designer built in packages (WSGL,WSGLM,WSGFL,WSGJSL and
associated objects) and designer application packages onto the Oracle
9i database, directly edit the packages using standard PL/SQL editors
(changes are not minor but manageable) and compile the same. This as
pointed out will be regular PL/SQLs with no designer fucntionality.

the second option is a crude way of doing things but will reduce
effort (&costs), provided it works and there are no incomptibilies
inbetween. In the end again, we will not have a repository and the
same problems will continue if future enhancements are made.

But then will such a thing work, as we will not be using the Designer
toolset to code/install any of the objects, also would additional
configurations be required on the Web Server apart from setting up the
mod_plsql to listen to SQL requests?

Have heard of a something of a Web PL/SQL toolkit.Can that help me in
any way here?

Thank Raz

Frank van Bortel <fvanbortel@netscape.net> wrote in message news:<cb9s6a$2pb$1@news3.tilbu1.nb.home.nl>... jhking wrote: Frank van Bortel wrote: Jason King wrote: Nonsense, you can reverse engineer any PL/SQL in designer.... It's just a pity you will not have any relations, code reusability, etc, what could have been inside the previous repository. Don't you have a backup, or application export lying around somewhere? Would be a tremendous help. If not - you're faced with the problem of distinguishing Designer added (transactional/referential) code from the actual application code. Not a nice task, as one of the long standing bugs has been the size of Designer generated packages and procedures being too large for SQLplus to execute... Assuming we have mod$ mod$comp and mod$js$comp for a simple one-component web pl/sql module you can indeed reverse engineer the 3 packages back, but they're just pl/sql code. There is no automated way to turn them back into a designer module and module component and use Designer to change, say, query criteria and regenerate. I believe that was the OPs question. For Developer modules I gather there is some ability to reverse engineer them back into modules, but as I've never worked on Developer modules I don't know how well that works. As I said. And there is no mention of developer modules in the OP's post, just revere engineering. Which indeed cannot be done, and engineering from the 'raw' pl/sql code is a pita. As I tried to make clear

[jhking]

Raz wrote: Jeez, this indeed is a mess! There is no trace of the earlier repository anywhere :( Also we are planning to move this application onto entirely new machines and will be creating the database afresh in which case i guess we have only the folliwng options. 1. Create a new repository and create the application from scratch using Designer 9i. This will take a considerable amount of work assuming we have sufficient knowledge of the screens and database. Thankfully the application as such is not humongous. 2. Compile all designer built in packages (WSGL,WSGLM,WSGFL,WSGJSL and associated objects) and designer application packages onto the Oracle 9i database, directly edit the packages using standard PL/SQL editors (changes are not minor but manageable) and compile the same. This as pointed out will be regular PL/SQLs with no designer fucntionality. the second option is a crude way of doing things but will reduce effort (&costs), provided it works and there are no incomptibilies inbetween. In the end again, we will not have a repository and the same problems will continue if future enhancements are made. But then will such a thing work, as we will not be using the Designer toolset to code/install any of the objects, also would additional configurations be required on the Web Server apart from setting up the mod_plsql to listen to SQL requests? Have heard of a something of a Web PL/SQL toolkit.Can that help me in any way here? Thank Raz

htp, htf and some owa_% packages are the web pl/sql toolkit. They
should be installed by default in any currently supported Oracle
database. The wsg% packages, a package named cg$errors and the table
api for your base table (if you're doing inserts or updates ) are
necessary to get web pl/sql modules to run. The table api is a set of
triggers and a package named cg$<table_name>, <table_name> being the
name of your base table. If you have some publicly accessible place I
could look at screen shots from your application I could quickly tell
you whether it had been extensively modified or not. If it is pretty
much wsg as generated you may have a shot at reverse engineering it, if
its been hacked up, your option two is probably the only practical way
to proceed.

[Raz]

Hi King,
Yup the web pl/sql toolkit was installed by default. We installed the
Web PL/SQL Generator library onto the user schema and just compiled
the old designer web code onto the database. And viola they are
working! Though we havent tested all the screens, initial signs have
been encouraging! Thats a relief. :)

We will be sticking to Option 2, as recreating all the modules on
Designer 9i would take a hell lot of time. so we stick to the
thankless job of manually editing these packages with no designer
help!

About the Table APIs u mentioned,i havent been able to trace any api
for the tables being updated from the screens in the current
production system. Is that api is optional and can be done without?
Atleast in the code there are simple insert/updates statements and no
reference to cg$<table_name> procedures. Also, as i will be executing
these as normal PLSQL packages are APIs needed at all?

Thanks for ur time
Raz

jhking <jhking@airmail.net> wrote in message news:<cbccjm$te0@library1.airnews.net>... Raz wrote: Jeez, this indeed is a mess! There is no trace of the earlier repository anywhere :( Also we are planning to move this application onto entirely new machines and will be creating the database afresh in which case i guess we have only the folliwng options. 1. Create a new repository and create the application from scratch using Designer 9i. This will take a considerable amount of work assuming we have sufficient knowledge of the screens and database. Thankfully the application as such is not humongous. 2. Compile all designer built in packages (WSGL,WSGLM,WSGFL,WSGJSL and associated objects) and designer application packages onto the Oracle 9i database, directly edit the packages using standard PL/SQL editors (changes are not minor but manageable) and compile the same. This as pointed out will be regular PL/SQLs with no designer fucntionality. the second option is a crude way of doing things but will reduce effort (&costs), provided it works and there are no incomptibilies inbetween. In the end again, we will not have a repository and the same problems will continue if future enhancements are made. But then will such a thing work, as we will not be using the Designer toolset to code/install any of the objects, also would additional configurations be required on the Web Server apart from setting up the mod_plsql to listen to SQL requests? Have heard of a something of a Web PL/SQL toolkit.Can that help me in any way here? Thank Raz htp, htf and some owa_% packages are the web pl/sql toolkit. They should be installed by default in any currently supported Oracle database. The wsg% packages, a package named cg$errors and the table api for your base table (if you're doing inserts or updates ) are necessary to get web pl/sql modules to run. The table api is a set of triggers and a package named cg$<table_name>, <table_name> being the name of your base table. If you have some publicly accessible place I could look at screen shots from your application I could quickly tell you whether it had been extensively modified or not. If it is pretty much wsg as generated you may have a shot at reverse engineering it, if its been hacked up, your option two is probably the only practical way to proceed.

[jhking]

Raz wrote: Hi King, Yup the web pl/sql toolkit was installed by default. We installed the Web PL/SQL Generator library onto the user schema and just compiled the old designer web code onto the database. And viola they are working! Though we havent tested all the screens, initial signs have been encouraging! Thats a relief. :) We will be sticking to Option 2, as recreating all the modules on Designer 9i would take a hell lot of time. so we stick to the thankless job of manually editing these packages with no designer help! About the Table APIs u mentioned,i havent been able to trace any api for the tables being updated from the screens in the current production system. Is that api is optional and can be done without? Atleast in the code there are simple insert/updates statements and no reference to cg$<table_name> procedures. Also, as i will be executing these as normal PLSQL packages are APIs needed at all?
Your packages related to the screens are somename$,
somename$someothername, somename$js$someothername. The first is the
module, the second the module component and the third is javascript for
the module component, so when I talk about module code I mean somename$,
when I talk about component code I mean somename$someothername. If
your components are doing insert and/or update and they aren't using the
table APIs (the cg$<tablename> procedure calls) then somebody has
already seriously hacked up these modules. In a generated module
component that does insert/update you have to have the cg$<tablename>
package executable in order for everything to work. You can see your
code, if there's no reference to cg$<tablename>.ins or
cg$<tablename>.upd then you don't need them. Thanks for ur time Raz
Glad I could help. I usually can't help the folks that have helped me,
but I can "pay them forward".

[Raz]

King,
I checked it up the code doesnot make use of any cg$ procedures. So i
guess i wouldnt be needing any table apis, one less thing to setup!
:).
Know what, I used to get irritated when most write ups referred to
module and module components without actually giving any clue (in
complete 'dodo' terms) as to what they were, now i know! :)
Thanks King for all the help, maybe in good time i would, to quote you
"pay them forward" too. :)

Cheers
Good day
Ravi


jhking <jhking@airmail.net> wrote in message news:<cbi32b$5b7@library1.airnews.net>... Raz wrote: Hi King, Yup the web pl/sql toolkit was installed by default. We installed the Web PL/SQL Generator library onto the user schema and just compiled the old designer web code onto the database. And viola they are working! Though we havent tested all the screens, initial signs have been encouraging! Thats a relief. :) We will be sticking to Option 2, as recreating all the modules on Designer 9i would take a hell lot of time. so we stick to the thankless job of manually editing these packages with no designer help! About the Table APIs u mentioned,i havent been able to trace any api for the tables being updated from the screens in the current production system. Is that api is optional and can be done without? Atleast in the code there are simple insert/updates statements and no reference to cg$<table_name> procedures. Also, as i will be executing these as normal PLSQL packages are APIs needed at all? Your packages related to the screens are somename$, somename$someothername, somename$js$someothername. The first is the module, the second the module component and the third is javascript for the module component, so when I talk about module code I mean somename$, when I talk about component code I mean somename$someothername. If your components are doing insert and/or update and they aren't using the table APIs (the cg$<tablename> procedure calls) then somebody has already seriously hacked up these modules. In a generated module component that does insert/update you have to have the cg$<tablename> package executable in order for everything to work. You can see your code, if there's no reference to cg$<tablename>.ins or cg$<tablename>.upd then you don't need them. Thanks for ur time Raz Glad I could help. I usually can't help the folks that have helped me, but I can "pay them forward".