Hi Team,
well i have a requirement i have a table here is the script of the table.
CREATE TABLE SCOTT.T
(
SOURCE VARCHAR2(50 BYTE),
ATLAS VARCHAR2(20 BYTE),
HER VARCHAR2(20 BYTE),
BUZZ VARCHAR2(20 BYTE),
AUDI VARCHAR2(200 BYTE),
BENZ VARCHAR2(20 BYTE),
BENTLE VARCHAR2(20 BYTE),
WINDOWS VARCHAR2(20 BYTE)
)
/* inserts */
insert into T
(SOURCE, ATLAS, HER, BUZZ, AUDI,
BENZ, BENTLE, WINDOWS)
Values
('kansas', '100', '200', '300', '400',
'500', '60', '70');
Insert into T
(SOURCE, ATLAS, HER, BUZZ, AUDI,
BENZ, BENTLE, WINDOWS)
Values
('texas', '10', '20', '30', '40',
'50', '60', '70');
Insert into T
(SOURCE, ATLAS, HER, BUZZ, AUDI,
BENZ, BENTLE, WINDOWS)
Values
('austr', '0', '60', '90', '80',
'63', '78', '74');
Insert into T
(SOURCE, ATLAS, HER, BUZZ, AUDI,
BENZ, BENTLE, WINDOWS)
Values
('newark', '60', '32', '74', '95',
'93', '47', '522');
Insert into T
(SOURCE, ATLAS, HER, BUZZ, AUDI,
BENZ, BENTLE, WINDOWS)
Values
('calfornia', '89', '87', '45', '96',
'78', '88', '88');
After creation of the table and inserting the data into the tables the table look as below
sql statment : select * from t ;
SOURCE ATLAS HER BUZZ AUDI BENZ BENTLE WINDOWS
kansas 100 200 300 400 500 60 70
texas 10 20 30 40 50 60 70
austr 0 60 90 80 63 78 74
newark 60 32 74 95 93 47 522
calfornia 89 87 45 96 78 88 88
5 rows selected.
Here my requirement starts. I need to write a procedure where the sql statement ("select * from t") will be present in a variable ("l_query")
i.e (l_query = select * from t) and using DBMS_SQL i need to insert all the data which comes from the select statement into the below table 'n'
*/output table syntax */
create table n
(
source varchar2(20),
column_nm varchar2(20),
actual_value number
);
As the table structure is above.As i am mentioning the required output after running the procedure should be as below as per my requirement.As i am unable to write i am seeking your help for the procedure
/* after compining the procedure my out put should be like */
select * from n;
/*output*/
source column_nm actual_value
kansas ATLAS 100
kansas HER 200
kansas BUZZ 300
kansas AUDI 400
kansas BENZ 500
kansas BENTLE 60
kansas WINDOWS 70
texas ATLAS 10
texas HER 20
texas BUZZ 30
texas AUDI 40
texas BENZ 50
texas BENTLE 60
texas WINDOWS 70
austr ATLAS 0
austr HER 60
austr BUZZ 90
austr AUDI 80
austr BENZ 63
austr BENTLE 78
austr WINDOWS 74
newark ATLAS 60
newark HER 32
newark BUZZ 74
newark AUDI 95
newark BENZ 93
newark BENTLE 47
newark WINDOWS 522
calfornia ATLAS 89
calfornia HER 87
calfornia BUZZ 45
calfornia AUDI 96
calfornia BENZ 78
calfornia BENTLE 88
calfornia WINDOWS 88
select 35 rows
The output should be as below each row should contain a source i.e(calfornia) along with column name next to the source i.e(atlas) and then the value for each column i.e(100). The rows should be inserted (calfornia atlas 89) ( source column_nm value) into the n table. I hope i am quiet breif about my question hope you reply me with my procedure .
Thank u
raj.
I have a script to list out data in such as way, eg
SQL> @pt "select * from t"
SOURCE : kansas
ATLAS : 100
HER : 200
BUZZ : 300
AUDI : 400
BENZ : 500
BENTLE : 60
WINDOWS : 70
-----------------
SOURCE : texas
ATLAS : 10
HER : 20
BUZZ : 30
AUDI : 40
BENZ : 50
BENTLE : 60
WINDOWS : 70
-----------------
SOURCE : austr
ATLAS : 0
HER : 60
BUZZ : 90
AUDI : 80
BENZ : 63
BENTLE : 78
WINDOWS : 74
-----------------
SOURCE : newark
ATLAS : 60
HER : 32
BUZZ : 74
AUDI : 95
BENZ : 93
BENTLE : 47
WINDOWS : 522
-----------------
SOURCE : calfornia
ATLAS : 89
HER : 87
BUZZ : 45
AUDI : 96
BENZ : 78
BENTLE : 88
WINDOWS : 88
-----------------
So you can easily modify that to do inserts rather than dbms_output.put_line.
Here is the script
set serverout on size 999999
set verify off
declare
p_query varchar2(32767) := q'{&1}';
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
n number := 0;
procedure p(msg varchar2) is
l varchar2(4000) := msg;
begin
while length(l) > 0 loop
dbms_output.put_line(substr(l,1,80));
l := substr(l,81);
end loop;
end;
begin
execute immediate
'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
p( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
n := n + 1;
end loop;
if n = 0 then
dbms_output.put_line( chr(10)||'No data found '||chr(10) );
end if;
end;
/
set verify on