Very Urgent

can you create a procedure for each task and call the procedure in the report builder?
I am not familiar with report builder, can you tell me if this is possible so that I will offer you two procedure to do those tasks?

Yes I can call the procedures in report builder
so please post the procedure for the following tasks:

How to print the last row in each page to the top of next page.

& 

How to start the records displaying on next page when it is started from 1st of each month( A newer month start from a newer page)

using: Report Builder 6i
     Oracle 9i

I am quoted above the question from the above link for you

So Please help me

1. dispaly the last row in each page to the top of the next page.
--CREATE a package to declare a variable of REF CURSOR to hold an result set

CREATE OR REPLACE PACKAGE reports
AS
report_cursor_type IS REF CURSOR;
END reports;
/

CREATE OR REPLACE PROCEDURE report_page_proc (p_start_row IN number, p_row_in_page IN number, p_report_cursor OUT reports.report_cursor_type)
AS
l_row_count NUMBER ;
l_start_row NUMBER ;
BEGIN

--initialize the variable using the passing parameter:

l_row_count := p_row_in_page;
l_start_row :=p_start_row - 1; --This will make sure you offset the resultset so that it display the last row of previous page.
--create a global temporary table

EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TEST_TEMP ON COMMIT PRESERVE ROWS AS SELECT rownum AS row_number, test.* FROM TEST ORDER BY 1';

--open the cursor

OPEN p_report_cursor FOR select * from TEST_TEMP where row_number between l_start_row and (l_start_row + l_row_count);

END;
END report_page_proc ;
/


2. Create a procedure to display the records displaying on next page when it is started from 1st of each month( A newer month start from a newer page)

for illustration, say we select from table TEST with a date column "display_date".
we can reuse the package cursor variable upstairs:

CREATE OR REPLACE PROCEDURE report_month_proc (p_start_month IN VARCHAR, p_report_cursor OUT reports.report_cursor_type)
AS
l_start_month VARCHAR;
BEGIN

--initialize the variable using the passing parameter:

l_start_month :=p_start_month; --this will get the month

--In this case you don't even need a temporary table because you don't need to limit number of rows in each page
--you display all the rows within a month in one page
--open the cursor
--to_char(display_date, 'MM') will return the month of the date as a char . for instance '01-SEPT-02' , it will return '09'
--order by display_date will make sure in the page, the smallest date in the month display the first.

OPEN p_report_cursor FOR select * from TEST where to_char(display_date, 'MM') = l_start_month order by display_date

END;
END report_page_proc ;
/


that will be it. if you have further question, shoot....

ok i will test it and told you then.....

thanks for quick response

LeoAhmad

seazodiac,

Your procedures are not executed successfully.Can you do it with some temporary table on your machine and then from sql*plus cut and copy the code for me

i will be very thankful to you


LeoAhmad

Just Tell me that how you use these procedures....

Then I will use it in Report Builder

thanks

Leo

first compile these procedures into your database library by just executing them in the sqlplus or sql worksheet.

then in your application, you need to call them explicitly.
for example:

in sqlplus you need to call : "execute <procedure_name>"

in java JDBC you need to issue "{call <procedure_name>}"

read your report builder tool help or documentation as to how to call a procedure..I am sure you will find it

i do it in sql plus but can not get results

LeoAhmad

of course, you cannot get any output from sqlplus because your PL/SQL procedure will return a resultset as a CURSOR.

you should do this in sqlplus if you want to see what's in the cursor:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> VARIABLE test_cursor REFCURSOR

SQL> exec report_page_proc (1, 25, test_cursor);
SQL> print test_cursor;