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
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]....
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.
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]........
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
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.
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.
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
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]........
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
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
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.
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.
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:
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.
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;
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?