Questions tagged [java-stored-procedures]

1

votes
1

answer
100

Views

JDBC CallableStatement - call multiple stored procedures in one shot

To call Oracle's stored procedure using only simple JDBC in Java we could do something like this: con.prepareCall('{ call ? = my_procedure(?) }'); callStm.registerOutParameter(1, Types.INTEGER); callStm.setInt(2, -100); callStm.execute() I want to know is it somehow possible to call more then one pr...
jsosnowski
1

votes
0

answer
172

Views

Spring batch: storedProcedureItemReader Invalid parameter index 0

I trying to use spring batch with storedProcedureItemReader and always i get this exception and i cant found the error i use Sybase at BBDD org.springframework.batch.item.ItemStreamException: Failed to initialize the reader Caused by: org.springframework.jdbc.BadSqlGrammarException: Executing stor...
Naghost
1

votes
0

answer
30

Views

Implementation of ODCITableDescribe in a Java Stored Procedure

The current question is the second part of this ODCI related question. I have implemented a collection type in Oracle SQL which is practically defined as a type and a table of that type. CREATE TYPE row_type AS OBJECT ( C1 VARCHAR2(50), C2 VARCHAR2(50), C3 VARCHAR2(50) ); / CREATE TYPE row_type_se...
Tiberiu Zulean
1

votes
3

answer
596

Views

JDK 6: Is there a way to run a new java process that executes the main method of a specified class

I'm trying to develop an application that just before quit has to run a new daemon process to execute the main method of a class. I require that after the main application quits the daemon process must still be in execution. It is a Java Stored Procedure running on Oracle DB so I can't use Runtime.e...
1

votes
3

answer
730

Views

SQL stored procedures VS external language procedures [closed]

I would like to know what are benefits and what are drawbacks of SQL stored procedures and procedures written on some common programming language, more precisely, which method is preferred in which situations.
noname
1

votes
1

answer
1.8k

Views

Calling multiple oracle stored procedure in java and maintaining data consistency

This method has some issues I failed to understand, such as it doesn't rollback the changes made in previous procedure call if any of the procedure calls down the hierarchy throws an exception.... Please help me out public synchronized boolean save(DTO dto) throws DAOException,IllegalArgumentExcepti...
user1728387
1

votes
1

answer
1.1k

Views

Mapping hibernate datatypes to output from stored procedure of mixed types

Have a problem mapping datatypes from stored procedure output to a java datatype Using a SQLServer, I am given the following 2 tables initially - can't change their definitions. CREATE TABLE [dbo].[tblDetail]( ... [dCdeType] [dbo].[Tcde] NOT NULL, [dCdeState] [dbo].[Tvch] NOT NULL, ... ) CREATE TABL...
Dima R.
1

votes
1

answer
230

Views

“java.lang.Thread.sleep(long)” is incomplete or malformed

I have installed Oracle XE 11g version in Red Hat Linux. I followed the steps here. The Oracle is getting installed successfully and I can use the database. But while trying to run Java store procedure its showing that the Java is malformed. Store procedure - CREATE OR REPLACE PROCEDURE my_java_sl...
Amit
1

votes
1

answer
274

Views

SP not returning resultset

I have a Stored Procedure which should return a code. PFB the sample SP. USE [TEST] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[test_sp] -- Add the parameters for the stored procedure here @id int, @name varchar(25), @return_code int OUTPUT AS BEGIN select @return_...
Divya Rose
1

votes
2

answer
173

Views

How do I ignore the yield value from a java stored procedure in cypher (neo4j)?

I have a CYPHER statement which first nearly matches every node in my graph. MATCH (n:node) CALL procedure(n) YIELD node RETURN node This blows up my memory (within python) because the response is so large. If I don't need or want the yielded value of the procedure, is it possible to tell neo4j usin...
spanishgum
1

votes
1

answer
49

Views

Oracle SQL - How can I call the ODCI pipelined function using JSP

The following is the exact implementation from https://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pipelined_example.htm#CHDHDHEE Let's assume we have the following table deifinition: CREATE TABLE StockTable ( ticker VARCHAR(4), openprice NUMBER, closeprice NUMBER ); Then, we've got the followin...
Tiberiu Zulean
1

votes
1

answer
0

Views

calling oracle stored procedure with table object as input using java

i try to call oracle stored procedure with 4 parameters 1 as input of custom type table and get the following error and PriceInBundle_Input_Table is an object with two element of MaterialNumber nvarchar2(22), BundleNumber nvarchar2(22) java.sql.SQLException: ORA-03115: unsupported network dataty...
fathy elshemy
1

votes
3

answer
318

Views

Postgres stored procedure call from java, table to return

I made a Postgres stored procedure: CREATE OR REPLACE FUNCTION GetUser(ipUserId integer) RETURNS setof users AS $$ BEGIN IF ipUserId is null THEN return query select * from users A order by modifieddate desc; END IF; return query select * from users where iduser = ipUserId; END; $$ LANGUAGE plpgsql;...
Bogdan
1

votes
2

answer
3.2k

Views

PLS-00103 error when comping Oracle stored function

