Home » RDBMS Server » Server Utilities » Urgent: ORA-29280
Urgent: ORA-29280 [message #172446] |
Tue, 16 May 2006 09:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
brandointheweb
Messages: 25 Registered: August 2005 Location: Germany
|
Junior Member |
|
|
Hi all,
with following trigger I try to generate a .txt-file!
CREATE OR REPLACE TRIGGER TBINBEV_START_AFT_UPD
AFTER UPDATE ON TBINBEV_START
FOR EACH ROW
DECLARE
vAUFTRAGSID NUMBER(10);
vERSTELLDATUM DATE;
vERSTELLER VARCHAR2(12);
vEMPFAENGER VARCHAR2(10);
vZAEHLER NUMBER(2) := 0;
vFILE UTL_FILE.FILE_TYPE;
vPFAD VARCHAR2(250);
vDATEINAME VARCHAR2(100);
CURSOR c_EXPORT IS SELECT DS_ART,TEXT FROM TBINBEV_EXPORT ORDER BY GUID;
CURSOR c_AUFTRAGSID IS SELECT "AuftragsID","ArtikelID","Artikelnummer","Sollmenge","Mengeneinheit" FROM LIEFERPOS@LAGERSUITE
WHERE "AuftragsID" = vAUFTRAGSID AND "Mandant" = (SELECT LSMANDANT FROM TBINBEV_KONFIGURATION WHERE GUID = 1);
BEGIN
-- Tabelle TBINBEV_EXPORT mit Daten füllen
SELECT "AuftragsID","Erstelldatum",SUBSTR("Ersteller",1,12),SUBSTR("Firma1",1,10)
INTO vAUFTRAGSID,vERSTELLDATUM,vERSTELLER,vEMPFAENGER FROM AUFTRAG@LAGERSUITE
WHERE "Auftragsnummer" = :NEW.SDGVERSSDGNR AND "Mandant" = (SELECT LSMANDANT FROM TBINBEV_KONFIGURATION WHERE GUID = 1);
INSERT INTO TBINBEV_EXPORT (DS_ART,TEXT)
VALUES ('H',RPAD('ZO0300030202',12,'0') || LPAD(vEMPFAENGER,10,'0') || TO_CHAR(TRUNC(vERSTELLDATUM),'YYYYMMDD') || RPAD(:NEW.SDGVERSSDGNR,35) || RPAD(' ',31)|| '30' || RPAD(vERSTELLER,12) || RPAD(' ',12) || TO_CHAR(:NEW.SDGENTLADETAG,'YYYYMMDD'));
FOR vPos IN c_AUFTRAGSID LOOP
vZAEHLER := vZAEHLER + 1;
IF UPPER(SUBSTR(vPos."Artikelnummer",LENGTH(vPos."Artikelnummer") - 1)) = '-V' THEN
UPDATE TBINBEV_EXPORT SET TEXT = SUBSTR(TEXT,1,110) || 'X ' || SUBSTR(TEXT,113) WHERE DS_ART = 'H';
END IF;
INSERT INTO TBINBEV_EXPORT (DS_ART,TEXT)
VALUES ('I',LPAD(TO_CHAR(vZAEHLER),6,'0') || LPAD(SUBSTR(vPos."Artikelnummer",1,18),18,'0') || LPAD(TO_CHAR(vPos."ArtikelID"),18,'0') || LPAD(TO_CHAR(vPos."Sollmenge"),13,'0') || RPAD(SUBSTR(vPos."Mengeneinheit",1,3),3));
END LOOP;
-- Textdatei erzeugen
SELECT SPEICHERORT INTO vPFAD FROM TBINBEV_KONFIGURATION;
vDATEINAME := 'InBev_Orders_' || TO_CHAR(TRUNC(SYSDATE,'MI'),'DDMMYYYY_HH24MI') || '.txt';
vFILE := UTL_FILE.FOPEN(location => vPFAD, --'E:\Digital_Data\TEST',
filename => vDATEINAME,
open_mode => 'w',
max_linesize => 32000);
FOR vPos_1 IN c_EXPORT LOOP
UTL_FILE.PUT_LINE(vFILE,vPos_1.DS_ART || vPos_1.TEXT);
END LOOP;
UTL_FILE.FCLOSE(vFILE);
-- Daten in Archivtabelle TBINBEV_ARCHIV speichern
INSERT INTO TBINBEV_ARCHIV(SENDUNGNR,SDGVERSSDGNR,SDGERFASSTWER,SDGENTLADETAG,SDGEMPFAENGER,DATEINAME)
VALUES(:NEW.SENDUNGNR,:NEW.SDGVERSSDGNR,:NEW.SDGERFASSTWER,:NEW.SDGENTLADETAG,:NEW.SDGEMPFAENGER,vDATEINAME);
-- SENDEDATUM in Tabelle TBSENDUNGEN speichern
UPDATE TBSENDUNGEN_SMSUSER SET SDGSENDEDATUM = SYSDATE WHERE SENDUNGNR = :NEW.SENDUNGNR;
-- Daten in der Tabelle TBINBEV_EXPORT löschen
DELETE FROM TBINBEV_EXPORT;
--EXCEPTION
--WHEN UTL_FILE.INVALID_PATH THEN
--DBMS_OUTPUT.PUT_LINE('INVALID_PATH');
--WHEN UTL_FILE.INVALID_MODE THEN
--DBMS_OUTPUT.PUT_LINE('INVALID_MODE');
--WHEN UTL_FILE.INVALID_FILEHANDLE THEN
--DBMS_OUTPUT.PUT_LINE('INVALID_filehandle');
--WHEN UTL_FILE.INVALID_OPERATION THEN
--DBMS_OUTPUT.PUT_LINE('INVALID_OPERATION');
--WHEN UTL_FILE.WRITE_ERROR THEN
--DBMS_OUTPUT.PUT_LINE('WRITE_ERROR');
--WHEN UTL_FILE.READ_ERROR THEN
--DBMS_OUTPUT.PUT_LINE('READ_ERROR');
--WHEN UTL_FILE.INTERNAL_ERROR THEN
--DBMS_OUTPUT.PUT_LINE('INTERNAL_ERROR');
--WHEN OTHERS THEN
--NULL;
END TBINBEV_START_AFT_UPD;
But I get the ORA-29280-fault! I really tried everything!
Path exists, dropped and Declared the path several times with following etc.:
CREATE DIRECTORY out_dir AS 'F:\DigitalData';
GRANT WRITE ON DIRECTORY out_dir TO PUBLIC;
What can I do else??
Thanks
MArlon
|
|
|
|
Re: Urgent: ORA-29280 [message #172527 is a reply to message #172446] |
Wed, 17 May 2006 01:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
brandointheweb
Messages: 25 Registered: August 2005 Location: Germany
|
Junior Member |
|
|
Yes the user has write permission to the folder!
Now I tried a simple test like this to check:
create or replace directory out_dir as 'F:\Aktuell';
--Grant Write on Directory out_dir TO IID
declare
f utl_file.file_type;
begin
f := utl_file.fopen('out_dir', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.put_line(f, 'line two: more text');
utl_file.fclose(f);
end;
But the result is the same: ORA-29280
Do I need to restart my oracle instant?
I really need help!
Thanks
Marlon
|
|
|
|
|
Goto Forum:
Current Time: Thu Aug 29 20:03:05 CDT 2024
|