Prev: dbstart with wrong shell / wrong systax
Next: How to determine the Oracle session's constraint state?
From: The Magnet on 3 Feb 2010 16:26

Hi,

Can you call a procedure from SQLPLUS that takes a collection as
input?

INSERT_PORTFOLIO_ARR (
p_customer_id NUMBER,
p_price IN_ARR,
p_shares IN_ARR,
p_date IN_ARR,
p_product VARCHAR2 DEFAULT NULL)

IN_ARR is defined as:

CREATE OR REPLACE TYPE "IN_ARR"
AS VARRAY (100) OF VARCHAR2(100)

Not sure how to do that. I'm looking around for some examples, but
has anyone ever attempted this?

Many thanks!
From: Malcolm Dew-Jones on 3 Feb 2010 16:25
The Magnet (art(a)unsu.com) wrote:

: Hi,

: Can you call a procedure from SQLPLUS that takes a collection as
: input?

: INSERT_PORTFOLIO_ARR (
: p_customer_id NUMBER,
: p_price IN_ARR,
: p_shares IN_ARR,
: p_date IN_ARR,
: p_product VARCHAR2 DEFAULT NULL)

: IN_ARR is defined as:

: CREATE OR REPLACE TYPE "IN_ARR"
: AS VARRAY (100) OF VARCHAR2(100)

: Not sure how to do that. I'm looking around for some examples, but
: has anyone ever attempted this?

Presumably you could use a declare/begin/end block, something like

declare
the_in_arr IN_ARR;
begin
the_in_arr := IN_ARR('first line goes here');
INSERT_PORTFOLIO_ARR( ... the_in_arr ... );
end;

Of course in my example I am assuming there is an IN_ARR() constructor
that accepts that single string parameter, you would use whatever is
appropriate. The EXEC sqlplus command is really just a short cut for
begin/end, and I asusme you could use exec something like this.

EXEC INSERT_PORTFOLIO_ARR(... IN_ARR('first line goes here') ...)

 | 
Pages: 1
Prev: dbstart with wrong shell / wrong systax
Next: How to determine the Oracle session's constraint state?