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

Forms :: Calling Java Class Files From PLSQL?

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

Windows :: Copy A File To DB Server From A Remote Machine Using Plsql / Java

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

JDeveloper, Java & XML :: Using Java.lang.Math In Oracle / ORA-31011 / Parsing Failed

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

JDeveloper, Java & XML :: Java Stored Procedure Invoking Windows Run Time Process

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

JDeveloper, Java & XML :: Java.sql.SQLException - Execute Batch - Exception Equal To 1?

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

JDeveloper, Java & XML :: Calling Java Stored Procedure From Oracle

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

JDeveloper, Java & XML :: Create Or Replace And Compile Java - Getting Ora 29531?

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

JDeveloper, Java & XML :: Best Driver For Java To Oracle Database Connection

Jul 30, 2012

Which is the best driver for java to Oracle database connection ?

View 1 Replies View Related

How To Use Parameters In Plsql Procedure

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

Error In PLSQL Developer

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

JDeveloper, Java & XML :: Call DB Function From Java Hibernate

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

JDeveloper, Java & XML :: How To Pass Any Array From Java To Oracle

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

JDeveloper, Java & XML :: Native Java In Oracle Versus PL/SQL?

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

JDeveloper, Java & XML :: Using PL/SQL Function To Execute Java Class?

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

Program That Uses SQLPlus To Run PLSQL Scripts

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

Best Practice For Using Commit In PLSQL Blocks

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

Dynamic Matrix Format In Sql / Plsql

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

Passing Unstructured Data From Pro*C To PLSQL

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

PL/SQL :: Finding Commits In Plsql During Runtime?

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

Writing A PLSQL Procedure To Housekeep Files?

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

Debug The Procedure In PLSQL Developer Tool?

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

At PLSQL Level How To Make The Program Re-startable

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

Forms :: How To Send Pic And TEXT In Email Using PLSQL

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

Networking And Gateways :: C Program Call From Plsql

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

PLSQL Via Data Link To Remote Database

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

JDeveloper, Java & XML :: Connection Between Java And Oracle

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

Performance Tuning :: PLSQL And Related Cursors Generated In V$sql

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

Client Tools :: Spool Without SQL Statement And Commands In PLSQL?

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

Backup & Recovery :: Execute A BATCH File Through PLSQL?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved