Re: Bind Variables in a Procedure

From: AlexJent <alexjent_at_aol.com>
Date: 1997/01/25
Message-ID: <19970125025200.VAA28748@ladder01.news.aol.com>#1/1

I don't think you really need a bind variable to do what you want. I created a table
called TEST with two primary key columns... Tweaked your code a bit and ran it against my TEST table and it works just fine.

Maybe you just want to use a bind variable????

Any way you code "tweaked" is below:

---

set serveroutput on

CREATE OR REPLACE
PROCEDURE PKEY (TABLENAME VARCHAR2)
AS
  CURSOR COLUM IS
    SELECT COLUMN_NAME

      FROM ALL_CONS_COLUMNS
     WHERE CONSTRAINT_NAME IN (
 	      SELECT CONSTRAINT_NAME 
        	FROM ALL_CONSTRAINTS
       		WHERE TABLE_NAME = TABLENAME
         	  AND CONSTRAINT_TYPE = 'P'

    );

  PRIMKEY COLUM%ROWTYPE; BEGIN
  OPEN COLUM;
  LOOP
    FETCH COLUM INTO PRIMKEY;
    EXIT WHEN COLUM%NOTFOUND;     dbms_output.put_line (PRIMKEY.COLUMN_NAME);   END LOOP; ---   CLOSE COLUM;
END;
/

SHOW ERRORS EXECUTE PKEY('TEST')
/

Alex Jentilucci - Consultant
Information System Dynamics, Inc
Minneapolis, MN 55323
Tele: 612-476-7395 FAX: 612-473-5954 E-Mail: infosys_at_aol.com

E-Mail Work: jentilucci_at_notes.seagate.com

          Home: AlexJent_at_aol.com Received on Sat Jan 25 1997 - 00:00:00 CST