ID#

Questions

Answers

Oracle Developer - General 

1

I have trouble with icons in Dev 2.1 (and therefore, in Dev 6.0). I upgraded an application built in Dev 1.5.x (in which all the icons in buttons were well centered), and after that, several icons were not centered anymore.

From Duncan Mills, Oracle Enterprise Internet Tools - Product Management

The last letter in the Icon name is taken by the toolkit to indicate the

size of the icon and it therefore repositions the icon according to that

information.

So you should avoid having icon names that end in s, m, l, a, and x. (Small,

Medium, Large, Alley, eXtra-large).

This is documented in the 6i help - Icon Filename Property. (it was not

documented in earlier versions of the help)

2

I certainly believe that it is possible to make a good exam that tests if the

person can build forms. I don't think the OCP exam for developers does

that. I have a somewhat higher opinion of the DBA exam, but even that tests

conventional wisdom and is frequently wrong or incomplete.

My most ringing indictment of the OCP exam came from a woman at Oracle Education who had passed the test (she was trying to recruit me to teach exam classes). I asked her if she could actually build applications using Forms. She asked me what I meant... I said: "If I laid out a design for a form, are you confident that you could build me a production-ready application that met the design requirements?" She replied that she had never actually ever built a real form so would have no idea how to do what I asked.

I think that this damns the OCP exam as testing if you know the parts of the product but not if you are actually a Developer. The interview questions that Peter Koletzke and I put in our Advanced Developer book reflect that kind of thinking that I would like to see a test go after. The problem is that the test will test the test-author's style of development. Michael Olin (the tech reviewer of the Advanced Developer book) criticized several of the questions in our book as being Dulcian coding style- specific.

As the OCP exam presently stands, I think it is useful for experienced

developers to become certified since it ensures that you have really looked at all parts of the product. It will help you make sure you don't have any obvious holes in your product knowledge. For prospective employers, who are trying to find competent developer talent, do not rely too heavily on certification as being a good measure of developer quality.

3

We are currently creating a whole ERP for a half billion dollar retailer with probably about 20-40 forms. In our environment, it makes no sense to use automated testing tools. We even brought in a vendor and explained to him our environment and he agreed that they couldn't help us.

Our testing tool is a college kid. This method works great!!! And is less work than dealing with the tool. If you have hundreds of simple forms and are working in an environment where a retest would cost $100,000 or better, then you will be well served by any of the leading testing tools. Make sure that they EXPLICITLY have dealt with Oracle forms as you have to deal with the way that Forms renders items. Not all tools can handle this.

You will also have to modify your forms to render your items do that the tool can see them (at least you needed to do this the last time I looked). Even in a large environment, the pain of the tool will mean that on the first project, you will receive little or no benefit from the tool. I mean that learning the tool and figuring out how to integrate it into your development environment will offset the time savings the tool will give you.

Oracle Developer - Forms

           1

Although most things will work in the same way, do not trust that anything to do with web forms 

will be exactly the same as the way it is in client/server.

There are obvious things that don't work or shouldn't be used, such as

 timers. But, the problems are all the subtle things that get you. Also,

 every point release fixes more bugs (and sometimes introduce new bugs).

 Suggestions:

1)You should use Forms v. 6.x.

2) Set up a few test forms and test them very thoroughly. This includes full stress

 testing. This will give you a chance to work out how your GUI standards will

 have to be adapted.

 3) Test as you go. For each form, have your developers test every

 Form both in client/server and in web mode. You can even do this directly from Form

 Builder but it is kind of slow. Most people keep a calling web

 form open to call the form in web mode.

 4) Do not jump to a new release without testing all of your existing Forms.

I have a form with three tab pages with different blocks. When I run the

form, no object is found on the first tab page. The page is blank). When I

navigate to the second tab page and come back to the first tab page, all the objects are visible.  Why does this problem occur?

Try navigating to an item in the first block in the WHEN-NEW-FORM-INSTANCE trigger.

4

I have an LOV on a field with the Filter Before Display property set to "Yes." When I press F9 to see the LOV and click on "FIND," the list comes up empty regardless of the filter value (even when it is %). It only seems to work when the Filter Before Display property is set to "No." What is wrong?

The problem is probably in your query. Keep in mind that the Filter Before Display property places an "and..." at the end of your query. If your query has a UNION in it or is built in such a way that this addition won't work, the list will still come up empty. If you can't change your query, try using a view.

5

How can you handle security in Oracle Forms?

Outside of menu-level security, there is no native security in Oracle Forms. For menus you can make any menu item enabled or not based upon your Oracle role-level security. So, you have to define your roles in Oracle and you can only support it in the menu.... pretty weak.

The good news:

I think the reason we get so few requests for security enhancements for Forms is that it is pretty easy to build whatever you want in the tool. Everyone has a different idea about what is appropriate for security. 

As an example, Dulcian has built two sets of security enhancements. The first is for standard applications. We have a repository that stores properties that we want to change at runtime in a form. Depending upon which security preference set is appropriate for the user, the form enables/disables any item on the form to provide field-level security. (This also supports runtime changes to labels, help and error text.) The second is for complex, process-related business events.

Business events go through processing states. At each state, we enable/disable the appropriate fields for that state. This gives us security at the state/field level.

The conclusion is that Forms delivers largely useless security but is easily extendable to support very complex security.

6

Stevan Bulajic  asked: Is it possible to send e-mail from a  trigger? I work in Forms 5.0, Oracle 8i, and I would like to send e-mail from a trigger as a warning after certain action.

See Note 106513.1 in Metalink for more info.

The Forms Menu security just selects from the view FRM50_ENABLED_ROLES as defined in the script FRM60SEC.SQL. - So you can look at that view to see how it works.

As far as Forms is concerned it just expects that view to return a column called ROLE and another called FLAG - there is nothing to stop you from creating your own view that queries the Role information in the way you want - or for that reason simply uses tables of your own.  As long as the view is called the right thing and returns the correct columns you're fine.

As an FYI we don't use the FLAG column any more so Just return Zero for that in your view.

It's not supported to do this - of course we can't support a view that you write yourself - however it is "safe" to do

Thanks to Duncan Mills of Oracle Forms Product Management for this information.

  I know we can get the count by calling:

  d2fitmg_lst_element_count

But how do we iterate thru the elements and get their names and values?

How does D2FP_LIST_ELEM come into play?

  Thanks to Jesse Johnson - Managing Principal August Tenth Systems, Inc. for the following:

 1. Get the number of elements in the list: d2fitmg_lst_element_count

 (m_pd2fctx,

 pd2fitm, &nNumber);

2. Get the List Element object associated with the List Item:

 d2fobgo_GetObjProp

 (m_pd2fctx, pd2fitm,  D2FP_LIST_ELEM, &pd2fob);

3. Use the List Element object to get the 'Label' (list element name) and 'Text' (list element value)  properties:

       d2fobgt_GetTextProp (m_pd2fctx, pd2fob, D2FP_LABEL, &ptrText);

       d2fobgt_GetTextProp (m_pd2fctx, pd2fob, D2FP_TEXT, &ptrText);

4. Loop thru all the elements (i.e. get next): d2fobgo_GetObjProp

 (m_pd2fctx,

 pd2fob, D2FP_NEXT, &pd2fob);

Is it better to put PL/SQL code on a trigger, table or form? My code is supposed to update another table if data from some column is changed.

To place this code in the Form is not the best solution. Such code should CERTAINLY be placed in the DB.  I agree that it is perhaps nominally easier, but it is bad programming practice in anything except a tiny, trivial, single developer, system.

The problem is that the code logically belongs with the table.  The behavior you are talking about is behavior of the table and not the application.  If you place this code in the form, you will have to replicate that logic on any form that touches that table. Such a strategy leaves you open to forgetting about the logic or having a different developer sink your system.

What are the alternatives of authorization in Forms applications?
Which is better ? Limiting user rights with predefined roles making the RDBMS check everything combined with role based menus or code the user rights logic into the application and create your own tables for storing user rights per block

The environment in question is: RDBMS 8.0.5, Forms + Reports 6i

Which is "better" is hard to answer.  Forms has very limited ability to enforce security. It has been my experience that when you need security on a form, the capability that Forms gives you is inadequate.

Unfortunately, doing it "right" (meaning that you support what your users really want), is VERY difficult. Typically users have contingent rules such as:

"After the purchase order is closed only a manager is allowed to change the status field."

