Happy coding. Trilochan.This message has been edited. Last edited by: Trilochan,
WebFOCUS 7.6.1 Windows, All Outputs
January 15, 2013, 05:22 AM
Trilochan
I have found error in this method.
It works upto a limit of records.
If Record count is more than 500,the system throw a error.
INSUFFICIENT MEMORY IS AVAILABLE FOR PROGRAM/CACHE .
Please help me.
My Webfocus version is 7.6.1.
Thank you.
WebFOCUS 7.6.1 Windows, All Outputs
February 06, 2013, 10:18 AM
Trilochan
I have solve this problem by implement paging in the FEX file.
Below is my fex code.
-SET &&CONNECTION_NAME='CONNECTION NAME OF ORACLE'
-SET &NO_OF_RECORDS=100;
-DEFAULTS &PG=1;
-DEFAULTS &SUBMITTED=0;
-TYPE &SUBMITTED;
-*---Start-Insert Record
-IF &SUBMITTED EQ 1 THEN GOTO INSERT_DATA ELSE GOTO EXIT_INSERT;
-RUN
-INSERT_DATA
-* Start Insert Into Database
-SET &RecordCount = &txtRecordCount;
-SET &PAGE_COUNT=&PAGE_C;
-SET &PAGE_COUNT1= IF &PAGE_COUNT NE 1 THEN &PAGE_COUNT-1 ELSE &PAGE_COUNT;
-SET &I = IF &PAGE_COUNT EQ 1 THEN 1 ELSE &PAGE_COUNT1.EVAL01;
-SET &RecordCount =IF &RecordCount GT 9 THEN &RecordCount ELSE 0|&RecordCount.EVAL;
-SET &RecordCount2=IF &RecordCount EQ 100 THEN &PAGE_C.EVAL00 ELSE &PAGE_COUNT1.EVAL|&RecordCount.EVAL;
-SET &RecordCount1 = IF &PAGE_COUNT EQ 1 THEN &RecordCount ELSE IF &TOTAL_PG EQ &PAGE_C THEN &RecordCount2 ELSE &PAGE_COUNT.EVAL00;
-TYPE &RecordCount1;
-LOOP
-SET &REMARK_VALUE=&rem.&I;
SQL SQLORA SET SERVER &&CONNECTION_NAME
SQL SQLORA
-TYPE &I;
EX TEST_INSERT_UPDATE_RPT_STUDENT(&I,'&REMARK_VALUE');
END
-SET &I=&I+1;
-IF &I EQ &RecordCount1+1 GOTO ENDLOOP;
-GOTO LOOP
-ENDLOOP
-HTMLFORM BEGIN
<html>
<body >
[b]Record updated successfully[/b]
</body>
</html>
-HTMLFORM END
-*---End-Insert Record
-EXIT_INSERT
-*-Start-Report Data
-*---Start-Total number of records
SQL SQLORA SET SERVER &&CONNECTION_NAME
SQL SQLORA
EX TEST_STUDENT_COUNT &NO_OF_RECORDS;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DATA_COUNT
END
-*TABLE FILE DATA_COUNT
-*PRINT
-*T/A40 NO NOPRINT
-*END
-RUN
-READ DATA_COUNT &T.A40.
-SET &A=&T;
-SET &A = TRUNCATE(&A);
-TYPE &A;
-*---End-Total number of records
SQL SQLORA SET SERVER &&CONNECTION_NAME
SQL SQLORA
EX TEST_STUDENT_DATA &PG,&NO_OF_RECORDS;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DATAFILE
END
DEFINE FILE DATAFILE
REMARKS/A255 = '<input maxlength="10" type="text" value="' || REMARKS || '" name="rem' || ID || '" >';
END
TABLE FILE DATAFILE
PRINT
'DATAFILE.DATAFILE.ID'
'DATAFILE.DATAFILE.STUDENT_ID' AS 'PIECE ID'
'DATAFILE.DATAFILE.REMARKS' AS 'REMARKS'
HEADING
"Student Information"
ON TABLE HOLD AS HLDS FORMAT HTMTABLE
END
-SET &SHOWBUTTON = IF &A GT 1 THEN '' ELSE 'none';
-IF &LINES EQ 0 THEN GOTO NO_DATA ELSE PIECE_DATA;
-PIECE_DATA
-HTMLFORM BEGIN
<html>
<head><title>WebFOCUS Report with paging</title>
<script type="text/javascript">
function runrep(direction)
{
var frm = document.WForm;
var totalpage=parseInt(&A);
if (direction == 'prev')
{
if(parseInt(frm.PG.value)>1)
{
frm.PAGE_C.value=frm.PG.value;
frm.PG.value--;
frm.submit();
}
else
{
alert("You are in the report First Page");
}
}
if (direction == 'next')
{
if(parseInt(frm.PG.value)<totalpage)
{
frm.PAGE_C.value=frm.PG.value;
frm.PG.value++;
frm.submit();
}
else
{
alert("You are in the report Last Page");
}
}
if (direction == 'submit')
{
frm.PAGE_C.value=frm.PG.value;
frm.submit();
}
}
</script>
</head>
<body>
<form name="WForm" action="/ibi_apps/WFServlet" method="post">
<input type="hidden" name="IBIF_ex" value="test_dynamic_input_p.fex">
<input type="hidden" name="TOTAL_PG" value="&A">
<input type="hidden" name="PG" value="&PG">
<input type="hidden" name="PAGE_C">
<input type="hidden" name="SUBMITTED" value="1">
<input type="hidden" name="txtRecordCount" value="&LINES">
<table>
<tr>
<td><input type="button" style="display:'&SHOWBUTTON'" value="Prev" onClick="runrep('prev')"/></td>
<td><input type="button" style="display:'&SHOWBUTTON'" value="Next" onClick="runrep('next')"/></td>
<td><input type="button" value="Submit" onClick="runrep('submit')"/></td>
<td><input type="reset" value="Reset"/></td>
</tr>
</table>
!IBI.FIL.HLDS;
</form>
</body>
</html>
-HTMLFORM END
-EXIT
-NO_DATA
-HTMLFORM BEGIN
<html>
<body >
<center>
[b]No data exists[/b]
</center>
</body>
</html>
-HTMLFORM END
-*-End-Report Data
TEST_STUDENT_COUNT procedure code is given below
CREATE OR REPLACE PROCEDURE "TEST_STUDENT_COUNT"(NOOFRECORDS IN NUMBER DEFAULT NULL,
RC1 OUT SYS_REFCURSOR) AS
RECCNT NUMBER(6, 2);
TOTALPAGES NUMBER(10, 0);
BEGIN
TOTALPAGES := 0;
BEGIN
SELECT COUNT(1)
INTO RECCNT
FROM STUDENT;
TOTALPAGES := CEIL(RECCNT / NOOFRECORDS);
OPEN RC1 FOR
SELECT TO_CHAR(ROUND(TOTALPAGES)) AS T FROM DUAL;
END;
END "TEST_STUDENT_COUNT";
TEST_STUDENT_DATA procedure code is given below
CREATE OR REPLACE PROCEDURE "TEST_STUDENT_DATA"(PAGENO IN NUMBER DEFAULT NULL,
NOOFRECORDS IN NUMBER DEFAULT NULL,
RC1 OUT SYS_REFCURSOR) AS
RECCNT NUMBER(6, 2);
FROM_ NUMBER(10, 0);
TO_ NUMBER(10, 0);
BEGIN
BEGIN
SELECT COUNT(1)
INTO RECCNT
FROM STUDENT;
FROM_ := ((PAGENO - 1) * NOOFRECORDS) + 1;
TO_ := FROM_ + (NOOFRECORDS - 1);
OPEN RC1 FOR
SELECT to_char(ROWNUM) ID, STUDENT_ID,REMARKS
FROM STUDENT
WHERE ROWNUM >= FROM_
AND ROWNUM <= TO_;
END;
END "TEST_STUDENT_DATA";
TEST_INSERT_UPDATE_RPT_STUDENT procedure is given below
CREATE OR REPLACE PROCEDURE TEST_INSERT_UPDATE_RPT_STUDENT(V_R_NO NUMBER,
V_REMARKS VARCHAR2) IS
R_COUNT NUMBER;
V_STUDENT_ID NUMBER;
BEGIN
SELECT STUDENT_ID
INTO V_STUDENT_ID
FROM STUDENT
WHERE ROWNUM = V_R_NO;
SELECT COUNT(1)
INTO R_COUNT
FROM RPT_TEST_STUDENT
WHERE STUDENT_ID = V_STUDENT_ID;
IF R_COUNT = 1 THEN
UPDATE RPT_TEST_STUDENT
SET REMARKS = V_REMARKS
WHERE STUDENT_ID = V_STUDENT_ID;
ELSE
INSERT INTO RPT_TEST_STUDENT
(STUDENT_ID, REMARKS)
VALUES
(V_STUDENT_ID,V_REMARKS);
END IF;
COMMIT;
END;
