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

Error During Execution Trigger

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

SQL & PL/SQL :: Package Variables And Execution Error

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

SQL & PL/SQL :: ORA-04088 / Error During Execution Of Trigger Test_email_change

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

SQL & PL/SQL :: CREATE OR REPLACE - Error During Execution Of Trigger

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

SQL & PL/SQL :: Lock Inserting Transaction - Error During Execution Of Trigger

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

SQL & PL/SQL :: Error Occurred During Execution Of Simple JDBC Program In Eclipse

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

OUI-10150 / A Runtime Execution Error Occurred While Setting S_digcfgnaminglabel

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

Server Utilities :: Execution Of Oracle 11g Wrapped Procedure Gives Ora-00900 Error

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

Different AWR For Same Execution?

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

Longer SP Execution Time

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

Function And Procedure Execution

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

Modifying SQL Queries Before Execution

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

SQL & PL/SQL :: To Generate Procedure Execution LOG

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

Forms :: SQL - PL/SQL Execution Platform In 6i

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

SQL & PL/SQL :: Parallel Execution Of A Procedure?

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

SQL & PL/SQL :: Procedure And Function Execution

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

SQL & PL/SQL :: Execution Time Of A Query

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

SQL & PL/SQL :: Dropping A Procedure During Execution

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

SQL & PL/SQL :: Execution Completed With Warning

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

SQL & PL/SQL :: Different Result On Every Execution Of Query?

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

SQL & PL/SQL :: Execution Of Blocks In Oracle

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

SQL & PL/SQL :: Self Join Command Execution?

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

How To Stable Execution Plan Of SQL

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

SQL & PL/SQL :: Fix Execution Plan Using Hints

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

PL/SQL :: Execution Order In Case Of Row Num

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

History Of Frequency Of Execution Of A Particular Sql

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

Different Execution Plans For Same SQL In 10R2

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

PL/SQL :: Query Execution On Where Clause

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

How To Bypass Manual Query Execution

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







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