Error While Calling External C Function From Oracle?
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
ADVERTISEMENT
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
Jun 18, 2013
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?
View 13 Replies
View Related
Nov 26, 2006
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
SQL> exec ext_proc_wrapper('sh /ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.ksh');
PL/SQL procedure successfully completed.
SQL>
View 5 Replies
View Related
Aug 12, 2011
I have a problem here I am using this script to create an external table
CREATE TABLE SUPP_GIM.T_CSV_DATA
(
UPC VARCHAR2(30),
AGEIND VARCHAR2(30),
DEPT VARCHAR2(30),
CREG VARCHAR2(30),
SUPP VARCHAR2(30),
PRICE VARCHAR2(30),
[code]....
I have created the directory pointing on 'C:data'...And loaded a comma delimited CSV file in there...
- Checked the csv permissions ther are set to 'everyone'
- Checked the previladges of the directory, they are set to 'READ/WRITE'
But when I issue a select statement against the exte table I get an error '
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file input.csv in DUMP_TXT not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1'
View 3 Replies
View Related
Feb 27, 2011
When I replace the params manualy with dates in format of:
'27-feb-2011 08:00:00'
I get the real output.
But when I call the function with the TEST button in the PLSQL I don't get any output (Empty table).
Here is the original function (Bad output) and attaching a file with manuly added dates (Good output):
FUNCTION WhatRoomsTaken(dStartTime date, dEndTime date, dEventDate date)
RETURN Genrefcursor IS
Retval Genrefcursor;
BEGIN
open Retval for
[code].....
Why do I need to change in order to get the good output by calling the function with params?
View 5 Replies
View Related
Aug 1, 2011
for my r&d purpose i create function which call procedure,
create table test_tab
(a NUMBER(10),
B NUMBER(10),
C NUMBER(10)
[Code]...
but it will gv me error ORA-06575:FUNCTION IS IN INVALID STATE that means function created with compilation error.
but when i complied fucntion it doesnt gv me any error.
View 15 Replies
View Related
Mar 9, 2010
I have this following pakg
CREATE OR REPLACE PACKAGE pkg_test AS
-- 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;
[code]...
View 2 Replies
View Related
May 8, 2010
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
View 4 Replies
View Related
Sep 6, 2012
11.2
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.
Is it SELECT or EXECUTE ?
grant select on SCOTT.GET_EMPIDS to john;or
grant execution on SCOTT.GET_EMPIDS to john;
View 2 Replies
View Related
Dec 2, 2010
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.
View 7 Replies
View Related
Jun 2, 2011
I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?
View 4 Replies
View Related
Jul 23, 2010
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),
[code].....
View 5 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 9, 2013
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: .(*%&................
where my mistake is?
View 7 Replies
View Related
Oct 20, 2011
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.
View 1 Replies
View Related
Jan 28, 2011
I have an external table. The table gets created successfully. Once the table is created when I try to access it, I get the following error :
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /cmpnt/dev/test/ADE_TEMP_4992.log
The default directory is valid and does not have any issues. The IP address of my DB server and the server from which I am connecting to the DB are different. Is this is the issue ? However , all SQL queries are working fine except this one.
View 1 Replies
View Related
Jul 5, 2012
SQL>
create table oldemp8
(
fname varchar2(30),
salary number(6),
job_id varchar2(20)
)
[code].......
Table created.
SQL> select * from oldemp8;
select * from oldemp8
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "(": expecting one of: "comma, defaultif,
nullif, )"
KUP-01007: at line 7 column 16
ORA-06512: at "SYS.ORACLE_LOADER", line 19
SQL>
what is the syntax error in the above command. I place the notepad file properly.i create external table before many time but cant find any this type of error.
os windows xp 2000. oracle 10g (10.2.0.1.0)
View 7 Replies
View Related
Jun 4, 2013
In a package I have one procedure "setscores_ram_bulk" with collection varaiavles.And other two procedures are
"UpdateRelatedOrders" and "SetGeoFraudScore"
with out collections.I am calling collection procedure "setscores_ram_bulk" in non collection procedures
"UpdateRelatedOrders" and "SetGeoFraudScore".
I am getting the below error.PLS-00306:wrong number or types of arguments in call to 'SETSCORES_RAM_BULK' .I am sending my code.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.INTERNAL_SCORING_setscore_Bulk
IS
TYPE rec_setscores IS RECORD
(
pBUID score.buid%TYPE,
OrderNum score.order_num%TYPE,
ScoreType VARCHAR2(100),
p_Score score.velocity_score%TYPE
);
[code]....
View 19 Replies
View Related
Sep 27, 2013
It's been some time since I've written any PL/SQL, I'm getting Found 'CURSOR' Expecting: External Language for line 76 when I try to compile? :
1 create or replace PACKAGE pa_user_maint
2 AS
3
4
5 -- ------------------------------------------------------------------------------------
6 -- Exceptions
7 -- ------------------------------------------------------------------------------------
8
9 -- User Exists
10
11 ex_existingUser exception;
12 PRAGMA EXCEPTION_INIT (ex_existingUser, -01920);
13
[code].......
View 1 Replies
View Related
Jun 27, 2013
I'm calling a web service with HTTPS protocol from a pl/sql program and i'm using the UTL_HTTP package for do this.
I have a Oracle DB installation 11g Enterprise Edition Release 11.2.0.1.0 on my personal computer with a Windows XP OS, and when i run the code it works fine and returns the result I expect. But, when i run the same code on the Oracle DB of my work (Oracle 10g Enterprise Edition Release 10.2.0.4.0 and OS HP-UX) it fails and give me an error, i think that the error has to do with the configuration of the wallet and the certificates in it, because i consume the WS from Os prompt using the command WGET and works fine and i test consuming a WS with HTTP no secure and works fine too.
This is the code and the result in the Oracle DB 11g on my computer:
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as oe@oRCL
SQL>
SQL> declare
2 v_debug_mode boolean := false;
3 v_req utl_http.req;
[Code]....
PL/SQL procedure successfully completed
SQL>
And this is the code(Is the same, the only difference is the path of the wallet) and the error resultant:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as gpacheco@acseld
SQL>
SQL> declare
2 v_debug_mode boolean := false;
3 v_req utl_http.req;
[Code].....
View 4 Replies
View Related
Jun 5, 2012
getting error frm 21011 , ora -65020 , while calling forms from menu , the forms are also passing parameters between forms 1 and forms 2 .
The following code is in menu item .
declare
uname varchar2(30);
cname varchar2(40);
[code]....
View 1 Replies
View Related
Aug 6, 2010
i created the External Table using the script below.
CREATE TABLE EXT_ST_FINANCEIRO_REAL (
DT_DATA NUMBER,
TIPO NUMBER,
ENTIDADE NUMBER,
VALOR Varchar2(40))
[code]....
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "missing" expecting on of: "column, exit,("
KUP-01007: at line 6 column 1
ORA-06512: at "SYS.ORACLE_LOADER", line 19
View 3 Replies
View Related
Sep 19, 2012
create or replace package body test_exp_pkg as
procedure l_test_exp (errcode out Varchar2, errm out varchar2)
as
l_rec zt574%rowtype;
begin
[code]]....
output:
---------
anonymous block completed
in package init
caught in l_test_exp-1422ORA-01422: exact fetch returns more than requested number of rows
caught in l_test_exp errcode and errm set to -1422sqcORA-01422: exact fetch returns more than requested number of rows
caught in test_exp1User-Defined Exception
caught in others errcode and errm
View 8 Replies
View Related
Jan 3, 2013
Iam facing another issue with Skillbuilder modal page(for those who have used this plugins). Ive the CREATE part working well the problem is with EDIT part to edit records via the link i applied all the steps and clicking the edit link pops up the page but the issue is for any clicked row the parameter is not being passed and seleceted record doesnt show on the pop up page where could the problem be? though if i set it back to normal link created by the wizard the edit link works well and shows that record on the editable page.
Apex 4.1.1/oracle 11gR2 ; issue happens in FF & IE
View 3 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
Jan 23, 2013
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
View 5 Replies
View Related
Jan 22, 2013
i am trying to create a external table.Table is getting created but there is no data.While checking the log file, its saying
KUP-04021: field formatting error for field CUST_CODE
KUP-04026: field too long for datatype
KUP-04101: record 1 rejected in file /data/mmi_mig_data/PCOM_CUSTOMER_1.csv
KUP-04021: field formatting error for field CUST_CODE
KUP-04026: field too long for datatype
KUP-04101: record 2 rejected in file /data/mmi_mig_data/PCOM_CUSTOMER_1.csv
But according to me in CSV file everything looks to be okay.External Table:
CREATE TABLE ET_PCOM_CUSTOMER(CUST_CODE VARCHAR2(12), CUST_TITLE VARCHAR2(12),
CUST_NAME VARCHAR2(240), CUST_MIDDLE_NAME VARCHAR2(240),
CUST_FIRST_NAME VARCHAR2(240), CUST_LAST_NAME VARCHAR2(240),
CUST_GENDER VARCHAR2(12), CUST_MARITAL_STS VARCHAR2(12),
CUST_NIC_NO VARCHAR2(60), CUST_ADDR_01 VARCHAR2(240),
[code]...
View 3 Replies
View Related
Jun 6, 2011
How i use external tables as a form datablock in oracle oracle forms 10g an i use anyway external tables in a form datablock ?
View 3 Replies
View Related
Dec 15, 2010
Encountering an issue with an Oracle external table. We get the following error when we load a particular file in this table:
ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
We have narrowed this down to a text field in the file that contains the following text (text obfuscated):
XXXXXXXX ł XXXXXXXXXXXXXXXXXXXXXXXXXXXXX
This is nominally 40 characters long, which matches the maximum field size, but it is being rejected because it appears the 'ł' character is causing Oracle to interpret the length as 41 characters instead. If I remove a X then the file loads without issue.
We tried this in a new schema we created where we added the same table and used the same file. There was no problem at all. The oracle database has the following settings:
NLS_CHARACTERSETUTF8
NLS_NCHAR_CHARACTERSETUTF8
NLS_LENGTH_SEMANTICSBYTE
NLS_LANGUAGEAMERICAN
The table is defined as follows:
CREATE TABLE XXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXX_INTERFACE
(
XXXXXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXX VARCHAR2(40 CHAR),
[code]........
We tried adding the following attributes but they did not seem to make any difference:
CHARACTERSET UTF8
STRING SIZES ARE IN CHARACTERS
View 6 Replies
View Related
