April 10, 2013 at 10:19 am
Hi All,
I'm trying to call an Oracle procedure from an SSIS OLE DB source data flow task and have a record set returned so i can write it to a flat file destination.
When i try to preview it, i get the error:
"No disconnected record set is available for the specified SQL statement."
Any ideas? thanks, Scott
Below is the statement i'm using to cal it and the procedure..
DECLARE
TYPE Cursor_Type_2 IS REF CURSOR;
precordset Cursor_Type_2;
BEGIN
avmindev.freedomex.freedomexport(
pdate=>TO_DATE('2012-03-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
pstore_number=>471,
precordset=>precordset);
:PRECORDSET := PRECORDSET;
END;
PROCEDURE FreedomExport
(pDate IN DATE,
pStore_Number IN NUMBER,
pRecordSet OUT SYS_REFCURSOR)
AS
BEGIN
OPEN pRecordSet FOR
SELECT
SUBSTR(f2.quickid,1,4) as StudentID,
SUBSTR(t.ticketid,1,7) as InvoiceNum,
'I' as TicketType,
SUBSTR(s.quickid,1,5) as ServiceID,
TO_CHAR(sysdate,'YYMMDD-HH24MISS') as BatchID,
' ' as Grade,
'XXXXXXXXXXXXXXXX' as Filler
FROM TicketData t
LEFT JOIN Service s ON t.ItemID = s.ID AND t.Store_Number = s.Store_Number
LEFT JOIN Staff f2 on t.staffid2 = f2.id
WHERE t.Status=1
AND t."Date" = pDate
AND t.datatype = 5
AND t.store_number = pStore_Number
AND NOT f2.ID is Null;
END; /* FreedomExport */
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply