Dynamic SQL

Hi every one,

With regards to dynamic SQL, I have a following query:

I want to insert rows into a table TEST with the following structure: (ID,Source_Parameter)

I want to insert the name column of v$datafile in the column "source_parameter" of the above table "TEST".

I am using following command to insert the rows into that table:

Code:

begin
execute immediate
'insert into TEST (Parameter_ID, Source_Parameter) select 1, name from v$datafile';
end;
/

When I query the table it displays the following result:

PARAMETER_ID                 SOURCE_PARAMETER


------------                 ---------------

   1                         D:\ORACLE91\SYSTEM01.DBF
   1                         D:\ORACLE91\UNDOTBS01.DBF
   1                         D:\ORACLE91\CWMLITE01.DBF

With regards to the above results how should I suppress the repetition of 1 in the column PARAMETR_ID?

I want that, only 1 row display in the PARAMETER_ID column against different datafiles in the SOURCE_PARAMETE column. Because I want to add other parameters as well in the SOURCE_PARAMETER column like controlfile etc.

The bottom line is that I want the following results:

PARAMETER_ID                 SOURCE_PARAMETER


------------                 ---------------

   1                         D:\ORACLE91\SYSTEM01.DBF
                             D:\ORACLE91\UNDOTBS01.DBF
                             D:\ORACLE91\CWMLITE01.DBF
   2                         D:\ORACLE91\CONTROL01.CTL
                             E:\ORACLE91\CONTROL02.CTL
   3	                     D:\ORACLE91\REDO01.LOG
                             D:\ORACLE91\REDO02.LOG

Could some one give me the solution to do the above task?

Thanks

David Received on Sat Jan 31 2004 - 03:01:19 CST