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
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..
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;
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.
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; /
create or replace PROCEDURE TESTPERFORMANCE ( o_statuscode OUT NUMBER, o_statusdescription OUT VARCHAR2, starttime out timestamp, time_after_query_TESTJOB out timestamp,
[Code]...
This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.
My problem is that i don't want to execute a procedure from my scheduler if the previous execute didn't finished. Is there a manage view or table that provide the procedure state?
All this has within is an SQL statement which is built up (using the string, 'strSql') How can I view the output of a refCur to check what the final strSql is?
I have a stored procedure that is run from a command within our Clarity application.
The procedure involves some SQL Reads and SQL Inserts.
We have experienced users running the SP at the same time (slim chance to do this) and it creating duplicate entries.
if there is a clever way of preventing the same SP to be run concurrently?
Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table - which the SP then sets to 1 if it is running, and 0 at the end.
Are there better more efficient/effective ways of doing this?
I have a requirement to create a packaged proc which lists down a set of database objects and its statuses whenever the status of objects is changed as valid/invalid in user_objects. Also, those valid objects need to be compiled while running the packaged proc.
CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2) is type ind_table is table of varchar2(20); p_ind ind_table; v_sql varchar2(2000); begin select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab); for i in 1..p_ind.count loop v_sql :='alter index '||p_ind(i)|| ' monitoring usage' execute immediate v_sql using p_ind(i); end loop; end;
I have a list of strings ( like a,b ,c) that I want to loop againts. I will be creating string to execute them as dynamic sql.I could have put the string in a table and loop againt the table but this is a deployment script so I do not want to create any table.I can also do
DECLARE cursor c is SELECT 'a' FROM dual UNION SELECT 'b' FROM dual ; BEGIN for i in c loop ....... end loop. END;
But I have many strings to loop over. What is the best way to achieve it. Can i use collection to achieve this?
What I want to produce is a table in the following format
basket_ref, all_fruit 1, apple banana pear 2, apple lemon
There could theoretically be any amount of fruit in a given basket, so I will need to set up some kind of loop in order to read each record from the original table. I am not sure how it would be best to do this.
I have a cursor which I am opening and then looping through. Within this loop I am comparing attributes within this cursor with attributes from another loop that this one is within.
you will see in the IF statements (there are several distinct IF statements within the loop) that there is a check which assesses if the attributes are not equal.
If they are NOT, the value of v_mismatch is set to 1
What I need to do instead of setting this to 1, is to go to the next record in the loop. How can I achieve this?
OPEN c_distMatrix; LOOP FETCH c_distMatrix INTO r_dist; EXIT WHEN c_distMatrix%NOTFOUND; --compare each field and update the counter [code].....
[CODE] DECLARE D1 DATE:='&D1'; D2 DATE; BEGIN WHILE D1<=D2 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY')); D1:=D1+1; END LOOP; END;
Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.
This query that I pasted is working correctly.Let's say a case has 4 owners, it finds me the first owner and show me its address.
However, I want to execute this query for all owners so it should jump the previous owner it found. Lets say for that example that the loop ends at 4. How may I fix this problem so that the loop do not return always the first owner but it keeps getting 1,2,3,4...also I should increase the sequence value for each situation
begin for i in 1..10000 loop update table1 set col1= col1+1 where type =1; commit; end loop; end;
My question is, if a strored procedure contains the script above and the said procedure will be invoked by two or more sessions at the same time. Does it mean that the 1st session will lock the related rows and other sessions will have to wait for loop in session one to finish?
I am an experienced SAS programmer jumping into PL/SQL for the first time and have already encountered a problem.Let's assume I have 7 records (shown below). (In reality, I have millions of records, but the concept's the same.) The Value field is only populated when it changes. Therefore, I am forced to "fill in the gaps" as I read the data file. It's fairly straightforward. I carry the value foward one record at a time, using it if the Value field in not populated. The ANSWER I want is also shown.
In reading through a PL/SQL book, I realized that only 2 chapters are relevant to what I do. My guess is this solution involves cursors; probably a Cursor FOR loop,
------------------------------------------------------------------------------------------------------- While Condition LOOP BEGIN Insert into table1 values(......);
[Code]...
EXCEPTION When OTHERS THEN <capture the error while inserting into an Error table> END; END LOOP; -------------------------------------------------------------------------------------------------------
Now I want that If one of the insert statement within the loop fails (say table2) the exception should be captured and next insert statement (table3) should be executed.
How can I do this ? I guess I wont even need a loop
in my loop with cursor in a procedure body i am displaying some field values to create a report and after every record i am displaying horizontal line(-------) but i don t want this line to appear after the last record displayed like below,
CREATE OR REPLACE FUNCTION get_project_id( schema_p IN VARCHAR2, table_p IN VARCHAR2) RETURN VARCHAR2 IS projects_pred VARCHAR2 (400); [code].......
I am trying to get the projects a user has from the works_on table (user_id, project_id). The user_id is retrieved from the context projects_ctx. I am getting the error Function created with compilations errors.
I want to update column in table 1 based on a substraction of two column, one from the same table and the other from different table. Then update the result of substraction in table 1. Number of rows in two tables are different.
--for r in (( select (table2.y - table1.y as x from table1, table2 where table1.x = c and table2,.x = m)) declare i number := 1; c number ; m number;
