?
Solved

Reports: multiple problems: printing, querying, trigger

Posted on 2004-08-11
25
Medium Priority
?
12,109 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 5
25 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 11780624
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 620 total points
ID: 11811121
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
ID: 11818887
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:boelaars
ID: 11819123
*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
ID: 11820458
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
ID: 11821716
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 620 total points
ID: 11828153
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
ID: 11828300
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
ID: 11828614
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
ID: 11828744
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á
ID: 11828761
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
ID: 11828897
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
 

Author Comment

by:boelaars
ID: 11829249
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á
ID: 11829358
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
ID: 11829451
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á
ID: 11829479
If you comment line after line  - what line does cause a compilation problem ?
0
 

Author Comment

by:boelaars
ID: 11829600
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 620 total points
ID: 11829638
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
ID: 11829723
*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
ID: 11829749
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á
ID: 11829836
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
ID: 11838778
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á
ID: 11838837
I think that you are right that all you can do is passing all parameters to the report.
0
 

Author Comment

by:boelaars
ID: 11839159
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á
ID: 11839232
You are welcome :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

-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 …
This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

765 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