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]......
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]...
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]....
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
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.
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.
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]....
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
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]........
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.
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]....
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?
I cerated a function with two date parameters. I am using these parameters in query. The function created successfully.But when i executed this function, it throw following error:
ORA-01840: input value not long enough for date format in function
I searched on internet, but not found the proper solution. n with example.Table column is varchar2 type and values store in 'RRRR/MM/DD' format.
I have function that accepts 2 dates namely sDate and eDate i.e start and end dates. See the declaration part of the function:
startDate eab.eod_date%type;
endDate eab.eod_date%type;
begin
startDate := to_date(sDate,'DD-MON-YY');
endDate := to_date(eDate,'DD-MON-YY');
end
When I called the function it gives me the following error: ORA-01843: not a valid month..And I tried to simulate the process, the below code also show the same error:
select TO_DATE(to_date('13-DEC-09', 'dd-MON-yy', 'nls_date_language=english'),'DD-moN-YY') from dual;
how I can check that error. I need to pass dates to my function.
I have error ORA-00904: "USERNAME" : identificateur non valide. What is the problem ?
My command impdp :
impdp datapump/password@%ORACLE_SID% DIRECTORY=datapump schemas=gcom INCLUDE=PROCEDURE remap_tablespace=gpao_indx:indx remap_tablespace=gpao_data:data DUMPFILE=%ORA_DUMPFILE% LOGFILE=Imp_%annee%%mois%%jour%%hh%%min%%sec%_%ORACLE_SID%.log
The result :
;;;
Import: Release 10.2.0.4.0 - Production on Jeudi, 15 Septembre, 2011 17:47:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connecté à : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Table maître "DATAPUMP"."SYS_IMPORT_SCHEMA_29" chargée/déchargée avec succès
[Code]....
I having issue when i try to use CLOB as varchar2 is not enough in my case. I'm developing function column in oracle report. I'm using developer 6i. I get error function return must char?
How I can use CLOB in oracle report?
function CF_RnoFormula return Char is
--v_release_num CLOB;
v_release_num varchar2(32767);
begin
FOR rec IN
[code]........
I have 2 tables used in this problem: ODETAILS and ORDERS.
ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST
UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);
In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.
my java program i am executing a pl/sql function using callable statement i checked everything in the function and the function is created without compilation errors in sql*plus environment, and i gave all DBA permissions to the user, but i got the error like below
java.sql.SQLException: ORA-06550: line 1, column 28: PLS-00201: identifier 'NAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
and i wrote the function like below for the below table
create table student(regno number(6),name varchar2(15),dob date,phone number(10),
address varchar2(30),cat varchar2(2),password varchar2(12),hallno number(6),
fee_paid varchar2(3),constraint student_pk primary key(regno))
create table fee(regno number(6), branch varchar2(15),amount number(4), ddno number(12), fee_paid varchar2(3),
dddate date, dd_received_date date default sysdate, constraint fee_pk primary key(regno))
[code]...
and the java program is like below
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnection(dbURL,dbUserName,dbPassword);
CallableStatement st=connection.prepareCall("{?=call
[code]...
I am using lag function to display values like below:
order details date starttime
----------------- -------- --------------
main order 1 07/10/12 06:00am
line 1 07/10/12 06:21am
line 2 07/10/12 06:31am
main order 2 07/11/12 07:00am
line 1 07/11/12 07:01am
line 2 07/11/12 07:02am
the data displays correctly when i use lag function except that the line 1 details are never getting displayed ie first line under every order does not get displayed? is using lag function in this case correct?
I have written a query which basically retrieves id and created date. IF i put MAX function it is returning id which have max created date. But if i use min function this query is not providing id with min created date,its not returning any rows.
SELECT To_char(OSH.osh_id),
OSH.osh_created
FROM tn_order_status_history osh,
tn_order_status_type ost,
tn_orderline_product op
[code]..........
Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;
[code]
to_number(to_char('a_date','YYYY'))
[code]
There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.
if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.
I have the following function:
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.
can we use htp package in function?
something like
if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;
Here is the test-table creation script:
CREATE TABLE TEST1 (AGG_DATE DATE, COL1 NUMBER(9), COL2 NUMBER(9), COL3 NUMBER(9));
Here is the test-data population script:
insert into TEST1 (AGG_DATE, COL1, COL2, COL3)
values (to_date('01-01-2012', 'dd-mm-yyyy'), 1, 1, 1);
[code]....
The problem is when I wrote an analytical query, it is giving the BEGIN_DATE and END_DATE by taking all the partition values together and so instead of the values above, it is creating an answer as follows:
Wrong Dataset
BEGIN_DATEEND_DATECOL1COL2COL3
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/4/20121/11/2012222
1/4/20121/11/2012222
[code]....
Only the last row is correct. What can I do to get the right answer as I know am falling short? Here is my current query:
SELECT MIN(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) BEGIN_DATE,
MAX(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) END_DATE,
COL1,
COL2,
COL3
FROM TEST1;
I have an oracle package that i am using to search for a string in a blob entry. I compiled the package and the package body in one environment, it had no errors, when i execute, i get my results.I went ahead and created the same package and function in another environment and it fails by giving me the below error
ORA-06503: PL/SQL: Function returned without value ORA-06512: at "SYSTEM.IMPACTUS_PCODE", line 158 for sysadm
I have used this on other environments often and have never had an issue.
when i press the save button in my application the record will be saved but the value in my "display item" wont be refreshed. when i minimize the form designer and maximize it again, the "display item" is refreshed.
so i am searching for a "refresh" function
SYNCHRONIZE;
redisplay;
these functions doesn't work.
I have a table with date column (16/06/1996 15:03:59) as value in the displayed format. As I have tried with the below query format I could not able to retrieve the
records. select * from <table> where DATE_INSERT=SYSDATE;
Retrieve the rows for the tables ?
Is it possible to use WITH statement in an Oracle procedure or function?
I've had problems compiling a procedure with "WITH" statement.
We have a function that is called in various other PL/SQL packages, and performance has always been very good. On 29th Sept we upgraded our db to 10.2.0.5.0 and since then, a package that calls the function has gone from ~4mins, to ~2.5hrs to run.
In PL/SQL Developer, a simple select that calls the function has gone from ~0.5secs to retrieve the first 100 rows, to ~12secs. I ran a profile of the main package, which highlighted the where the bottleneck was (a fetch from an explicit cursor). Running an explain plan on the cursor SQL doesn't really show up anything untoward.
However, I found that if I subtly changed the cursor SQL, (so that it did the same thing, but was written differently), it fixed the performance problems.
where ade_start_date between cpDate-cpDays and cpDate-1
/*and ade_start_date < cpDate
and ade_start_date >= (cpDate-cpDays)*/
From this, we thought that there may have been a bad cached execution plan which the change of code forced a recalculation of. However, about 2 hours later, the changed code ran slowly again. So a further subtle change was made, which fixed the issue again. Until this morning, when it was running slowly again.
This feels like it is CBO/stats related potentially, but is out of my area of knowledge unfortunately. We have our DBA investigating this, but there may be things I can test to narrow down the possibilities in the meantime.
I am using REGEXP_LIKE function, but it is giving me error like this.
SQL>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
SQL>select regexp_like(testcol,'^ab[cd]ef$') from test;
select regexp_like(testcol,'^ab[cd]ef$') from test
*
ERROR at line 1:
ORA-00904: "REGEXP_LIKE": invalid identifier
But in Oracle documentation it is given under heading of 10g. Does Oracle 10g supports this function?