I have built various security engines over the years that worked for a particular system.  What you need to do is very precisely gather the security requirements and then build a security engine to support you. The engine that we use is integrated into our whole rules-based approach so we support security at the object, state, state-event, and field levels based upon the user's security role. This is probably more security than most systems require.

Sikandar Hayat Awan wrote some techniques that are posted in the tips section of Peter Koletzke’s website at http://ourworld.compuserve.com/homepages/Peter_Koletzke/tip.htm

Sample of how to run CCMail and send messages to it from a Forms application. See the README.DOC file in this archive file for details. Just create a directory for the files, unzip them to that directory, make an icon shortcut for Forms Designer with your new directory as the Start In directory (that way you don't have to change the Forms path or anything like that). This is a working version that requires CCMail to run. Be sure to always do a virus check.

There is also a sample for sending email from Forms through Outlook. F4CCMAIL.ZIP (317K) - CCMail, Forms 4.5

I am using ADD_GROUP_ROW & SET_GROUP_CHAR_CELL.

Forms help says "The values of a static record group are specified at design time and cannot be changed at runtime"

14

 From Alroy Mascranghe

Subject: Forms Architecture-Dynamic Objects

 When I create an object, the code to create that object is generated by the tool automatically. So if I want to create the same object during runtime I can write the same code.

But when I create a block or item in Forms, I don't see the code that the Form Builder engine uses to create the block. In an attempt to make it very user-friendly, it has been made less code intensive.

In earlier versions, there must have been a built-in (CREATE_BLCOK(x,y,z)). When I pressed the creation button the Form Builder engine produced the code to create the object i.e CREATE_BLOCK etc. So when I wanted to create an object myself I could call the same built-in.

In the current architecture, is there a way to create objects dynamically?

Forms has almost no ability to create objects at runtime.  You cannot even more boiler plate stuff at runtime or change any properties. About all you can create at runtime are record groups.

This is a huge weakness in the product.  The best you can do is to create everything you need at design time and  dynamically move it around and make it appear at run time.  This is not as bad as it sounds.  Back in the 5.0 days, there were serious limitations on a form as to how many objects you could put in a block prior to the form becoming unstable.  But I have heard from people that are now trying the same approach that you can place many items in a block without having the form fail.

You can deal with the boilerplate moving limitations by placing the items on little canvases and moving them around and resizing them.

Or - you can use JDeveloper instead.

Are there any drawbacks in keeping the complete application on a single form (many blocks) rather than making separate forms for each option and then using call_form to put it all together?

 I am currently making this application that has 20 blocks (stacked) so far and will grow to around 80 blocks down the road. Most people have discouraged this idea.  I must say that I do face problems in making 'base table' blocks with buttons. Any work around?

One Answer:

With my experience, I found one canvas per form should be used as a guideline.  This is in line with the suggestion mentioned  - one business operation per form.

This will allow you to query this one canvas form from many forms based on your business requirements.

I am not sure I agree with this approach.

I agree that Forms does not always work well with lots of functionality.  But fewer forms buys you lots of things:

1) A better user interface.  I single form that handles all customer interaction means that the user goes to one place to maintain customer information.

2) Less forms to maintain.

3) Many business functions require the interaction of multiple objects. This means that not only do you have lots more forms, you even have lots more special forms.

4) The lots of forms approach means that you get hundreds of forms for a big system.  The fewer forms approach means you get a few dozen forms for the same system.

The downside is that there will be a few forms that collapse under their own weight.  They are hard to build, harder to maintain and as little business rules enter into the form, the whole situation gets worse and worse.

As we move to a web-based environment, the single form approach looks even more necessary as we are in a world where we do not have the same level of technology to support our application development.

So...How do we get the best of both worlds?

If you move the business rules out of code and into a repository, you can enforce those rules in a form and still not have the form bloat.  At the logical end of this approach, you can have a single form that maintains your whole system but is still small and carries the functionality of a very complex form.

My suggestion is to still build forms that are based around the logical core objects in a system (i.e. customer, employee, etc.) but to move the business rules out of the form into a repository.

16

Another tip from Zlatko Sirotic

from Croatia:

Deferred database constraints and Forms

