PL/SQL :: Calling Procedure In Loop

Oct 2, 2013

I am calling a procedure with the following parameters 

DECLARE     PROFIT_CENTER NUMBER;   BEGIN   PROFIT_CENTER:= 1109 --( Similarly I am running the proc for 5 more profit_centers 1123,1132, 1122,3211, 1111

one by one by passing values manually, it is taking almost a minute for each profit center

)Prc_test_calc (  PROFIT_CENTER);  COMMIT;   END;
For each profit_center proc is taking 1 mins (Approx) time.  

But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center. Anything wrong with the below loop  

DECLARE     PROFIT_CENTER NUMBER;  cursor c_profit is select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111); --

Plng_pc is a table from where profit_center information is getting fetched  

BEGIN For pc_rec in c_profit loop   Prc_test_calc (  pc_rec .PROFIT_CENTER );  COMMIT; End loop;   END;

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: Variables In Loop Procedure

Feb 15, 2010

PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2,
P_Txn_Ccy IN VARCHAR2,
P_Allocation IN VARCHAR2,
l_lty_id IN VARCHAR2 ) IS
l_balance_fmt NUMBER := 0;
BEGIN
[code]....

it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..

View 3 Replies View Related

Calling Procedure Using Trigger

Aug 26, 2013

I am in the process of creating a trigger for a procedure I created. The procedure is working fine but I keep getting an error on the trigger. I am getting Error(7,5): PL/SQL: Statement ignored AND Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'

Here is my procedure:

create or replace PROCEDURE INSERTINTOPHYSLOG
AS
prec_num long;
srec_num long;

[Code]....

View 2 Replies View Related

Calling PL/SQL Procedure From JavaScript?

Oct 8, 2007

The application I am working on uses Oracle HTP procedures to generate HTML pages for our application. I am trying to perform some cleanup when a user closes a web application by clicking the 'X' button in there browser. I am thinking of using the onUnload trigger to call a javascript function, which in turn would call a procedure in my PL/SQL pacakge for cleaning up logical locks on records.

The body tag delaration looks like this code fragment:

