No question at this time
DBA Top 10
1 B. Vroman 13400
2 M. Cadot 10400
3 J. Schnackenberg 8300
4 T. Boles 7850
5 A. Kavsek 5700
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
8 P. Wisse 1000
10 . Lauri 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48302
Total active users1583
Act. users last 24h3
Act. users last hour0
Registered user hits last week198
Registered user hits last month812
Go up

File Empty
Next thread: ORA-04021: timeout occurred while waiting to lock object
Prev thread: INSERT INTO RETURNING with VBA using ADO/OLE DB

Message Score Author Date
Hi We have an issue where when we run a select ...... Stan M Oct 10, 2018, 10:15
Hi, how do you think anyone can possibly help y...... Ales Kavsek Oct 10, 2018, 11:24
what a rude response, waiting for a client to send...... Stan M Oct 10, 2018, 11:25
create or replace PROCEDURE CUSTOMER_DATA_WHS IS ...... Stan M Oct 10, 2018, 11:47
Hi Stan, being a rude person as I'm, I didn't b...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Ales Kavsek Oct 10, 2018, 12:46
thanks... Stan M Oct 10, 2018, 14:13
Hi Ales I am getting this error: SQL> show e...... Stan M Oct 10, 2018, 15:16
Hi, add commas to the end of columns...please p...... Ales Kavsek Oct 10, 2018, 15:28
thanks will try that and feedback... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Stan M Oct 10, 2018, 16:36
Since we are all so rude here, and since you're no...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Jan Schnackenberg Oct 11, 2018, 09:05
Its fine, you are entitled to your opinion and its...... Stan M Oct 11, 2018, 09:32

Follow up by mail Click here


Subject: File Empty
Author: Stan M, South Africa
Date: Oct 10, 2018, 10:15, 67 days ago
Os info: Linux
Oracle info: 11.2.0.4
Message: Hi

We have an issue where when we run a select statement to a file(csv or txt) we can see contents of the file but when we run it as a procedure using UTL_FILE the file gets created but without data just commas.

Any idea what we need to change, the procedure runs without any errors.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Ales Kavsek, Slovenia
Date: Oct 10, 2018, 11:24, 67 days ago
Message: Hi,

how do you think anyone can possibly help you without seeing (at least) your PL/SQL code!?

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Stan M, South Africa
Date: Oct 10, 2018, 11:25, 67 days ago
Message: what a rude response, waiting for a client to send me the info and will post.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Stan M, South Africa
Date: Oct 10, 2018, 11:47, 67 days ago
Message: create or replace PROCEDURE CUSTOMER_DATA_WHS IS
FILE_HANDLE UTL_FILE.FILE_TYPE;
PUT_LINE VARCHAR(32767);
FCLOSE VARCHAR(32767);
KEY NVARCHAR2(32767);
KEY VARCHAR2(45);
BPCSTA_0 NUMBER(3);
BPCNUM_0 VARCHAR2(45);
XBPRNAM2_1 VARCHAR2(45);
ZZTSCCOD_0 VARCHAR2(45);
ZIMPORT1_0 VARCHAR2(45);
ZIMPORT2_0 VARCHAR2(45);
CRN_0 VARCHAR2(45);
EECNUM_0 VARCHAR2(45);
BPAADD_0 VARCHAR2(45);
BPADES_0 VARCHAR2(45);
BPAADDLIG_0 VARCHAR2(45);
BPAADDLIG_1 VARCHAR2(45);
BPAADDLIG_2 VARCHAR2(45);
CTY_0 VARCHAR2(45);
CRYNAM_0 VARCHAR2(45);
TEL_0 VARCHAR2(45);
FAX_0 VARCHAR2(45);
MOB_0 VARCHAR2(45);
WEB_0 VARCHAR2(45);
CNTFNA_0 VARCHAR2(45);
CNTTTL_0 VARCHAR2(45);
REP_0 VARCHAR2(45);
REPNAM_0 VARCHAR2(45);
CUR_0 VARCHAR2(45);
CCE_2 VARCHAR2(45);
STOFCY_0 VARCHAR2(45);
BEGIN
FILE_HANDLE := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'CUSTOMER_DATA_TO_DATE.txt', 'W',32767);
FOR RWS IN (
SELECT DISTINCT
C.BPCNUM_0 || '_' || A.BPAADD_0 AS "KEY",
C.BPCSTA_0 AS ACTIVE,
C.BPCNUM_0 AS "ACCOUNT NUMBER",
SUBSTR(R.XBPRNAM2_1,0,50) AS "TRADING NAME",
Z.ZZTSCCOD_0 AS "DIVISION",
C.ZIMPORT1_0 AS "BRANCH CODE 1",
C.ZIMPORT2_0 AS "BRANCH CODE 2",
R.CRN_0 AS "COMPANY REGISTRATION",
R.EECNUM_0 AS "VAT NUMBER",
A.BPAADD_0 AS "ADDRESS CODE",
A.BPADES_0,
A.BPAADDLIG_0 AS "ADDRESS LINE 1",
A.BPAADDLIG_1 AS "ADDRESS LINE 2",
A.BPAADDLIG_2 AS "ADDRESS LINE 3",
A.CTY_0 AS "CITY",
A.CRYNAM_0 AS "COUNTRY",
A.TEL_0 AS "TELEPHONE NUMBER",
A.FAX_0 AS "FAX NUMBER",
A.MOB_0 AS "MOBILE NUMBER",
A.WEB_0 AS "EMAIL ADDRESS",
Y.CNTFNA_0 AS "CONTACT NAME",
Y.CNTTTL_0 AS "CONTACT TITLE",
C.REP_0 AS "REP CODE",
S.REPNAM_0 AS "REP NAME",
C.CUR_0 AS "CURRENCY",
Z.ZZTSCCOD_0 AS "AREA",
C.CCE_2 AS "SALES SITE",
P.STOFCY_0 AS "SHIP SITE",
CASE
WHEN R.BPAADD_0 = A.BPAADD_0 THEN 'NO' ELSE 'YES' END AS "SHIP ADDRESS"
FROM BPCUSTOMER C
LEFT JOIN BPARTNER R ON C.BPCNUM_0 = R.BPRNUM_0
LEFT JOIN ZTSCCOD Z ON C.BPCNUM_0 = Z.BPCNUM_0
LEFT JOIN BPADDRESS A ON C.BPCNUM_0 = A.BPANUM_0
LEFT JOIN CONTACT X ON A.BPANUM_0 = X.BPANUM_0
LEFT JOIN CONTACTCRM Y ON X.IDTSGL_0 = Y.IDTSGL_0
LEFT JOIN SALESREP S ON C.REP_0 = S.REPNUM_0
LEFT JOIN BPDLVCUST P ON A.BPANUM_0 = P.BPCNUM_0
WHERE A.BPAADD_0 <> '3'
-- YOUR QUERY HERE
) LOOP
UTL_FILE.PUT_LINE(FILE_HANDLE,
BPCSTA_0 || ',' || BPCNUM_0||',' || XBPRNAM2_1||',' ||ZZTSCCOD_0||',' || ZIMPORT1_0 ||',' ||ZIMPORT2_0 ||',' || CRN_0 ||',' ||EECNUM_0
||',' ||BPAADD_0||',' ||BPADES_0||',' ||BPAADDLIG_0||',' ||BPAADDLIG_1||',' ||BPAADDLIG_2||',' ||CTY_0||',' ||CRYNAM_0||',' ||TEL_0||',' ||FAX_0
||',' ||MOB_0||',' ||WEB_0||',' ||CNTFNA_0||',' ||CNTTTL_0||',' ||REP_0||',' ||REPNAM_0||',' ||CUR_0||',' ||ZZTSCCOD_0||',' ||CCE_2||',' ||STOFCY_0-- YOUR COLUMNS HERE
);
END LOOP;
UTL_FILE.FCLOSE(FILE_HANDLE);
END CUSTOMER_DATA_WHS;
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Ales Kavsek, Slovenia
Date: Oct 10, 2018, 12:46, 66 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Hi Stan,

being a rude person as I'm, I didn't bother to check if my version will work or not, so try and see for yourself. Btw. I also rudely formatted your code, sorry for that too.


