| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie Dynamic SQL Question
A copy of this was sent to bravo6500_at_my-deja.com
(if that email address didn't require changing)
On Wed, 08 Dec 1999 00:14:43 GMT, you wrote:
>Thanks for the reply. I have come across the DESCRIBE_COLUMNS procedure
>in pl/sql 8 and am trying to use that to build the query. I can get
>information such as the name, type, etc of the columns in the specified
>table from this procedure. I am now trying to take that information and
>build my dynamic query with that information. When I use the procedure
>DEFINE_COLUMN, it appears that I have to create different versions of
>the output definition dependent on what the datatype of the column is.
>In reading the documentation, I am unaware as to what the procedure is
>looking for in its 'column' parameter. In the examples it seems that
>the 'column' parameter (e.g. DEFINE_COLUMN(c IN INTEGER, position IN
>INTEGER, column)) is just taking in the type of the column. However,
>the datatype of the column paramater varies. Do I just have to pass an
>example of the datatype in the 'column' parameter? I'm sorry if the
>question is muddled, I'm quite confused. Any help is greatly greatly
>appreciated.
>
>
just use one variable and bind everything to a varchar2. Since you are creating a flat file -- they'll all get converted to strings ultimately anyhow. Here is a dump_csv that works with utl_file, give it a query and it dumps it:
create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
for i in 1 .. 1000 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
l_status := dbms_sql.execute(l_theCursor);
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/
create or replace procedure test_dump_csv as
l_rows number;
begin
l_rows := dump_csv( 'select * from all_users where rownum < 25', ',',
'/tmp', 'test.dat' );
end;
>
>
>In article <82h7l2$uiq$1_at_nnrp1.deja.com>,
> AleX <korrozia_at_my-deja.com> wrote:
>> Yes, you will need to build the statements dynamically.
>DBA_TAB_COLUMNS
>> is the view that I'd use - it gives the name name of the columns in
>the
>> right sequence. You can also datatypes from it. However, it gets
>pretty
>> hairy when you have to bind the columns of the return dataset. So you
>> may want to define the max number of columns, etc.
>>
>> In article <82h5jq$t2b$1_at_nnrp1.deja.com>,
>> bravo6500_at_my-deja.com wrote:
>> > Hello,
>> > I am trying to write a stored procedure that takes a user
>inputted
>> > tablename and dumps all the contents of the table into a flat file.
>> > Since I will be using the dbms_sql package to build my query, it
>seems
>> > that it is necessary to definte output variables. I am not sure how
>> to
>> > proceed since the output variables will vary dependent on what
>> > tablename the user enters. Will I just have to write a long
>> > conditional statement that looks explicitly for what table the user
>> > enters then picks the output variables from it, or is there a
>sleeker,
>> > more efficient way of doing this? Any advice is greatly
>appreciated.
>> >
>> > Thanks.
>> > MM
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.
>> >
>>
>> --
>> Alex Shterenberg
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Dec 07 1999 - 19:30:26 CST
 |
 |