Questions tagged [java-stored-procedures]
53 questions
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...
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...
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...
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.
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...
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...
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...
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_...
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...
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...
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...
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;...
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...
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...
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...
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...
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...
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...
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...
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...
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!
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 /*...
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...
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.
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...
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!
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...
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?
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...
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...
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...
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...
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...
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 =...
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...
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...
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...
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)....
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...