The Oracle database (from version 8.0) has the possibility of delaying the checking of the declarative constraints (NOT NULL, Primary Key, Unique Key, Foreign Key, Check Constraints) until the commit. Unfortunately, if we use Oracle Forms as a front-end application, the errors raised from deferred constraints are not handled very well.

Another problem (much bigger) is if we use POST built-in in Forms e.g. we add a deferred primary key to the DEPT table:

ALTER TABLE dept

   ADD CONSTRAINT dept_pk PRIMARY KEY (deptno) INITIALLY DEFERRED

   USING INDEX

/

Then we create a simple Forms module with the Forms Data Block Wizard.

When we insert two records with the same value in column deptno and send a COMMIT statement in SQL+, we get the errors:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (SCOTT.DEPT_PK) violated

When we insert the same data with Forms, we don't get any error messag in the ON-ERROR trigger or the SQLERRM after the COMMIT_FORM built-in.The Forms message 'FRM-40400: transaction finished, 2 Record saved' appears.But the records are not inserted. The constraint works, but Forms don't get the error message.

Seems that this is a Forms bug, and maybe will be fixed in future Forms 6i patches (in patch 11?)."  This bug is not resolved in Forms 6i patch 11.

We can wait for the patch. But, what to do when we use POST statement in Forms?

The POST built-in writes data in the form to the database, but does not perform a database COMMIT. After POSTing rows, Forms "forgets" which rows are inserted/changed/deleted.

The problem with deferred database constraints is that when something goes wrong within the transaction, then the complete transaction is rolled back after the COMMIT statement.

Solution for both problems is simple - we can check deferred constraints before the COMMIT statement occurs, using:

SET CONSTRAINT dept_pk IMMEDIATE;

or, in Forms:

FORMS_DDL ('SET CONSTRAINT dept_pk IMMEDIATE');

So, in the ON-COMMIT trigger (which fires whenever Forms would normally issue a database COMMIT statement to finalize a transaction), we can write code like this:

FORMS_DDL ('SET CONSTRAINT dept_pk IMMEDIATE');

IF NOT FORM_SUCCESS THEN

   MESSAGE ('COMMIT ERROR: ' || DBMS_ERROR_CODE || ' ' || DBMS_ERROR_TEXT); PAUSE;

   FORMS_DDL ('SET CONSTRAINT dept_pk DEFERRED');

   RAISE FORM_TRIGGER_FAILURE;

ELSE

   FORMS_DDL ('SET CONSTRAINT dept_pk DEFERRED');

   COMMIT_FORM;

END IF;

The previous code works OK if the user executing the Forms module is the owner of the table DEPT. When the non-owner tried to execute the SET command, they would get: ORA-02448 Constraint does not exist The solution is - instead of using SET CONSTRAINT directly in Forms, we can write database package like this:

CREATE OR REPLACE PACKAGE deferred_constraints AS

   PROCEDURE set_immediate (p_constraint VARCHAR2);

   PROCEDURE set_deferred  (p_constraint VARCHAR2);

END deferred_constraints;

/

CREATE OR REPLACE PACKAGE BODY deferred_constraints AS

   PROCEDURE set_immediate (p_constraint VARCHAR2) IS

   BEGIN

      EXECUTE IMMEDIATE 'SET CONSTRAINT ' || p_constraint || ' IMMEDIATE';

   END;

   PROCEDURE set_deferred (p_constraint VARCHAR2) IS

   BEGIN

      EXECUTE IMMEDIATE 'SET CONSTRAINT ' || p_constraint || ' DEFERRED';

   END;

END deferred_constraints;

/

Then we create public synonym and grant::

-- user like SYSTEM

CREATE PUBLIC SYNONYM deferred_constraints FOR scott.deferred_constraints

/

-- user SCOTT

GRANT EXECUTE ON deferred_constraints TO your_user

/

In Forms ON-COMMIT trigger, instead of:

FORMS_DDL ('SET CONSTRAINT dept_pk IMMEDIATE');

we can write:

FORMS_DDL ('BEGIN deferred_constraints.set_immediate (''dept_pk''); END;');

(and the same for DEFERRED).

But, maybe it is cleaner is to write:

BEGIN

   deferred_constraints.set_immediate ('dept_pk');

   deferred_constraints.set_deferred  ('dept_pk');

   COMMIT_FORM;

