-
How can I call this procedure from sql*plus worksheet (GUI) or sqlplus,
Please help ME. . . . .
TYPE vAryAttribute_name IS VARRAY(100) OF VARCHAR2(40);
create or replace PROCEDURE SearchAssay( InAryAttribute_name IN vAryAttribute_name) IS
sql_stmt VARCHAR2(4000);
sql_stmtatt VARCHAR2(1000);
idx SMALLINT;
BEGIN
FOR idx IN 1 .. InAryAttribute_name.count
LOOP
IF InAryAttribute_name(idx) IS NULL THEN
EXIT;
ELSE
IF (idx = 1) THEN
sql_stmtAtt := '(select distinct assay_id from assay_attribute where attribute_id = (select attribute_id from attribute where attribute_name = ''' || InAryAttribute_name(idx) ||'''))';
ELSE
sql_stmtAtt := '(select distinct assay_id from assay_attribute where assay_id IN '|| rtrim(sql_stmtatt) ||' AND attribute_id = (select attribute_id from attribute where attribute_name = ''' || InAryAttribute_name(idx) || '''))';
END IF;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(sql_stmtAtt);
END SearchAssay;
-
I think you can do it in the following way, you can pass values in the bracket also to send any value for the array.
declare
name varyattribute_name:=varyattribute_name();
begin
searchassay(name);
end;
or
declare
name varyattribute_name:=varyattribute_name('1','2');
begin
searchassay(name);
end;
-
Knarayan U R outstanding
ThanX a lot
