PL/SQL :: Best Way To Send A Procedure / Function Output To The Application

Sep 8, 2013

What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc. 

View 49 Replies


ADVERTISEMENT

Application Express :: How To Do Selectable Filtering Of Output From Table-function

Jun 26, 2013

I have a report which comes from a table-function.  This produces hundreds of lines of output which the user would like to be able to select and then export just the subset of lines.Looking at the Forum's (this article and this one) this seems possible if you are working with a table where you can re-query the original dataset with just a specific rowset required however as the table-function is generating output on the fly it is hard to rerun the query to reselect the same output for redisplay.  Also most of the reports are working with interactive reports - whilst this is just static output. Is there a way to redisplay the information which has already been shown in an filtered way with check boxes?Is there then a way to have hidden fields which are not shown when the check boxes are selected but which can be shown in the "filtered" view? I am using Apex 4.0 at the moment on an Oracle 10g instance.

View 0 Replies View Related

SQL & PL/SQL :: Send Output As Xls Sheet In Mail

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

SQL & PL/SQL :: Send Again Function Of Equivalent Of DATEADD

Apr 19, 2011

Is it possible to send again the function of equivalent of DATEADD in sql for plsql??

View 3 Replies View Related

SQL & PL/SQL :: Set / Send / Input CLOB Value To Function?

Jan 19, 2012

Trying to set/send/input CLOB value to function.

I tried:

Clob size is about 4-5KB of data.

DECLARE clobz CLOB:=EMPTY_CLOB();
BEGIN
SELECT '<claim>'+
'<lbo_id>34</lbo_id>'+
'<claim_type_id>11</claim_type_id>'+
'<claim_subtype_id>11</claim_subtype_id>'+

[code]...

Error:
Error report:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete

[code]...

View 18 Replies View Related

PL/SQL :: Send Regexp Back Reference To A Function?

Jul 23, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL;
for all text after Password= and before line break WITH t AS (SELECT '[MyDB]
Army=@MyDBarmy
Navy=@MyDBnavy
Airforce=@MyDBairf

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Send Email From Procedure

Dec 9, 2010

Need info regarding sending an excel file attachment from plsql.I am using utl_smtp package in procedure and want to attach some sql query results in excel file.Is it possible to use sql query in same stored procedure that i am using to send the email attachment or i need to use external directory path to load the file as attachment.

View 9 Replies View Related

Application Express Listener :: Using PL/SQL Procedure / Function In Resource Template (listener 1.1.3)

Feb 15, 2013

I'd like to call an existing function/procedure from a resource template. For example, I have a procedure called 'rest' with an out parameter of 'p_out' - how do I use this within a resource template?

At the moment I have defined the template as type 'PL/SQL Block' and with the 'GET' defined as:

begin
myschema.rest(:p_out);
end;

APEX_PUBLIC_USER has been granted 'all' on the procedure. The p_out is populated with a piece of text.

When I call this resource, I get a '500 Internal Error' returned. I've tried adding p_out as an X-APEX-FORWARD parameter, but nothing seems to work.

Query-based templates seem to work OK.

Using Listener 1.1.3 with Tomcat6.

View 0 Replies View Related

Send Email With Attachment Using Stored Procedure?

Jun 17, 2011

I would like to send an email with attachment using a STORE PROCEDURE, with a PL/SQL, I was testing a package called SRW.RUN_REPORT but the problem is that the report generate a pdf file but this file is storage in application server because the report server run over the application, wo we can not get the physical file to send as attach.

the problem is generate the pdf file save in a place that we can get easier.

View 8 Replies View Related

SQL & PL/SQL :: Procedure To Send Invoices To Customer Through Mails

Nov 3, 2010

i'm working on procedure to send invoices to customer thru mails.

i'm using UTL_Mail package to send html content in a mail , it's working if i'm using my company mail exchange BUT if i put "xxxx@hotmail.com" or "xxxx@yahoo.com" it doesn't work and raise

error :ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for xxxxx@hotmail.com

View 10 Replies View Related

SQL & PL/SQL :: Send Mail From Procedure With Attach From File System

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

SQL & PL/SQL :: How To Send Email In HTML Format By Executing Anonymous Procedure

Oct 11, 2010

How to send email in HTML format as an attachment/email body by executing oracle anonymous procedure

View 1 Replies View Related

SQL & PL/SQL :: Output Of Oracle Function Dump

Oct 18, 2010

what the following output of the oracle function "dump" means?

query: select dump(:new.messaging_state) from dual

output:

Typ=2 Len=1: 193

I need this debug output because I want to know which value the program wants to write in the columns messaging_state.

The update fails because of a parent Key violation.

View 2 Replies View Related

PL/SQL :: No Output From Procedure

Dec 4, 2012

When I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.

So what am I going wrong here.

set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN

[Code]....

View 7 Replies View Related

PL/SQL :: Output Like Using Sql Query Without Using Dense-rank Function?

May 1, 2013

Here table - tac has

row1
-----
X
X
X
B

[code]...

I want ouptut like using sql query with out using dense_rank function,

row1 row2

X 1
X 1
X 1
B 2

[code]...

View 7 Replies View Related

How To Output Parameter To App C# Procedure PL/SQL

Sep 17, 2013

this PROCEDURE for Paging.

PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;

[code]...

View 1 Replies View Related

PL/SQL :: Calling Procedure From Another And Get Combined Output

Jun 3, 2013

I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:

CREATE OR REPLACE PROCEDURE sp_all_attribute_difference
(
i_product_id_new IN NUMBER,
o_product_attr_diff_list OUT product_attr_diff_list,
o_usage_attr_list OUT usage_attr_diff_list
)
IS
[code]....

I am not able to compile tis proc.

View 5 Replies View Related

PL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point

Oct 19, 2012

I have a question on Round Function

Round(###.###,1)

Will the round function output depend on 2nd digit after decimal point also or not?

View 6 Replies View Related

PL/SQL :: Function Retrieve Output Very Slowly - Alternative Code To Replace With Join?

Aug 6, 2012

In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join

CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS

[code]...

View 2 Replies View Related

How To Transfer Output File From One Server To Another Using Stored Procedure

Apr 9, 2012

We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).

Functionality of the stored procedure:Generating a tab delimited file and place in a directory.

Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).

Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).