Given the following Java which is loaded into the database using loadjava: package grassie.example; public class Example { public static String test(){ return 'Hello World!'; } } And given the following Oracle Stored Function: CREATE OR REPLACE FUNCTION TEST_FUNCTION RETURN VARCHAR2 AS LANGUAGE JAVA...
Stuart Grassie
3

votes
2

answer
6.6k

Views

Spring JcbcTemplate to call Oracle Stored Proc. Spring 3.2

I have some straight JDBC code using a CallableStatement working. I have been trying to convert it to Spring leveraging the DataSource, JdbcTemplate and SimpleJdbcCall. I have tried basically every tutorial, example and snippets from the Spring docs that I can find. With tweaking, all the Spring sol...
user3596751
2

votes
1

answer
74

Views

Executing java MySQL stored procedure from PHP

I have a PHP page that processes incoming information, it takes the parameters and constructs a command to execute a Java class on the server using shell_exec(). The Java class takes the parameter and calls a stored procedure in MySQL and returns the required results. i have tested my Java class ind...
Tarounen
2

votes
1

answer
1.3k

Views

Java stored procedure

I have the following Java stored procedure: CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED 'RetreiveLdap' AS import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Hashtable; import javax.naming.Context; import ja...
Adnan
2

votes
0

answer
2.9k

Views

BCrypt implementation in Oracle

I am trying to save the user password using Bcrypt algorithm in Oracle 11g. I know this can be done in Java easily, but in this case i want to do this in DB side. Can any one help me in achieving this. Currently i am using SHA256, which is done through Java stored procedure as 11g supports till SHA1...
Manoj
2

votes
1

answer
1.1k

Views

Executing Unix Sun Solaris Commands Using ORACLE PL/SQL trouble with permissions

