SQL & PL/SQL

Hi all

when i run the following Pl/SQL,nothing is happening

I want to pass array to following procedure

DECLARE
SQL_STMT      VARCHAR2(1000);
BEGIN
SQL_STMT  := 'Select '|| '''populate_table_test(''' || '|| chr(39)||id_name||chr(39)||' ||''','''||'|| chr(39)||attribute1||chr(39)||' ||''','''||'|| chr(39)||attribute2||chr(39)||' ||''');'''||' from table_test1';
dbms_output.put_line(sql_stmt);
execute immediate sql_stmt;
Exception when others then
dbms_output.put_line(sqlerrm);

end;
/

SQL> ed
Wrote file afiedt.buf

  1  Select 'populate_table_test('|| chr(39)||id_name||chr(39)||','||
  2  chr(39)||attribute1||chr(39)||','|| chr(39)||attribute2||chr(39)||');' from
  3* table_test
SQL> /

'POPULATE_TABLE_TEST('||CHR(39)||ID_NAME||CHR(39)||','||CHR(
------------------------------------------------------------
populate_table_test('AAA','AA','AA');
populate_table_test('AAA','AA','AA');

CREATE
  TABLE TABLE_TEST1
  (
    "ID_NO"      NUMBER(10,0)     ,
    "ID_NAME"    VARCHAR2(10 BYTE),
    "ATTRIBUTE1" VARCHAR2(10 BYTE),
    "ATTRIBUTE2" VARCHAR2(10 BYTE)
  );


Insert into TABLE_TEST1 (ID_NO,ID_NAME,ATTRIBUTE1,ATTRIBUTE2) values (null,'AAA','AA','AA');
Insert into TABLE_TEST1 (ID_NO,ID_NAME,ATTRIBUTE1,ATTRIBUTE2) values (null,'AAA','AA','AA');
commit;

CREATE TABLE TABLE_TEST
(ID_NO NUMBER(10) , 
id_NAME VARCHAR2(10) , 
ATTRIBUTE1 VARCHAR2(10), 
ATTRIBUTE2 VARCHAR2(10)
);

select * from "TABLE_TEST"; 


CREATE OR REPLACE TYPE T_TYPE IS OBJECT ( 
id_NAME VARCHAR2(10), 
ATTRIBUTE1 VARCHAR2(10), 
ATTRIBUTE2 VARCHAR2(10) 
) ;

Create or replace  type TB_T_TYPE as varray(200) of  T_TYPE ;


CREATE OR REPLACE
PROCEDURE POPULATE_TABLE_TEST (EXAMPLE IN TB_T_TYPE) AS 

begin 

FOR I IN 1..EXAMPLE.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(TREAT(EXAMPLE(I) AS T_TYPE).ID_NAME);
DBMS_OUTPUT.PUT_LINE(TREAT(EXAMPLE(I) AS T_TYPE).ATTRIBUTE1);
DBMS_OUtPUT.PUT_LINE(TREAT(EXAMPLE(I) AS T_TYPE).ATTRIBUTE2);
insert into TABLE_TEST(id_name,attribute1,attribute2) 
values (treat(example(i) as T_TYPE).id_NAME, 
treat(example(i) as T_TYPE).ATTRIBUTE1, 
treat(example(i) as T_TYPE).ATTRIBUTE2 
);
end loop;
end; 

Select 'populate_table_test('|| chr(39)||id_name||chr(39)||','||
chr(39)||attribute1||chr(39)||','|| chr(39)||attribute2||chr(39)||');' from
table_test

Edited by: user1849 on Nov 30, 2011 12:40 PM

Edited by: user1849 on Nov 30, 2011 1:18 PM