Attached File(s)
Procedure.txt ( 3.28K )
Number of downloads: 4

View 2 Replies View Related

SQL & PL/SQL :: Create Stored Procedure With Array As Output Parameter?

Jul 14, 2011

how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...

View 7 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

create a procedure so that I could get minus query of 2 table as a result.

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

View 11 Replies View Related

Forms :: 10g - Send Email From Application?

May 28, 2013

I want to send an email from my Application forms 10g .But I receive

DECLARE
l_mailhost VARCHAR2(64) :=... //admin mail
l_from VARCHAR2(64) := ... //admin mail
l_to VARCHAR2(64) := ...//user mail
l_mail_conn sys.UTL_SMTP.connection;

[Code] ........

message displayed is: no internet connection !!

View 3 Replies View Related

Application Express :: Can't Send Email Using 3.0.1.00.07 Oracle 10g

Jul 17, 2012

Can't send an email using Application Express 3.0.1.00.07 on oracle 10g.

my code is:
DECLARE
l_body CLOB;
v_email varchar(30);
BEGIN

SELECT CONTACT_EMAIL
INTO v_email
FROM persal_info
WHERE TRIM(PERSAL)= TRIM(:P48_PERSAL);
l_body := 'Your access code for the Transport system is:'|| :P48_CODE ;
l_body := l_body || '

This access code is needed in order for you to complete the claim form. It is important to change this access code for the confidentiality of your information.';

apex_mail.send( p_to => v_email,
p_from => 'PietC@google.com',
p_body => l_body,
p_subj =>'Access Code'
);

View 1 Replies View Related

Application Express :: 4.2 - Oracle Apex_Mail Send

Nov 6, 2012

I am using oracle apex 4.2 version.i am trying to send mail using APEX_MAIL.send.My process like

begin
APEX_MAIL.send(
p_to       => 'xxx@xxx.com',
p_from     => 'xxx@xxx.com',
p_body     => l_body,
p_subj     => 'APEX_MAIL Package - Plain Text message');
end;

When i check mail queue from administrator part HomeManage > InstanceMail > Queue

It show no error and after the send all mail still i am not getting the mail..i also check my email setting in administrator part.

View 1 Replies View Related

Application Express :: Send Link To Update Some Record

Mar 8, 2013

Is there some simple solution how to send email to user with link ('I don't want to receive emails from you in the future') ?

The link should contain some key information for each user.

If user click the link - update of the table should be done. (some IDC updates from 'Y' to 'N').

Specs: APEX 4.1.1
Listener 1.1.3
DB 11G
Apache Tomcat 6.0

View 1 Replies View Related

Application Express :: Send Mail To GMAIL With Attachments

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

Application Express :: How To Automatically Send Report After It Is Created

Sep 16, 2013

I am beginner . I found this tutorial how to save and access report.Storing and Accessing Reports in the Database In that tutorial report is saved onclick.Is there a way to automatically email report everytime report is generate or I should first save it (PDF, CSV,..) with SQL script and then email it? I also found that in Interactive report there is page process that can send e-mails. Is it possible that page process send e-mail with report or report should be also saved before sending? The reason for this is that we want to have a history of created reports.

View 6 Replies View Related

Windows :: Calling Stored Procedure With Input And Output Parameters From Batch File?

Oct 4, 2011

when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14

code which i have written ...

DEClARE
RETCODE NUMBER;
RETMSG VARCHAR2(200);
EXEC SP_SELCT_EMPLOYEE_DATA(277080,'EMPNAME ','EMAIL@EMAIL.COM', 9028045686,111333,25000,'PUNE',35,RETCODE ,RETMSG );
EXIT

Procedure Name :

PROCEDURE SP_SELCT_EMPLOYEE_DATA (
-- A_EMPLOYEE_ID IN VARCHAR2,
--A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE,
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE,
P_EMAIL IN EMPLOYEES.EMAIL%TYPE,

[code]....

View 2 Replies View Related

Reports & Discoverer :: Create A Procedure In Program Unit And Assign Output To A Field In Report?

May 8, 2013

i just want to know how can i create a procedure in the program unit and assign the output to a field in the report.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved