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 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
-- Create a table type of the table you want TYPE tbl_test IS varray(100) of VARCHAR2(30);
-- Function that will return the table type FUNCTION fnc_test RETURN tbl_test;
-- End package END;
CREATE OR REPLACE PACKAGE BODY pkg_test AS FUNCTION fnc_test RETURN tbl_test IS -- type table_name_va is varray(100) of VARCHAR2(30); -- Variable of the type tbl_test
[code]...
But i am having problem calling this to test it.
declare TYPE tbl_test IS varray(100) of VARCHAR2(30); var_tbl_test tbl_test; begin var_tbl_test:= pkg_test.fnc_test;
I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY ACCOUNTING 8750 RESEARCH 10875 SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
For procedures, we need to grant EXECUTE privilege.
For example:
grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.
I am getting a (PL/SQL: ORA-00903: invalid table name) compile error in a procedure using a merge statement. I have seen many examples using this technique and am at a loss as to why I can't compile.
the pipelined function is:
FUNCTION f_crcli_pipe(pi_source_data IN sys_refcursor, pi_limit_size IN PLS_INTEGER DEFAULT pkg_crcli_variables.c_cursor_limit_def) RETURN CRCLI_AA PIPELINED PARALLEL_ENABLE(PARTITION pi_source_data BY ANY) IS
[code].....
the error is pointing to the TABLE function in the USING clause of the merge statement.
I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.
My code (sample): ----------------- DECLARE type r_parameters is record (cd_query cons_query_param.cd_query%type, cd_usuario cons_query_user.cd_usuario%type, nr_param cons_query_param.nr_param%type, vl_param varchar2(2000),
We have a daily batch job executing a oracle-plsql function. Actually the quartz scheduler invokes a java program which makes a call to the oracle-plsql function. This oracle plsql function deletes data (which is more than 6 months) from 4 tables and then commits the transaction.
This batch job was running successfully in the test environment but started failing when new data was dumped to the tables which happened 2 weeks ago (The code is supposed to go into production this week). Earlier the number of rows in each table was not more than 0.1 million. But now it is 1 million in 3 tables and 2.4 million in the other table.
After running for 3 hours, we are getting a error in java (written in the log file) "...Connection reset; nested exception is java.sql.SQLException: Io exception: Connection reset....". When the row-counts on the tables were checked, it was clear that no record was deleted from any of the tables.
Is it possible in oracle database, for the plsql procedure/function to be automatically terminated/killed when the connection is timed out and the invoking session is no longer active?
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST ( V_COUNT OUT NUMBER ) AS V_FOO NUMBER(2,0); BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.
a) basic definitions (work and are ok)
CREATE OR REPLACE TYPE eventtype AS OBJECT ( relevance NUMBER, bid1 NUMBER,tid1 NUMBER,prob1 NUMBER, bid2 NUMBER,tid2 NUMBER,prob2 NUMBER, bid3 NUMBER,tid3 NUMBER,prob3 NUMBER, bid4 NUMBER,tid4 NUMBER,prob4 NUMBER, bid5 NUMBER,tid5 NUMBER,prob5 NUMBER, bid6 NUMBER,tid6 NUMBER,prob6 NUMBER ); CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype; /
b) The following call works (but is not exactly that what I want):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal'; / SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;
c) The following call doesn't work (but should have the intended semantics):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal'; / SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;
d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.
The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.
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]....
I am trying to call a shell script through external procedure. To summarize the steps that I followed:
Placed the library file,i.e., the .so file in bin folder of the database server. I have not compiled the .c file and created this. I have taken this from another application.
Then I logged into the sqlplus. Created one library pointing this .so file.
create or replace library ext_proc as '/ngs/app/ivndrptd/bin/execute_cmdlib.so';
After this created the external procedure. This got compiled without any errors.
SQL> create or replace procedure ext_proc_wrapper(in_script IN varchar2) as external library ext_proc NAME "execute_cmd" parameters (in_script string); / Procedure created.
I am trying to execute a script test.ksh which contains a touch command to create a test.txt file.
However irrespective of the procedure running successfully the script is not getting called. I tried several ways of calling the script which are as follows
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
