We have an existing package that inserts data into a Table stored in a remote Database (SQL Server). Below is the summary of the package:
CREATE OR REPLACE PACKAGE XXMyPackage
AS
PROCEDURE GiveMyCursor;
END XXMyPackage;
/
CREATE OR REPLACE PACKAGE BODY XXMyPackage
AS
PROCEDURE GiveMyCursor IS
cursor cursor_1 is
SELECT 'really long query with a lot of joined tables' column1
, 'this is an existing code' column2
, 'i cannot modify this' column3
from dual;
BEGIN
for cursor_x in cursor_1 loop
sql_stmt := 'insert into some_remote_table (column1, column2, column3, column4, column5)'||
'values (:1, 0, :2, :3, 0) ';
EXECUTE IMMEDIATE sql_stmt
USING trim(cursor_x.column1),
trim(cursor_x.column2),
trim(cursor_x.column3);
l_count := get_records_for_insert%rowcount;
end loop;
END GiveMyCursor;
END XXMyPackage;
/
I am tasked to add functionality to this package by dumping its contents into an XLSX file in a directory after the insertion.
In addition, I should make very minimal modifications to existing code, and make the code as short as possible.
I was able to do this with the below modifications (note the lines with the "added" comments:
CREATE OR REPLACE PACKAGE XXMyPackage
AS
TYPE refMyCursor IS REF CURSOR RETURN curMyCursor%ROWTYPE; -- added
TYPE typMyCursor IS TABLE OF curMyCursor%ROWTYPE; -- added
PROCEDURE GiveMyCursor;
END XXMyPackage;
/
CREATE OR REPLACE PACKAGE BODY XXMyPackage
AS
PROCEDURE GiveMyCursor IS
pCursor SYS_REFCURSOR; -- added
l_directory DBA_DIRECTORIES.DIRECTORY_NAME%TYPE := 'DATA_DIR'; -- added
l_filename VARCHAR2(250) := 'SAMPLE_FILENAME'; -- added
cursor cursor_1 is
SELECT 'really long query with a lot of joined tables' column1
, 'this is an existing code' column2
, 'i cannot modify this' column3
from dual;
BEGIN
for cursor_x in cursor_1 loop
sql_stmt := 'insert into some_remote_table (column1, column2, column3, column4, column5)'||
'values (:1, 0, :2, :3, 0) ';
EXECUTE IMMEDIATE sql_stmt
USING trim(cursor_x.column1),
trim(cursor_x.column2),
trim(cursor_x.column3);
l_count := get_records_for_insert%rowcount;
end loop;
/* **************** Start of added Code **************** */
-- cursor query was re-used
OPEN pCursor FOR
SELECT 'really long query with a lot of joined tables' column1
, 'this is an existing code' column2
, 'i cannot modify this' column3
from dual;
l_filename := l_filename||'_'||TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS')||'.xlsx';
-- dumps the query results into an xlsx file
as_xlsx.query2sheet( p_cur => pCursor -- Uses Sys_RefCursor
, p_sheetname => l_filename -- This is where we assign the Sheet Names
, p_directory => l_directory -- Saves it into the directory
, p_filename => l_filename -- The filename of the xlsx file
);
/* **************** End of added Code **************** */
END GiveMyCursor;
END XXMyPackage;
/
Note: The package as_xlsx was taken from another SO post (Create an Excel File (.xlsx) using PL/SQL)
I've confirmed that the modified package works and can see the file in the directory when I run XXMyPackage.GiveMyCursor in an anonymous block. However, cursor_1 is extremely long and was re-used in opening the SYS_REFCURSOR pCursor. I attempted to make this shorter my further modifying the code to:
CREATE OR REPLACE PACKAGE XXMyPackage
AS
/* **************** Start of added Code **************** */
cursor cursor_1 is
SELECT 'really long query with a lot of joined tables' column1
, 'this is an existing code' column2
, 'i cannot modify this' column3
from dual;
TYPE typMyCursor IS TABLE OF cursor_1%ROWTYPE;
/* **************** End of added Code **************** */
PROCEDURE GiveMyCursor;
END XXMyPackage;
/
CREATE OR REPLACE PACKAGE BODY XXMyPackage
AS
PROCEDURE GiveMyCursor IS
tabMyCursor typMyCursor; -- added
pCursor SYS_REFCURSOR; -- added
l_directory DBA_DIRECTORIES.DIRECTORY_NAME%TYPE := 'DATA_DIR'; -- added
l_filename VARCHAR2(250) := 'SAMPLE_FILENAME'; -- added
BEGIN
for cursor_x in cursor_1 loop
sql_stmt := 'insert into some_remote_table (column1, column2, column3, column4, column5)'||
'values (:1, 0, :2, :3, 0) ';
EXECUTE IMMEDIATE sql_stmt
USING trim(cursor_x.column1),
trim(cursor_x.column2),
trim(cursor_x.column3);
l_count := get_records_for_insert%rowcount;
end loop;
/* **************** Start of added Code **************** */
OPEN cursor_1;
FETCH cursor_1 BULK COLLECT INTO tabMyCursor;
CLOSE cursor_1;
OPEN pCursor FOR
SELECT *
FROM TABLE(tabMyCursor);
l_filename := l_filename||'_'||TO_CHAR(SYSDATE, 'DDMMYYYYHHMISS')||'.xlsx';
-- dumps the query results into an xlsx file
as_xlsx.query2sheet( p_cur => pCursor -- Uses Sys_RefCursor
, p_sheetname => l_filename -- This is where we assign the Sheet Names
, p_directory => l_directory -- Saves it into the directory
, p_filename => l_filename -- The filename of the xlsx file
);
/* **************** End of added Code **************** */
END GiveMyCursor;
END XXMyPackage;
/
However, when I do this, I get the error No more data to read from socket some seconds after running XXMyPackage.GiveMyCursor in an anonymous block. What could be the cause of this error?
Database Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
