SQL & PL/SQL :: Get Result Query Of Three Columns And Pass Into Send-mail Proc?
May 10, 2011
I had never done this before, basically I need to get the result of the SQL query which had three columns and pass into thesend _mail proc, should I build a variable to hold the 3 value and send mail from it?
below is my send_mail proc
Quote:
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
[code]...
and below is my sql query
Quote:
select a.id, b.hr_info, c.details
from contact a, employees b, grid c
where a.id=b.id
and b.hc=c.hc
View 1 Replies
ADVERTISEMENT
Jul 28, 2012
I m using UTL_SMTP to send mail using my mail server. could i check the availability of 1st mail server, if not available then could i set alternate mail server.
View 7 Replies
View Related
May 25, 2013
11.2.0.3 got the send mail code from [URL]
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
[Code].....
/I want to call send_mail to pass a query output to "p_message": as example: select sid, program,module,machine from v$session where program='XXX';
And have been struggling due to lack of experience in PLSQL.
View 18 Replies
View Related
Apr 26, 2010
I want to pass Number of columns dynamically to a query. I got success in SQL.
SQL> select &column_list from emp;
Enter value for column_list: empno,ename,sal
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
But the same i need to achieve in pl/sql. I try with the Ref cursor, but not succeeded.
View 15 Replies
View Related
Feb 23, 2009
I want to send mail using plsql.I know that this can be possible by UTL_SMTP package.So I went thro some doc's & I tried this code :
PROCEDURE send_test_message
IS
mailhost VARCHAR2(64) := 'mailhost.fictional-domain.com'; /*(my computer name)*/
sender VARCHAR2(64) := 'prasadsug@yahoo.com';
recipient VARCHAR2(64) := 'prasadsug@rediffmail.com';
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
[code]....
I got 'ora-00900:INVALID SQL STATEMENT'. which line i've done the mistake .how to rectify that?.
View 5 Replies
View Related
Aug 6, 2013
I have a requirement how to send a mail from pl/sql procedure and that mail content will be in HTML format and the data i have to fetch from one table
View 2 Replies
View Related
Jan 24, 2011
I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
View 1 Replies
View Related
Feb 17, 2012
how can i send sms and mail through d2k 6i form?
View 14 Replies
View Related
Jul 26, 2011
I had a question :
How to send mail from Oracle ??
Implement utl_mail??
How to implement ?? , or have any examples for reference??
View 6 Replies
View Related
Feb 1, 2012
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
View 1 Replies
View Related
Feb 20, 2011
how to create trigger or procedure for send all ORA- error alert mail from database. if any ORA - error comes in ALERT_SID.log then after trigger send the mail to my mail id
View 4 Replies
View Related
Jun 7, 2010
How to send mail with attachment ?
View 2 Replies
View Related
Jun 21, 2013
i want to send mail from database for this
C:UsersAdministrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 21 16:03:04 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys@orcl as sysdba
Enter password:
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> @ F:oracleproduct10.2.0db_1RDBMSADMINutlmail.sql
[code]....
View 17 Replies
View Related
Sep 24, 2010
i want to send mail with xml attachment.i create a procedure to send mail, when i send pdf,text file, they are open properly but if i send xml file i does not open...
/* Formatted on 2010/09/24 15:25 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.send_mail
AS
p_sender VARCHAR2 (80)
:= 'my@company.com';
p_recipient VARCHAR2 (80)
[code]....
View 1 Replies
View Related
Mar 6, 2013
I am using utl_smtp to send emails. I am getting the below error message.
Error in XX(Package name) ORA-29279: SMTP permanent error: 501 5.5.2 Syntax error in parameters scanning "TO" at - 5150
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 5200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 5200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 7200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 6000
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 7250
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 8450.
I have checked the mail id in the "TO" list and found them to be valid. Is there could be any other reason? I have run this package in test instance and it is working fine. I am able to receive mails. In the test instance, I have put my mail id in the to list.
View 1 Replies
View Related
Mar 15, 2011
Send a CSV file as attachment in a mail PLSQL. MY CSV file is already present in server. I just need to pickup that file and send the same as mail attachment.
View 1 Replies
View Related
Jan 9, 2012
give procedure that sends mail as an attachment. I have a query.. It should send the output as a xls sheet in the mail.
View 1 Replies
View Related
Mar 23, 2012
I want to send the mail as HTML format in oracle.
The email message column is in clob datatype .
if i click the column it shows the text output(various tags ,symbols)
if i click the HTML it shows the correct format .,
i m sending the mail using that message column .,the mail Received with the text format ,actually i need it in the html format., How to send HTML output?
View 4 Replies
View Related
Jul 21, 2011
How to send reports from d2k form to mail.
View 1 Replies
View Related
Apr 5, 2011
I can send mail with UTL_MAIL using this:
CREATE OR REPLACE PROCEDURE send_mail_file_seti IS
BEGIN
UTL_MAIL.SEND_ATTACH_VARCHAR2(
sender => 'mail@from.com',
recipients => 'mail@to.com',
message => '<HTML><BODY>See attachment</BODY></HTML>',
[code]........
What I want to do is send mail attaching a file from file system. For example, I have this file:
/oracle/pmp/file
I want to send it using a procedure like the one above.
View 6 Replies
View Related
Sep 14, 2010
I 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.
View 1 Replies
View Related
Feb 25, 2013
I am able to send mail to Gmail from APEX. How an attachment can be send along with it.
View 6 Replies
View Related
Jun 15, 2012
what document I should look for to pass a cursor generated by PL/SQL to java script code in APEX?
View 15 Replies
View Related
Feb 7, 2011
Query :"how to send mail to lotus notes with attachment from oracle forms"?
View 2 Replies
View Related
Aug 15, 2013
We want to send the following XML message as IN parameter to the procedure
<?xml version="1.0" encoding="UTF-8" ?>
- <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
- <s:Body>
- <CalculateWEDBCached xmlns="http://wedb.GGG.Services/Contracts/">
- <skuItems xmlns:a="http://schemas.datacontract.org/2004/07/wedb.GGG.Services.Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
- <a:SkuItem>
[code].......
I need a procedure to get the records from the table for every SKU of XML message from the table and take one record for each SKU based on order precedence.And send the set of output records to the .net code. The order precedence is
If MODEL is not null that record should be high precedence and consider max lead_time
If MODEL and VK_UNIT is NULL then the precedence goes to STATE_ID.
The parameters in the procedure are
CREATE OR REPLACE PROCEDURE sku_proc
(
p_bu_id IN number,
p_model IN varchar2,
p_vk_unit IN XMLTYPE,
p_country_id IN varchar2,
[code].......
Query to get the data from the table based on the parameters.
SELECT bu_id,
model,
vk_unit,
country_id,
state_id,
Lead_Time,
[code].......
Here every SKU from XML message should be passed as p_vk_unit to VK_UNIT column in the query
210-39348
210-39348
210-39348
405-12132
340-30904
340-30904
340-30904
403-10890
403-10890
709-10007
Based on the above query assume we got the following records.
BU_ID MODEL VK_UNIT COUNTRY_ID STATE_ID LEAD_TIME FC
0 M123 210-39348 AB 0 20 A
0 M123 210-39348 AB 0 30 B
0 NULL 210-39348 AB 0 10 C
0 M123 405-12132 AB 0 10 A
0 NULL 340-30904 AB 0 30 C
0 M123 340-30904 AB 0 20 B
0 M123 340-30904 AB 0 10 A
0 NULL 403-10890 AB 0 10 B
0 M123 403-10890 AB 0 20 A
0 M123 709-10007 AB 0 10 B
0 NULL NULL AB 0 20 B
0 NULL NULL AB 1 30 A
Th final query has to return the following result to the OUT parameters p_Lead_Time p_FC of the procedure.
LEAD_TIME FC
30 B
10 A
20 B
20 A
10 B
30 A
How to implement my above requirement using BULK COLLECT.
View 11 Replies
View Related
Aug 16, 2010
I am facing with one problem while creation of function.
CREATE OR REPLACE FUNCTION fun1(
v_finyear VARCHAR2,
v_yrno NUMBER,
[Code].....
The function created successfully...
But it return the wrong values, like
SQL> SELECT fun1('2004-05', 8, 'FEB')
2 FROM DUAL
3 ;
TB('2004-05',8,'FEB')
--------------------------------------------------------------------------------
FEB
SQL> spool off;
2004-05 is the value of TABLE1
08 is also value of TABLE1
FEB is the Column_name of TABLE1
Actually, i have a value in FEB month, but it return FEB. FEB is the column_name of 'TABLE1'
View 1 Replies
View Related
Feb 24, 2012
I am trying to perform a dml operation(insert) to insert data into a table. I created a procedure in which a insert statement is generated. In another procedure I am trying to execute this auto generated statement using execute immediate. For that I created 2 variables . First one contain the insert statement and other one contain the list of columns. I m passing these strings to execute immediate like this
Execute Immediate(v_query) using(v_col_list)
after execution i m getting the following error
ORA-01008: not all variables bound
ORA-06512": at ' db_name.load_data' , line no 217
but when i tried it with list of column into using clause it is working like
Execute Immediate(v_query) using col1,col2,col3
I have more than 150 tables in the schema and i am creating a single procedure to load the data into base tables by using external tables. By passing the table name to procedure it generates the update and insert statements but i am getting error while executing the statements with dynamic sql.
View 11 Replies
View Related
Aug 29, 2011
I have a scenario to frame an xml as below, below i have given a sample data
Create table xml_type
(msg varchar2(1000),
desp varchar2(1000),
val number) ;
[Code]....
Actually i need the output as below, i tried and succeeded doing it in Procedure , but they are asking me to do it in SQL query. How to get from an sql query.
<DETAIL>
<ERROR> 'Invalid Name ID' </ERROR>
<ID> 'Name ID' </ID>
<VALUE> 6 </VALUE>
<VALUE> 7 </VALUE>
<VALUE> 8 </VALUE>
</DETAIL>
[Code]...
Or i was planning to get the values as below with two columns and pass it to informatica to frame the XML.
Col1 Col2
Invalid Name Id Error
Name ID ID
6 Value
7 Value
8 Value
Invalid FAQ ID Error
FAQ Id ID
9 Value
10 Value
Let me know the best option and how to do this task.
View 1 Replies
View Related
Oct 29, 2013
How can i retrieve result of multiple rows into single row. Ex
select ename from emp;
Ename:
--------
ALLEN
WARD
JONES
MARTIN
BLAKE
[Code]...
Result should be like
ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
View 8 Replies
View Related
May 4, 2010
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
View 8 Replies
View Related
