Re: Bind Variables in a Procedure

In article <32E7C442.7DA3_at_icv.net>, jeffd_at_icv.net wrote:

Do you want a function, not a procedure, then you want to declare the bind variable in sqlplus, like this:

var keycols varchar(2000) -- or some other suitably large area

Then you write your function, like this:

>CREATE OR REPLACE function PKEY (TABLENAME VARCHAR2)

   returns varchar(2000)
   AS
> CURSOR THEKEYS IS
> SELECT COLUMN_NAME PRIMKEY_COLUMN FROM USER_CONS_COLUMNS
> WHERE CONSTRAINT_NAME IN (
> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
> WHERE TABLE_NAME = TABLENAME
> AND CONSTRAINT_TYPE = 'P'
> );
> COL THEKEYS%ROWTYPE;

 cname varchar(2000);
>BEGIN
> FOR COL IN THEKEYS LOOP
> CNAME := COL.PRIMKEY_COLUMN || ', '; -- or whatever you want to
 separate columns
> END LOOP;
>END;
>/

(I dont' have the documentation here, and my PL/SQL is rusty. Check the order and spelling of keywords.)

To use the function in sqlplus (if you're declared the variable keycols):

>SQL>EXEC :keycols := PKEY('CLIENT');

To see what you've received:

SQL> print keycols -- note: here you don't put the colon

It took me a long time to figure this out. The documentation is even below ORACLE's usual standards on this topic.

-- 
Larry Reid                                          lcreid_at_web.net