SQL & PL/SQL :: Missing UTL_SMTP Package Body?
Mar 5, 2012I tried to create a UTL_SMTP package using '@?/rdbms/admin/utlmtp.sql' script but there are no package body for UTL_SMTP is created. There are only UTL_SMTP package is create.
View 4 Replies
ADVERTISEMENT
SQL & PL/SQL :: UTL_SMTP.DATA / Why Body Of Email Is Missing
Jul 23, 2012I have a procedure called SANDEEP_INC105657, which calls another procedure called "Send_Prodcontrol_Mail_Pr". This procedure should be sending out an email with the following message:
From: <sandeep.tanjore@xxx.xx.ca>
Subject: Remit Advice Slips Report (TR000336).
To: sandeep.tanjore@xxx.xx.ca
Report TR000336 - Remit Advice Slips.
Total number of records that needs slips printed are: 1
However what's happening is we had a database change from 10g to 11g and ever since this has happened the email is sent with "From", "Subject" and "To" but the body(content of the email : Total number of records that needs slips printed are: 1) is missing.The code in Send_prodcontrol_Mail_pr is as follows:
CREATE OR REPLACE PROCEDURE Send_Prodcontrol_Mail_Pr( v_mailhost IN VARCHAR2,
v_sender IN VARCHAR2,
v_recipient IN VARCHAR2,
v_subject IN VARCHAR2,
v_message1 IN VARCHAR2,
v_message2 IN VARCHAR2,
v_message3 IN VARCHAR2)
[code]....
Any reason why the body of the email is missing? I tried the following:
replaced utl_smtp.data(mail_conn,mesg); with utl_smtp.write_data(mail_conn, utl_tcp.CRLF||mesg);
It sends out the email as required but it repeats the "From" two times with no "TO", and "Subject"... however then embeds the whole "from" "to" and the body in an email.
SQL & PL/SQL :: Multiple Recipients Using UTL_SMTP Package?
Feb 17, 2004how to send an email to multiple recipients. Is there a delimeter that i have to use to separate the email addresses, or do i have to do the utl_smtp.rcpt(c,email) mulitple number of times.
View 9 Replies View RelatedSQL & PL/SQL :: Attach External File Using UTL_SMTP Package?
Feb 7, 2012I want to attache an external file to a email using UTL_SMTP package.
A file which attach will be present in directory.
PL/SQL :: Creating A Package Body
Jan 6, 2013I'm trying to create a package body but i keep getting an error message saying package created with complication.
CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
PROCEDURE MY_PROC(A NUMBER, B NUMBER)
IS
C NUMBER(4);
BEGIN
C:=A+B;
[code].......
SQL & PL/SQL :: Compiling Package BODY Causes Invalidation?
Aug 25, 2010Up until today we assumed that compiling a package BODY-only would not cause invalidation of other code. However when we compiled the body of the HTP package, the OWA package became invalid and required re-compilation.
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL & PL/SQL :: How To See The Contents Of Wrapped Package Body
Mar 31, 2011I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.
View 1 Replies View RelatedSQL & PL/SQL :: Error While Compiling Package Body?
Jun 28, 2011When i compile the package body i get errors.
CREATE OR REPLACE PACKAGE BODY CEE_OSPCM_PKG AS
PROCEDURE CEEOT_WORK_TYPE_PRC IS
CURSOR CUR_WORK_TYPE IS
SELECT *
[code]...
SQL & PL/SQL :: How To Export Package Body Using Spool Command
Jan 8, 2013I am trying to Spool a Package Body into a file using sqlplus. I tried 2 days:
WAY1
SET LINESIZE 32000;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET ECHO OFF;
SET TERM OFF;
SET FEEDBACK OFF;
[code]....
WAY2
.....
SELECT dbms_metadata.get_ddl( 'PACKAGE', 'SPI_xxxx_PCK', 'owner') FROM dual;
......
In Way1, I got different DDL or partial DDL. In Way2, I got only first 2 lines of Package body. I am running this command from Schema user only.
Forms :: Error While Compiling Package Body
Sep 3, 2010while compiling this package body, i get error
error 102 at line 21 column 11
encounterd symbol "Group" when expecting one of following
begin function package pragma procedure subtype type use form cursor
PL/SQL :: Grant Command To View Package Body
Aug 3, 2012I sent a request for the DBA's to grant me rights to the package body but I only see the spec with the grant execute command.
Here is the command I just sent:
grant create any procedure to DEVELOPER1;
But the DBA changed my grant command to:
grant create procedure to DEVELOPER1;
But I still cannot see the package body. This is on a test environment.
Would the "create any procedure" command see the package body of different schema owners package bodies?
Or is there another grant command to see different schema package bodies?
Client Tools :: How To See Specs / Body Of Package Procedures
Jan 27, 20111- I do not have access to TOAD for Oracle yet
2- I can connect to SQLPLUS: Release 9.2.0.1.0
3- We have many stored procedures in packages that are available from the TOAD for Oracle interface
4- I need to be able to see the specs/body of some packages containing some procedures.
5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?
For example: SchemaName1.PackageName1.ProcedureName1
SQL & PL/SQL :: Package Body Created With Compilation Errors (Warnings On)
Aug 6, 2013I've compiled a package with warnings ON. Getting message SP2-0809: Package Body Created with Compilation Errors.When I checked the errors by issuing "Show Errors", it shows "No error".
SQL> ALTER PACKAGE PKG_ABC COMPILE PLSQL_WARNINGS = 'ENABLE:ALL';
SP2-0809: Package Body Created with Compilation Errors
SQL> SHOW ERRORS
No Errors
how to check errors for that, or oracle simply pointing that package might have performance issue.
SQL & PL/SQL :: How To Declare Global Variable In Package Body (Not Spec)
Feb 26, 2013I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
View 11 Replies View RelatedSQL & PL/SQL :: Hide Declaration Or Declare It Inside The Body Of Package
Mar 11, 2010I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS
type table_name_i is varray(100) of VARCHAR2(30);
c_add constant number := 1;
c_del constant number := 2;
c_select_frm_tmp constant number := 1;
[code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
Server Utilities :: ORA-04063 - Package Body Has Errors
Jan 30, 2013I am getting the below error when i am trying to import the dmp file into my oracle 11g.
impdp system/password@orcl dumpfile=aaa.dmp directory=datapump remap_schema=dev:user
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_
LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 949
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"
SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body
Jul 26, 2011CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
SQL & PL/SQL :: Create Or Replace - Package Body Created With Compilation Errors
Mar 8, 2012CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection
AS
PROCEDURE sp_final_load_mkt_hub;
END pkg_mkt_hub_load_collection;
/
CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS
c_default_limit CONSTANT PLS_INTEGER:=5000;
[code]....
show error
error code
SQL> @pkg_mkt_hub_load_collection.sql
Package created.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
57/4 PL/SQL: Statement ignored
PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'
SQL>
Server Utilities :: Impdp - Package Body Import Taking Huge Time?
Sep 13, 2012I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).
I have tried both parallel and non-parallel.how to improve speed of the package body import.
Details about the Schema & Import No. of objects in Schema
SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);
OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1
ORA-00933: SQL command not properly ended
SQL & PL/SQL :: Dynamic Statement In Database Package - Missing Expression
Feb 29, 2012I have the following statement dynamic sql statement in a database package.
begin
EXECUTE IMMEDIATE 'SELECT REPLACE(SUBSTR('||V_WHERE_CLAUSE||',1,3, ''AND'', NULL) INTO V_WHERE_FILTER FROM DUAL';
EXCEPTION
NULL;
end;
When executed it gives following error.
ORA-20000: ORA-20000: ORA-00936: missing expression
### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 931
### END TRACE ###
### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 935
ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 164
### END TRACE ###
ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 184
ORA-06512: at line 9
SQL & PL/SQL :: User Can't Use Utl_smtp?
Dec 29, 2011user can't use utl_smtp. I tried - [URL] can use utl_smtp but other user can't.
I selected:
SELECT NVL(dbms_network_acl_admin.check_privilege_aclid(aclid, 'user123', 'connect'), 0)
FROM xds_acl;
And the result is:
1
0
0
0
0
0
0
0
0
0
0
0
SQL & PL/SQL :: Using UTL_SMTP To Send Simple Emails
Aug 2, 2006I tried using UTL_SMTP to send simple emails. My code is as follows:-
PROCEDURE prc_send
IS
- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the contents of the email message
v_smtp_host VARCHAR2(100) DEFAULT 'smtp.server.com';
-- variable to hold the smtp port
v_smtp_port NUMBER DEFAULT 25;
BEGIN
-- establish the connection to the smtp server
[code].........
When I execute this, It shows authentication error. Error looks like ORA-29278: SMTP transient error: 454 5.7.3 Client was not authenticated. I know my smtp server requires password authentication. But I am not aware of how to do it with UTL_SMTP.
SQL & PL/SQL :: Send Email Using UTL_SMTP With UTF-8 Coding
Dec 30, 2011I'm trying to send e-mail using UTL_SMTP with UTF-8 coding. I have a problem that the UTF-8 letters become `?`.
So here is the
p_to varchar2(250);
p_from varchar2(250):='email_address';
p_subject varchar2(250):='...vienotā...';
P_smtp_hostname varchar2(255) := 'IP';
P_smtp_portnum varchar2(5) := '25';
[code].......
The code is taken an example from internet source. Everything works fine but the UTF-8 not .
SQL & PL/SQL :: Send Emails From Oracle Using Both UTL_MAIL As Well As UTL_SMTP
Oct 9, 2013I developed a program to send e-mails from Oracle using both UTL_MAIL as well as UTL_SMTP. My requirement now is to send around 75,000 emails per day, When I tested for 1000 emails it is taking around 6 mins., Then for 75,000 it would be around 7-8 hours. Not to put the server busy for these many hours. Can we really send this much of e-mails from Oracle? UTL_MAIL is little faster than UTL_SMTP)
View 12 Replies View RelatedPL/SQL :: Adding Image As Attachment To Email UTL_SMTP?
Jun 21, 2013I am trying to add an image as attachment to an e-mail.
I am reading the image from the table and then passing it as a BLOB in my procedure.
Following is the part of the code which adds the attachment
{code}
PROCEDURE add_mail_attachment
(
/*
[Code].....
SQL & PL/SQL :: Sending Mail Through UTL_SMTP / Unable To Send CLOB In It
Sep 14, 2010I am sending mail thru UTL_SMTP. But i am unable to send the CLOB in it.If i break the CLOB into varchar2 then also it doesn't work.
Variables:-
vBuffer VARCHAR2 (4000);
l_amount BINARY_INTEGER := 4000;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
[code].....
While using the write_data procedure it is giving error as "Invalid OPeration"...how we can send the mail with CLOB.
SQL & PL/SQL :: Sending UTL_SMTP Mail With JPG Picture As Header Inside Email?
Oct 7, 2013I have to embed a picture as a header with an e-mail content for my work, I google'd and tested with different options but I failed. (The JPG is stored in a table as a blog object) When I tried with the below code the JPG file is e-mailed as an attachment with an e-mail content, I want it to be displayed inside an e-mail.
I tried att_inline: TRUE option as well - still not working.
Here is my code I tested and worked as an attachment.
DECLARE
/*LOB operation related varriables */
v_src_loc BLOB :=EMPTY_BLOB;
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
[code]....
PL/SQL :: Find Out In Database Package Where Package Is Installed
May 28, 2013I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
SQL & PL/SQL :: Global Exception In Package Available Outside Package?
Jan 15, 2012I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
SQL & PL/SQL :: ORA-29266 - End-of-body Reached
Apr 11, 2010I am getting the error (ORA-29266: end-of-body reached) at utl_http.read_text. below is a code
create or replace procedure sendMail(p_fromaddress IN VARCHAR2, -- Optional
p_toaddress IN VARCHAR2,
p_bcc IN VARCHAR2, -- Optional
p_cc IN VARCHAR2, -- Optional
p_subject IN VARCHAR2,
[code]....