Passing an array of data as an input parameter to an Oracle procedure
This is one way to do it:
SQL> set serveroutput on
SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
2 /
Type created
SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS
2 BEGIN
3 FOR i IN 1..t_in.count LOOP
4 dbms_output.put_line(t_in(i));
5 END LOOP;
6 END;
7 /
Procedure created
SQL> DECLARE
2 v_t MyType;
3 BEGIN
4 v_t := MyType();
5 v_t.EXTEND(10);
6 v_t(1) := 'this is a test';
7 v_t(2) := 'A second test line';
8 testing(v_t);
9 END;
10 /
this is a test
A second test line
To expand on my comment to @dcp's answer, here's how you could implement the solution proposed there if you wanted to use an associative array:
SQL> CREATE OR REPLACE PACKAGE p IS
2 TYPE p_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
3
4 PROCEDURE pp (inp p_type);
5 END p;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p IS
2 PROCEDURE pp (inp p_type) IS
3 BEGIN
4 FOR i IN 1..inp.count LOOP
5 dbms_output.put_line(inp(i));
6 END LOOP;
7 END pp;
8 END p;
9 /
Package body created
SQL> DECLARE
2 v_t p.p_type;
3 BEGIN
4 v_t(1) := 'this is a test of p';
5 v_t(2) := 'A second test line for p';
6 p.pp(v_t);
7 END;
8 /
this is a test of p
A second test line for p
PL/SQL procedure successfully completed
SQL>
This trades creating a standalone Oracle TYPE (which cannot be an associative array) with requiring the definition of a package that can be seen by all in order that the TYPE it defines there can be used by all.
How to pass a python array to an oracle stored procedure?
Try to use a plsql array in the parameters of the procedure and after that you pass the content of a sql array. The last one will be used to the sql statement into the procedure. It solve my trouble using oracle database 11g because in 12g you don't need to pass the content to an sql array. This could be the code:
def select_ids_entre_amistades(self,cod_us,ids_not):
lista = []
try:
cursor = self.__cursor.var(cx_Oracle.CURSOR)
varray = self.__cursor.arrayvar(cx_Oracle.NUMBER,ids_not)
l_query = self.__cursor.callproc("PACKFACE.P_SELECT_IDBFRIENDS", [cursor, cod_us, varray])
lista = l_query[0]
return lista
except cx_Oracle.DatabaseError as ex:
error, = ex.args
self.guardar_errores('dato ' + str(error.message))
return lista
And the stored procedure like this:
First you create a type
CREATE OR REPLACE TYPE LIST_IDS AS TABLE OF INT;
Second you create your package
CREATE OR REPLACE PACKAGE PACKFACE IS
TYPE LISTADO_IDS IS TABLE OF INT INDEX BY PLS_INTEGER;
PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT,IDS_NOT IN LISTADO_IDS);
END;
And finally create the body of the package
CREATE OR REPLACE PACKAGE BODY PACKFACE IS
PROCEDURE P_SELECT_IDBFRIENDS (CONSULTA OUT SYS_REFCURSOR,COD_US IN INT, IDS_NOT IN LISTADO_IDS)
IS
num_array LIST_IDS;
BEGIN
num_array:=LIST_IDS();
for i in 1 .. IDS_NOT.count
loop
num_array.extend(1);
num_array(i) := IDS_NOT(i);
end loop;
OPEN CONSULTA FOR
SELECT * FROM T_TABLE WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE(num_array));
END;
END;
I hope that It helps you.
Passing array to Oracle procedure from c#
Define an array type and a procedure:
CREATE or replace PACKAGE Testpackage AS
TYPE Areas_t is table of VARCHAR(100) index by BINARY_INTEGER;
PROCEDURE TESTPROCEDURE(Areas IN Areas_t);
END Testpackage;C# routine:
public void InsertQuestion(IEnumerable<string> area_list)
{
var connect = new OracleConnection("YOUR CONNECTION STRING");
var command = new OracleCommand("BEGIN Testpackage.Testprocedure(:Areas); END;", connect);
connect.Open();
var arry = command.Parameters.Add("Areas", OracleDbType.Varchar2);
arry.Direction = ParameterDirection.Input;
arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
arry.Value = area_list.ToArray();
arry.Size = area_list.Count();
arry.ArrayBindSize = area_list.Select(_ => _.Length).ToArray();
arry.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, area_list.Count()).ToArray();
command.ExecuteNonQuery();
connect.Close();
}
How to pass parameters in exec Oracle procedure which has array parameter
You are missing type reference while passing the arrays into the procedure,
try below,
BEGIN
flange_derating_upsert('A1'
,'2 inch Quad High Pressure'
,'2'
,'Quad High Pressure'
,pressure_array(275, 235, 215, 195, 170, 140, 125, 110, 95, 80, 65)
,temperature_array(100, 200, 300, 400, 500, 600, 650, 700, 750, 800, 850));
END;
/
OR
exec flange_derating_upsert('A1','2 inch Quad High Pressure','2','Quad High Pressure',pressure_array(275,235,215,195,170,140,125,110,95,80,65),temperature_array(100,200,300,400,500,600,650,700,750,800,850));
pass array to oracle procedure
Here's an example of how to do it.
The following script sets up a table, a type and a stored procedure in the database. The procedure takes a parameter of the array type and inserts each row of the array into the table:
CREATE TABLE strings (s VARCHAR(4000));
CREATE TYPE t_varchar2_array AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE PROCEDURE p_array_test(
p_strings t_varchar2_array
)
AS
BEGIN
FOR i IN 1..p_strings.COUNT
LOOP
INSERT INTO strings (s) VALUES (p_strings(i));
END LOOP;
END;
/
The Java code then demonstrates passing an array into this stored procedure:
import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;
public class ArrayTest {
public static void main(String[] args) throws Exception {
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe", "user", "pass");
CallableStatement stmt = conn.prepareCall("BEGIN p_array_test(?); END;");
// The first parameter here should be the name of the array type.
// It's been capitalised here since I created it without using
// double quotes.
ArrayDescriptor arrDesc =
ArrayDescriptor.createDescriptor("T_VARCHAR2_ARRAY", conn);
String[] data = { "one", "two", "three" };
Array array = new ARRAY(arrDesc, conn, data);
stmt.setArray(1, array);
stmt.execute();
conn.commit();
conn.close();
}
}
If you run the SQL script and then the Java class, and then query the table strings
, you should find that all of the data has been inserted into the table.
When you say 'an array of chars', I'm guessing that you mean an array of Java char
s. If I've guessed right, then I think you'd be best off converting the char
s to String
s and then using the same approach as above.
Mybatis - Passing String[] to Oracle stored procedure
Oracle's JDBC driver (as of version 19.8.0.0) does not support java.sql.Connection#createArrayOf()
which is used by MyBatis' built-in ArrayTypeHandler
, unfortunately.
So, you need to write a custom type handler.
I just tested and the following implementation worked.
package test;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import oracle.jdbc.OracleConnection;
public class OracleStringArrayTypeHandler extends BaseTypeHandler<String[]> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i,
String[] parameter, JdbcType jdbcType) throws SQLException {
OracleConnection conn = ps.getConnection().unwrap(OracleConnection.class);
Array array = conn.createOracleArray("STRINGARRAY", parameter);
ps.setArray(i, array);
array.free();
}
...
And specify the type handler in the parameter reference.
call create_deliverable(
#{name},
#{filename,jdbcType=ARRAY,typeHandler=test.OracleStringArrayTypeHandler}
)
Related Topics
A Simple Http Server with Java/Socket
Why Did Servlet.Service() for Servlet Jsp Throw This Exception
Freemarker Iterating Over Hashmap Keys
How to Check a Uploaded File Whether It Is an Image or Other File
Java Httpservletrequest Get Url in Browsers Url Bar
Detect Enter Press in Jtextfield
How to Create an 2D Arraylist in Java
Generate JPA 2 Entities from Existing Database
How to Modify JSONnode in Java
How to Send Java.Util.Logging to Log4J
The Matching Wildcard Is Strict, But No Declaration Can Be Found for Element 'Context:Component-Scan
Maven "Build Path Specifies Execution Environment J2Se-1.5", Even Though I Changed It to 1.7