Home » SQL & PL/SQL » SQL & PL/SQL » Formatting Data and inserting into a table
Formatting Data and inserting into a table [message #202170] Wed, 08 November 2006 08:17 Go to next message
amkotz
Messages: 72
Registered: May 2005
Location: Bangalore
Member
Hello All,


create or replace package my_dbms_sql
as
procedure define_all( p_cursor in integer );
type varchar2_table is table of varchar2(4000) index by
binary_integer;
function fetch_row( p_cursor in integer )
return varchar2_table;
end;
/

create or replace package body my_dbms_sql
as
g_number_of_columns dbms_sql.number_table;
procedure define_all( p_cursor in integer )
as
l_columnValue varchar2(4000);
l_descTbl dbms_sql.desc_tab2;
l_colCnt number;
begin
dbms_sql.describe_columns2( p_cursor,
l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column( p_cursor, i,
l_columnValue, 2000 );
end loop;
g_number_of_columns(p_cursor) := l_colCnt;
end;
function fetch_row( p_cursor in integer )
return varchar2_table
is
l_return varchar2_table;
begin
for i in 1 .. g_number_of_columns(p_cursor) loop
l_return(i) := NULL;
dbms_sql.column_value( p_cursor, i,
l_return(i) );
end loop;
return l_return;
end;
end;
/

set verify off
declare
l_theCursor integer default dbms_sql.open_cursor;
l_status integer;
l_data my_dbms_sql.varchar2_table;

procedure execute_immediate( p_sql in varchar2 )
is
BEGIN
dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
l_status := dbms_sql.execute(l_theCursor);
END;
begin
-- execute_immediate( 'alter session set nls_date_format= ''dd-mon-yyyy hh24:mi:ss'' ');
dbms_sql.parse( l_theCursor,
replace( 'select * from HIS_TMP_PAV_SEARCH where bea_id = 182805',dbms_sql.native );
my_dbms_sql.define_all( l_theCursor );
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_data := my_dbms_sql.fetch_row( l_theCursor );
for i in 1 .. l_data.count loop
dbms_output.put_line( l_data(i) ); --insert into some table...
end loop;
end loop;
-- execute_immediate( 'alter session set nls_date_format= ''dd-MON-yy'' ');
exception
when others then
execute_immediate( 'alter session set
nls_date_format=''dd-MON-yy'' ');
raise;
end;
/


I am trying to insert data into one table at one of the above location.

But i am not able to get, how to get the data at that point and insert into some table. I donot want to display the data.

Thanks in Advance,
Amkotz

[Updated on: Wed, 08 November 2006 08:19]

Report message to a moderator

Re: Formatting Data and inserting into a table [message #202311 is a reply to message #202170] Thu, 09 November 2006 01:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I must be missing something.

If you want to insert the data into a table, the Insert command would appear almost perfect.

What exactly are you trying to do, and what isn't working?
Re: Formatting Data and inserting into a table [message #202374 is a reply to message #202170] Thu, 09 November 2006 07:08 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
sql>show errors;

what do you get?
Previous Topic: SP2-0552: Bind variable "00" not declared.
Next Topic: simple sql question (Oracle 9.2)
Goto Forum:
  


Current Time: Fri Dec 02 16:58:50 CST 2016

Total time taken to generate the page: 0.19259 seconds