Re: passing IN parameter to stored procedure

From: earl <earlcoombs_at_gmail.com>
Date: 10 Aug 2005 12:36:53 -0700
Message-ID: <1123702613.815487.59880@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. Received on Wed Aug 10 2005 - 14:36:53 CDT