asked on

Explicit Cursor To .csv Not Returning Data

When I run the below sql script I am not getting any data.

set echo off
set wrap off
set verify off
set linesize 500
set heading off
set feedback off
set pagesize 0
set serveroutput on size unlimited

/* Write data to a file, if an error, the error message ends up in the file */ 
spool &2


declare

C_HEADER	CONSTANT VARCHAR(149)	DEFAULT 'PONUM~POLINENUM~DESCRIPTION~ORDERDATE~STATUS~VENDOR~'||
						'CONTRACTREFNUM~ORDERQTY~UNITCOST~LINECOST~RECEIVEDQTY~'||
						'GLDEBITACCT~PROJECTID~PROJECT_TASK~SITEID';
C_DELIMITER	CONSTANT VARCHAR(01)	DEFAULT '~';

/* Variables */
  V_DATA		VARCHAR(205);

/* Flat File Variables */
  V_PONUM              	VARCHAR(07);  
  V_POLINENUM          	VARCHAR(03);
  V_DESCRIPTION        	VARCHAR(50);
  V_ORDERDATE		VARCHAR(08);  
  V_STATUS             	VARCHAR(25); 
  V_VENDOR             	VARCHAR(11);
  V_CONTRACTREF        	VARCHAR(04);   
  V_ORDERQTY           	VARCHAR(11);  
  V_UNITCOST           	VARCHAR(16);   
  V_LINECOST           	VARCHAR(16); 
  V_RECEIVEDQTY        	VARCHAR(11);
  V_GLDEBITACCT        	VARCHAR(18);  
  V_POLALN1            	VARCHAR(10);
  V_POLALN2            	VARCHAR(10);  
  V_SITEID             	VARCHAR(05);


/* PO DATA */

CURSOR POLINE_CUR IS
		select distinct(poline.ponum), poline.polinenum, poline.description, po.orderdate, po.status, companies.name as vendor_name, poline.contractrefnum,
  		poline.orderqty, poline.unitcost, poline.linecost, poline.receivedqty, poline.gldebitacct,
  		poline.polaln1, poline.polaln2, poline.siteid

		from maximo.poline poline

		inner join maximo.po po
		on poline.ponum = po.ponum
		and poline.SITEID = po.siteid
		and poline.orgid = po.orgid
		and poline.revisionnum = po.revisionnum

		left outer join maximo.companies companies
		on po.vendor = companies.company
		and po.orgid = companies.orgid

		WHERE PO.CHANGEDATE > (select nvl(max(EXPORTEDDATE), sysdate) FROM miftab.GA_PODATA_SQL_LOG where EXPORTEDDATE is not null)
		and (poline.gldebitacct like '%654000' or poline.gldebitacct like '%654100' or poline.gldebitacct like '%654200')
		order by ponum, polinenum;


POLINE_REC POLINE_CUR%ROWTYPE;

BEGIN
       	DBMS_OUTPUT.ENABLE(500000000);  

	
	/* Open File and Populate Flat File */

	OPEN POLINE_CUR;
	LOOP
	BEGIN
	FETCH POLINE_CUR INTO POLINE_REC;
	IF POLINE_CUR%NOTFOUND
		THEN
		CLOSE POLINE_CUR;
		EXIT;
	END IF;

	   V_DATA := C_HEADER;	-- 001 - 205
	   DBMS_OUTPUT.PUT_LINE (V_DATA);	   

	   V_DATA          := ' ';
                                                                            	-- PO Number     
           V_PONUM         := LPAD(NVL(POLINE_REC.PONUM, ' '),7,'0'); 
                                                          
                                                                              	-- PO Line Number 
           V_POLINENUM     := LPAD(NVL(POLINE_REC.POLINENUM, 0), 3, '0'); 
                                                                            
                                                                            	-- Poline Description
           V_DESCRIPTION   := NVL(POLINE_REC.DESCRIPTION, ' ');
                                                                  		-- Order Date
           V_ORDERDATE     := NVL(TO_CHAR(POLINE_REC.ORDERDATE,'YYYYMMDD'),' ');   
                                                                 		-- Status
           V_STATUS        := NVL(POLINE_REC.STATUS,' ');   
                                                                   		-- Vendor Name
           V_VENDOR        := NVL(POLINE_REC.VENDOR_NAME,' ');
                                                                  		-- Contract Reference Number
           V_CONTRACTREF   := NVL(POLINE_REC.CONTRACTREFNUM, ' ');
                                                                  		-- Quantity Ordered
           V_ORDERQTY      := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.ORDERQTY,'S0000000.00')),' '),11, '0');
                                                                   		-- Unit Cost
           V_UNITCOST      := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.UNITCOST,'S00000000.000000')),' '),16, '0'); 
                                                                            	-- Line Cost
           V_LINECOST      := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.LINECOST,'S00000000.000000')),' '),16, '0');
										-- Received Qty        
           V_RECEIVEDQTY   := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.RECEIVEDQTY,'S0000000.00')),' '),11, '0');
                                                                     		-- GL Debit Account
           V_GLDEBITACCT   := NVL(SUBSTR(POLINE_REC.GLDEBITACCT,1,4)  ||
                                  SUBSTR(POLINE_REC.GLDEBITACCT,6,4)  ||
                                  SUBSTR(POLINE_REC.GLDEBITACCT,11,4) ||
                                  SUBSTR(POLINE_REC.GLDEBITACCT,16,6), ' ');  
                                                                            	-- Project ID
           V_POLALN1       := LPAD(NVL(POLINE_REC.POLALN1, ' '), 10);
                                                                            	-- Task ID
           V_POLALN2       := LPAD(NVL(POLINE_REC.POLALN2, ' '), 10);                                         
                                                                            	-- Site ID
           V_SITEID        := NVL(POLINE_REC.SITEID, ' ');      


	       V_DATA         := V_PONUM          || 
                              C_DELIMITER         || 
                              V_POLINENUM         || 
                              C_DELIMITER         ||
                              V_DESCRIPTION       ||
                              C_DELIMITER         ||
			      V_ORDERDATE	  ||
			      C_DELIMITER	  ||                        
                              V_STATUS            || 
                              C_DELIMITER         ||                         
                              V_VENDOR            || 
                              C_DELIMITER         || 
                              V_CONTRACTREF       || 
                              C_DELIMITER         || 
                              V_ORDERQTY          || 
                              C_DELIMITER         || 
                              V_UNITCOST          ||                     
                              C_DELIMITER         || 
                              V_LINECOST          || 
                              C_DELIMITER         || 
                              V_RECEIVEDQTY       || 
                              C_DELIMITER         || 
                              V_GLDEBITACCT       || 
                              C_DELIMITER         || 
                              V_POLALN1           || 
                              C_DELIMITER         || 
                              V_POLALN2           || 
                              C_DELIMITER         || 
                              V_SITEID;                                                          
               
        /* Write Record */
             DBMS_OUTPUT.PUT_LINE (V_DATA);  

	END LOOP;
	CLOSE POLINE_CUR;
	END;

END;


spool off

Open in new window

slightwv (䄆 Netminder)

If you mean the spool file is empty, a PL/SQL block needs an ending '/':

...
END LOOP;
	CLOSE POLINE_CUR;
	END;

END;
/

Open in new window

slightwv (䄆 Netminder)

You mention CSV in the title but it appears you are using fixed-length format.

Why are you using PL/SQL and a cursor loop to do this? It seems straight SQL will work and probably be faster.

You also shouldn't be using DBMS_OUTPUT for this. It is meant for debugging not actual data output.

johnsone

Looks to me like it is creating a ~ delimited file. Don't see fixed length.

Definitely not a job for DBMS_OUTPUT. Should be using UTL_FILE.

I have always had issues with getting straight SQL to give consistent output. Always preferred UTL_FILE, much more control over things that way.

Of course, UTL_FILE would only be able to write to the database server, so you would need a way to get the file from there.

If you cannot get the file off the server, then it certainly could be done with straight SQL and spool commands. If you concatenate the whole thing together and don't blow out the buffer that is. Never had good luck trying to use straight SQL and column commands to get it to come out right. Could be me, but I tried a lot.

slightwv (䄆 Netminder)

>>Looks to me like it is creating a ~ delimited file. Don't see fixed length.

I missed the C_DELIMITER but was going off all the LPADs. It is character delimited, don't see a need for them.

johnsone

Delimited is needed. Not all the fields are padded. Looks like only the numbers are formatted. All the character fields appear to be variable length.

slightwv (䄆 Netminder)

Does look like most are doing some sort of number formatting but you have ones like:
V_POLALN1 := LPAD(NVL(POLINE_REC.POLALN1, ' '), 10);
V_POLALN2 := LPAD(NVL(POLINE_REC.POLALN2, ' '), 10);

I still haven't gone through line by line picking it apart because I saw enough in my first scan to know that it isn't likely the correct approach. Wasn't going to dvie into the detail until I heard back from Denise.

johnsone

My first scan saw a lot like this:

V_DESCRIPTION := NVL(POLINE_REC.DESCRIPTION, ' ');
V_VENDOR := NVL(POLINE_REC.VENDOR_NAME,' ');

I doubted a description or vendor name would be only a single character.

slightwv (䄆 Netminder)

Let's just agree it isn't likely the most efficient or way either of us would have done it?

ASKER CERTIFIED SOLUTION

Denise Fisher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

What was the final solution?