Home » RDBMS Server » Server Utilities » Urgent: ORA-29280
Urgent: ORA-29280 [message #172446] Tue, 16 May 2006 09:36 Go to next message
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 #172449 is a reply to message #172446] Tue, 16 May 2006 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 26765
Registered: January 2009
Location: SoCal
Senior Member
At OS level does user (oracle?) have write permissions to folder?
Re: Urgent: ORA-29280 [message #172527 is a reply to message #172446] Wed, 17 May 2006 01:49 Go to previous messageGo to next message
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
Re: Urgent: ORA-29280 [message #172541 is a reply to message #172527] Wed, 17 May 2006 02:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Try using upper case for the name of the directory.
'OUT_DIR' instead of 'out_dir'
Solved [message #172544 is a reply to message #172446] Wed, 17 May 2006 02:55 Go to previous message
brandointheweb
Messages: 25
Registered: August 2005
Location: Germany
Junior Member
That's it!!!!!
Many thanks!!!

Best regards!
Marlon
Previous Topic: Import using Toad
Next Topic: SQL*Loader - how to load a file like this?
Goto Forum:
  


Current Time: Sat Jun 12 09:58:42 CDT 2021