Re: Dynamic SQL

"DAVID" <david_nat2000_at_yahoo.com> wrote in message news:db8bd827.0401310009.7b0cf39b_at_posting.google.com...
| 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

as sybrand pointed out, this is a reporting (SQL*Plus command) issue, not a SQL issue

but you also need to look into a couple other issues: 1) execute immediate is superfluous in this code -- only use it when the SQL statement must be constructed at runtime 2) in fact, the PL/SQL block is superfluous for this task -- just execute the SQL statement all be itself, unless you are going to add some procedural elements
3) your TEST table does not have a primary key -- bad design practice unless this is actually a global temporary table, which may justify the omission 4) depending on the problem you're trying to solve, you may not even need the table -- if all you're doing is reporting results as indicated, you probably want to use either a single SELECT statement to get all needed results in one operation, or, if necessary, several SELECTs combined with the UNION operator

• mcs