Re: pl/sql question

From: <DBarbour_at_austin.isd.tenet.edu>
Date: Tue, 02 Oct 2001 10:34:57 -0700
Message-ID: <F001.0039F179.20011002102503@fatcity.com>

Yep - here's an example. There is really a whole lot more that goes with this, but I've included the pertinent portions so you can get an idea. Hope this helps.

SET DEFINE OFF; CREATE OR REPLACE PROCEDURE Student_Course_Report(

        fromSchool      varchar2,
        toSchool        varchar2,
        fromCourse      varchar2,
        toCourse        varchar2)

        schoolWhereClause       varchar2(250);
        courseWhereClause       varchar2(250);


        TYPE RefCurType IS REF CURSOR;
        schoolCur       RefCurType;

        v_schoolnum                  varchar2(3);
        v_schoolname            varchar2(35);
       ACRSVar                 varchar2(13);

BEGIN IF fromSchool = 'All Schools' THEN

           schoolWhereClause:= ' Where schoolnum
                not in ( ' || '''' || '800' || '''' ||
                ' , ' || '''' || 'D01' || '''' || ')
                Order by schoolnum';

        END IF;

        OPEN schoolCur for
           'Select schoolnum, name
           From sasi.asch ' ||
           schoolWhereClause;

           LOOP
             Fetch schoolCur into v_schoolnum, v_schoolname;
           EXIT WHEN schoolCur%NOTFOUND;

             ACRSVar:= 'sasi.ACRS1'||v_schoolnum;

                OPEN courseCur for
                  'Select statecrs1, title, course
                   From ' || ACRSVar ||
                   courseWhereClause;

                   LOOP
                     Fetch coursecur into v_statecrs1, v_title,
                        v_course;
                   EXIT WHEN courseCur%NOTFOUND;

David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002

                                                                                       
                       
                    Eric.Chesebro@                                                     
                       
                    chase.com            To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>  
                    Sent by:             cc:                                           
                       
                    root_at_fatcity.c       Subject:     pl/sql question                  
                       
                    om                                                                 
                       
                                                                                       
                       
                                                                                       
                       
                    10/02/2001                                                         
                       
                    11:50 AM                                                           
                       
                    Please respond                                                     
                       
                    to ORACLE-L

Can I somehow use a variable for the table name in a cursor select?

Here is the example:

--Declaration Section

sSrcTableName VARCHAR2(50) := iFeedNm||'_1_1_' ||TO_CHAR(SYSDATE,'YYMMDD')||'_SRC';

     --cursor for tmo daily source records
     CURSOR cTMODaily IS
          SELECT    *
          FROM           sSrcTableName;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Eric.Chesebro_at_chase.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: DBarbour_at_austin.isd.tenet.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message