Home » SQL & PL/SQL » SQL & PL/SQL » Regarding passing Array as a argument in the stored procedure/Function
Regarding passing Array as a argument in the stored procedure/Function [message #344973] Tue, 02 September 2008 01:13 Go to next message
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 #344994 is a reply to message #344973] Tue, 02 September 2008 02:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Can you post what you have tried.

Regards,
Rajat
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 Go to previous messageGo to next message
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 #345027 is a reply to message #345022] Tue, 02 September 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Tue, 02 September 2008 03:05]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #345105 is a reply to message #345102] Tue, 02 September 2008 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 02 September 2008 10:04
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #345135 is a reply to message #345132] Tue, 02 September 2008 06:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sorry i don't have idea regarding JDBC my friend.

Can you paste the code how you are calling it from JDBC.

what i can guess is that you are not assigning the value returned via the function to correct datatype.

Regards,
Rajat

[Updated on: Tue, 02 September 2008 07:01]

Report message to a moderator

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #345158 is a reply to message #345144] Tue, 02 September 2008 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 02 September 2008 12:46
Michel Cadot wrote on Tue, 02 September 2008 10:04
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel




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 Go to previous messageGo to next message
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 #345694 is a reply to message #345687] Thu, 04 September 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 02 September 2008 14:40
Michel Cadot wrote on Tue, 02 September 2008 12:46
Michel Cadot wrote on Tue, 02 September 2008 10:04
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel






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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #345950 is a reply to message #345946] Fri, 05 September 2008 07:00 Go to previous messageGo to next message
anuragsv
Messages: 5
Registered: September 2008
Junior Member
Hint:-

insert into <table name> select * from Table(


Regards,
Anuragsv

[Updated on: Fri, 05 September 2008 07:07]

Report message to a moderator

Re: Regarding passing Array as a argument in the stored procedure/Function [message #345959 is a reply to message #345950] Fri, 05 September 2008 07:13 Go to previous messageGo to next message
raimanish4u
Messages: 8
Registered: September 2008
Junior Member
Yes, this query I know, but I need to take input from user or any file as we have did in Stored function/Procedure.
suppose we have hundreds of record or data. So how can i insert these data/record in a table 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 Go to previous messageGo to next message
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
Re: Regarding passing Array as a argument in the stored procedure/Function [message #345963 is a reply to message #345946] Fri, 05 September 2008 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 04 September 2008 16:03
Michel Cadot wrote on Tue, 02 September 2008 14:40
Michel Cadot wrote on Tue, 02 September 2008 12:46
Michel Cadot wrote on Tue, 02 September 2008 10:04
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel








Re: Regarding passing Array as a argument in the stored procedure/Function [message #345975 is a reply to message #344973] Fri, 05 September 2008 07:49 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Also, I don't consider forall to be a loop. It is a single statement.
Previous Topic: Not exist
Next Topic: AGGREGATE FUNCTIONS
Goto Forum:
  


Current Time: Tue Dec 06 14:25:21 CST 2016

Total time taken to generate the page: 0.10198 seconds