Solved

Reports: multiple problems: printing, querying, trigger

Posted on 2004-08-11
25
12,040 Views
Last Modified: 2013-12-01
I am making my first baby stept with Oracle Reports Services and have a few things I can't figure out. (This is more or less a 'help me get started please' :-)

a) I installed Oracle (9.0.4) on both Win2k and WinXP. Both are on the same network and both have the same printer installed as default (and only) printer. Both Oracle Reports servers have the same configurations (Except for their hostname and IP dependent config switches ofcourse). Running a report on the WinXP system goes just fine: I enter the correct URL in a webbrowser and it rolls out of the printer. But whenever I try to run a report on the Win2k server it gives me the error "REP-3002: Error initializing printer.  Please make sure a printer is installed." Which is rediculous because it is installed perfectly fine. I tried this with multiple printers. Also on the XP box no matter what the printer settings are in the windows printer dialog, it always wants to print from the multi-purpose paper feeder (tray 0) and no other one. Which is rather anoying. I suppose there must be some way to tell Reports Services to use another one. Any hints/tips/suggestions?

b) In my report I defined a user parameter KP_ID which I use in my query as '... where id = :KP_ID'. The report is printed without any text though. Just the background image appears. I suppose this is because I fail to understand how to create the proper key in cgicmd.dat. Currently it looks like this:

brief: userid="user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testserver)(PORT=1521))(CONNECT_DATA=(SID=MYSID)))" report=D:\projects\reports\brief.rdf destype=printer

What to add? I fail to understand the bit of documentation in the cgicmd.dat file I guess.

c) I installed the event-based reporting API in the Oracle 9i DB server (9.2) but now am searching for explanation on how to create a trigger that will call this report whenever an insert occurs on a specific table, but the documentation CD is not of any help, or I am just to stupid to find the correct page in that jungle of documents. Heh.

A lot of questions. I know. Assigned all points I currently own. Hmm... think I should become a paying member here. Will fix that soon.
0
Comment
Question by:boelaars
  • 12
  • 8
  • 5
25 Comments
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
b) Maybe this note 274006.1 from Metalink will help you:

How to include a reports parameter value within the URL and pass to report

Fix
Find parameter name within report and follow example.

Example:
http://your_host:8888/reports/rwservlet?report=test.rdf
&desformat=html&destype=cache
&userid=scott/tiger@db&server=rep_ora9ids?meter_name=parameter_value.
0
 
LVL 1

Assisted Solution

by:Poornimashiv
Poornimashiv earned 155 total points
Comment Utility
Hi,

a) Are you trying to print a Report when running it from the Report Server?
If you have the Report Builder on Windows 2000, have you tried sending an output to Printer from the Builder?

Try this on your Windows 2000 box:

- Start-> Settings-> Control Panel-> Administrative Tools-> Services
- Double click on the Reports Server
- Go to "Log On" tab
- Change on the "Log On As" section to "This account" and enter the userid/password of the
  user that installed Reports Server
- Ensure that the user account where the Reports Server is installed has administrator privileges
  and a default printer.
- Restart the Reports Server as the new user.

Try printing a Report from the URL.

Regards,
Poornima
0
 

Author Comment

by:boelaars
Comment Utility
a) Win2k issue solved, thanks to Poornima.

b) Solved this myself

c) For some reason the DB server doesn't accept the trigger I try to add to it. Although it is what the documentation tells me to do:

CREATE TRIGGER MY_TRIGGER
AFTER INSERT on MY_TABLE FOR EACH ROW
myPlist SRW_PARAMLIST;
myIdent SRW.Job_Ident;
BEGIN
myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
srw.add_parameter(myPlist,'GATEWAY','http://tstnt01/reports/rwservlet');
srw.add_parameter(myPlist,'SERVER','rep_tstnt01');
srw.add_parameter(myPlist,'CMDKEY','brief');
srw.add_parameter(myPlist,'KP_ID',:new.ID);
myIdent := srw.run_report(myPlist);
END;

I DON'T know if the gateway parameter and server parameter are correct. I took the server parameter from the rwservlet.properties file from the working reports server, and the gateway... the docs don't tell me what exactly to put after the 'http://' part.

But apart from that, the DB server complains on row 3 already (myPlist SRW_PARAMLIST;) which looks to me like it thinks the event-driven publishing api isn't installed. Which I double-checked, and it is.
0
 

Author Comment

by:boelaars
Comment Utility
*doh*
Just had to add DECLARE above line 3. Seems like the documentation isn't 100% correct.

But it isn't all fine yet. When I do an insert so the trigger gets fired this happens:

SQL> insert into MY_TABLE (id, description) values (666, 'test');
insert into MY_TABLE (id, description) values (666, 'test')
            *
ERROR at line 1:
ORA-20100: Error occurred while parsing: Expected ';'.
ORA-06512: at "SYS.XMLPARSER", line 22
ORA-06512: at "SYS.XMLPARSER", line 91
ORA-06512: at "user.SRW", line 333
ORA-06512: at "user.SRW", line 813
ORA-06512: at "user.MY_TRIGGER", line 10
ORA-04088: error during execution of trigger 'user.MY_TRIGGER'

Line 10 is "srw.add_parameter(myPlist,'CMDKEY','brief');"

No clue how to go from here. This is too reports services specific for me.
0
 
LVL 1

Expert Comment

by:Poornimashiv
Comment Utility
Hi,

The Gateway parameter would be something like:

srw.add_parameter(myPlist,'GATEWAY','http://<server_name>:<port>/reports/rwservlet');

And the Server parameter would be the name of the Report Server.

Can you clarify what CMDKEY you are using? I mean the entry in your cgicmd.dat file.

Regards,
Poornima
0
 

Author Comment

by:boelaars
Comment Utility
the key in the cgicmd.dat file:

brief: userid="user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tstnt01)(PORT=1521))(CONNECT_DATA=(SID=MYSID)))" report=D:\projects\reports\brief.rdf destype=printer %*

So I guessed the server and gateway thing correctly. good. Now for the trigger part :)
0
 
LVL 1

Assisted Solution

by:Poornimashiv
Poornimashiv earned 155 total points
Comment Utility
Hi,

Did you run the srwAPIins.sql script in your Database?

Run the script srwAPIins.sql (resides under <9iDSHome>/reports/admin/sql) as follows:
      a) Navigate to the directory <9iDSHome>/reports/admin/sql
         (On Windows open command prompt and navigate to the directory.)

      b) connect to SQL*Plus from the command prompt and then execute the
         script from SQL prompt:  
           SQL>@srwAPIins.sql
     
      c) This script asks for portal username and password. If you want to secure your Reports Server through portal, provide the portal username/password. However, you could run this script with any user who has execute privilege on sys.xmlparser and sys.xmldom packages. You need not have portal installed in the database.

2) Run srwAPIgrant.sql (resides under <9iDSHome>/reports/admin/sql) to grant privileges on the varray, object types and the package created by the srwAPIins.sql script to the user who runs a report. Enter the API objects owner username/password first(the user who ran srwAPIins.sql) and then the grantee username/password.  
   
3) Create the following procedure in your database to run your report, for
   example, emp.jsp:

   create or replace procedure event_driven_report as
     myPlist SRW_PARAMLIST;
     myIdent SRW.Job_Ident;
   BEGIN
    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'GATEWAY','http://<server_name>:<port>/reports/rwservlet');
    srw.add_parameter(myPlist,'SERVER','myRepserver');
    srw.add_parameter(myPlist,'REPORT','d:\work\emp.jsp');
    srw.add_parameter(myPlist,'USERID','myuser/mypassword@orcl');
    srw.add_parameter(myPlist,'AUTHID','portal_username/portal_password@orcl');
    srw.add_parameter(myPlist,'DESTYPE','file');
    srw.add_parameter(myPlist,'DESFORMAT','HTMLCSS');
    srw.add_parameter(myPlist,'DESNAME','c:\temp\emp.htm');
    myIdent := srw.run_report(myPlist);
   EXCEPTION
    when others then
    raise;
   END;
   /

Note: AUTHID parameter is required when your reports server is secured.
 
4) Execute the procedure:
 
    SQL>exec event_driven_report;

   This will submit the job for execution. You can now check the status of the job using SHOWJOBS. For example:
   http://<server_name>:<port>/reports/rwservlet/showjobs?server=myRepserver

Let me know if this helps.

Regards,
Poornima
0
 

Author Comment

by:boelaars
Comment Utility
Yes, I already stated that the api is installed.
And when I add the trigger it doesn't complain so it seems to compile fine.
But when doing an insert like stated above it will bail out on me:

The trigger:

1  CREATE TRIGGER MY_TRIGGER
2  AFTER INSERT on MY_TABLE FOR EACH ROW
3  DECLARE
4      myPlist SRW_PARAMLIST;
5      myIdent SRW.Job_Ident;
6  BEGIN
7      myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
8      srw.add_parameter(myPlist,'GATEWAY','http://tstnt01/reports/rwservlet');
9      srw.add_parameter(myPlist,'SERVER','rep_tstnt01');
10     srw.add_parameter(myPlist,'CMDKEY','brief');
11     srw.add_parameter(myPlist,'KP_ID',:new.ID);
12     myIdent := srw.run_report(myPlist);
13 END;

The problem:

SQL> insert into MY_TABLE (id, description) values (666, 'test');
insert into MY_TABLE (id, description) values (666, 'test')
            *
ERROR at line 1:
ORA-20100: Error occurred while parsing: Expected ';'.
ORA-06512: at "SYS.XMLPARSER", line 22
ORA-06512: at "SYS.XMLPARSER", line 91
ORA-06512: at "user.SRW", line 333
ORA-06512: at "user.SRW", line 813
ORA-06512: at "user.MY_TRIGGER", line 10
ORA-04088: error during execution of trigger 'user.MY_TRIGGER'
0
 
LVL 1

Expert Comment

by:Poornimashiv
Comment Utility
Hi,

Try setting destype=cache and desformat=html and check if you get the same error.
Also, is this a long running Report or a simple one?

Regards,
Poornima
0
 

Author Comment

by:boelaars
Comment Utility
same error all the time.
The report is the simplest test thingy which just takes that 1 parameter and prints out the newly created record. Works great when invoking it through a webbrowser.
Seems to me like the DB doesn't even get to the reports server yet because it encounters a parse error.
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
I think that the problem is in using SRW package in a database trigger. It cannot be done directly. You can try to call Poornima's procedure from trigger.
0
 
LVL 1

Expert Comment

by:Poornimashiv
Comment Utility
Hi,

Yes, the trigger could be a problem. Try calling the procedure event_driven_report from a database trigger:

    Create or replace trigger tr_emp after update or insert or delete on emp
    for each row
    begin
       event_driven_report;
    end;

Regards,
Poornima
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:boelaars
Comment Utility
This is what I just tried:

create or replace procedure my_table_pcd as
DECLARE
    myPlist SRW_PARAMLIST;
    myIdent SRW.Job_Ident;
BEGIN
    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'GATEWAY','http://tstnt01:7777/reports/rwservlet');
    srw.add_parameter(myPlist,'SERVER','rep_tstnt01');
    srw.add_parameter(myPlist,'CMDKEY','brief');
    srw.add_parameter(myPlist,'KP_ID',:new.ID);
    myIdent := srw.run_report(myPlist);
END;

create or replace trigger my_table_trg after insert on my_table
for each row
begin
   my_table_pcd;
end;

But both the procedure and the trigger give me a warning uppon creation:
Warning: Procedure created with compilation errors.
Warning: Trigger created with compilation errors.

I tried tweaking a bit (removing or adding quotes and the declare statement and such) but couldn't solve it.

Also, as a sidenote, the oracle reports documentation sais you can call the reports server through the SRW api stuff straight frmo a trigger. In fact all examples they give are triggers, no procedures.

?
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
Here is the correct code for procedure - the DECLARE cannot be there, that is why there is a compilation error there:

create or replace procedure my_table_pcd as
    myPlist SRW_PARAMLIST;
    myIdent SRW.Job_Ident;
BEGIN
    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'GATEWAY','http://tstnt01:7777/reports/rwservlet');
    srw.add_parameter(myPlist,'SERVER','rep_tstnt01');
    srw.add_parameter(myPlist,'CMDKEY','brief');
    srw.add_parameter(myPlist,'KP_ID',:new.ID);
    myIdent := srw.run_report(myPlist);
END my_table_pcd;
0
 

Author Comment

by:boelaars
Comment Utility
I already experimented with that and find this not the case:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create or replace procedure my_table_pcd as
  2      myPlist SRW_PARAMLIST;
  3      myIdent SRW.Job_Ident;
  4  BEGIN
  5      myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
  6      srw.add_parameter(myPlist,'GATEWAY','http://tstnt01:7777/reports/rwservlet');
  7      srw.add_parameter(myPlist,'SERVER','rep_tstnt01');
  8      srw.add_parameter(myPlist,'CMDKEY','brief');
  9      srw.add_parameter(myPlist,'KP_ID',:new.ID);
 10      myIdent := srw.run_report(myPlist);
 11  END my_table_pcd;
 12  /

Warning: Procedure created with compilation errors.
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
If you comment line after line  - what line does cause a compilation problem ?
0
 

Author Comment

by:boelaars
Comment Utility
Line 9:  srw.add_parameter(myPlist,'KP_ID',:new.ID);

that's the bastard. I can understand a trigger to know about that parameter. But shouldn't I pass the new record to the procedure in some way so it'll know about it too?
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 155 total points
Comment Utility
So put the input parameter to the procedure:
create or replace procedure my_table_pcd (new_id NUMBER) as
...
srw.add_parameter(myPlist,'KP_ID',new_id);
...
0
 

Author Comment

by:boelaars
Comment Utility
*woohoo*

almost!

I added the suggested change to the procedure and that went fine. Then I changed the trigger to look like this:

create or replace trigger my_table_trg after insert on my_table
for each row
begin
   my_table_pcd(:new.ID);
end;

it seems to be an OK, trigger, and when I did an insert on the table... guess what... the printer started printing! GREAT.
BUT it displays no text yet. Which means: the ID parameter isn't passed to the report server. Something is still not completely ok there, but getting close now.
Did I change the trigger correctly? This reports services stuff makes me do my first moves in the oracle db trigger/procedure world, heh.
0
 

Author Comment

by:boelaars
Comment Utility
executing the procedure manualy works like a charm. nice reports come from the printer.
So my guess is that I don't do the trigger part correctly yet.
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
Here is an example:
(http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem6d.htm#2063994)

Calling a Procedure in a Trigger Body Example

You could create the salary_check trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure scott.salary_check, which verifies that an employee's salary in in an appropriate range. Then you could create the trigger salary_check as follows:

CREATE TRIGGER scott.salary_check
   BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
   FOR EACH ROW
   WHEN (new.job <> 'PRESIDENT')
   CALL check_sal(:new.job, :new.sal, :new.ename);

The procedure check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.
0
 

Author Comment

by:boelaars
Comment Utility
Hmmmm
The trigger is fine.
The real problem seems to be the fact that (ofcourse) the data is not committed to the DB yet, while Reports Services is querying the DB for the ID passed to it, and doesn't get a result.

Hmmm.... I don't think there is another solution than do the querying stuff all in the trigger/procedure and pass all parameters to the reports server so that it won't have to do any querying anymore then.
Or if there would be another solution I'd love to hear about it.
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
I think that you are right that all you can do is passing all parameters to the report.
0
 

Author Comment

by:boelaars
Comment Utility
Nice.
This works good now, and I learned a lot again.
Thanks for your help Henka & Poornimashiv.
I'll split and share the points :)

Cheers!
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
You are welcome :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

-Xmx and -Xms are the two JVM options often used to tune JVM heap size.   Here are some common mistakes made when using them:   Assume BigApp is a java class file for the below examples. 1.         Missing m, M, g or G at the end …
Configure Web Service (server application) I. Configure security for Web Services methods First, we need to protect Session bean which implements the service: 1. Open EJB deployment descriptor (ejb-jar.xml) in the EJB project that contains you…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now