SQL & PL/SQL :: Pass Output Associative Array To Java?

Aug 11, 2011

For Nested table i have done it in follwing way? This is pl/sql stored procedure.

CREATE or REPLACE PROCEDURE TEST(
activationStartDate IN DATE,
activationEndDate IN DATE,

[Code]....

My JavaCode is

--First getConnection
--prepare sql string : sql = "{ Call Test(?,?,?,?,?) } ";
--Use prepareCall function on Connection object and passed this sql string and retrived CallableStatement class object.

[Code].....

Then what if i want to select more than one column from EMPLOYEE and pass to my javaCode......How my Pl/Sql will look like?........ I know how to retrieve all the coloumns of resultset in java.

I want to ask how pl/sql code will look like, i know the retrieveal process in java.

List<List> result = new ArrayList()<List>;
List col = new ArrayList();
ResultSet rs = (ResultSet)stmt.getObject(5);
ResultSetMetaData rsMetaData = rs.getMetaData();

[Code].....

View 1 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Populating Associative Array With A Column?

Jun 27, 2011

create or replace function nothin
return int
as
type arr_type is table of departments.department_id%type index by binary_integer;
arr arr_type;
begin
select department_id into arr from departments;
return 4;
end;

It gives the error : Quote:expression 'ARR' in the INTO list is of wrong type I can't figure out why.

View 6 Replies View Related

SQL & PL/SQL :: Associative Array - No Data Found

Dec 28, 2012

find the sample of code which is giving me the same error. whts wrong with below piece of code.

Programe
declare
cursor c1 is select * from x1;
l_x1 type1;

[Code]....

View 3 Replies View Related

PL/SQL :: Hold Values In A Associative Array?

May 6, 2013

I am providing the complete code and my exact requirement.

CREATE OR REPLACE PACKAGE INTERNAL_SCORING_RAM
IS
PROCEDURE TrendScoring_ram(pBUID       IN STAGING_ORDER_DATA.BUID%TYPE,
                         OrderNum    IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
                         ReturnValue OUT VARCHAR2);

[code]...

/In my code the procedure "trendscoring_ram" is calling "inserttrend_ram" procedure 70 times for different variable values. Instead of calling the "inserttrend_ram" procedure 70 times.

want to hold the values in a associative array , defining it in package and call that procedure only once.As below.

Inserttrend_ram(pBUID, OrderNum, Associativearray, Associativearray, Associativearray,Associativearray);

For that I have tried the following but it's not working.IN the package I have declared the associative array like this.

TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;

I have modified the inserttrend_ram as below.

PROCEDURE InsertTrend_ram(
                            P_TYPE_OTS_REC IN type_ots
                           )
        IS
        BEGIN
     
[code]...

View 1 Replies View Related

SQL & PL/SQL :: Collections - Save Associative Array In Database?

Jan 10, 2011

We can save Associative array in data base, if not why?.

View 3 Replies View Related

Forms :: Call Associative Array In Oracle Procedure?

Apr 25, 2013

I've one package in which one record is created. associative array is craeted on that record.create procedure on associative array.using forms 6i i want to call this procedue.(package_name.procedure_name(paramerters)). but my problem is what paramerter shuld i provide to excute the procedure?like PK_EXCEL_TO_DB.PR_DO_INSERT(LIST_ROUTE); but i am getting error while doing this.

CREATE OR REPLACE PACKAGE PK_EXCEL_TO_DB IS
TYPE ROUTE IS RECORD (COL_ROUTE VARCHAR2(255), VAL_ROUTE VARCHAR2(4000));
TYPE LIST_ROUTE IS TABLE OF ROUTE;
PROCEDURE PR_DO_INSERT(i_lData IN LIST_ROUTE);

[code]...

View 14 Replies View Related

PL/SQL :: Uses And Advantages - Associative Array / Nested Table And Varray?

Jun 4, 2013

What is the real time uses for Associative array, nested table and varray ?

View 2 Replies View Related

Application Express :: Associative Array Shape Is Not Consistent With Session Parameters

Sep 24, 2013

APEX_ITEM functions do not seem to be working correctly. When I create a region with this query:

select APEX_ITEM.SELECT_LIST_FROM_QUERY(1, 1, 'select ''Jan'' d, 1 r from dual union select ''Feb'' d, 2 r from dual') month_id

from dual I get the error:report error:ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parametersWhen I run that query in SQL Developer I get this:

<select name="f01" ><option value="%null%">%</option><option value="1" selected="selected">1</option></select>It

works but returns an incorrect result, there is no list.When I run that query in SQL Workshop on apex.oracle.com I get this:

<select name="f01" ><option value="%null%">%</option><option value="2" >Feb</option><option value="1" selected="selected">Jan</option></select>

which is correct.I tried changing NLS_COMP parameter from Linguistic to Binary and changing NLS_SORT to BINARY_CI but that did not make a difference. This seems like a rare problem and generally related to installation but it has me stopped dead in my tracks.

View 2 Replies View Related

Pass Array To Oracle Stored Procedure?

Oct 3, 2010

I am passing a string from vb.net through oracle store procedure. String consists of varchar values for eg '123456;I|N,2424424;O|A'. But how can i call these string values into array using oracle stored procedure? After getting these values into array , i wanted to loop through these values.

View 1 Replies View Related

Precompilers, OCI & OCCI :: How To Pass Array Of Strings To And Return Table Set From Proc

Jan 24, 2011

I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like

'000887S','000780S'.

Now I have a query in the procedure, which will return a row, for each of the array elements. For example:

SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......

In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.

Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.

what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.

View 1 Replies View Related

SQL & PL/SQL :: Create Stored Procedure With Array As Output Parameter?

Jul 14, 2011

how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...

View 7 Replies View Related

JDeveloper, Java & XML :: Parsing Containing Array Of Data?

Jan 12, 2011

I have to parse an XML and store the data in a table.

My XML :-
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>

[Code].....

The output is :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

There will be cases when I will get multiple values for the columns conversationid, command, value.

<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>

[Code].....

In that case the output should be as :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V2

Not sure how to go about XML parsing when I have multiple values.

View 4 Replies View Related

JDeveloper, Java & XML :: Calling Function With (array Of) Complex Data Type

Oct 20, 2011

I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.

a) basic definitions (work and are ok)

CREATE OR REPLACE TYPE eventtype AS OBJECT
(
relevance NUMBER,
bid1 NUMBER,tid1 NUMBER,prob1 NUMBER,
bid2 NUMBER,tid2 NUMBER,prob2 NUMBER,
bid3 NUMBER,tid3 NUMBER,prob3 NUMBER,
bid4 NUMBER,tid4 NUMBER,prob4 NUMBER,
bid5 NUMBER,tid5 NUMBER,prob5 NUMBER,
bid6 NUMBER,tid6 NUMBER,prob6 NUMBER
);
CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype;
/

b) The following call works (but is not exactly that what I want):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal';
/
SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;

c) The following call doesn't work (but should have the intended semantics):

public class pcompDP {
public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException {
...
}
}
create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal';
/
SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;

d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.

The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.

View 1 Replies View Related

SQL & PL/SQL :: How To Pass Output Of Regexp_substr As Number Input To A Cursor

Sep 13, 2012

I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.

Here is an example.

initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;

But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.

View 12 Replies View Related

JDeveloper, Java & XML :: Pass Null As Argument In Any Method Parameter?

Feb 23, 2013

why we pass null as a argument in any method parameter....What is its use in method reference..

View 1 Replies View Related

JDeveloper, Java & XML :: Get Values With Two Columns And Pass It To Informatica To Frame

Aug 29, 2011

I have a scenario to frame an xml as below, below i have given a sample data

Create table xml_type
(msg varchar2(1000),
desp varchar2(1000),
val number) ;

[Code]....

Actually i need the output as below, i tried and succeeded doing it in Procedure , but they are asking me to do it in SQL query. How to get from an sql query.

<DETAIL>
<ERROR> 'Invalid Name ID' </ERROR>
<ID> 'Name ID' </ID>
<VALUE> 6 </VALUE>
<VALUE> 7 </VALUE>
<VALUE> 8 </VALUE>
</DETAIL>

[Code]...

Or i was planning to get the values as below with two columns and pass it to informatica to frame the XML.

Col1 Col2
Invalid Name Id Error
Name ID ID
6 Value
7 Value
8 Value
Invalid FAQ ID Error
FAQ Id ID
9 Value
10 Value

Let me know the best option and how to do this task.

View 1 Replies View Related

JDeveloper, Java & XML :: Table Output To XML

Dec 15, 2010

I am exploring the possibilities of getting table output or query output to an XML file.

View 3 Replies View Related

JDeveloper, Java & XML :: Flatten Output From (pivot Xml)?

Jan 18, 2011

How do you query the output from the 'pivot xml' sql?

Just as an example, How can you query the output(xmltype) from this sql so that it is flattened into a relational type view?

select *
from ( select owner, count(*) as owner_segment_count
from dba_segments
where owner in( 'OLAPSYS','ORDSYS','QDATA','SYS','SYSMAN','SYSTEM','XDB' )
group by owner )
pivot xml( sum( owner_segment_count ) as owner_segment_count for owner in( any ) )

CM: removed unescessary formatting. Personally I find size 1 hard on the eyes. Also added [code] tags,

View 13 Replies View Related

JDeveloper / Java & XML :: Using Input And Output Stream Updating BLOB In Database

Nov 30, 2011

I have little code that's don't really works.

InputStream is = null;
OutputStream os = null;
Connection conn = null;
Blob value = null;
[code]......

I know that there is 90% of Java code. But I have few questions:

I know that I can insert date using INSERT. But is it possible that I can use SELECT and JAVA will know that it need to update the SELECTED column ?

View 6 Replies View Related

SQL & PL/SQL :: Associative Arrays Counter

Mar 30, 2012

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

declare
l_sql_stmt VARCHAR2(1000);
l_year NUMBER(4);
l_event_id NUMBER(2);

[Code]....

Sql gives result like

YEAR EVENT_ID
1995 111
1996 111
1997 111
1998 111
1996 111
1995 111
1995 111

SO I am trying to do like

l_my_arr(1995):= 1 ;
l_my_arr(1996):= 1 ;
l_my_arr(1997):= 1 ;
l_my_arr(1998):= 1 ;
l_my_arr(1996):= 2 ;
l_my_arr(1995):= 2 ;
l_my_arr(1995):= 3 ;

But if I put l_my_arr(l_year) := 0, immediately after FETCH, then the counter is always 1, and I cannot put it before because l_year has to be FETCHED.

View 6 Replies View Related

Passing Associative Arrays Across DB Links?

Apr 1, 2013

Basically, we have 2 procedures in 2 different databases. One of them makes a call to the other over a database link passing an associative array as a parameters.

This has woked fine for many years 9.2.0.8.0 but we are in the process of migrating to 11.2.0.2.0 and have started getting compilation errors.

1 > Create TYPE in Database_1
=====================
CREATE OR REPLACE PACKAGE DB1_Pkg
IS
type x_tab is table of number index by binary_integer;
v_tab x_tab;
END DB1_Pkg;

[code]....

View 3 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 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

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







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