OraFAQ Forum: Reports & Discoverer » Error on pressing hyperlink of the object in a report

Home » Developer & Programmer » Reports & Discoverer » Error on pressing hyperlink of the object in a report

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error on pressing hyperlink of the object in a report [message #552684]

Fri, 27 April 2012 05:36

dhivyaenjoy
Messages: 49
Registered: June 2011

Member

Hi,

I have a report where the Shipments list will appear.

The shipment_id appears as hyperlink and when it is pressed it should be redirected to another report which displays the cost details on the respective shipments

But we get an error message as in the attachment when i press the hyperlink.

Can anyone suggest me what would be the issue and where should i check for the error to resolve?

Also the query used to extract data is

/*select
decode(cost_type,
'B','Base',
'A','Accessorial',
'D','Discount',
'O','Other',
'S','Stop-off Charge',
'C','Circuity Charge',
cost_type) Cost_Type,
round(sum(Cost),2) Cost,
decode(cost_gid,'GBP','£','USD','$',cost_gid) curr_symbol
from shipment_cost
where &p_shipment_gid
group by Cost_Type,decode(cost_gid,'GBP','£','USD','$',cost_gid)*/

Thanks in advance

Regards
Dhivya

[MERGED by LF]

Attachment: screen_shot_of_error.JPG
(Size: 24.50KB, Downloaded 1301 times)

[Updated on: Wed, 02 May 2012 07:07] by Moderator

Report message to a moderator

Re: Error on pressing hyperlink of the object in a report [message #552734 is a reply to message #552684]

Fri, 27 April 2012 10:58

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Before posting your next message, please, spare a few seconds of your time and see how to format your code. I ran your query through a formatter and this the result. I'm sure that you agree that formatted code is much easier to read and follow.

SELECT Decode(cost_type, 'B', 'Base', 
                         'A', 'Accessorial', 
                         'D', 'Discount', 
                         'O', 'Other', 
                         'S', 'Stop-off Charge', 
                         'C', 'Circuity Charge', 
                         cost_type) Cost_Type, 
       Round(SUM(Cost), 2)          Cost, 
       Decode(cost_gid, 'GBP', '£', 
                        'USD', '$', 
                        cost_gid)   curr_symbol 
FROM   shipment_cost 
WHERE  &p_shipment_gid 
GROUP  BY Cost_Type, 
          Decode(cost_gid, 'GBP', '£', 
                           'USD', '$', 
                           cost_gid)

It appears that it is OK, but the problem might be in P_SHIPMENT_GID lexical parameter. How is it created? Check its value.

Report message to a moderator

Re: Error on pressing hyperlink of the object in a report [message #552946 is a reply to message #552734]

Mon, 30 April 2012 06:34

dhivyaenjoy
Messages: 49
Registered: June 2011

Member

Hi,

Apologies, will follow the same going forward to format the code.

Please find the attached set lexical parameter function and P_shipment_gid value specified in the report.

These values are reported in the child report and the Parent report has the call for child report to appear as hyperlink

Please advise whats going wrong with the lexical parameter.

Thanks in advance

Regards,
Dhivya

Attachment: set_lexical_parameter & P_shipment_gid value.JPG
(Size: 46.22KB, Downloaded 1182 times)

Report message to a moderator

Re: Error on pressing hyperlink of the object in a report [message #552951 is a reply to message #552946]

Mon, 30 April 2012 07:09

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That also seems to be OK (just in case, you might display lexical parameter's value at the end of that code (use SRW.MESSAGE built-in)).

Which report does the query (you posted in the first message) belong to? Is it the "first one", or the one that should be opened when you click the hyperlink?

Error message says that libraries have been modified. Are there any libraries attached to the report? If so, did you try to recompile them as well?

Report message to a moderator

Re: Error on pressing hyperlink of the object in a report [message #552962 is a reply to message #552951]

Mon, 30 April 2012 08:56

dhivyaenjoy
Messages: 49
Registered: June 2011

Member

Hi,

Thanks for the reply.

The query in the first message belongs to the Second report which is the one that should be opened when we click the hyperlink.

Also there is attached library with the report and can you please advise me on compiling the library for the report.

Is it the same as we how we compile the report?

Also i want to bring to your notice that the first Parent report has a Format trigger defined for this particular variable as attached.

The value of Shipment_gid fetched will be like 'ORACL.12345678'

The value shipment_xid used in the attached trigger will be like '12345678' i.e substr(shipment_gid,7)

But both the variables shipment_gid and shipment_xid are Varchar2 in database and Character in Reports.

Will the attached Format trigger is fine or needs modification?

Please advise.

Regards,
Dhivya

Attachment: f_shipment_xidFormat Trigger.JPG
(Size: 13.11KB, Downloaded 1275 times)

[Updated on: Mon, 30 April 2012 09:05]

Report message to a moderator

Re: Error on pressing hyperlink of the object in a report [message #553157 is a reply to message #552962]

Wed, 02 May 2012 07:00

dhivyaenjoy
Messages: 49
Registered: June 2011

Member

Hi LittleFoot,

The report is working successfully now.

The problem was there in the set lexical parameter form.

it was like this before

PROCEDURE set_lexical_parameter IS
pls_orig_p1 VARCHAR2(32766) ;
BEGIN
	IF NOT( :P_SHIPMENT_GID IS NULL or :P_SHIPMENT_GID = '1=1') then
  	pls_orig_p1 := :P_SHIPMENT_GID ;
  	:P_SHIPMENT_GID := 'shipment_gid IN ('||:P_SHIPMENT_GID||')' ;
	ELSE
  	:P_SHIPMENT_GID := '1=1' ;
	END IF ;
END;

Now modified to

PROCEDURE set_lexical_parameter IS
pls_orig_p1 VARCHAR2(32766) ;
BEGIN
	IF NOT( :P_SHIPMENT_GID IS NULL or :P_SHIPMENT_GID = '1=1') then
  	pls_orig_p1 := :P_SHIPMENT_GID ;
  	:P_SHIPMENT_GID := 'shipment_gid ='''||:P_SHIPMENT_GID||'''' ;
	ELSE
  	:P_SHIPMENT_GID := '1=1' ;
	END IF ;
END;

This worked and the report is returning the data as expected.

Thanks for all your help.

Really a very helpful forum.

Regards,
Dhivya

Report message to a moderator

Set lexical parameter [message #553158 is a reply to message #552684]

Wed, 02 May 2012 07:03

dhivyaenjoy
Messages: 49
Registered: June 2011

Member

Hi,

Can anyone spot me out the difference between the below two set lexical parameter codes.

PROCEDURE set_lexical_parameter IS
pls_orig_p1 VARCHAR2(32766) ;
BEGIN
	IF NOT( :P_SHIPMENT_GID IS NULL or :P_SHIPMENT_GID = '1=1') then
  	pls_orig_p1 := :P_SHIPMENT_GID ;
  	:P_SHIPMENT_GID := 'shipment_gid IN ('||:P_SHIPMENT_GID||')' ;
	ELSE
  	:P_SHIPMENT_GID := '1=1' ;
	END IF ;
END;

PROCEDURE set_lexical_parameter IS
pls_orig_p1 VARCHAR2(32766) ;
BEGIN
	IF NOT( :P_SHIPMENT_GID IS NULL or :P_SHIPMENT_GID = '1=1') then
  	pls_orig_p1 := :P_SHIPMENT_GID ;
  	:P_SHIPMENT_GID := 'shipment_gid ='''||:P_SHIPMENT_GID||'''' ;
	ELSE
  	:P_SHIPMENT_GID := '1=1' ;
	END IF ;
END;

Thanks in advance

Regards
Dhivya

Report message to a moderator

Re: Set lexical parameter [message #553160 is a reply to message #553158]

Wed, 02 May 2012 07:06

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Great, I'm glad you made it work! Thank you for the information.

The first one uses IN, the second one =.

[After topics have being merged, I merged my messages as well]

[Updated on: Wed, 02 May 2012 07:08]

Report message to a moderator

Re: Set lexical parameter [message #553161 is a reply to message #553160]

Wed, 02 May 2012 07:11

dhivyaenjoy
Messages: 49
Registered: June 2011

Member

Hi,

I can make out that difference but what it means actually by the code?

Because the code with IN dint work but the code with '=' worked as expected.

Kindly advise.

Regards,
Dhivya

Report message to a moderator

Re: Set lexical parameter [message #553163 is a reply to message #553161]

Wed, 02 May 2012 07:21

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

The problem isn't in vs =.
It's the number of quotes.
It always helps, when dealing with dynmaic sql, to output the dynamic string so you can see what it's done.
Watch:

SQL> DECLARE
  2  
  3  P_SHIPMENT_GID VARCHAR2(50) := 'ABC';
  4  BEGIN
  5   IF NOT( P_SHIPMENT_GID IS NULL or P_SHIPMENT_GID = '1=1') then
  6     P_SHIPMENT_GID := 'shipment_gid IN ('||P_SHIPMENT_GID||')' ;
  7   ELSE
  8     P_SHIPMENT_GID := '1=1' ;
  9   END IF ;
 10    
 11    dbms_output.put_line(P_SHIPMENT_GID);
 12    
 13  END;
 14  /
shipment_gid IN (ABC)

PL/SQL procedure successfully completed.

SQL>

Value is not in quotes so it's treated as a column name, which I assume it isn't.

SQL> DECLARE
  2  
  3  P_SHIPMENT_GID VARCHAR2(50) := 'ABC';
  4  BEGIN
  5   IF NOT( P_SHIPMENT_GID IS NULL or P_SHIPMENT_GID = '1=1') then
  6     P_SHIPMENT_GID := 'shipment_gid ='''||P_SHIPMENT_GID||'''' ;
  7   ELSE
  8     P_SHIPMENT_GID := '1=1' ;
  9   END IF ;
 10    
 11    dbms_output.put_line(P_SHIPMENT_GID);
 12    
 13  END;
 14  /
shipment_gid ='ABC'

PL/SQL procedure successfully completed.

SQL>

And that one's in quotes.

Report message to a moderator

Re: Set lexical parameter [message #553164 is a reply to message #553161]

Wed, 02 May 2012 07:29

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Lexical parameters are used in a SELECT statement.

Now, take it and supply the first lexical parameter's value into your query, then the second one, and see what happens.

Here's an example:

SQL> create or replace function fun_gid_equal (p_shipment_gid in varchar2)
  2    return varchar2
  3  is
  4    retval varchar2(100);
  5  begin
  6    return 'shipment_gid ='''||P_SHIPMENT_GID||'''' ;
  7  end;
  8  /

Function created.

SQL> create or replace function fun_gid_in (p_shipment_gid in varchar2)
  2    return varchar2
  3  is
  4    retval varchar2(100);
  5  begin
  6    return 'shipment_gid IN ('||P_SHIPMENT_GID||')' ;
  7  end;
  8  /

Function created.

SQL>
SQL> select
  2    'where '|| fun_gid_equal('ORACL.12345678') result_equal,
  3    'where '|| fun_gid_in('ORACL.12345678') result_in
  4  from dual;

RESULT_EQUAL                             RESULT_IN
---------------------------------------- ----------------------------------------
where shipment_gid ='ORACL.12345678'     where shipment_gid IN (ORACL.12345678)

SQL>

Obviously, your IN code is wrong (single quotes are missing).

P.S. Ooops! While I was composing my example, Cookiemonster posted his own. Never mind, I guess.

[Updated on: Wed, 02 May 2012 07:30]

Report message to a moderator