Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Stored procedure parameter

Re: Stored procedure parameter

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 09 Aug 2007 08:25:54 -0700
Message-ID: <1186673154.588014@bubbleator.drizzle.com>


Florian Reiser wrote:
> Hello sfauchille,
>
> in the stored procedure you first have to parse your string and convert
> the elements back to numbers again.
>
> With kind regards
> Florian Reiser
> http://www.ra-bc.de

No you don't. You can use CAST. Here's one example:

conn scott/tiger

CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(20); /

set serveroutput on

DECLARE
  i PLS_INTEGER;
  x InStrTab := InStrTab('10','30');
BEGIN
   SELECT COUNT(*)
   INTO i
   FROM emp
   WHERE deptno IN (

     SELECT column_value
     FROM TABLE(CAST(x AS InStrTab)));

   dbms_output.put_line(i);
END;
/

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Aug 09 2007 - 10:25:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US