Re: Bind Variables in a Procedure

AJeff Davidson wrote:
>
> Can someone help me with this procedure? I want to be able to say
>
> SQL>EXEC PKEY('CLIENT');
>
> and have it return the primary key of table CLIENT (or whatever table).
> However to do this I need to use a bind variable. I have searched
> through manuals and the online help for examples of using bind
> variables, but there is nothing that shows a full example of where and
> to declare one. Each example is a separate line, not showing everything
> together.
>
> My procedure looks like the following, but I need to declare a bind
> variable, which I've called CNAME type CHAR(30), and I can't figure out
> where or how to declare it. I've tried the declaration in many
> different places and many different ways.
>
> CREATE OR REPLACE PROCEDURE PKEY (TABLENAME VARCHAR2) 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 varchar2(30); /* add this and it ought to be fine */
> BEGIN
> FOR COL IN THEKEYS LOOP
> :CNAME := COL.PRIMKEY_COLUMN;
> PRINT (CNAME);
> END LOOP;
> END;
> /
>
> Can anyone out there drop a bind variable declaration into the above
> procedure so that it will compile? (or if someone has a better way of
> doing the same thing, that would be appreciated too). A reply by email
> would be primo, but is not necessary. Thanks for any help you can give!
>
> jeffdav_at_intergate.bc.ca

If you use the procedure builder this is all a lot easier...

...but you can declare in a stored procedure just like you did for your COL variable (which is pretty close to being a reserved word by the way)...e.g.

CNAME varchar2(30);

this syntax will definitely work when done via the procedure builder... Received on Fri Jan 24 1997 - 00:00:00 CST