Re: passing IN parameter to stored procedure

"earl" <earlcoombs_at_gmail.com> wrote in message news:1123702613.815487.59880_at_o13g2000cwo.googlegroups.com...
> Well David, I certainly DO know how to do the select - the "from dual"
> got dropped when I copied it. Here is the complete SP for your perusal.
> It does not compile:
>
> 15:25:23 Box:[NT000137]Database:[SKAALE3]User:<SYS>
> create or replace function web_security.TestGetWebSecRoles(APP_NAME IN
> VARCHAR2,
> 15:25:57 2
> USER_GRPS IN VARCHAR2)
> 15:25:57 3 return VARCHAR2 is
> 15:25:57 4 SEC_ROLES VARCHAR2(20);
> 15:25:57 5 v_roleid VARCHAR2(30);
> 15:25:57 6
> 15:25:57 7 CURSOR c1 IS
> 15:25:57 8 SELECT roleid
> 15:25:57 9 FROM WEBSEC_USERS_VW
> 15:25:57 10 WHERE WEBSEC_USERS_VW.APPNAME = APP_NAME AND
> 15:25:57 11 WEBSEC_USERS_VW.USERNAME IN (SEC_GROUPS)
> 15:25:57 12 ORDER BY ROLEID;
> 15:25:57 13
> 15:25:57 14 begin
> 15:25:57 15
> 15:25:57 16 OPEN c1;
> 15:25:57 17 LOOP
> 15:25:57 18 FETCH c1
> 15:25:57 19 INTO v_ROLEID;
> 15:25:57 20 EXIT WHEN c1%NotFound;
> 15:25:57 21 SEC_ROLES := SEC_ROLES || v_roleid || ',';
> 15:25:57 22 END LOOP;
> 15:25:57 23 CLOSE c1;
> 15:25:57 24 SEC_ROLES := SEC_ROLES || '99';
> 15:25:57 25 return(SEC_ROLES);
> 15:25:57 26 end TestGetWebSecRoles;
> 15:25:57 27 /
>
> Warning: Function created with compilation errors.
>
> 15:25:57 Box:[NT000137]Database:[SKAALE3]User:<SYS>show erro
> Errors for FUNCTION WEB_SECURITY.TESTGETWEBSECROLES:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 8/5 PL/SQL: SQL Statement ignored
> 11/41 PL/SQL: ORA-00904: "SEC_GROUPS": invalid identifier
>
> What I needed help with was how to pass a string parameter containing a
> proper list to a cursor. I solved this earlier today by parsing the
> string into individual USERS and opening and closing the cursor one
> user at a time. On a small table (my environment) that works fine, but
> as you probably know, that wouldn't scale up very well.
>

Go to asktom.oracle.com and do a search. Conceptually you declare a type and cast that type to a table.
Jim Received on Wed Aug 10 2005 - 20:35:48 CDT