PLSQL With Java?
Aug 2, 2010
This is a daily batch job which captures end of the day changes from a set of Oracle 10G "Source" tables in (.exp) file and another overnight batch job will read from (.exp) files and Insert into Oracle 10G "Target" Tables. The .exp files are created by Oracle 10G Data Pump utilities.In future we will be migrating Target to Teradata. All the tables will be migrated. No change in table names or structure.
The plan of action to write the later bit of PLSQL (which read from .exp files and loads it into Target tables) into Java so that the programe can be re-used on teradata.
Questions
1) Can Java read the contents with in .exp files and insert into Oracle tables?
2) Can Teradata read the contents with in .exp files and insert into Teradata tables?
3) Will we be achieving any benefit if we execute DDL (Insert) using Java rather than PLSQL. Obvious one being Platform independent.
there is no transformation of data in flight between .exp to Target tables.
View 2 Replies
ADVERTISEMENT
Jun 19, 2007
I have imported a java class file that inturn gets content from a webservice. One of the functions on that imported class file is as follows ...
Funtion getcontent(A1 JOBJECT,A2 JOBJECT, A3 String) return JOBJECT ....
In my PLSQL package ...How do I invoke this class file ?
So far I have done this ...
Declare
abc ORA_JAVA.JOBJECT;
xyz ORA_JAVA.JOBJECT;
xxx VARCHAR2(25);
value ORA_JAVA.JOBJECT;
BEGIN
[code]...
I am getting an error saying the object type is wrong .... I would like to know how to assign a hardcoded value to the JOBJECT just to test before I continue.
View 20 Replies
View Related
Oct 2, 2013
We want to copy a file from a remote machine to the DB server. copy \<IP_ADRESS>SHARED_FOLDERsome_file.sql d:copied_file.sql
I've used Java and a wrapped it into a PL/SQL procedure so I can do :
exec run_os_command(p_command => 'copy \<IP_ADRESS>SHARED_FOLDERsome_file.sql d:copied_file.sql');
The file was not copied.
I have mapped \<IP_ADRESS>SHARED_FOLDER to x: for example and issued : exec run_os_command(p_command => 'copy x:some_file.sql d:copied_file.sql');
The file was not copied.
But when I issued from the OS prompt copy x:some_file.sql d:copied_file.sql then the file is copied.
What can I add to the PL/SQL procedure so it wil work ?
View 19 Replies
View Related
Apr 26, 2013
I am trying to execute an xslt (for XML transformation) using xmltype.transform function.
I can't paste XSLT code here, but below is similar what I am doing.
[URL]
I have used a reference of random() method of java.lang.Math in xslt file, and it's giving me below error.
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00607: Invalid reference: 'random'.
I doubt, it's because of "Math" class, which is not present in Oracle, how to load standard java classes.
View 1 Replies
View Related
May 22, 2012
I am trying to invoke program from my local Windows machine (exactly java executor, which is in my environment on c:javajdkinjava) using database package with Java stored procedure.
Everything is fired by SQLPLUS script, this script invokes package with Java stored procedure (jar loaded into Oracle) and that procedure is trying to invoke runtime process (syntax:
c:javajdkinjava -cp
and here ara my classes and main jar) using my local java executor. I added all permissions using dbms_java.grant_permisions (execution forced me to give permissions java.io.FilePermission to <<ALL_FILES>> for execute), moreover I added to my database user JAVASYSPRIV and JAVAUSERPRIV role. Unfortunately, beside this I am still getting error:
C:javajdkinjava not found (Note that lookup with PATH isn't done due to the oracle executable being setuid.)
View 8 Replies
View Related
Jul 14, 2011
I was getting java.sql.SQLException: executeBatch, Exception = 1when i tried to use executeBatch().could not understand why i am getting it.
View 1 Replies
View Related
Jun 20, 2011
I am writing a java stored procedure in my package. In the java procedure I want to issue a query to be fired on the user table from which I am calling the java procedure.
Some thing like following:
I am logged in to USER1 and my package is inside USER1. From this package I am calling the Java Stored Procedure. In java procedure I want to make a jdbc connection to USER2. The details of USER2(password, hostname,portno) are stored in a table called 'connection_details' and this table is in the USER1.
My problem is how can I issue the
SELECT details FROM CONNECTION_DETAILS for USER2
so that i can get the details..?
Do i need to first make jdbc connection to USER1 and then execute the above query for which i need similar details of USER1. How can i obtain the details of USER1 here.
I am thinking if I am already in USER1 then do I need to make the jdbc connection to USER1 and then execute the query?
View 1 Replies
View Related
Dec 21, 2011
I've this java source create or replace and compile java source named "Decodificador" as
public class Decodificador {
public static String decodifica(String codigo)
return codigo;
and this function
create or replace function F_Decodificador(codigo varchar2) RETURN VARCHAR2 IS
LANGUAGE java NAME 'Decodificador.decodifica(String) return String';
when I execute the function the result is:
ORA-29531: no method decodifica in class Decodificador
View 8 Replies
View Related
Jul 30, 2012
Which is the best driver for java to Oracle database connection ?
View 1 Replies
View Related
Oct 21, 2010
I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.
Example:
in excel the column has : 'ABC','GEH','HGT',LTP'
create or replace procedure abc(temp_col varchar2)
.
....
....
...
SELECT COLA, COLB, COLC
FROM TABLE_TEMP
WHERE TCOL IN temp_col;
This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.
View 2 Replies
View Related
Dec 22, 2010
I have upgraded my Operating Syatem from Vista to Win7. I have SQL nav version but is not supported on Win7. So I have installed PLSQL developer. When I try to connect its giving the error connection not open. But when I tried the same credentials with SQLPLUS it's working fine.
View 3 Replies
View Related
Feb 10, 2011
I need to call DB function through Java Hibernate.
My Sample XML File looks like.
---------------------------------------------------------------
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<sql-query name="getchequeno" callable="true">
?:=call cz_nab_mw_fn_gen_ol_chq_no()
</sql-query>
</hibernate-mapping>
---------------------------------------------------------------
I am getting below error while reading the file
"java.lang.RuntimeException: org.hibernate.HibernateException: Errors in named queries: getchequeno"
View 1 Replies
View Related
Feb 4, 2005
Here is a problem. I have to pass an array e.g ('Account','Marketing',Computer' ) from java to Oracle 9i.
Would I be able to do in Oracle, if yes then how ?
View 11 Replies
View Related
Aug 26, 2011
we can you java code natively with Oracle. But I didn't get how to decide when to use native java in oracle and when to use pl/sql.
View 5 Replies
View Related
May 20, 2013
i`m trying to use pl/sql function to execute java class. I created 2 tables with around 100 values and java class with simple functionality. I need to create function to loop through my table1 and get with each iteration one value from table and pass that value as parameter to java class.
table1 is something like this:
ID NAME
1 name1
2 name2
3 name3
. .
. .
100 name100
table2 is empty with same columns as table1 ( table2 is for data obtained from java returns ) I created in sql+ java class.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "myClass" AS
import java.io.*;
import java.util.*
public class myClass {
public static String fun(String strName)
[code]....
Then i wanna make a pl/sql function for:
1)For i = 1 Obtain first value from table1 (column name) and pass it throught java class then return value and save it in table2 with the same id as id from table1
2) change i=1 to i=2 and do that same
3) end if i=101
View 1 Replies
View Related
Oct 19, 2010
we have a program that uses SQLPlus to run PLSQL scripts - this program then checks the return code of SQLPlus to determine whether the script ran successfully. Our PLSQL scripts often call other PLSQL scripts via "@@OtherScript.sql" - one problem we have is that if (due to an engineering mistake) the OtherScript.sql file is missing, SQLPlus throws an SP2 error stating that the file was not found. However, the return code of SQLPlus does not indicate any problem. I have found, by searching the internet, that I can simply add a line "whenever OS error exit failure" to our script which then allows our program to detect the failure... however, the error message is not as informative:
CODESQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 19 10:28:45 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[code]....
is it possible to get BOTH types of error messages? With "oserror exit" set it does not tell you which file was not found.. which is useful information. I would like the SQL Plus session to return failure but also print out what file it did not find.
View 4 Replies
View Related
Jun 14, 2011
If in a PlSQL procedures there are many DML statements which are part of the same transaction. and there are many calls to other PLSQL blocks also.
what is the best practice to use commit either after each DML statement or to use it in the End of Block?
View 8 Replies
View Related
Dec 14, 2012
how can i display the out in matrx format.
example;
select country_code
, item_number
, sum(quantity)
FROM item_table it
[Code]...
output format:
--------- country_code1 country_code2 country_code3 country_code4
item1 10 77 99 87
item2 30 67 56 76
item3 35 45 66 66
item4 50 33 56 67
FOR my case PIVOT_ won't work. I am using 10g database.
View 1 Replies
View Related
Dec 12, 2011
My application reads a binary file in C++ using fstream.read and needs to save that binary data into the database as a blob.
I am not using OCI.
My struggle is trying to determine the proper way to pass this unstructured data to a PLSQL procedure.
1 > What data type should the input paramter be for the PLSQL procedure?
2 > My binary data is in a char variable. Do I need to convert that to something else before passing it as a parameter.
View 3 Replies
View Related
Jul 19, 2012
In any application, we will have many commit statements issued in many places. How can we find out as to where all commits have happened during runtime.
Basically in our application, when am trying an operation, am getting the error ORA-01086: savepoint <save point name> never established.
My guess is that there is a commit somewhere because of which system is not able to rollback to that save point.
View 1 Replies
View Related
May 22, 2007
I am trying to implement a Houskeeping program for files generated in 4 different servers. This housekeeping program is run as a batch job and I need to use PLSQL to implement it. The files need to be housekept on the basis of the file creation date.
how I can go about doing this using a PLSQL stored procedure.
View 3 Replies
View Related
Feb 18, 2009
I want to debug the procedure using PLSQL Developer tool?How to use the debug option in that tool?.
View 1 Replies
View Related
Jun 19, 2011
At PLSQL level how can we make our program re-startable such that if there is some abort after the commited update program will restart from the last commit checkpoint.
View 2 Replies
View Related
Jun 13, 2010
I am create procedure send the message in form bulider10g to Internet email and the procedure is work .
but now I want to modify this procedure , I want to add picture of my country ( logo ) and the original message . why cant display the image in my email ?
this code of image
<p align="center"><img border="0" src="logo.gif" width="20" height="20"></p>
I Think the problem of this code because 'Content-type: text/html' not image ?????!!!!!!!
utl_smtp.data(v_Mail_Conn, 'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||mesg);
procedure
EMAIL_SEN VARCHAR2(250);
EMAIL_REC VARCHAR2(250);
DESC_EN VARCHAR2(250);
UB_SEQ NUMBER(10);
BEGIN
DECLARE
v_From VARCHAR2(80) :=admin@yahoo.com ;
[code].........
View 2 Replies
View Related
Jan 24, 2013
my db version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
entry in my listener.ora file
===========================
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
[code]......
entry in tnsnames.ora file:
===============================
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc0))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.example.com)
)
)
listener is running perfectly. created c program, compile the program by
gcc -c <filename>
ld -shared -o <filename.so> <filename.o>
cp <filename.so> $ORACLE_HOME/bin
create library & function from db. when I am invoking this function it is throwing error message:
ORA-28546: connection initialization failed, probable Net8 admin error
View 1 Replies
View Related
Nov 23, 2012
I have one PLSQL package that does a join of two tables of remote database instance via the datalink. I just wonder where the most calculation(the join) is done, local machine or remote machine? Is there any best practice to have a better performance for such configuration.
View 3 Replies
View Related
Nov 26, 2012
I Program with Java . when we connect oracle by program ,
Get the following error :
'No suitable driver found for jdbc:orcl:thin:@localhost:1521 '
View 3 Replies
View Related
Jan 26, 2012
I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
View 1 Replies
View Related
Jan 3, 2011
I have a PLSQL script.
sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0
[code]....
My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc
[code]....
I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc
without the SQL prompt and the statement, and the spool off command?
View 2 Replies
View Related
May 30, 2012
execute a BATCH File through PLSQL.
BEGIN
dbms_scheduler.Create_schedule(schedule_name => 'RMAN_TICKER_STARTING',
repeat_interval => 'FREQ=DAILY;BYHOUR=9; BYMINUTE=15,30,45,59',
comments
=> 'schedule to run daily');
dbms_scheduler.Create_program (program_name => 'TICKER_PROGRAME',
[code]....
It was created successfully, but when I execute, it shows the error message.
BEGIN
dbms_scheduler.Run_job ('RMAN_TICKER_JOB', TRUE);
END;
begin
dbms_scheduler.run_job (
'RMAN_TICKER_JOB',TRUE);
end;
Error at line 1
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
[code]....
But I have the CMD file in the location - "F:FEEDLGRTOOLSfeedlgr.cmd".
View 2 Replies
View Related
