I have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.
View 21 RepliesI have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.
how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.
Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)
I am using Oracle 9i.
I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.
View 2 Replies View RelatedWhen the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies View RelatedMy need is to check whether procedure execution is successful and write sysdate and procedure name into log table.
Sure I can check target table which procedure uses to write result and count lines after execution. But I've got different procedures, some of them can write 0 lines and it's not error. I think Oracle should have something like return codes and some number means "ok".
Oracle Version is 11g.
I need to call the same procedure with different parameter multiple time in parallel.
I have job_control Table
CREATE TABLE JOB_CONTROL
(
JOB_CONTROL_ID NUMBER NOT NULL,
JOB_SEQ_NO NUMBER NOT NULL,
MODULE_NAME VARCHAR2(32 BYTE) NOT NULL,
JOB_STATUS VARCHAR2(15 BYTE),
NO_OF_RECORDS NUMBER,
PROCESSED_RECORDS NUMBER
);
Insert into JOB_CONTROL
(JOB_CONTROL_ID, JOB_SEQ_NO, MODULE_NAME, JOB_STATUS, NO_OF_RECORDS)
Values
(20, 1, 'SALES', NULL, 5);
Insert into JOB_CONTROL
[code]........
Now the table look like this.
JOB_CONTROL_IDJOB_SEQ_NOMODULE_NAMEJOB_STATUSNO_OF_RECORDSPROCESSED_RECORDS
201SALES5
212SALES5
223SALES5
234SALES5
245SALES5
256SALES3
based on the data available in table i wanted to execute a procedure 6 times
create table job_table
(now date, seq_no number)
the procedure will have one parameter as job_seq_no
create of replace procedure job_call (i_job_seq_no number)
is
begin
for i in 1.. i_job_seq_no
loop
insert into job_table
values
[code]......
the problem is i want to do this activity in parallel , i.e. all six calls to the procedure would be parallel. and this will be one time activity.
I created the following stored procedure which I am calling from a script. I compiled my Stored Procedure with Debug Info. For some reason Execution jumps from the second BEGIN to the END statement.
Since the SP compiles w/o any errors, I suspect I have a logic error.
Stored Procedure:
CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW
(
VALIDATED OUT int,
LAST_NAME IN VARCHAR2 DEFAULT NULL,
FIRST_NAME IN VARCHAR2 DEFAULT NULL,
DOB date DEFAULT NULL,
PAT_NUMBER OUT int,
FACILITY_KEY OUT CHAR
)
AS
BEGIN
/* SELECT * */
BEGIN
[code].......
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies View RelatedIs there any way of returning output parameter values to calling environment before completion of procedure execution. I may achieve it by using GTTs, looking for any other way (because calling environment again need to issue select statement to retrieve data from GTT).
Example case:
Procedure have multiple ref cursors as out parameters.
....
...
if exp1=exp2
then
open v_ref_var1 for select ...from ... ;
end;
[code]..........
If the first if condition satisfies, ref cursor - v_ref_var1 data should be immediately available for the calling environment.
I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.
The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.
I tried even Oracle 10g wrap it is also not working fine.
I have two Tables, the one table is called (calves_per_breed) and contains all my query results. I then have another table (calves_per_breed_crosstable) which is used to place the generated count values in calves_per_breed to create a crosstable from the count data into calves_per_breed_crosstable.
I'm using the following procedure to generate the crosstable from the data inside the calves_per_breed table:
PROCEDURE pcalves_per_breed_genCrossTable
IS
BEGIN
---------------------------------------------------------------------------
--SEX CODES
update calves_per_breed_crosstable
set (m, f) = (select count(decode(geslag, 'M', 1, null)),
count(decode(geslag, 'F', 1, null))
from calves_per_breed)
[codee]....
Now this procedure works 100%, the only problem is it generates the ENTIRE table. if you know how a crosstable works, thetop right section of the table is exactly the same as the bottom left section of the table. I wish to optimize my code so that it only generates the values for the needed columns, and not ALL the columns, as values are generated twise now, which increases the query time! Here is a tipical output of the kalwers_per_ras_crosstable:
Note: You will notice that i used a Column called TID with string values to indicate the vertical columns for the crosstable. The Vertical Columns are the same as the top Columns(which are actual columns, and not row values as the vertical columns)
select *from kalwers_per_ras_crosstable:
TID M F NFR A B C SP RED BLACK SC1 SC2 SC3
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
M 138 0 0 0 0 0 138 122 16 74 64 0
F 0 173 0 0 0 0 173 161 12 92 81 0
NFR 0 0 0 0 0 0 0 0 0 0 0 0
A 0 0 0 0 0 0 0 0 0 0 0 0
[code]....
we are executing a load activity every day through .NET Application, we taking a time solt for Database to ensure nobody is using at that time.But the AWR reports showing different issues on different days.
View 11 Replies View RelatedWhen i am trying to execute the below in sql. i am getting the error.
create or replace type sum_n as object (
nodes node_d,
constructor function sum_n return self as result,
member procedure do_s (m date,exd varchar)
);
/
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/9 PLS-00201: identifier 'NODE_d' must be declared
I have a trigger that is called from an update on the table, this trigger performs the procedure and this procedure update the Same record in the table That shot the trigger. this situation returns error ORA-0060 - DEADLOCK DETECTED WHILE WAITING FOR RESOURCE. Is there any way that this works?
View 1 Replies View RelatedIn our production environment some SP's are executing longer duration, but when same SP is executed from PLSQL Developer client it is executing vary quickly.
View 3 Replies View RelatedWe have the following case: an application modifies a table in an Oracle db (10.2.0.3.0).
Unfortunately the update SQL statements from the application always use the condition "where Column1 = 'some given value'" which is wrong (never mind why).
It should be instead "where Column1 = 'some value' and Column2 = 'val for Column2'. The 'val for Column2 will be taken from the very SQL query being issued (we can make the application do an update for Column2 even if the value in it never changes).
So all the update queries from the application look at the moment like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value'".
We would like to capture them and somehow on the fly modify them to look like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value' and Column2 = 'val for Column2'".
Can a trigger "before update" do it? For some reason we cannot at the moment ask the vendor to change the hard code of the application so we are looking for a temporary workaround.
I have an understanding that the code written in Forms 6i will be executed by the oracle forms 6i run time on the client machine whether it be a sql/plsql .
e.g. i am using the following query in the procedure( of forms 6i interface ).
insert into emp_remote
select ename from emp@db1;
so where the above query would be executed , on client machine or server named db1.
I want to know that is there any way to know the execution time of a sql query.
View 1 Replies View RelatedI'm trying to run the following procedure and I'm getting he following message:
Warning: execution completed with warning
PROCEDURE etl_bago Compiled.
The procedure is created, the problem is that when I tried to run it with parameters I'm getting this error:
exec etl_bago ('july19.csv');
Error report:
ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.ETL_BAGO is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
[code]......
Btw, I'm using SQL Developer 1.5.3. And the procedure script is here:
[URL].....
when I am executing the below query getting diffrent count every time and not able to guess what is happening.
SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day
I have a block of code that looks something like this, I'll write it in pseudo code to avoid pasting 100's of lines of
IF <condition> THEN
FOR record in (select query here..)
LOOP
--add data
END LOOP
END IF;
IF <condition> THEN
--do soemthing
END IF;
IF <condiiton> THEN
--do soemthing
END IF;
Now, in the 1st IF statement there is a for loop which basically builds up a tab type array - this takes at least an hour to execute based on the select query which returns 1000's of records.
The strange thing is, that my log files shows that at the time the procedure was executed, the stuff in the 2nd IF statement was executed almost straight away.. is this even possible?
If the loop in the 1st IF statement takes over an hour to finish, how is it possible for the 2nd IF statement stuff to process straight away? My log files show me that the loop in the 1st IF statement was going on for a good hour.. yet the 2nd IF statement was executed straight away.
when we want to execute self join command for displaying employee names and their managers name we use the following query.
Select e1.ename"Employees",e2.ename"Managers" from emp e1,emp e2 where e1.Manager_ID=e2.Employee_ID
how it displays the above information or how it works?
[font="Lucida Console"][/font]
Issue: For this sql statement client is changing the date and this sql is running fine in development and taking time in production.So I created the sql profile and push it ot prodcution so for EFFDT <= '25-APR-2010' it was running fine as plan is same as development .....but then again client changed the EFFDT <= '28-AUG-2010' is changed then plan neglected my sql profile because of hardcoded value and so it has parsed the sql again.
How we can fix this plan ? there application is like that so they are goin to pass the hardoce value like this only.....so they can not use bind variable... they are going to fire the sql from one session can we set on the session level like cursor sharing or some hints to get the development plan *for proper formating see the attached file* Statement :
SELECT
DECODE(SUBSTR(JL.PAYGROUP,1,1),'P','P','T','P','C'), JL.DEPTID_CF, JL.OPERATING_UNIT, JL.FUND_CODE,
JL.CLASS_FLD, JL.PROGRAM_CODE, PC.EMPLID, PC.EMPL_RCD, BUGL.BUSINESS_UNIT, JL.ACCOUNT,
SUM(DECODE(JL.GL_NBR,'REGER',JL.AMOUNT,0)), 'Regular Earnings', SUM(DECODE(JL.GL_NBR,'OTERN',JL.AMOUNT,0)), 'Overtime', SUM(DECODE(JL.GL_NBR,'NRTAL',JL.AMOUNT,0)),
[code]....
I am executing below query, but optimizer generating 2 different plans for the same. I don't want to use sql profiles to fix execution plan.
Query
SELECT R.VENDOR_RECORD_SEQ_NO ,
R.VENDOR_SUBJECT_SEQ_NO ,
NVL(D.RESOLVED_VALUE, D.ORIGINAL_VALUE) VAL,
D.CONTROL_COLUMN_SEQ_NO
[code]....
I just wanted to know how the query will be executed in case of where clause and ROWNUM clause. e.g, consider below query.
SELECT * FROM emp
WHERE dept IN (20, 30, 21)
AND salary < 10000
AND rownum <2;
My question is that when rownum will be executed?
1. First all the data according to where clause will be fetched and rownum is assigned and then rownum will be executed on that data.
2. First complete data will be fetched, ROWNUM will be applied and then WHERE clause will be executed along with ROWNUM clause.
I am having a particular SQL, i want to know the frequency/count of execution of this particular sql in my prod DB. how can i get this.
I get the sql in v$sql but didn't get the same record in dba_hist_sqlstat view.
DB=10.2.0.5
OS=RHEL 3
Im not sure of this, but seeing different plans for same SQL.
select sql_text from v$sqlarea where sql_id='92mb4z83fg4st'; <---TOP SQL from AWR
SELECT /*+ OPAQUE_TRANSFORM */ "ENDUSERID","LASTLOGINATTEMPTTIMESTAMP","LOGINSOURCECD","LOGINSUCCESSFLG",
"ENDUSERLOGINATTEMPTHISTORYID","VERSION_NUM","CREATEDATE"
FROM "BOMB"."ENDUSERLOGINATTEMPTHISTORY" "ENDUSERLOGINATTEMPTHISTORY";
[code]....
I have the where cluase as below , I would like to know how does oracle decides which one to execute first,
WHERE S.PERSPECTIVE='S'and s.shipment_gid=sb.shipment_gid AND SB.BILL_GID=CBIL.INVOICE_GIDand inv.invoice_gid=cbil.invoice_gid AND S.SOURCE_LOCATION_GID=LC.LOCATION_GID and l.location_gid=lc.location_gid AND TRUNC(cbil.insert_date)=TRUNC(tc.tesco_cal_date)AND(lc.location_gid='N' OR lc.corporation_gid='TESCO.10719')AND s.source_location_gid=lc.location_gid AND tc.tesco_year='2013' AND tc.tesco_period=6AND tc.tesco_week_number=23
when i run a form no information shows up until i click execute query... i need the info to be their automatically to browse with the previous and next button
View 3 Replies View Related