asked on

Reports: multiple problems: printing, querying, trigger

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.

Helena Marková

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.

SOLUTION

Poornimashiv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

boelaars

ASKER

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.

boelaars

ASKER

*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.

Poornimashiv

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

boelaars

ASKER

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 :)

SOLUTION

Poornimashiv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

boelaars

ASKER

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'

Poornimashiv

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

boelaars

ASKER

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.

Helena Marková

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.

Poornimashiv

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

boelaars

ASKER

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.

?

Helena Marková

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;

boelaars

ASKER

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.

Helena Marková

If you comment line after line - what line does cause a compilation problem ?

boelaars

ASKER

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?

ASKER CERTIFIED SOLUTION

Helena Marková

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

boelaars

ASKER

*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.

boelaars

ASKER

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.

Helena Marková

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.

boelaars

ASKER

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.

Helena Marková

I think that you are right that all you can do is passing all parameters to the report.

boelaars

ASKER

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!

Helena Marková

You are welcome :)