OS: RHEL 5.7 64 bit
DB: 11.2.0.2 Standard Edition 64 bit
Everyday EOD is run and after the eod, users are requesting to receive a mail confirming the same from the database. For this we need to configure automated email which will be sent to a list of users email ID immediately after the EOD is done.
Am facing a problem in sending an email to certain group of people, if the recipient just one person it will work but if more it will not work, I tried to read every one as a single recipient through a cursor but it was not working, the following is the sending
v_mail=:control.test;
mail_pkg.send
( p_sender_email => 'test@test.com',
p_from => 'test@test.com',
p_to => mail_pkg.array(V_MAIL),
[code].......
:control.test data is quotes between to quotes for each address.
we are running Oracle R12.1.3 on DB version 11.2.0.3. I just migrated a created a custom subscription for the oracle.apps.ap.supplier.event Oracle Event. I execute a custom package when this event fires. My package is working fine and I'm getting the expected results. My problem is I keep getting a Workflow notification saying:
Sent: Monday, March 04, 2013 9:14 PM
To: SYSADMIN
Subject: Action Required: Local Event UNEXPECTED : oracle.apps.ap.supplier.event / 36422
To SYSADMIN
Sent 04-MAR-2013 21:13:40
ID 1659929
An Error occurred in the following Event Subscription: Event Subscription
Event Error Name:
Event Error Message: No Event Subscriptions exist for this Event
Event Error Stack:
Event Data: Event Data URL
Event Details
Event Field Value
[code]...
I saw an earlier post about this and have tried changing the Source Type to "External" but that didn't change anything. why Workflow is telling me a subscription doesn't exist when my subscription is executing with no problems.
I 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 RelatedI have to send several reports to various branch via email, I created a branch table in which email of that branch is stored in a column. Now if report is generated for particular branch, its PDF should also be sent via email to that branch, How can this be done on When Button Pressed,
View 1 Replies View RelatedIs there a way to query an oracle database in an automated fashion by a timestamp field based on current timestamp, like: 04/29/08 00:00:00 - 72 hours?
View 3 Replies View Related Is it possible for Access to extract data from an Oracle database and upload it directly?
Currently we have a business process where data is being extracted in scheduled queries (30+) to Excel spreadsheets, then manually edited to remove heading lines and imported to an Access database. I see an opportunity to automate a time consuming manual activity by having the Access db extract the data and directly upload it.
Provide automated shell script for renaming oracle DB 10gR2. Now it is a daily manual job for us.
View 4 Replies View RelatedI have a Database of My all customers Mobile Nos I want to send SMS to all customer from Oracle Database using Pl/SQL or any inbuilt Oracle Package, if available or through Oracle forms
How i can send SMS to my Customers through Oracle PL/SQL or Oracle forms or any other front end and what are the additional requirement in sending SMS and what utilities are needed .
i'm trying to send Arabic sms to mobile but the the message come in this way "?
View 29 Replies View Relatedi am able to send text/ csv attachment thru oracle PL/Sql Procedure. But i am facing this for excel attachment, some junk character is coming while opening that attachment excel file.
i am attaching here with my coding for your view. Anything i have to configure at my SMTP server.
Below code working fine when i am using Forms6i. But i want to send a mail by Forms10g.
declare
objOutlook OLE2.OBJ_TYPE;
objMail OLE2.OBJ_TYPE;
objArg OLE2.LIST_TYPE;
objAttach OLE2.OBJ_TYPE;
BEGIN
objOutlook := OLE2.CREATE_OBJ('Outlook.Application');
-- Previous example usually used 'mapi.session' but this doesn't work correctly
--anymore.
objarg := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(objarg,0);
objMail := OLE2.INVOKE_OBJ(objOutlook,'CreateItem', objarg);
OLE2.DESTROY_ARGLIST(objarg);
objAttach := OLE2.GET_OBJ_PROPERTY(objmail, 'Attachments');
[code].........
Our scheduled job is created using a chain of programs. Below is the flow.start the
job-->prg1-->COMPLEDED-->prg2--COMPLETED-->prg3-->COMPLETED-->prg4-->COMPLETED-->finish
the job I want to get the email notification(error messge also), when any of the steps in the chain fails in the job.I have tried this scenario, but I am not getting the error message if a program in the chain fails.
I 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.
I the past when working with apex 3 I was able to work with apex mail send very nice. In apex 4.0 with oracle 11g ee I'm not able to send emails anymore.
Last error message in the queue is:
ORA-29279: SMTP permanent error: 550 through this server without authentication.
the smtp server has not been changed, the code is fine. How I can go further?
Need pl/sql block to fetch all the emails of all the employees from the database and separate them by semicolon....
View 4 Replies View RelatedI've written a pl/sql to send mail. It works fine. Now i want to modify it to send an attachment txt/scv/xls file.This file is residing on client's pc.How to do it?
Following is pl/sql for sending mail.
create or replace PROCEDURE SEND_MAIL(P_SENDER IN VARCHAR2,P_RECIPIENT IN VARCHAR2,P_MESSAGE IN VARCHAR2,P_SUBJECT IN VARCHAR2 )IS
MAILHOST VARCHAR2(30) := 'MAILHOST.TIFR.RES.IN';
MAIL_CONN UTL_SMTP.CONNECTION;
BEGIN
MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(MAILHOST, 25); -- TYPICALY 25 S THE PORT NUMBER FOR INCOMMING SMTP REQUESTS ON YOUR SMTP MAIL HOST
UTL_SMTP.MAIL(MAIL_CONN, '<'||P_SENDER||'>'); -- THE '<' AND '>' DEPEDS ON WHAT TYPE OF SMTP MAIL HOST U'RE RUNNING THIS ONE'S A NETSCAPE SMTPHOST
[code]......
On a test instance with Oracle 11gR2 with Enterprise Manager Database Control I try to activate the Email Notifications in Enterprise Manager, so that I can use them for monitoring.
So i configured following settings:
Under Setup -> Notification method I gave him a valid mail address with a vaild SMTP server. The test mail was send successfully.
Under Preferences -> General i gave SYS a valid mail address to receive. The execution plan i let in standard configuration, so every time at everyday i will get an email, when something happens. the test mail was send successfully too.
Now i created an own rule to test the configuration:
I went to Preferences -> Rules an add an rule. Name and description I named "test" and set "database instance". At availability I activated all checkboxed, so i should be informed when the instance goes down, comes up, getting an error, when the error is fixed, when agent is not available, when agent is available again, when there is a metric error, when the emetic error is fixed, when a blackout starts and when it ends.
Metrics, policies and jobs there wasn't defined anything in standard, so i let it so and set nothing here. I activate the checkbox for sending emails to sys and sending repeat emails every 15 minutes 3 times at all.
Now, to test this notification i logged into my testserver and killed the listener process, to see, what will happened. After 1 Hour waiting i didn't get any email, so i restarted the listener. after the listener was started my outlook was fluted by emails from the server, that the listener was down. But why i didn't get them earlier.
I checked the procedure with the database to, here i get it down by "shutdown abort" and "shutdown immediate". But i get the mails, that the database is down, when its up already. And thats to late!
When I want to use such email notifications for productive servers and someting is going wrong, for that a notifications is configured of course, I want to get the mail directly, when that happened and not, hen the problem is fixed!
For example, here an email from testing, where the listener was down, that i've get at 13:01 :
Target Name=LISTENER_<hostname>
Target Type=Listener
Host=<hostname>
Metric=Status
[code].........
I am quiet confused with the optimizer collection stats job on 11g. when run the following query i see the statistics enabled.
SQL> select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
I can gather statistics manually using DBMS_STATS, but there is no automated gathering of optimizer stats. how can i have system run and collect statistics on a daily bases?
I have a form based on a table where i have 2 keys as primary key (col1,col2)==>composite pk. sample values are like:
col1 col2
1 21
2 21
62 21
62 1
But issue is at the point where the automatic row fetching is getting executed [Automated Row Processing section on edit page] Primary column value is col1(only one column is allowed in apex Auto row fetch) Secondary key column is col2.
For ex, when i access the row for col1=62, it fetches 2 rows where value=62 (based on col1) and i get error; (exact fetch returns more than 1 row)
How to avoid this
The manual work around on populating child tables for testing purpose are taking long time and its very painful work. So I am trying for a tool that takes parent table name and child table name as input and produce insert statements for child table with foreign keys as output.
View 5 Replies View RelatedI am trying to find out if there is a definite way to find out (by querying database) which database users have been created by Oracle (either during installation or as part of patching or adding new feature) and which database users have been created by DBAs.
I have looked into the documentation but could not find anything relevant. Ideally, I would like to know if this can be done for any versions starting from 9iR2.
i have a table with 15 coulums and containing millions of rows which is being updated everyday.Now i have created a similar report table with only the coulums i need to report on from the main table.what plsql script or if there is any better alternative do i need to write to copy the data from the coulums i need from the main table to the new report table. the new report table will be be updated every 01:00am with the data coming from the main table and the update is automated.
View 3 Replies View RelatedI'm working on a client-server application that uses ODBC to connect Windows XP or Windows 7 PCs to an Oracle 11 database. I use a dedicated account to connect to Oracle, and I get the users Windows login name using the call shown in the title. I use the Windows login name to verify that they can access my application. I've been asked by security conscious folks in our IT department how this information is obtained
My question is: where does Oracle get the value of the users Windows login name? Is it coming from a windows registry key? Environment variable? I heard that it's possible that the ODBC driver is calling a Microsoft Win32 function like GetUserName or GetUserNameEx to retrieve the user ID.
During installation of a database Oracle creates several users. Also when using DBCA in a later stage to add more options Oracle can create more users.
Here's my problem: Public synonyms never get exported using exp(dp)/imp(dp) utility. Ofcourse using public synonyms isn't good practise, but sometimes handy. Unfortunately in our environment they have been used a lot in the passed.
So I want to create an SQL statement that does the trick and returns only all users that were NOT created by Oracle software.
I've tried several things, but can't seem to distinguish Oracle users from Manually created users.
I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected.But when I ran below command to know sessions connected I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test';
no rows selected
I have a form on a table that allows me to edit, delete, or create records. If I choose to edit a record from the report the form get populated correctly and edits or deletes work fine. My problem is when I choose to create a new record. I get a blank form which is what I want, but once I fill it out and click the create button, the automated row fetch fires again and gives me a no data found error. How do I get it to not fire when the create button is pressed?
View 2 Replies View Relatedhow many default users available in oracle 9i version.
View 4 Replies View Relatedit is a good practise to assign different profile for oracle database users according to their job functions.what could be the resons for that?
View 3 Replies View RelatedI am looking for a query which can select all existing Oracle predefined users and roles from db.I need it because one of my other scripts needs to know a given user name or role is a customised one or a system one.It looks like sys.user$ does not have any column that can be used to distinguish predefined users/roles.
I could find all the list of Oracle predefined users and roles by googling and hardcoded them in a query. I can also collect all the customised users and roles as well. However, I prefer a query which can automatically reports that and minimise the maintenance cost as new names and roles, either Oracle predefined or customised, may come out at anytime.