Re: sp and data convertion

From: Lee J. <ljeznach_at_csi.com>
Date: Sun, 17 Dec 2000 18:05:33 GMT
Message-ID: <Nf7%5.101596$_5.21835701@news4.rdc1.on.home.com>

OK computer geeks,
To those who are watching this question, I have a solution using temporary table.
Unfortunately the below solution is not enough for me. Perhaps one of you knows something beyond that.

set serveroutput on
CREATE GLOBAL TEMPORARY TABLE IDS (
  rid NUMBER(4)
)ON COMMIT DELETE ROWS; DECLARE
CURSOR MYTABLE IS
  SELECT IDS.* FROM IDS;
c_row IDS%ROWTYPE;

mystr VARCHAR2(100) := '1,10,11,112,1234'; x NUMBER(4) :=0;
myid NUMBER(4) :=0;

BEGIN
mystr := TRIM(mystr);
IF LENGTH(mystr) > 0 THEN
  x := INSTR(mystr,',');
  IF x > 0 THEN
    WHILE x > 0 LOOP

• check if SUBSTR(mystr,1,x-1) is a number if so myid := TO_NUMBER(SUBSTR(mystr,1,x-1)); INSERT INTO IDS(rid) VALUES(myid); mystr := SUBSTR(mystr,x+1); x := INSTR(mystr,','); END LOOP; END IF; -- first and the only or the last parameter myid := TO_NUMBER(mystr); INSERT INTO IDS(rid) VALUES(myid); -- OPEN MYTABLE; FETCH MYTABLE INTO c_row; WHILE MYTABLE%FOUND LOOP DBMS_OUTPUT.PUT_LINE('ROW:' || TO_CHAR(c_row.rid)); FETCH MYTABLE INTO c_row; END LOOP; COMMIT; -- ELSE DBMS_OUTPUT.PUT_LINE('ERROR: Parameter missing'); END IF;

EXCEPTION
  WHEN OTHERS THEN
  --error handling procedure
  DBMS_OUTPUT.PUT_LINE('ERROR:' || TO_CHAR(SQLCODE));   DBMS_OUTPUT.PUT_LINE('ERROR MSG:' || SUBSTR(SQLERRM, 1, 200)); END;
/

"Lee J." <ljeznach_at_csi.com> wrote in message news:G1c_5.81895$_5.17624360_at_news4.rdc1.on.home.com...
> Hello,
> I have one procedure
>
> PROCEDURE SP_TEST(RECORD_SET OUT CURSOR_TYPE, MYPARAM VARCHAR2)
> AS ...
> MYPARAM can hold as many characters as VARCHAR2 data type allows in the
> format '1,2,3,22,500'
> I would like to use it in my query where e.g. SELECT * FROM ... WHERE id
 IN
> (MYPARAM)
> Of course, it will not work because my parameter is of VARCAR2 type.
> As fare, as I know there is no function that would convert my parameter to
> anything that would work with my query.
> If any of you guys has any idea how to make it working then I v.
 appreciate
> your help.
> Thanks.
>
>
>
Received on Sun Dec 17 2000 - 12:05:33 CST