EXCEPTION

   WHEN OTHERS THEN

      MESSAGE ('COMMIT ERROR: ' || DBMS_ERROR_CODE || ' ' || DBMS_ERROR_TEXT); PAUSE;

      deferred_constraints.set_deferred ('dept_pk');

      RAISE FORM_TRIGGER_FAILURE;

END;

The answer is that it is possible,  but it would be a very difficult job to do correctly. Forms does not support this well.

Consider that you would need to change

• fonts

• height width

• position

• boilerplate fonts

• frame sizes

Fonts, height, width of items, canvases, and windows could be handled with property classes.

Position is tougher, but could be done with API calls.

Boilerplate cannot be controlled at runtime so should be avoided or controlled using the API.

Frames can be faked with lines on canvases and controlled that way.

In practice, I have usually had 2 versions, one for each resolution that I had to support  (I never had to support more than 2 at once).  We would build for the lower resolution and then open the application and manually adjust the form a little.  It takes < 1 hour per screen to do the adjustment.

You have 2 object libraries with SmartClasses that are named the same to make it so you don't have to change property classes for your objects.

Using JClient or JSPs this problem is trivial.

 Paul Dorsey wrote:

The "point" as I see it is that we are between a rock and a hard place. Forms is on its way out JDeveloper is not entirely here. I think we can make a template for JDeveloper that will make us as productive as Forms.

If your organization is willing to still build with Forms, that will get you up.

This is similar to the situation we were in 1985.  IMS/IDMS were "dead" and relational was (more or less, but not really) here.

So, what do we do? We can either pretend there is no problem and build in Forms or we can work to make JDeveloper productive.  I think the choice is clear. It is not a particularly good choice, but I think a clear one.

Is Forms really on its way out? Or are people just upset about C/S going a way and are assuming that Forms will go away too. This is a major "point" that we should have Oracle resolve for us. Because I'm sure none of us want to embark on converting to Forms 9i just to learn in a couple of years Forms will be non-existent.

It is "on its way out" the same was that COBOL is (or was in 1990). Oracle is not throwing massive resources at Designer and Developer, but neither are they pulling the plug. You can count on incremental improvements to the products for years to come but probably not architectural improvements unless Oracle changes it's strategy.

 More Oracle money is going into JDeveloper than anything else at this point. I jokingly talk about how the Forms team is now "three guys in basement", but that is clearly an overstatement. Forms will probably be around for another decade.  But it may not look much different from how it looks now.

At the conferences you see loads of attention to JDeveloper and little to Designer and Developer.  For ODTUG 2003, we had more abstract submissions on JDeveloper than on Designer.  This is telling for a group that used to be called the CASE SIG.

That said, the market seems to have abandoned Forms more than Oracle has. I am seeing little interest in Forms work for consulting.  People don't know what to do, but they know that they don't want Forms.  I don't see any clear winner out there.  We have people building in VB, Forms, Java (using every product under the sun), JSPs.  Oracle Apps is going in the JSP/UIX direction and I am looking forward to getting that functionality in JDeveloper. JDeveloper is a solid contender and growing faster than any product I have ever  worked with.  I don't know if it will be the winner when the dust settles, but I don't think that anything else has a particularly greater chance for success.

Chuck Berger wrote

With all due respect, I think you are missing the point.  We still have to build all of our 100+ applications from scratch in less than two years, besides configuring and testing a whole new deployment  environment.  All of this with three developers.  I think a lot of organizations are in the same situation.

____________________________________________________________________________

Paul Dorsey replied:

I agree that Oracle did not act well in this.  We are being pushed to JDeveloper before it is ready.

Forms may have been "brilliant" in the 3.0 days. I would even agree that 4.5 and 5.0 were great products when they came out.  But then they failed to evolve.  Now Forms is an aging dinosaur that needs to retire.  If Oracle had spent it's development dollars integrating Forms and Designer,  if we now had an integrated repository for the products so  we could work in either

Forms or Designer transparently, if Forms had been re-architected so that blocks were not both data and visual elements, if Oracle had not  blown its development dollars on Cherokee, and SCM, then the products would still be viable.  If Oracle had not prematurely tipped their hand that

 the future is not in Forms and Designer, we would not be in this fix.

