Regarding passing Array as a argument in the stored procedure/Function [message #344973] |
Tue, 02 September 2008 01:13  |
raimanish4u
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hello Friends,
I need a help, i Want one stored proedure/Function which take array as a argument and return an array also.
and the size of array should not be fixed.
I don't want to use Varray because for this I have to specified its size first.
I think Associative arry will work( For dynamic size) but I am not able to run it .
Is Associative arry(Index by Pls_Integer) support JDBC?
If yes, please give me some clue. I have found some information of associative array in this url
http://www.oracle.com/technology/oramag/oracle/07-jan/o17odp.html
But I am not able to run this package through SQL and also not able to connect it through JDBC.
Using Oracle 9i.
Is there any other alternative solution?
Thanks & Regards,
manish Kumar
|
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345022 is a reply to message #344973] |
Tue, 02 September 2008 02:50   |
raimanish4u
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hi Rajat,
Thanks for quick reply.
This is my table structure :
SQL> desc jobs
Name Null? Type
---------- ------- --------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
My Requirement is, User will enter the above field value in the form of array. I need to store these value and after manupulating this I will return that value also.
Here I am using procedure just for testing purpuse.
In my case I will take one array argumentlike JOB_ID and after manupulating these data I will return that array value.
I am also not able to run this example through SQL.
Can U give me exact code for running this prog.
Can we connect Thish through JDBC?
create or replace package associative_array as
-- define an associative array type for each column in the jobs table
type t_job_id is table of jobs.job_id%type index by pls_integer;
type t_job_title is table of jobs.job_title%type index by pls_integer;
type t_min_salary is table of jobs.min_salary%type index by pls_integer;
type t_max_salary is table of jobs.max_salary%type index by pls_integer;
-- define the procedure that will perform the array insert
procedure array_insert (p_job_id in t_job_id,
p_job_title in t_job_title,
p_min_salary in t_min_salary,
p_max_salary in t_max_salary);
end associative_array;
/
create or replace package body associative_array as
-- implement the procedure that will perform the array insert
procedure array_insert (p_job_id in t_job_id,
p_job_title in t_job_title,
p_min_salary in t_min_salary,
p_max_salary in t_max_salary) is
begin
forall i in p_job_id.first..p_job_id.last
insert into jobs (job_id,
job_title,
min_salary,
max_salary)
values (p_job_id(i),
p_job_title(i),
p_min_salary(i),
p_max_salary(i));
end array_insert;
end associative_array;
[Edit MC: add code tags]
[Updated on: Tue, 02 September 2008 03:05] by Moderator Report message to a moderator
|
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345039 is a reply to message #345027] |
Tue, 02 September 2008 03:38   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Quote: |
I am also not able to run this example through SQL.
|
You have created a procedure not a function.Function can be called in sql statement not procedures.
Are you trying something like
select array_insert(values) from dual
And one more thing you have posted the procedure that is provided in the link not what you are trying to do.
can you paste your sqlplus session here what you are actually trying to do.
Regards,
Rajat
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345063 is a reply to message #345039] |
Tue, 02 September 2008 04:11   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Are you looking for something like this:-
Create table customers
(CUSTID number,
NAME varchar2(30),
CITY varchar2(30)
);
INSERT INTO CUSTOMERS (CUSTID,NAME,CITY) VALUES (1,'woodworks','baltimore');
INSERT INTO CUSTOMERS (CUSTID,NAME,CITY) VALUES (2,'Software Solutions','boston');
INSERT INTO CUSTOMERS (CUSTID,NAME,CITY) VALUES (3,'Food Supplies','New York');
INSERT INTO CUSTOMERS (CUSTID,NAME,CITY) VALUES (4,'Hardware shop','Washington');
INSERT INTO CUSTOMERS (CUSTID,NAME,CITY) VALUES (5,'Book Inc.','New orleans');
CREATE OR REPLACE TYPE ty_name AS TABLE OF VARCHAR2(200);
CREATE FUNCTION fn_returnname RETURN ty_name
DECLARE
custNames ty_name;
BEGIN
SELECT name BULK COLLECT INTO custNames FROM CUSTOMERS;
RETURN custNames;
END;
/
SELECT * FROM TABLE(fn_returnname);
Regards,
Rajat
[Updated on: Tue, 02 September 2008 04:12] Report message to a moderator
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345102 is a reply to message #344973] |
Tue, 02 September 2008 05:42   |
raimanish4u
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Yes, But I need to take input from user so I would like to pass
array as an argument and also want to return an array Some thing like this :
CREATE OR REPLACE TYPE Eno_ARRAY IS TABLE OF number(25);
CREATE OR REPLACE TYPE Ename_ARRAY IS TABLE OF varchar2(25);
CREATE OR REPLACE FUNCTION fn_returnename(empID IN Eno_ARRAY) RETURN Ename_ARRAY
IS
man_eid Eno_ARRAY;
man_ename Ename_ARRAY;
begin
forall i in empID.first..empID.last
select ename bulk collect into man_ename from emp where empno=empID(i);
RETURN man_ename;
end;
But, here I can't use bulk collect with forall .
So I stuck here, how can I convert ename to an array?
Regards,
manish.
|
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345113 is a reply to message #345102] |
Tue, 02 September 2008 06:12   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Something like this:-
CREATE OR REPLACE FUNCTION fn_returnname(id ty_name)
RETURN ty_name
AS
custNames ty_name:=ty_name();
BEGIN
FOR i IN id.first..id.last loop
custNames.extend();
select
name into custNames(i)
from customers where custid=id(i);
end loop;
return custNames;
END;
DECLARE
cust_Id ty_name:=ty_name(1,2,3);
custNames ty_name;
BEGIN
custNames:=fn_returnname(cust_id);
FOR i IN custNames.first..custNames.last LOOP
dbms_output.put_line(custNames(i));
END LOOP;
END;
DECLARE
cust_Id ty_name;
custNames ty_name;
BEGIN
SELECT custid BULK COLLECT INTO cust_id FROM CUSTOMERS;
custNames:=fn_returnname(cust_id);
FOR i IN custNames.first..custNames.last LOOP
dbms_output.put_line(custNames(i));
END LOOP;
END;
Regards,
Rajat
[Updated on: Tue, 02 September 2008 06:18] Report message to a moderator
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345132 is a reply to message #344973] |
Tue, 02 September 2008 06:50   |
raimanish4u
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Thanks rajat,
That problem is solved.
But one thing i noticed here is when I am calling this Function from SQL it's giving correct value but when I am calling this Function from Java Code or JDBC adapter then its giving value like :
'0x414C4C454E'
'0x57415244'
'0x4A4F4E4553'
'0x4D415254494E'
'0x434C41524B'
'0x53434F5454'
'0x4B494E47'
'0x5455524E4552'
Can U tell me the reson behind this?
Thanks in advance.
Regards
Manish.
|
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345144 is a reply to message #344973] |
Tue, 02 September 2008 07:10   |
raimanish4u
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hi Actually I am using IBM's JDBC adapter for calling Stored Function.But Earlier when I was trying to access stored function with java code then alsio I was getting same value as I posted U.
Sample code :
con =DriverManager.getConnection(url, "trg47", "oracle47");
OracleCallableStatement stmt =(OracleCallableStatement)con.prepareCall( "begin ? := getEmpArray; end;" );
// The name we use below, EMPARRAY, has to match the name of the
// type defined in the PL/SQL Stored Function
stmt.registerOutParameter( 1, OracleTypes.ARRAY,"ty_id" );
stmt.executeUpdate();
// Get the ARRAY object and print the meta data assosiated with it
ARRAY simpleArray = stmt.getARRAY(1);
System.out.println("Array is of type " + simpleArray.getSQLTypeName());
System.out.println("Array element is of type code "+simpleArray.getBaseType());
System.out.println("Array is of length " + simpleArray.length());
// Print the contents of the array
// simpleArray.
String[] values = (String[])simpleArray.getArray();
System.out.println( "simpleArray.getArray()" +simpleArray.getArray());
for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" + values[i].toString() +"'" );
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345153 is a reply to message #344973] |
Tue, 02 September 2008 07:29   |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Another option on the function code is to use bulk operations thee, along these lines:
create or replace function fn_returnname_bulk(id ty_id)
return ty_name
as
custNames ty_name;
begin
select name
bulk collect into custNames
from customers where custid in
(select * from table(cast(id as ty_id)));
return custNames;
end;
/
|
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345687 is a reply to message #345158] |
Thu, 04 September 2008 08:04   |
raimanish4u
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hi Rajat,
Can I return more then one column in a function?
As U have given example of customers table :
CREATE OR REPLACE FUNCTION fn_returnname(id ty_name)
RETURN ty_name
AS
custNames ty_name:=ty_name();
BEGIN
FOR i IN id.first..id.last loop
custNames.extend();
select
name into custNames(i)
from customers where custid=id(i);
end loop;
return custNames;
END;
Here, I want to return value of CITY field also with NAME field in the same variable custNames of ty_name data type.
Can I return more then one field in single object custNames?
Thanks in advance.
Regards,
manish.
|
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345823 is a reply to message #345687] |
Thu, 04 September 2008 23:12   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
CREATE TYPE ty_cust AS OBJECT
(
name VARCHAR2(20),
city VARCHAR2(20)
);
CREATE TYPE tbl_ty_cust AS TABLE OF ty_cust;
CREATE OR REPLACE FUNCTION FN_RETURNNAME(ID TY_NAME)
RETURN TBL_TY_CUST
AS
CUSTNAMES TBL_TY_CUST;
BEGIN
SELECT
CAST(MULTISET(
SELECT
NAME,CITY
FROM
CUSTOMERS WHERE
CUSTID IN (SELECT * FROM TABLE(CAST(ID AS TY_NAME)))
)
AS TBL_TY_CUST
) INTO CUSTNAMES FROM DUAL;
RETURN CUSTNAMES;
END;
SELECT * FROM TABLE(fn_returnname(ty_name(1,2,3)));
SELECT * FROM TABLE(fn_returnname(cast(multiset(SELECT custid FROM CUSTOMERS) AS ty_name)));
/
Regards,
Rajat
[Updated on: Thu, 04 September 2008 23:21] Report message to a moderator
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345946 is a reply to message #345823] |
Fri, 05 September 2008 06:45   |
raimanish4u
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Thanks Rajat,
I have one more doubt.
Can I insert an array of data in a table without using loop?
CREATE OR REPLACE TYPE ty_id AS TABLE OF number(20);
CREATE OR REPLACE Procedure Pro_insertArray1(id ty_id)
IS
begin
forall i in id.first..id.last
insert into customers1 values (id(i),
'Manish',
'JSR');
end;
But here I don't want to use loop i just want to insert array of data by writing a single insert statement.is it posible?
Or. suppose my input is array of object can is insert all record in a single insert statement without using loop?
Regards,
Manish.
|
|
|
|
|
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345962 is a reply to message #345959] |
Fri, 05 September 2008 07:23   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Mainsh We can't solve your homework for you.
Why don't you brush up your basics first with collections.
It will really help you out.
We can provide you the soltion but that will not help
you in long term.
If you really wan't some help.
Try it by yourself.Do google
On www.4shared.com
you can find many pl/sql books for free.
Just download one of them and do the job.
If still problem is thier we are ready to help.
Regards,
Rajat
|
|
|
|
|