htp.print('<BODY onUnload="clear_locks(' || p_user_id || ')"

Here is some psuedo code for the function i would like to use:

<SCRIPT>function clear_locks(p_user_id){
call clear_locks(p_user_id);
return true;
}</SCRIPT>

Note that clear_locks is a pl/sql stored procedure.So far I have gotten it to pass in the user ID correctly.Is there a way I can call my clear_locks procedure?

View 1 Replies View Related

SQL & PL/SQL :: Procedure Calling Methods

Oct 14, 2013

I have created this procedure for printing ename,sal,job as output using empno as input:-

CREATE OR REPLACE PROCEDURE p_get (
p_empno NUMBER,
p_name OUT VARCHAR2,
p_sal OUT NUMBER,
p_job OUT VARCHAR2
)
IS
BEGIN
SELECT ename, sal, job
INTO p_name, p_sal, p_job
FROM emp
WHERE empno = p_empno;
END;

Now My Requirement I want to call this procedure using Positional,Named and Mix Methods...I am calling this procedure using Positional Method:-

declare
p_name varchar2(20);
p_sal number;
p_job varchar2(20);
begin
p_get(7369,p_name,p_sal,p_job);
dbms_output.put_line('Name='||p_name||' Salary='||p_sal||' Job='||p_job);
end;

how to call the same procedure using NAMED and MIX methods.how to call same procedure using NAMED and MIX methods.

View 11 Replies View Related

SQL & PL/SQL :: Calling Procedure Into Function?

Aug 1, 2011

for my r&d purpose i create function which call procedure,

create table test_tab
(a NUMBER(10),
B NUMBER(10),
C NUMBER(10)

[Code]...

but it will gv me error ORA-06575:FUNCTION IS IN INVALID STATE that means function created with compilation error.

but when i complied fucntion it doesnt gv me any error.

View 15 Replies View Related

Procedure Running In Infinite Loop?

Oct 1, 2012

I have a procedure with following code which runs in infinite loop.

BEGIN
LOOP
line :='Test Message';
UTL_SMTP.write_data(l_mail_conn, line || Chr(13));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(NAMESFILE);
END;

I executed the procedure and disconnected from sql developer by killing the sql developer process from task manager. Reconnected and changed the code and when i tried to compile the procedure. It is not compiling, because the procedure is already running in infinite loop by some other session.

Is is possible to stop the running procedure without killing the session.What will happen if the procedure is not stopped which is running in infinite loop.When I queried the following found that the session is still ACTIVE and CPU used showing as '0' and executing the procedure.

select
SST.VALUE CPU, S.USERNAME, S.SID, s.status,S.SERIAL#, SQL_TEXT
FROM
V$SESSION S,
V$SQLTEXT_WITH_NEWLINES T,
V$SESSTAT SST
WHERE S.SQL_ID IS NOT NULL

[code]....

Can I do force drop or alter this procedure?

View 3 Replies View Related

SQL & PL/SQL :: Procedure Cursor For Loop Don't Execute

May 13, 2010

I'm running a PL/SQL with a For Loop cursor, but when trying to execute it doesn't run. It is as if there is no data, but I ran the cursor separately in a SQL Plus session and it runs perfectly. I'm enclosing the file with the procedure.

View 18 Replies View Related

Calling Procedure With Commit From Trigger

Dec 8, 2006

I searched, found this one hit and according to mateoc15, you cannot commit within a procedure that is called from a trigger. He must be right, because mine is not committing either, nor are any errors given.

Trigger

Create or replace trigger owner_name.table1_trg2
after update on table1
for each row
call owner_name.procedure1;

procedure Code (psuedo):

Create or replace procedure1 as
begin
update table1 set col1 = 'whatever';
commit;
exception when others then
rollback;

Executing the procedure as owner_name on SQLPlus works fine, but when I update a column of the table via the PL/SQL package (on the web form), the column does not update, telling me that the procedure never fired from the trigger.

View 5 Replies View Related

Calling Web Service From A Procedure Using SOAP API

Mar 5, 2013

I am trying to call a Web service using the SOAP API and I get the following error.

ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
ORA-06512: at "SYS.XMLTYPE", line 48
ORA-06512: at "MyConnection.SOAP_API", line 121
ORA-06512: at "MyConnection.MyFunction", line 28
31020. 00000 - "The operation is not allowed, Reason: %s"
*Cause: The operation attempted is not allowed
*Action: See reason and change to a valid operation.

However we do have another procedure that is able to make an HTTP call successfully within the same domain. Hence the ACLs that are required are for making an HTTP call are in place. Is there any other security setting to be tweaked to make the to enable me to make a Webservice call using the SOAP package?

View 1 Replies View Related

SQL & PL/SQL :: Calling Exe File From Stored Procedure

Jun 15, 2011

I want to call an exe file through DB Procedure. How it is possible?

1create or replace procedure proc3

2is
3begin
4 host('calc');
6* end;

from FORMS it works fine but it does not work through DB. I found same question through this link

[URL].........

but no answer is given.

View 2 Replies View Related

SQL & PL/SQL :: Calling Stored Procedure With Default Value?

Apr 3, 2012

I have the following Stored Procedure:

CREATE OR REPLACE PROCEDURE AFESD.TEST_PROC (I_NUM IN NUMBER,
I_NUM2 IN NUMBER DEFAULT 3, D_DATE IN DATE DEFAULT sysdate,D_OUT OUT DATE)
IS

[Code]....

I dont know what I need to pass in order to give it the default value.

View 3 Replies View Related

SQL & PL/SQL :: Calling Shell Script Within Procedure?

Jul 22, 2013

I have a procedure that checks in the table if the daily refresh was successful or not for the previous day. If it failed I would like to receive an email, which I accomplish with calling my EMAIL() procedure. Otherwise call my program_scheduler that will run a shell script.

Below is my procedure:

create or replace
PROCEDURE RD_ODS_REFRESH_LOG
IS
BEGIN
FOR i IN
(
SELECT RESULT FROM RD_REFRESH_LOG

[code].....

And here is CALL_SHELL job that my procedure calls:

BEGIN
SYS.dbms_scheduler.create_job (
job_name => 'CALL_SHELL',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/load_semantic.sh',

[code].....

My ELSE statement never gets executed, why?

View 18 Replies View Related

Precompilers, OCI & OCCI :: Calling Procedure From PRO C?

Nov 26, 2012

I am trying to call procedure from PRO C Procedure has many parameters and I do not need to put all of them when I call procedure. Is there way to make the same way as in PL/SQL

like this

my_procedure( q=>a1 , b=>'abc )

View 1 Replies View Related

SQL & PL/SQL :: Calling Other Procedures From Autonomous Procedure?

Feb 26, 2013

It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.

Scenario is like below:

create user a identified by a;
grant connect,resource to a;
create user b identified by b;
grant connect,resource to b;
create user c identified by c;
grant connect,resource to c;

[code]....

Not getting why the schema is not changing... and how to resolve the error.

View 8 Replies View Related

PL/SQL :: How To Run Report / Calling Web URL Into Stored Procedure

Dec 17, 2012

I have a requirement to run below given URL by Stored Procedure. I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

[URL]

View 4 Replies View Related

PL/SQL :: Calling Oracle Procedure With Two OUT Parameters

Jun 20, 2012

I am having an Oracle procedure which return ref cursor. I also want to result one more out parameter result. How Can I call the procedure in SQL. Below is the way I am calling my stored procedure with one parameter.

proc_Test (p_resultset=> My_cursor)

How can I call the procedure when I have one more OUT parameter. Second parameter returns 0 or 1.

View 5 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 :: Differences Between Calling SQL Statement / Procedure?

Apr 22, 2013

I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?

Examples:
a) WebApp->Call("select * from employees where department_id = ?", 10) ;

b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);

create package mypackage is
function get_emp(dep_id in number) return sys_refcursor is
   begin
    open cur for select * from employees where department_id = dep_id;   
end;
end;==========================================
Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.

View 10 Replies View Related

SQL & PL/SQL :: Error In Calling Collection Procedure

Jun 4, 2013

In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are

"UpdateRelatedOrders" and "SetGeoFraudScore"

with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures

"UpdateRelatedOrders" and "SetGeoFraudScore".

I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.

CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk
IS
TYPE rec_setscores IS RECORD
(
pBUID score.buid%TYPE,
OrderNum score.order_num%TYPE,
ScoreType VARCHAR2(100),
p_Score score.velocity_score%TYPE
);
[code]....

View 19 Replies View Related

Out Of Process Exception On Calling Java Procedure?

Sep 28, 2011

In my application, we are getting the following exception on calling the Java procedure.

java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError
ORA-04030: out of process memory when trying to allocate 10804020 bytes (joxp heap,f:OldSpace)

[Code].....

View 2 Replies View Related

SQL & PL/SQL :: Calling Database Link Dynamically For A Procedure

Feb 15, 2011

How to call a procedure by passing the db link dynamically.

View 1 Replies View Related

SQL & PL/SQL :: Trigger Calling An Autonomous Stored Procedure

Jul 7, 2011

I have a trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure. I'm not positive of this since the ability to log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION?

View 18 Replies View Related

Forms :: Calling Function Inside The Procedure

Jun 2, 2011

I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?

View 4 Replies View Related

SQL & PL/SQL :: Calling Shell Script Through External Procedure

Nov 26, 2006

I am trying to call a shell script through external procedure. To summarize the steps that I followed:

Placed the library file,i.e., the .so file in bin folder of the database server. I have not compiled the .c file and created this. I have taken this from another application.

Then I logged into the sqlplus. Created one library pointing this .so file.

create or replace library ext_proc as '/ngs/app/ivndrptd/bin/execute_cmdlib.so';

After this created the external procedure. This got compiled without any errors.

SQL> create or replace procedure ext_proc_wrapper(in_script IN varchar2)
as external
library ext_proc
NAME "execute_cmd"
parameters (in_script string);
/
Procedure created.

I am trying to execute a script test.ksh which contains a touch command to create a test.txt file.

However irrespective of the procedure running successfully the script is not getting called. I tried several ways of calling the script which are as follows

SQL> exec ext_proc_wrapper('sh /ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.ksh');
PL/SQL procedure successfully completed.
SQL>

View 5 Replies View Related

Application Express :: How To Set Request When Calling Procedure

Apr 23, 2013

I use the procedure gReport.navigate.paginate. If there is the possibility to set a value to REQUEST when calling this procedure?

View 7 Replies View Related

SQL & PL/SQL :: Usage Of Loop - Creating A Procedure With 4 Input Parameters

Jun 6, 2012

I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.

create or replace procedure PRC_add_data(
P_Emp_No varchar2,
P_Member_Name varchar2,
P_IDvarchar2,
P_UHID varchar2
)
is
BEGIN
INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID)
END;
/

View 6 Replies View Related

SQL & PL/SQL :: How To Pass In Dynamic Variable For Calling Store Procedure

Apr 9, 2012

I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.

For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".

DECLARE
bef_val CUST%ROWTYPE;
aft_val CUST_TEMP%ROWTYPE;
BEGIN
SELECT * INTO bef_val FROM CUST WHERE name = 'ABC';
SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC';
[code]....

For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?

View 8 Replies View Related

SQL & PL/SQL :: Calling Packaged Procedure With Refcursor As Out Parameter Over DBlink?

Oct 7, 2011

I have a requirement in which I have to call a packaged procedure created in one database DB1 to other database DB2 through a DBLink. The packaged procedure to be called has refcursor as OUT parameter.

When I run the procedure in DB1 , it works fine:
Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);

Begin
apps.XXCU_DB1_PKG.get_DB1('101062','9138', v_ref_data);

FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);
End;

When I run the packaged procedure from DB2 using DBlink, it gives error:

Declare
v_ref_data SYS_REFCURSOR;
a1 Number;
b1 varchar2(100);
c1 varchar2(100);

Begin
apps.XXCU_DB1_PKG.get_DB1@dblink('101062','9138', v_ref_data);
FETCH v_ref_data into a1,b1,c1;
dbms_output.put_line(a1|| ' '|| b1|| ' '|| c1);

[code]...

I read somewhere on other forums that refcursors can not be passed through the DB links, is it true??

View 1 Replies View Related

Precompilers, OCI & OCCI :: Calling Stored Procedure From C Program

Mar 19, 2010

I need to call a PL/SQL stored procedure from a C program. Something like this sample program provided by Oracle -

main()
{
int i;
EXEC SQL BEGIN DECLARE SECTION;
/* Define type for null-terminated strings. */
EXEC SQL TYPE asciz IS STRING(20);
asciz username[20];
asciz password[20];
[code].......

The question is - how is the Stored procedure get_employees declared ? Or more specifically, how is the salary parameter declared in get_employees ?

View 4 Replies View Related







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