CREATE OR REPLACE PROCEDURE CUSTOMER_DATA_WHS IS
BEGIN
FILE_HANDLE := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'CUSTOMER_DATA_TO_DATE.txt', 'W',32767);
FOR RWS IN (
SELECT DISTINCT
C.BPCNUM_0 || '_' || A.BPAADD_0 AS "KEY",
C.BPCSTA_0
C.BPCNUM_0
SUBSTR(R.XBPRNAM2_1,0,50)
Z.ZZTSCCOD_0
C.ZIMPORT1_0
C.ZIMPORT2_0
R.CRN_0
R.EECNUM_0
A.BPAADD_0
A.BPADES_0,
A.BPAADDLIG_0
A.BPAADDLIG_1
A.BPAADDLIG_2
A.CTY_0 AS
A.CRYNAM_0
A.TEL_0
A.FAX_0
A.MOB_0
A.WEB_0
Y.CNTFNA_0
Y.CNTTTL_0
C.REP_0
S.REPNAM_0
C.CUR_0
Z.ZZTSCCOD_0
C.CCE_2
P.STOFCY_0
CASE
WHEN R.BPAADD_0 = A.BPAADD_0 THEN 'NO' ELSE 'YES' END AS "SHIP_ADDRESS"
FROM BPCUSTOMER C
LEFT JOIN BPARTNER R ON C.BPCNUM_0 = R.BPRNUM_0
LEFT JOIN ZTSCCOD Z ON C.BPCNUM_0 = Z.BPCNUM_0
LEFT JOIN BPADDRESS A ON C.BPCNUM_0 = A.BPANUM_0
LEFT JOIN CONTACT X ON A.BPANUM_0 = X.BPANUM_0
LEFT JOIN CONTACTCRM Y ON X.IDTSGL_0 = Y.IDTSGL_0
LEFT JOIN SALESREP S ON C.REP_0 = S.REPNUM_0
LEFT JOIN BPDLVCUST P ON A.BPANUM_0 = P.BPCNUM_0
WHERE A.BPAADD_0 <> '3'
-- YOUR QUERY HERE
) LOOP
UTL_FILE.PUT_LINE(FILE_HANDLE,
RWS.BPCSTA_0 || ',' || RWS.BPCNUM_0||',' || RWS.XBPRNAM2_1||',' ||RWS.ZZTSCCOD_0||',' || RWS.ZIMPORT1_0 ||',' ||RWS.ZIMPORT2_0 ||',' || RWS.CRN_0 ||',' ||RWS.EECNUM_0
||',' ||RWS.BPAADD_0||',' ||RWS.BPADES_0||',' ||RWS.BPAADDLIG_0||',' ||RWS.BPAADDLIG_1||',' ||RWS.BPAADDLIG_2||',' ||RWS.CTY_0||',' ||RWS.CRYNAM_0||',' ||RWS.TEL_0||',' ||RWS.FAX_0
||',' ||RWS.MOB_0||',' ||RWS.WEB_0||',' ||RWS.CNTFNA_0||',' ||RWS.CNTTTL_0||',' ||RWS.REP_0||',' ||RWS.REPNAM_0||',' ||RWS.CUR_0||',' ||RWS.ZZTSCCOD_0||',' ||RWS.CCE_2||',' ||RWS.STOFCY_0);
END LOOP;
UTL_FILE.FCLOSE(FILE_HANDLE);
END CUSTOMER_DATA_WHS;


Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Stan M, South Africa
Date: Oct 10, 2018, 14:13, 66 days ago
Message: thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Stan M, South Africa
Date: Oct 10, 2018, 15:16, 66 days ago
Message: Hi Ales

I am getting this error:

SQL> show errors
Errors for PROCEDURE CUSTOMER_DATA_WHS_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/1 PL/SQL: SQL Statement ignored
15/2 PL/SQL: ORA-00923: FROM keyword not found where expected
SQL>
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Ales Kavsek, Slovenia
Date: Oct 10, 2018, 15:28, 66 days ago
Message: Hi,

add commas to the end of columns...please put some effort in the troubleshooting. I can't validate syntax instead of you (don't have tables etc.).
I removed variables at the beginning of procedure, removed unnecessary column aliases (obviously with commas) in the select and properly address columns with rw prefix. Now it's up to you to complete it. I posted draft that it should work after you fix up the syntax errors.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Stan M, South Africa
Date: Oct 10, 2018, 16:36, 66 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: thanks will try that and feedback
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Jan Schnackenberg, Germany
Date: Oct 11, 2018, 09:05, 66 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Since we are all so rude here, and since you're not the first to take that stance, please know that:

It's rude to expect people to invest time to help and not providing them with at least a minimum of required information to do it.

It's rude to make it hard for people to understand what you're writing by being too lazy to apply at least a minimum of sensible formatting.

It's rude to take an answer, that was provided even though insufficient information was given, and not make a minimum effort to make it work.

It's especially rude to then just fire back an error-message, without at least something like "I'm not good at this stuff, do you have any idea?". You sound as if it's Ales responsibility to put up with you.

It's rude to get advice and not award it (I see no "good" or "excellent" points awarded by you).

So, as I see that Ales went out of his way to provide you with an answer, and since I know that Ales has been helping people in this forum for years, with pretty good success, I'll take my side with the rude people here.

Regards...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: File Empty
Author: Stan M, South Africa
Date: Oct 11, 2018, 09:32, 66 days ago
Message: Its fine, you are entitled to your opinion and its noted.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here