SQL & PL/SQL :: Error While Execution
Jul 23, 2010
When 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
View 2 Replies
ADVERTISEMENT
Jan 17, 2011
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 Related
Sep 27, 2013
I have a package with pipelined function in it. using the below stmt to execute the function in Oracle 11g.
select * FROM table(Test_PKG.Test_PIPELINED ('A','--N/A--','1/1/2010','1/1/2011'));
Throwing an error: invalid month error.
CREATE TYPE WBS_ROW IS OBJECT
(
Product VARCHAR2 (100),
DESC VARCHAR (1000),
RecDATE DATE);
[code]....
View 12 Replies
View Related
Sep 13, 2011
I created one Update or Insert trigger on a table.When I executing the update statement it is giving error
Ora-04091 : Table gtest is mutating, trigger/function may not see it.
ORA-04088: error during execution of trigger test_email_change
ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.
View 2 Replies
View Related
Aug 9, 2011
I create this trigger:
CREATE OR REPLACE TRIGGER T_HST AFTER INSERT
On HST
FOR EACH ROW
DECLARE
Begin
INSERT INTO HST (EMP_NO, START_DATE )
SELECT EMP_NO, SYSDATE
FROM HST
WHERE EMP_NO = :NEW.EMP_NO;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/
when I insert a new record in table HST I get this error:
ORA-04091: table HST is mutating, trigger/function may not see it
ORA-06512: at "T_HST", line 3
ORA-04088: error during execution of trigger 'T_HST'
What I wrong in this trigger?How can I modify it?
View 7 Replies
View Related
Apr 30, 2013
i create this trigger to lock inserting transaction for any data greater than 30-04-2013 on a certain table
CREATE OR REPLACE TRIGGER INVALID_DATE_VALUE BEFORE INSERT or UPDATE on ra_cust
FOR EACH ROW
BEGIN
IF :NEW.TRX_DATE >'30-APR-2013'
THEN
-- RAISE_APPLICATION_ERROR('ora-0000','DATE CANNOT FALL IN RANGE...Invalid Date');
RAISE_APPLICATION_ERROR(-20000, 'IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN ''30-04-2013''');
END IF;
END;
and this trigger created successfully
when i try to insert data in the table ra_cust even in a range less than 30-apr-2013
i got this error
ORA-20000: IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN '30-04-2013'
ORA-06512: at "ARASK_HAGAR.INVALID_DATE_VALUE", line 5
ORA-04088: error during execution of trigger 'ARASK_HAGAR.INVALID_DATE_VALUE'
View 2 Replies
View Related
Jun 27, 2013
java.sql.SQLException: ORA-04054: database link GMAIL.COM does not exist
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1615)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1580)
at com.jdbc.pack.Lab1.main(Lab1.java:31)
View 7 Replies
View Related
Mar 27, 2013
Environment is CentOS 6.3, 64-bit OS
I get the following error when I use the universal installer (runInstaller.sh)
OUI-10150: Error: A runtime execution error occured while setting s_digcfgnaminglabel isidnull in compnent oracle database 11g 11.2.0.1.0. Installation cannot continue for this component.
I have check my /etc/hosts entries and I believe my jre paths are correct since I get the JAVA windows.
View 2 Replies
View Related
Apr 4, 2013
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.
View 4 Replies
View Related
Mar 12, 2013
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 Related
May 5, 2011
In 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 Related
Jul 7, 2010
I 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.
View 3 Replies
View Related
Jul 30, 2012
We 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.
View 3 Replies
View Related
Sep 3, 2013
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 Replies
View Related
Apr 21, 2011
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.
View 6 Replies
View Related
Jul 22, 2010
Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)
I am using Oracle 9i.
View 2 Replies
View Related
Jul 8, 2010
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 Related
Mar 16, 2010
I want to know that is there any way to know the execution time of a sql query.
View 1 Replies
View Related
Apr 26, 2012
When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies
View Related
May 2, 2011
I'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].....
View 33 Replies
View Related
Jan 13, 2011
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
View 6 Replies
View Related
Sep 26, 2010
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.
View 7 Replies
View Related
May 18, 2011
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?
View 5 Replies
View Related
Oct 8, 2010
[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]....
View 3 Replies
View Related
Mar 6, 2012
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]....
View 3 Replies
View Related
Jul 26, 2012
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.
View 8 Replies
View Related
Jun 18, 2012
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.
View 3 Replies
View Related
Jan 14, 2013
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]....
View 5 Replies
View Related
Oct 22, 2013
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
View 9 Replies
View Related
Apr 13, 2013
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