But the damage is now done.  I can't find any company interested in using Forms/Designer on new projects.  I am not sure that it would even be professional of me to suggest to companies to build on products that are still "supported" but not aggressively evolving.

I have been frustrated for years by the lack of progress in Developer and Designer.  In contrast, I am thrilled by both the progress and direction of JDeveloper.  My behind the scenes conversations with Oracle folks on JDeveloper make me even more optimistic.  Every time I have ranted to them about what seems to be a dumb direction in JDeveloper, it turns out that they were already going in the direction that I thought they should be going in.

 OK, so what do we do?

JDeveloper can be just as efficient as Forms, but we need to build a good JClient template.  We had to build one for Forms as well, so this is not so terrible.  JDeveloper represents a wonderful platform that Oracle can build on. They are going in absolutely in the right direction.

Also, the business rules approach seems to be taking over.  Get your application logic out of your application and into a repository.  Then generate your application logic to either the application or the DB but make it so that the amount of hand coding in the app is minimal.  Then any lower efficiency in the app environment is minimized.

 From Tim Bealby - Technical Manager - Hamilton Hall

I have been fascinated by this debate.  We should not have to find alternatives to Oracle Developer. Oracle Developer is the only truly data-centric rapid application development tool available using a proper 4GL called PL/SQL that  underpins all of Oracle's technology.  I have talked to several  companies who have moved to Java or JDeveloper and they have all said  that their productivity has remained at best 33% compared to Developer.  This is not a viable solution for many ISVs.

Oracle Developer in its Forms 6 mantle is a brilliant cross-platform development and runtime tool.  We are only considering other  alternatives because Oracle has decided upon the disastrous path of removing client-server mode in Forms 9.  If we compare this strategy with another industry it is like a 4X4 vehicle manufacturer saying that future model can only be driven on nice straight paved roads!!!

So come on all you Developer fanatics keep the pressure up on Oracle to change its mind.  We must have client-server mode in Forms 9!!!

Oracle Developer - Reports

a) Place your summary columns at the bottom of the first page as normal, resetting at page.

b) For the totals at the top of the page, create a local package and update variables in the package using format

triggers on the page summary columns (or a single trigger on a frame that surrounds all of the totals).

c) Place fields on the next page attached to placeholder columns (you don't need the placeholder columns if you are using version 6.

d) Retrieve the values from the package using a format trigger on the fields using srw.set commands.

In a report where a page total is printed on all pages, you don't want to print a page total on the last page, but instead print a report total in the same place.

Can this be done as two different display items, displaying one and hiding the other at the appropriate time?  If so, how do you know if you are on the last page or not?

You can't detect you are on the last page, so you need a trick. Add a dummy row to your report query, i.e. a union that won't show up as real data. In your repeating group, use a format trigger that will set a flag in a package variable that the last record has been encountered and not print the dummy record. Use the value of the package variable to tell which summary to print. Be sure to adjust any summary columns that use count of the detail rows.  

Use the parameter "oracle_shutdown" e.g. 

Add_Parameter(pl_id, 'ORACLE_SHUTDOWN,

TEXT_PARAMETER, 'Yes');

Things you should consider:

1) The report should be using one query. Frequently, people make multiple

queries for master detail reports and link them.

2) You should not have any formula columns with cursors. Make them

functions, put them in the DB and call them in your main SQL query.

3) Don't use bind variables, swap them for lexical parameters. It may change

the explain plan to something strange.

4) Test your SQL in SQL Navigator or SQL+. Make sure your query runs fast

first.

Crystal Reports is NOT far superior to Oracle Reports. Oracle Reports is a deeper and

much more flexible product than Crystal. However, because of its

flexibility, Oracle Reports is harder to master than Crystal. 

With Crystal, you will be forced to write a significant number of your reports using

temporary tables. With Oracle Reports, the product is deep enough that you

almost never need to resort to temporary tables (however, many people that

use Oracle Reports are not skilled with the product and do all sorts of

stupid things with it).

With Crystal, you probably will never do anything really stupid because it

doesn't do as much. With a good Developer, I would prefer Reports, but with a less

skilled developer, Crystal may be a safer choice. I think Crystal may be better as an end user reporting tool as well.

If you are looking for a solid, bomb proof architecture for reporting, I

would suggest Oracle Reports. However, there are still some annoying product bugs such as the latest version of Developer exploiting an alleged bug in the HP printer drivers. 

6

The most recent version of Zetafax (7 now supports faxing using embedded printer codes. This is similar to RightFax but less expensive.

The vanilla license (about $600US for 5 users) has limited embedded command

support.The license plus API (about $1400US) supports a more robust embedded

command set. So far, I have performed limited testing using Reports 3.0 on Windows 98 and Windows 2000 clients.This is a much cleaner solution than the DDE interface.

See www.equisys.com for more information.

Put the codes in the body (not the margin) of the report. Took me a while

to convince myself that they did not have to be the first and last items in

the report.

Thanks to Robert Monical for this information.

There is no real object orientation in reports. So things that you change after running the wizard can only be guessed at by reports.  It is a key architectural flaw in the product.

Try to work in a "100% generation mode".  If you need to make changes, do them in the wizard and regenerate after wiping out that portion of your layout.

This is all explained pretty well in the Chapter 20 – The Layout Model of Oracle Developer: Advanced Forms & Reports (Koletzke & Dorsey, Oracle Press, 2000)

This written up in the Advanced Developer Forms and Reports book by Peter   Koletzke and me.

Basically the idea is to use lexical parameters.  You write your query as

select 'xxxx' break1, ename, dname

from emp, dept

where ...

<'xxxx' break1> is replaced as a lexical parameter with <'xxxx' break1> as the initial value.

Then make your query with a break group with the column break1 in it.

Then if you pass <deptno> to the lexical parameter you will break on deptno. You used to have to make

<'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxx xxxx'break1> 

as your initial value since you couldn't change the column width after compiling the query, but the last time I tried, that wasn't necessary. However, I haven't done this recently.

You will also need to pass the column heading in a separate parameter.

You can do this for more than one level of break, just use multiple break columns and multiple break groups.

You can suppress the usage of the break group by checking for your initial value and killing the break frame with a format trigger.

9

From Gary Belleville of James Bay Consulting Inc.:

I am a big proponent of generating Forms out of Designer (especially 100% generation!), but years ago I didn't find the Reports Generator to be of much use.  Has anyone had any success generating reports out of Designer 6i? Does it make more sense just to build reports directly in Reports Developer 6i?

I am a big believer in building reports right in Developer.  I like to use lexical parameters to build flexible reports that allow me to build a single report instead of dozens.  This kills most of benefits of generation anyway.

If you are a firm believer in generation, then you are probably also still in the world of hundreds of little tables in your data model.  You may be able to achieve some level of success with generation of reports.  The only people I ever knew to subscribe to real reports generation and do it with some flair were the Aris guys (Paul Dirkson, et. al.).  The Oracle Designer Generation book (Atkins, Dirksen, Ince – Oracle Press, May 1999) has the most material available on reports generation and it is still not an exhaustive reference.  The book however is the bible for Forms generation.

I would devote a few days to seeing what kind of results you can get and then make your decision.  One of the advantages of generation is that you don't need to understand a lot about reports.  But with the wizards in Reports now, I suspect that Reports generation is of questionable value. The big benefit would be that if you decide to build hundreds of little reports, they will be maintained in a repository.

I think the real question is: What kind of reports do you want?  If you are going to have to maintain a few hundred reports, then I would give Designer generation a hard look.  If you want to devote more time to Reports as a product and build a small number of flexible reports, then forget about generation.

Sohail Jehangir asked:

I am using Reports 6.0 and want to use a user parameter value for an ORDER BY clause in a data mode SELECT statement. Is there any way can I use it? My users want to sort the report in our required order.

Peter Koletzke of Quovera replied:

If you want to let the user specify the column that the query uses to sort, use the lexical parameter syntax in the data model:

SELECT ...

FROM ...

WHERE ...

ORDER BY &p_order_by

where P_ORDER_BY is the user parameter that contains the column name. Be sure to put a "1" or the default order by column name (for example, "EMPNO") as a default value in that user parameter.

Paul Dorsey added:

Peter's answer is completely correct.  As a related topic:

This method will only work for the most detailed group of the query. The breaks on the other groups are determined by the break order columns. You can dynamically set the sort of the groups other than the detail group by using the lexical parameters to replace what column is queried and then used as the break column.