Archive for June 2015
SET SERVEROUTPUT ON;
DECLARE
V1 VARCHAR2(32767);
l_file UTL_FILE.FILE_TYPE;
V_COUNT NUMBER :=0;
V_FILENAME VARCHAR2(50):=NULL;
l_buffer raw(32767);
l_amount NUMBER := 32767;
l_pos NUMBER := 1;
L_BLOB BLOB;
L_BLOB_LEN NUMBER:=0;
CURSOR GET_CLAIM
IS SELECT RECEIPT,ATTACH_FILENAME FROM EMPLOYEE_DATA ;
BEGIN
FOR REC IN GET_CLAIM
LOOP
L_BLOB := REC.RECEIPT;
v_FILENAME := rec.ATTACH_FILENAME;
l_blob_len := dbms_lob.getlength(l_blob);
L_POS := 1; --re-init l_pos var
-- Open the destination file.
l_file := UTL_FILE.FOPEN('LOCAL_DIR',v_FILENAME, 'WB');
DBMS_OUTPUT.PUT_LINE(V_FILENAME||' '||l_blob_len);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len
LOOP
dbms_lob.read(l_blob, l_amount, l_pos, l_buffer);
utl_file.put_raw(l_file, l_buffer, true);
L_POS := L_POS + L_AMOUNT;
END LOOP;
UTL_FILE.FCLOSE(l_file);
V_COUNT:=V_COUNT+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_COUNT||' '||'files created');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20000, 'ERROR: Invalid PATH FOR file.');
DBMS_OUTPUT.PUT_LINE('error');
-- Close the file if something goes wrong.
WHEN OTHERS THEN
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
DBMS_OUTPUT.PUT_LINE('error');
END ;
DECLARE
V1 VARCHAR2(32767);
l_file UTL_FILE.FILE_TYPE;
V_COUNT NUMBER :=0;
V_FILENAME VARCHAR2(50):=NULL;
l_buffer raw(32767);
l_amount NUMBER := 32767;
l_pos NUMBER := 1;
L_BLOB BLOB;
L_BLOB_LEN NUMBER:=0;
CURSOR GET_CLAIM
IS SELECT RECEIPT,ATTACH_FILENAME FROM EMPLOYEE_DATA ;
BEGIN
FOR REC IN GET_CLAIM
LOOP
L_BLOB := REC.RECEIPT;
v_FILENAME := rec.ATTACH_FILENAME;
l_blob_len := dbms_lob.getlength(l_blob);
L_POS := 1; --re-init l_pos var
-- Open the destination file.
l_file := UTL_FILE.FOPEN('LOCAL_DIR',v_FILENAME, 'WB');
DBMS_OUTPUT.PUT_LINE(V_FILENAME||' '||l_blob_len);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len
LOOP
dbms_lob.read(l_blob, l_amount, l_pos, l_buffer);
utl_file.put_raw(l_file, l_buffer, true);
L_POS := L_POS + L_AMOUNT;
END LOOP;
UTL_FILE.FCLOSE(l_file);
V_COUNT:=V_COUNT+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_COUNT||' '||'files created');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20000, 'ERROR: Invalid PATH FOR file.');
DBMS_OUTPUT.PUT_LINE('error');
-- Close the file if something goes wrong.
WHEN OTHERS THEN
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
DBMS_OUTPUT.PUT_LINE('error');
END ;
In iReport to pass parameter from Main report to Dataset use following steps:
1. Create parameter in main report section.
2. Set parameter properties accordingly.
3. Add new dataset by right click on main report.
4. Add parameter in dataset by right click on dataset.
5. Drag “Table” element
on report and select appropriate dataset as data source for it.
6. Right click on table in report and click on “edit table datasource”
7. Below window will appear.
8. Select “parameters”
tab and click on add button.
9. Select dataset parameter name from select list to which
we want to pass parameter from main report. Click on pencil to add value
expression to select parameter.
Below error occurs because there is no any data to show
on report.
If you are using the Detail Bandeport, then you will need a
Data Query for the report. Since it's returning "No Pages" in
iReport, then you either don't have a query. Or simply your query is not
returning any rows.
In old iReport, from
Data menu, choose Report Query and write your query here. If you want only one
record (i.e. Detail band only one time) you can use a dummy table. Like:
Select ‘a’ from dummy;
Or
Select 1 from dual;
Your problem is not exceptions or errors, it's just no data to show.
