5 Replies Latest reply: Apr 13, 2007 9:06 AM by JensPetersen RSS

    table dba_directories no exist

    499841
      Hi,
      I use a procedure to send a email, and after I can use this for more time, today I found this message, when I tryed to compile

      141/13 PL/SQL: SQL Statement ignored 141/63 PL/SQL: ORA-00942: table o view doesn't exist

      The rows 140 and 141 is:

      select dd.directory_name into directory_name from dba_directories dd
      where dd.directory_path = line and rownum = 1;

      The only difference is that I use this procedure also to another funcionality, so now I use this procedure for 2 functions

      Do you have any suggestion?!
      Thank's
      Bye bye

      ps. If you need I can post all the procedure
        • 1. Re: table dba_directories no exist
          JensPetersen
          I would guess that your DBA cleaned up some privileges
          • 2. Re: table dba_directories no exist
            499841
            Thank's for your answer, but my dba doesn't modify anything.

            I've studied the situation a little better, and I explain you:
            1. When the pl/sql procedure run (called by a asp procedure) have only one directory used to send a file
            2. I modify another asp procedure to call this pl/sql procedure too.
            3. I forget to create new dir for the second asp procedure :(
            4. I run the second asp procedure, that doesn't run
            5. I compile the pl/sql procedure again.

            Do you think that in those steps are some causes, that produce this error?

            Thank's a lot

            Paolo

            ps. sorry for my bad english
            • 3. Re: table dba_directories no exist
              499841
              I'm not a DBA,
              but I run the "desc dba_directories" from the user that cannot compile procedure, and the user can "see" the table.

              Where do I mistake!?

              Thank's
              Paolo
              • 4. Re: table dba_directories no exist
                499841
                Hi,
                I run this statment

                select dd.directory_name
                from dba_directories dd
                where rownum = 1

                and it's ok
                The difference is only this

                select dd.directory_name
                into directory_name
                from dba_directories dd
                where dd.directory_path = line
                and rownum = 1;

                But why?!?!


                This is all the procedure

                Thank's

                =====================================

                create or replace procedure P_SPEDEMAIATTA( from_name IN VARCHAR2,
                                    to_names IN VARCHAR2,
                                    subject     IN VARCHAR2,
                               message     IN VARCHAR2 default null,
                          html_message     IN VARCHAR2 default null,
                               cc_names     IN VARCHAR2 default null,
                          bcc_names     IN VARCHAR2 default null,
                          filename1 varchar2 default null,
                          filetype1 varchar2 default 'text/plain',
                               filename2 varchar2 default null,
                               filetype2 varchar2 default 'text/plain',
                               filename3 varchar2 default null,
                          filetype3 varchar2 default 'text/plain')
                is
                -- Change the SMTP host name and port number below to your own values,
                -- if not localhost on port 25:
                smtp_host varchar2(256) := 'SMTP.INET.IT';
                smtp_port number := 25;
                -- Change the boundary string, if needed, which demarcates boundaries of
                -- parts in a multi-part email, and should not appear inside the body of
                -- any part of the e-mail:
                boundary constant varchar2(256) := 'CES.Boundary.DACA587499938898';
                recipients varchar2(32767);
                directory_path varchar2(256);
                file_name varchar2(256);
                crlf varchar2(2):= chr(13) || chr(10);
                mesg varchar2(32767);
                conn UTL_SMTP.CONNECTION;
                type varchar2_table is table of varchar2(256) index by binary_integer;
                file_array varchar2_table;
                type_array varchar2_table;
                i binary_integer;
                my_code number;
                my_errm varchar2(32767);
                -- Function to return the next email address in the list of email addresses,
                -- separated by either a "," or a ";". From Oracle's demo_mail. The format
                -- of mailbox may be in one of these:
                -- someone@some-domain
                -- "Someone at some domain" <someone@some-domain>
                -- Someone at some domain <someone@some-domain>
                FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
                addr VARCHAR2(256);
                i pls_integer;
                FUNCTION lookup_unquoted_char(str IN VARCHAR2,
                chrs IN VARCHAR2) RETURN pls_integer IS
                c VARCHAR2(5);
                i pls_integer;
                len pls_integer;
                inside_quote BOOLEAN;
                BEGIN
                inside_quote := false;
                i := 1;
                len := length(str);
                WHILE (i <= len) LOOP
                c := substr(str, i, 1);
                IF (inside_quote) THEN
                IF (c = '"') THEN
                inside_quote := false;
                ELSIF (c = '\') THEN
                i := i + 1; -- Skip the quote character
                END IF;
                GOTO next_char;
                END IF;
                IF (c = '"') THEN
                inside_quote := true;
                GOTO next_char;
                END IF;
                IF (instr(chrs, c) >= 1) THEN
                RETURN i;
                END IF;
                <<next_char>>
                i := i + 1;
                END LOOP;
                RETURN 0;
                END;
                BEGIN
                addr_list := ltrim(addr_list);
                i := lookup_unquoted_char(addr_list, ',;');
                IF (i >= 1) THEN
                addr := substr(addr_list, 1, i - 1);
                addr_list := substr(addr_list, i + 1);
                ELSE
                addr := addr_list;
                addr_list := '';
                END IF;
                i := lookup_unquoted_char(addr, '<');
                IF (i >= 1) THEN
                addr := substr(addr, i + 1);
                i := instr(addr, '>');
                IF (i >= 1) THEN
                addr := substr(addr, 1, i - 1);
                END IF;
                END IF;
                i := lookup_unquoted_char(addr, '@');
                IF (i = 0 and smtp_host != 'localhost') THEN
                i := instr(smtp_host, '.', -1, 2);
                addr := addr || '@' || substr(smtp_host, i + 1);
                END IF;
                addr := '<' || addr || '>';
                RETURN addr;
                END;
                -- Procedure to split a file pathname into its directory path and file name
                -- components.
                PROCEDURE split_path_name(file_path IN VARCHAR2, directory_path OUT VARCHAR2,
                file_name OUT VARCHAR2) IS
                pos number;
                begin
                -- Separate the filename from the directory name
                pos := instr(file_path,'/',-1);
                if pos = 0 then
                pos := instr(file_path,'\',-1);
                end if;
                if pos = 0 then
                directory_path := null;
                else
                directory_path := substr(file_path,1,pos - 1);
                end if;
                file_name := substr(file_path,pos + 1);
                end;
                -- Procedure to append a file's contents to the e-mail
                PROCEDURE append_file(directory_path IN VARCHAR2, file_name IN VARCHAR2,
                file_type IN VARCHAR2, conn IN OUT UTL_SMTP.CONNECTION) IS
                generated_name varchar2(30) := 'CESDIR' || to_char(sysdate,'HH24MISS');
                directory_name varchar2(30);
                file_handle utl_file.file_type;
                bfile_handle bfile;
                bfile_len number;
                pos number;
                read_bytes number;
                line varchar2(1000);
                data raw(200);
                my_code number;
                my_errm varchar2(32767);
                begin
                begin
                -- Grant access to the directory, unless already defined, and open
                -- the file (as a bfile for a binary file, otherwise as a text file).
                begin
                line := directory_path;
                select dd.directory_name into directory_name from dba_directories dd
                where dd.directory_path = line and rownum = 1;

                exception
                when no_data_found then
                directory_name := generated_name;
                end;
                if directory_name = generated_name then
                execute immediate 'create or replace directory ' || directory_name ||
                ' as ''' || directory_path || '''';
                execute immediate 'grant read on directory ' || directory_name ||
                ' to public';
                end if;
                if substr(file_type,1,4) != 'text' then
                bfile_handle := bfilename(directory_name,file_name);
                bfile_len := dbms_lob.getlength(bfile_handle);
                pos := 1;
                dbms_lob.open(bfile_handle,dbms_lob.lob_readonly);
                else
                file_handle := utl_file.fopen(directory_name,file_name,'r');
                end if;
                -- Append the file contents to the end of the message
                loop
                -- If it is a binary file, process it 57 bytes at a time,
                -- reading them in with a LOB read, encoding them in BASE64,
                -- and writing out the encoded binary string as raw data
                if substr(file_type,1,4) != 'text' then
                if pos + 57 - 1 > bfile_len then
                read_bytes := bfile_len - pos + 1;
                else
                read_bytes := 57;
                end if;
                dbms_lob.read(bfile_handle,read_bytes,pos,data);
                utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data));
                pos := pos + 57;
                if pos > bfile_len then
                exit;
                end if;
                -- If it is a text file, get the next line of text, append a
                -- carriage return / line feed to it, and write it out
                else
                utl_file.get_line(file_handle,line);
                utl_smtp.write_data(conn,line || crlf);
                end if;
                end loop;
                -- Output any errors, except at end when no more data is found
                exception
                when no_data_found then
                null;
                when others then
                my_code := SQLCODE;
                my_errm := SQLERRM;
                dbms_output.put_line('Error code ' || my_code || ': ' ||
                my_errm);
                end;
                -- Close the file (binary or text)
                if substr(file_type,1,4) != 'text' then
                dbms_lob.close(bfile_handle);
                else
                utl_file.fclose(file_handle);
                end if;
                if directory_name = generated_name then
                execute immediate 'drop directory ' || directory_name;
                end if;
                end;
                begin
                -- Load the three filenames and file (mime) types into an array for
                -- easier handling later
                file_array(1) := filename1;
                file_array(2) := filename2;
                file_array(3) := filename3;
                type_array(1) := filetype1;
                type_array(2) := filetype2;
                type_array(3) := filetype3;
                -- Open the SMTP connection and set the From and To e-mail addresses
                conn := utl_smtp.open_connection(smtp_host,smtp_port);
                utl_smtp.helo(conn,smtp_host);
                recipients := from_name;
                utl_smtp.mail(conn,get_address(recipients));
                recipients := to_names;
                while recipients is not null loop
                utl_smtp.rcpt(conn,get_address(recipients));
                end loop;
                recipients := cc_names;
                while recipients is not null loop
                utl_smtp.rcpt(conn,get_address(recipients));
                end loop;
                recipients := bcc_names;
                while recipients is not null loop
                utl_smtp.rcpt(conn,get_address(recipients));
                end loop;
                utl_smtp.open_data(conn);
                -- Build the start of the mail message
                mesg := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') || crlf ||
                'From: ' || from_name || crlf ||
                'Subject: ' || subject || crlf ||
                'To: ' || to_names || crlf;
                if cc_names is not null then
                mesg := mesg || 'Cc: ' || cc_names || crlf;
                end if;
                if bcc_names is not null then
                mesg := mesg || 'Bcc: ' || bcc_names || crlf;
                end if;
                --
                -- Inserito da L.Algeri
                --
                if html_message is not null then
                mesg := mesg || 'Mime-Version: 1.0' || crlf ||
                'Content-Type: multipart/mixed; boundary="-1463811839-324931406-994342670=:16889"'||
                crlf || crlf ||' Per Bindi ----> detto anche er Maradona '
                || crlf || crlf||'---1463811839-324931406-994342670=:16889';
                mesg := mesg||crlf
                ||'Content-Type: text/html; charset=iso-8859-1'||crlf
                ||'Content-Transfer-Encoding: 7bit '||crlf||crlf
                ||html_message ||crlf||crlf||crlf||'---1463811839-324931406-994342670=:16889'||crlf||crlf;
                mesg := mesg||crlf || crlf|| crlf;
                utl_smtp.write_data(conn,mesg);
                end if;
                --
                --
                --
                /*
                mesg := mesg || 'Mime-Version: 1.0' || crlf ||
                'Content-Type: multipart/mixed; boundary="' || boundary || '"' ||
                crlf || crlf ||
                'This is a Mime message, which your current mail reader may not' || crlf ||
                'understand. Parts of the message will appear as text. If the remainder' || crlf ||
                'appears as random characters in the message body, instead of as' || crlf ||
                'attachments, then you''ll have to extract these parts and decode them' || crlf ||
                'manually.' || crlf || crlf;
                utl_smtp.write_data(conn,mesg);
                -- Write the text message or message file, if any
                if message is not null then
                mesg := '--' || boundary || crlf ||
                'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' ||
                crlf ||
                'Content-Disposition: inline; filename="message.txt"' || crlf ||
                'Content-Transfer-Encoding: 7bit' || crlf || crlf;
                utl_smtp.write_data(conn,mesg);
                if instr(message,'/') = 1 or instr(message,':\') = 2 or
                instr(message,'\\') = 1 then
                split_path_name(message,directory_path,file_name);
                append_file(directory_path,file_name,'text',conn);
                utl_smtp.write_data(conn,crlf);
                else
                utl_smtp.write_data(conn,message || crlf);
                end if;
                end if;
                -- Write the HTML message or message file, if any
                if html_message is not null then
                mesg := '--' || boundary || crlf ||
                'Content-Type: text/html; name="message.html"; charset=US-ASCII' ||
                crlf ||
                'Content-Disposition: inline; filename="message.html"' || crlf ||
                'Content-Transfer-Encoding: 7bit' || crlf || crlf;
                utl_smtp.write_data(conn,mesg);
                if instr(html_message,'/') = 1 or instr(html_message,':\') = 2 or
                instr(html_message,'\\') = 1 then
                split_path_name(html_message,directory_path,file_name);
                append_file(directory_path,file_name,'text',conn);
                utl_smtp.write_data(conn,crlf);
                else
                utl_smtp.write_data(conn,html_message || crlf);
                end if;
                end if;
                */
                -- Append the files
                for i in 1..3 loop
                -- If the filename has been supplied ...
                if file_array(i) is not null then
                split_path_name(file_array(i),directory_path,file_name);
                -- Generate the MIME boundary line according to the file (mime) type
                -- specified.
                -- mesg := crlf || '--' || boundary || crlf;
                mesg := crlf ||'---1463811839-324931406-994342670=:16889'|| crlf;
                if substr(type_array(i),1,4) != 'text' then
                mesg := mesg || 'Content-Type: ' || type_array(i) ||
                '; name="' || file_name || '"' || crlf ||
                'Content-Disposition: attachment; filename="' ||
                file_name || '"' || crlf ||
                'Content-Transfer-Encoding: base64' || crlf || crlf ;
                else
                mesg := mesg || 'Content-Type: application/octet-stream; name="' ||
                file_name || '"' || crlf ||
                'Content-Disposition: attachment; filename="' ||
                file_name || '"' || crlf ||
                'Content-Transfer-Encoding: 7bit' || crlf || crlf ;
                end if;
                utl_smtp.write_data(conn,mesg);
                -- Append the file contents to the end of the message
                append_file(directory_path,file_name,type_array(i),conn);
                utl_smtp.write_data(conn,crlf);
                end if;
                end loop;
                -- Append the final boundary line
                -- mesg := crlf || '--' || boundary || '--' || crlf;
                mesg := crlf ||'---1463811839-324931406-994342670=:16889'|| crlf;
                utl_smtp.write_data(conn,mesg);
                -- Close the SMTP connection
                utl_smtp.close_data(conn);
                utl_smtp.quit(conn);
                exception
                when utl_smtp.transient_error or utl_smtp.permanent_error then
                my_code := SQLCODE;
                my_errm := SQLERRM;
                begin
                utl_smtp.quit(conn);
                exception
                when utl_smtp.transient_error or utl_smtp.permanent_error then
                null;
                end;
                raise_application_error(-20000,
                'Failed to send mail - SMTP server down or unavailable: Error code ' ||
                my_code || ': ' || my_errm);
                when others then
                my_code := SQLCODE;
                my_errm := SQLERRM;
                raise_application_error(-20000,
                'Failed to send mail: Error code ' || my_code || ': ' || my_errm);
                end;
                • 5. Re: table dba_directories no exist
                  JensPetersen
                  but I run the "desc dba_directories" from the user that cannot compile procedure, and the user can "see" the table.
                  I still think that there must have been some changes with the privileges.
                  Make sure that the select privilege on dba_privilege has been granted directly and not through a role
                  http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html