OraFAQ Forum: Reports & Discoverer » Getting the query text from Reports

Home » Developer & Programmer » Reports & Discoverer » Getting the query text from Reports (Developper 6i)

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

Getting the query text from Reports [message #469303]

Wed, 04 August 2010 03:52

Amine
Messages: 377
Registered: March 2010

Senior Member

Hi All,
Am looking for some stuff that allows me to get the query text of a query in Oracle Reports.
Something like :

get_query_text(query_name IN VARCHAR2);

Thank you in advance,
Amine

Report message to a moderator

Re: Getting the query text from Reports [message #469341 is a reply to message #469303]

Wed, 04 August 2010 05:03

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

Senior Member
Account Moderator

Why don't you open a report in Reports Builder and take the query out?

What is the source? Is it a REP or RDF or JSP or ... file? Where would you like to run this "get_query_text"?

Report message to a moderator

Re: Getting the query text from Reports [message #469618 is a reply to message #469303]

Thu, 05 August 2010 05:49

Amine
Messages: 377
Registered: March 2010

Senior Member

This is not the purpose.
Say we have a basic report based on parameters.
I want to process the data that have been printed through this parameters.

Doing this process on the 'AFTER REPORT' trigger would work.
And it works.

Now I don't want to re-write the query that brings the data on the AFTER REPORT trigger. I want to get it at runtime.

This is done to avoid doing changes in two parts of the report :
In the named query (the principal query)AND In the AFTER REPORT trigger.

Thank you in advance

Report message to a moderator

Re: Getting the query text from Reports [message #469630 is a reply to message #469618]

Thu, 05 August 2010 06:14

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

Senior Member

You're going to have to explain in more detail what you are trying to do here.

Report message to a moderator

Re: Getting the query text from Reports [message #469717]

Thu, 05 August 2010 11:31

Amine
Messages: 377
Registered: March 2010

Senior Member

Well.
Say I want to retrieve a list of employees and print it in a report. I want to add a dynamic clause to the report.
The query in the data model looks like :

select id_emp, emp_name
from emp
where 1 = 1
&clause;

where &clause is the dynamic clause.

After the report is printed, I want, for example, to log all the printed employees in a certain table say log_printed_emps.

For now, to achieve this, I am writing an 'AFTER REPORT' trigger that insert the printed employees into log_printed_emps.
The trigger look like :

begin
cursorID	EXEC_SQL.CURSTYPE;
sqlstr		VARCHAR2(1000);
nIgn		PLS_INTEGER;
nRows		PLS_INTEGER := 0;
BEGIN
	cursorID := EXEC_SQL.OPEN_CURSOR;
	
	sqlstr := 
	' select id_emp, emp_name ' ||
	' from emp ' ||
	' where 1 = 1 ' ||
	:clause ;
	
	EXEC_SQL.PARSE(cursorID, sqlstr);
	
	...
	
	nIgn := EXEC_SQL.EXECUTE(cursorID);
	
	WHILE (EXEC_SQL.FETCH_ROWS(cursorID) > 0 ) 
	LOOP
		insert into log_printed_emps ...
	END LOOP;
	commit;
end;

The sqlstr in the trigger body is THE SAME query used in the data model. I want to retrieve the query in the data model.
Hope I was clear now Smile

Report message to a moderator

Re: Getting the query text from Reports [message #469721 is a reply to message #469717]

Thu, 05 August 2010 11:44

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

Senior Member

It might be easier for you to base the report query on a ref cursor in a stored procedure in the DB.
Then you could do the logging in the procedure rather than the after report trigger.

Report message to a moderator

Previous Topic:	rep-0737
Next Topic:	.rtf report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 12 04:07:18 CDT 2026