Re: Running PL/SQL procedure (182 Views)
Reply
Regular Advisor
Denis_35
Posts: 165
Registered: ‎12-06-2004
Message 1 of 3 (248 Views)
Accepted Solution

Running PL/SQL procedure

Hello!

I have huge PL/SQL procedure (it generates xml file).

So i am wondering, what is the best way to run this procedure?

Should i format it as it shown in example (it will be VERY long string :) ):

It is possible to run PL/SQL procedures using this operation. The examples below present the syntax to be used for having the same effect as the following PL/SQL script:
BEGIN
  send_message(addressee=>'b@hp.com',sender=>'pas@hp.com',subject=>'Test',message=>'Test');
END;

Example 1: BEGIN send_message(addressee=>'b@hp.com',sender=>'pas@hp.com',subject=>'Test',message=>'Test');END;


Or there are other ways?

 

And is it possibe to get result of PL/SQL script?

Please use plain text.
Trusted Contributor
ntt00015
Posts: 91
Registered: ‎09-18-2011
Message 2 of 3 (219 Views)

Re: Running PL/SQL procedure

Hi,

 

It is not possible to get directly the output of the PL/SQL Procedure. But if your output has only one output, you can do it using a function with the SQL Query Operation.

 

SELECT your_function(inputs) function_result FROM dual;

 

Another way is to get the outputs is to write the outputs into a temporary table and then select the outputs with SQL Query operation at the preceding steps.

 

To run a SQL Command operation you can set the command input as a normal PL/SQL Script. For example :

**start***

BEGIN

 insert into some_table (column1, column2) values('${value1}', '${value2}');

p_some_procedure('${input1}', '${input2}');  /*p_some_procedure(input1 in varchar2, input2 in varchar2);*/

END;

***end***

 

 

Please use plain text.
Regular Advisor
Denis_35
Posts: 165
Registered: ‎12-06-2004
Message 3 of 3 (182 Views)

Re: Running PL/SQL procedure

I got agreement with DB admins - procedure will be stored in DB and run directly from it. I think this is the best decision for me.

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation