Perform Operation With Case Function - Getting Error?
Apr 3, 2012
I want to perform some operation with case statement. But I am confusing with ora 00932 error. My question is what data type should I use while performing case function?
SQL> select * from samp;
NAME EMPID SALARY DEPT
---
sony 10680 8200 sap
bala 10708 4300 .net
sam 10600 9000 oracle
chris 10655 5500 java
rose 10487 8700 oracle
[code]....
My big question is
different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
// just I am trying to perform basic operation. why oracle didn't support?
View 1 Replies
ADVERTISEMENT
Oct 8, 2013
Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;
[code].....
View 27 Replies
View Related
Aug 9, 2011
Calling function
select PACK.MAIN('blah') from dual
generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245
Unfortunately the Body is not accessible to see.The spec of the function is:
FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:
var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar
But this generates: ORA-01008: not all variables bound
View 3 Replies
View Related
Dec 9, 2010
Attempted to execute the Procs below with
Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error:
ORA-14551: cannot perform a DML operation inside a query.
The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update
Package Definition
create or replace
PACKAGE OTMP_TCIS_RS AS
--1 PROCEDURE Get_UserInfo
PROCEDURE Get_UserInfo(
o_OutCode OUT INT,
i_language IN VARCHAR2);
FUNCTION Get_UserInfo(
i_language IN VARCHAR2)
RETURN NUMBER;
[code]....
View 5 Replies
View Related
Jun 16, 2010
I have a function declared as PRAGMA AUTONOMOUS_TRANSACTION.
If i execute this function everything is fine.
If I call this function from a remote database, I have this error message:
"ORA-14551: cannot perform a DML operation inside a query".
select function('parameter') from dual;
Result: "OK"
select function@dblink1('parameter') from dual;
Result: "ORA-14551: cannot perform a DML operation inside a query"
View 14 Replies
View Related
Dec 2, 2010
Is it possible to perform any operation using oracle like addition and division in csv file before loading data in oracle. and after the operation changes must save.
Is it possible or not.
View 2 Replies
View Related
Jan 10, 2011
i have Oracle 10g data guard set up on windows environment.....i need to know the what are the right steps to perform failover in case the primary database gets fail.
View 4 Replies
View Related
Jun 26, 2013
Below is my import command for importing specific function from export file but iam getting below errors
impdp system/PASSWORD schemas=TNC6 directory=dumpdir dumpfile=FULL01-02-2011.dmp logfile=IMP.log include=FUNCTION:"IN ('TNC_IS_NUMBER')"
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
View 4 Replies
View Related
Sep 13, 2012
I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary)
from employee e join department d
on e.deptno=d.deptno
where d.deptname=upper('finance')
group by d.deptno, d.deptname;
[code]...
View 3 Replies
View Related
May 20, 2013
first_tbl
First_dt second_dt Item
01-JAN-09 01-JUL-09 item1
01-APR-09 01-DEC-09 item1
01-JUL-09 31-DEC-09 item1
15-DEC-09 10-MAY-10 item1
20-MAR-12 10-AUG-12 item2
31-JUL-12 15-DEC-12 item2
01-APR-09 31-DEC-09 item2
second_tbl
Start_dt end_dt item flag
01-AUG-09 01-NOV-09 item1 null
02-NOV-09 01-DEC-09 item1 null
01-DEC-09 15-DEC-09 item1 null
15-DEC-09 31-DEC-09 item1 null
01-JAN-10 10-MAY-10 item2 null
10-MAY-10 31-DEC-10 item2 null
01-JAN-12 15-MAR-12 item2 null
20-MAR-12 31-JUL-12 item2 null
I need o/p in such a way that,if first_dt or second_dt in between start_dt and end_dt, i need to make the flag as y(item is the common join) other wise 'N'.
I was able to make the flag as'Y' but not 'N'
Select A.first_Dt, A.second_Dt, B.Start_Dt, B.End_Dt, A.item, B.item,
Case When B.falg is null
Then 'N'
Else 'Y'
End
From firsttbl A,second_tbl B
Where
A.item=B.item
and
((A.first_Dt>=B.Start_Dt And A.first_Dt<=B.End_Dt)
Or
(A.second_Dt>=B.Start_Dt And A.second_Dt<= B.End_Dt));
View 13 Replies
View Related
May 13, 2010
I am getting the same error on a form I am setting up under version 10.1.2 Form Builder. But when I press Shift-F1 I get a Getting Started with Internet Explorer popup. I am running the form under OAS. I have a parent/child relationship between 2 tables that I query on the form.
A copy of the fmb file is attached to access it.
View 11 Replies
View Related
Mar 3, 2013
I am using oracle 11G database,I have to check length of name column value from employee table and if length(name) > 39 then value should be substr(name,0,39) else value should be name only. i tried below code
select CASE when length(name) > 39,substr(name,0,39)
else name
END
from employee but its not working ..can I do this using decode too ? ,,which one would be better or this is not a right way ?
View 3 Replies
View Related
Jun 20, 2013
I am using: Desktop / Discoverer 4.1 / Windows XP.
I am attempting to add a new calculated column and have had some success with the CASE function but need to add additional criteria.
What I have that works is:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' THEN Total Spend Plus Commit ELSE 0 END)
What I need to add are a few additional criteria. I attempted and failed with a few variants of this:
SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' AND Capitalizable = 'Y' AND
Task Owing Company = '534' OR '915' THEN Total Spend Plus Commit ELSE 0 END)
The three criteria points that I am looking to includea are:
•Expenditure Type = 'Supplier Rebates'
•Capitalizable = 'Y'
•Task Owing Company = '534' OR '915'
View 8 Replies
View Related
Feb 28, 2011
I am trying to use on trigger to get the code of an DDL operation, I use the code supplied on orafaq (orafaq.com/scripts/plsql/auditdll.txt) as base code and I added a collumn objcode and a call to DBMS_METADATA.GET_DDL to save the object code, however I am receiving an Oracle error ORA-00604.See code below.
This error is raised when I try to create a new view on the schema, on objects that I already have and I changed something usually the error did not happens, how to get the object code of one object when it is created, or changed.
I am using Oracle 11g
SQL> select banner
2 from sys.v_$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE11.2.0.1.0Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
cl scr;
DROP TRIGGER audit_ddl_changes
/
DROP TABLE dll_audit_log
[Code]....
CREATE FORCE VIEW V3 AS SELECT * FROM ALL_OBJECTS
ORA-00604: error occurred at recursive SQL level 1
ORA-31603: object "V3" of type VIEW not found in schema "XXISV_V11R01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1
ORA-06512: at line 2
View 1 Replies
View Related
Jun 26, 2012
One of our production database we are getting error "TNS:operation timed out" from unknown port in alert logfile. Need to check why TNS error is occurring to these unknown port as our listener is running on default 1521 port. Also to inform that host=192.191.118.24 itself is the same database hosting server IP.
TNS-12535: TNS:operation timed out
TNS-12535: TNS:operation timed out
ns secondary err 12606
ns secondary err 12606
nt main err 0
[code]......
View 3 Replies
View Related
Oct 24, 2013
select iloan_code,inst_due_date,paid_flag,late_fee,case late_fee when sysdate-inst_due_date between 1 and 10 then 10 when sysdate-inst_due_date > 10 and late_fee <>10 then 5 when sysdate-inst_due_date > 10 and late_fee = 10 then 15 else 0 end as new_late_fee from st_il_schedule where paid_flag='N'; i am getting error
View 3 Replies
View Related
Aug 12, 2010
I have deinstalled Oracle database, ASM and oracle software. I installed the oracle software, ASM and database. I have seen the error in the ASM trace called
Errors in file /opt/oracle/admin/+ASM/bdump/+asm_gmon_10518.trc:
ORA-29702: error occurred in Cluster Group Service operation
ORA-29702: error occurred in Cluster Group Service operation
View 11 Replies
View Related
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Oct 16, 2013
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME)
WHEN 'ALERT' THEN
SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A
WHERE A.ALERT_ID = IN_PARENT_NODE_ID;
INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
[code]....
View 23 Replies
View Related
Jul 5, 2012
trying to do impdp but getting error
UDI-22303: operation generated ORACLE error 22303
ORA-22303: type "SYS"."DBMSOUTPUT_LINESARRAY" not found
ORA-00600: internal error code, arguments: [kokaocr], [], [], [], [], [], [], [], [], [], [], []
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT"
ORA-06512: at line
View 2 Replies
View Related
Nov 15, 2010
Oracle version - 10.1.0.4 OS - HP-UX
In my alert log I am getting the following error quite repeatedly; My application hangs, and have to restart the instance.
CODEORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 6
View 4 Replies
View Related
May 11, 2011
I'm receiving these errors when I try to run my function. The object of my function is to "This function will format the employee's name in the following format:
if the employee has a middle initial: John P. Smith
If the employee does not have a middle initial: John Smith"
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: Encountered the symbol "P_INITIAL" when expecting one
of the following:
:= ) , default character
The symbol "," was substituted for "P_INITIAL" to continue.
4/1 PLS-00103: Encountered the symbol "P_LNAME" when expecting one of
the following:
:= ) , default character
The symbol "," was substituted for "P_LNAME" to continue.
[code]....
And that is the syntax I currently have.
View 6 Replies
View Related
Apr 16, 2013
I'm using the following code for a function which is working fine in Oracle 9i, but throwing error like ORA-06512 (Numeric value error) in Oracle 11g.
CREATE OR REPLACE FUNCTION Decrypt(toconvert VARCHAR2) RETURN VARCHAR2 IS
str_original VARCHAR2(30);
str_new VARCHAR2(30);
sub_str VARCHAR2(1);
j NUMBER;
[code]......
View 17 Replies
View Related
Apr 7, 2011
I tried to use external C procedure from the database and i did all required steps as below.
1. create a C program
2. compile and link the C program
3. copy it to the Oracle_home/bin directory
4. configure listener.ora and tnsnames.ora files
listener.ora
=======
callout_listener =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP) (HOST = ip_address)(PORT = 1521)
[code]...
View 4 Replies
View Related
Jun 22, 2011
error call a function which alter sequence value..I created a function to reset a sequence value as below
CREATE OR REPLACE FUNCTION rebuildSequence
return number
As
l_csmsgid1 PLS_INTEGER;
l_csmsgid2 PLS_INTEGER;
l_val PLS_INTEGER;
plsql_block VARCHAR2(500);
[code]....
I verified and executed the pl/sql block without a problem. but when only put into a fuction and call it, got then error.
View 7 Replies
View Related
Apr 16, 2010
I have wrote a Stored Procedure Function that get all the rows from a Staging Table and assigns it to a CLOB and returns the CLOB. The issue is I'm getting the dreaded
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "F_CLOB_TEST", line 21
ORA-06512: at line 7
The error does not occur when I remove the 'PRC_ID', 'S_FIL_NAME' & 'exportDt' from the query. It works fine. The PRC_ID has data such as "700$702$7 05$706$707$708$709$710$711$712$713$714$715$294404$294405$294407$294408$294409$294410"
and S_FIL_NAM is the same for all columns - "SBENE_FILID810-2010-04-07-10.59.17"
The query returns 829 rows. Also I have to include a few more columns in the query which have data larger than the 'PRC_ID', but I have not included them here in the sample code, as this code by itself returns the ORA-6502 error.
create or replace
FUNCTION "F_CLOB_TEST" (job_id in Number)
return clob
is
c_clob clob;
[code]....
View 26 Replies
View Related
Jun 25, 2012
I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.
CREATE TABLE MIS.TEMP
(
ID NUMBER(8),
STOCKDATE DATE,
STOCKQTY NUMBER(10,2)
);
[code]....
SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual;
select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MIS.GETSTOCKQTY", line 11
View 12 Replies
View Related
Apr 11, 2013
i have ir report and one column value coming from function,when open the IR report its giving error..
IR report code
SELECT CS_ID ,CS_NAME, Util_func(PASS_ID) as "ALERT_DAYS" from "CSD_MASTERS" Function code
FUNCTION Util_func(PASS_ID NUMBER) RETURN number
IS
ALERT_DAYS NUMBER:=0;
BEGIN
[code]........
View 8 Replies
View Related
Feb 17, 2010
why function does no raise error no_data found when call in select statement.
1) create one function.
CREATE OR REPLACE function fn_sal(v_id NUMBER) RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp where empno=0;
RETURN v_sal;
END;
2) call it in select statement.
SELECT fn_sal(e.sal),e.* FROM emp e
select satement cause no error , it displayes all the records but null for the function cloumn.
why it not gives no_data_found error.
View 4 Replies
View Related
May 9, 2013
I am getting an ORA-00902: invalid datatype error when I am trying call the below function from a select statement. Here I am trying to get a table out from a function.
create or replace package pkg10
as
type tabletype1 is table of table1%rowtype
index by binary_integer;
function func1 return tabletype1;
end pkg10;create or replace package body pkg10
as
[code]....
View 2 Replies
View Related