I have created 2 procedure and a java source One procedure is calling java class TestHostCommand PROCEDURE TEST2 (p_command IN Varchar2) AS LANGUAGE JAVA NAME 'TestHostCommand.executeCommand(java.lang.String)'; Java Source is: import java.io.*; public class TestHostCommand { public void executeComma...
CRL88
2

votes
1

answer
392

Views

Oracle Java stored librairies won't compile

I'm looking to store Java libraries inside my Oracle database. Note that this is the first time I'm doing that. That library is pd4ml. It is made up of two jars, which contains the compiled classes. pd4ml.jar ss_css2.jar (required by pd4ml.jar) As loading them with Toad did not work (Toad said they...
tsc
1

votes
2

answer
93

Views

How To Call a Java Stored Procedure In Oracle 10gR2?

Here is my code: SET DEFINE OFF; CREATE OR REPLACE AND COMPILE NOFORCE JAVA SOURCE NAMED 'SCHEMA'.'DigestUtils' AS /* imports here... */ public class DigestUtils { private static final char[] hexCode = '0123456789ABCDEF'.toCharArray(); private static final int DEFAULT_BUFFER_SIZE = 1024 * 4; public...
Stephan
3

votes
2

answer
3.8k

Views

Can anyone say Stored procedures pros and cons AND Java Stored Procedures pros and con?

Can anyone say Stored procedures pros and cons AND Java Stored Procedures pros and con? And how to test it. Best regards!
Debuger
3

votes
1

answer
2.9k

Views

Calling stored JavaScript function in MongoDB from PHP

I have this function stored in db.system.js function SomeFunc(o) { x = db.Collection; while( 1 ) { var c = x.find({},{_id:1}).sort({_id:-1}).limit(1); var i = c.hasNext() ? c.next()._id + 1 : 1; o._id = i; x.insert(o); var err = db.getLastErrorObj(); if( err && err.code ) { if( err.code == 11000 /*...
amirka
7

votes
2

answer
285

Views

StoredProcedureCall 1x Varchar output 1x Cursor output

My question is maybe a little bit confusing. I have the problem that I'm calling an procedure with StoredProcedureCall from Java e.g.: StoredProcedureCall call = new StoredProcedureCall(); call.setProcedureName('test'); call.addNamedArgument('p_year'); call.addNamedArgument('p_rel'); call.useNamedC...
Xanne
3

votes
2

answer
3.4k

Views

Are Java stored procedures slower than PL/SQL in Oracle?

This is a straightforward question: do you know if Java stored procedures are slower than PL/SQL in a Oracle database? We've migrated a stored procedure from PL/SQL to Java just to benchmark it and it's taking approximately two times the time of the PL/SQL one. Is this also your experience? Thanks.
Juan
1

votes
6

answer
9.1k

Views

Confusion over class resolution in Oracle java stored procedures

I'm trying to use a third-party java library within oracle. The library seems compatible with the same 1.4 version of the jvm that our Oracle 10g server hosts, since it runs fine outside of Oracle, so I feel like I should be able to get this to work. This library ends up making SOAP-based http req...
Chris Farmer
4

votes
2

answer
10.4k

Views

How to include jar files to a Java Stored Procedure in Oracle?

I'm realizing a stored procedure and need now the ability to include jar files to my stored procedure, which do not belong to the standard deployment of the JRE. How can I do this? As I know wasn't it possible with Oracle 9 ... Thanks for help!
Florian Müller
2

votes
2

answer
1.7k

Views

Implementing Java stored procedure IBM DB2 for i (AS400)

I created the Java stored procedure as follows, able to generate a .class file using AS400 Qshell command interpreter. import java.sql.*; public class sample { public sample(){ super(); } /** * @param args */ public static void Test(int test) throws SQLException, Exception { // TODO Auto-generated...
Anthony
4

votes
1

answer
904

Views

Bundling Java libraries with a DB2 Java stored procedure

Creating a DB2 Java stored procedure using IBM Data Studio, I have some libraries that are required in the classpath. I'm running Data Studio in a VM, and deploying remotely to my DB2 server in a Cloud instance. How do I bundle up the jars with my stored procedure?
Marius Butuc
2

votes
1

answer
27

Views

Setting parameters during stored procedure execution

Hi I am calling an SP from Java code. This SP has 50+ input parameters. So I set ct.setInt(1, id); like this from 1 to 50. So if i need to remove say 4th parameter, i need to change all the numbers manually which is a tedious process. Is there any good way to implement this? Some loop way where we...
Divya Rose
2

votes
0

answer
392

Views

Java Stored Procedure to return ARRAY

I have the following use case where I have a table type as OUT param to my function which invokes a java stored procedure. I am struggling to get this working. Can you plz help me in this regard ? SQL Record Type TYPE tmsint_xfer_html_ws_objr AS OBJECT...
harish
14

votes
1

answer
5.7k

Views

Java Stored Procedure Vs PL/SQL Stored Procedure

In Oracle DBMS, which is better performance, calling a java stored procedure from another pl/sql stored procedure or calling a pl/sql stored procedure from another pl/sql stored procedure. By the way I have a loop in my pl/sql procedure that will call the java procedure multiple times (i.e. my code...
Khalid Galal
4

votes
1

answer
4.7k

Views

Call Oracle Stored procedure from JDBC with complex Input and Output type

I'm so close in solving this question but I'm apparently missing something. My requirement is to call a stored procedure in Oracle from JDBC. The stored procedure takes 1 user-defined Oracle object as INput and another user-defined Oracle object as OUTput. The INput and OUTput objects have mix of bo...
Suji Thang
4

votes
2

answer
12k

Views

Resultset of stored procedure contains list of LinkedCaseInsensitiveMap<V>

I have this stored procedure call in Java: @Autowired public ScoreDao(DataSource dataSource) { setDataSource(dataSource); mScoreStoredProcedure = new ScoreStoredProcedure(dataSource); } public List loadAllScore(String pUsername, String pUUID, int pLimit) { return mScoreStoredProcedure.execute(pUsern...
Stefan Beike
2

votes
4

answer
305

Views

Mysql 5.7 Stored Procedure error

I an trying to create stored procedure in following format : DELIMITER $$ CREATE PROCEDURE `CreateInsertLocation` (tableName VARCHAR(255),ts BIGINT(20),systs INT(20),lat FLOAT,lon FLOAT) BEGIN DECLARE FoundCount INT; SELECT COUNT(1) INTO FoundCount FROM information_schema.tables WHERE table_schema =...
Anil Kadam
3

votes
1

answer
3k

Views

Spring StoredProcedure Optional Parameters

I have a procedure in Oracle that has 12 total parameters and 3 optional. How can I account for these optional parameters if I'm using the StoredProcedure object in Spring 3.1.0? Here's what I have so far in my StoredProcedure class public Map execute(Evaluation evaluation) { Map input_params = new...
Robert
5

votes
1

answer
1.3k

Views

java user defined function in oracle using byte[]

Given this table in Oracle create table test (bytes raw(100), chset varchar2(50)) insert into test (bytes, chset) values (hextoraw('454647'), 'iso-8859-1') or in MSSQL create table test (bytes varbinary(100), chset nvarchar(50)) insert into test (bytes, chset) values (0x454647, 'iso-8859-1') I'm loo...
gordy
3

votes
1

answer
354

Views

Oracle database - use external Java

I'm looking to find a way how to use higher versions of Java (1.6,1.7,...) instead of Java 1.4 which is available for Java stored procedures in a Oracle 10g. I'm thinking about deploying a GlassFish server on the database server, and deploy web applications which functionalities would I use by calli...
Vanja Keglevic
2

votes
2

answer
15.7k

Views

Inserting byte[] array as blob in Oracle Database getting ORA-01460: unimplemented or unreasonable conversion requested

I have a java stored procedure that I am trying to insert a byte[] array into an oracle blob field in a table. I create a prepared statement as follows but it will randomly fail when I execute the prepared statement. I have narrowed down that the issue is coming from the pstmt.setBytes(4,content)....
medium
2

votes
1

answer
38

Views

Java Streams for Stored Procedures

I would like to create a stream of certain values and map them to an internal class. My current code is as follows: return Stream.of((int)levenshteinScore, reverseComparison, regionStart, regionLength).map(Match::new); public static class Match{ // Properties public int levenshteinSimilarity; public...
Ryan Falzon

View additional questions