Search this site
for OWA_SYLK UTL_FILE to see an example from the database


SYLK is an industry standard file format (search GOOGLE for info on SYLK)

OWA_SYLK is a package Chris Beck and I wrote together -- its "freeware" available here.

see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:878828052727 <code>

Thats windows locking it.

I guess you'll have to open it, try to write to it and catch the exception (that is how you'll discover that it is already optening).

Else, you'll want to write a windows dll with windows specific OS calls that you can call from ORA_FFI. These windows specific calls (of which I know not -- not a windows programmer) would tell you if the file is locked or not.

Path of least resistance -- write a small routine that opens and trys to write to the file and then closes it. Have that return a boolean true or false. Call that utility routine.

Your where clause is probably getting the 6502 -- not the query string itself.

You are building a where clause that compares a number to a date or a string to a number or something and the conversion error is happening during the execution of the QUERY itself.

You can prove this by putting an exception block around the execute statement in the procedure. You can:

begin
execute it
exception
when others then raise_application_error( -20001, 'Error processing your query ' || sqlerrm );
end;

if you start getting ora-20001's you know it is the query itself that is generating the error (you are putting invalid where clauses there)

I see many many more then 2 ways.

3) use a join

select emp.empno, emp.ename, dept.dname, emp.hiredate
from emp, dept
where emp.depto = dept.deptno

4) use a select of a select

select empno, ename, (select dname from dept where dept.deptno=emp.deptno), emp.hiredate
from emp;



Use #3.

Doh -- read it again. How many tables are in query #3, now, how many queries are in query #4

The answer is the SAME.

You scare me (people like you do). the database was built to join, joins are not evil, improperly designed tables (eg: a lookup without an index perhaps) are. And in fact, query #3 would be infinitely preferable in all cases give this example -- and we would certainly hope the cost based optimizer would AVOID any and all indexes and do the right thing which would be a hash join for a large set!!!! That query doesn't WANT to use any indexes

No, you scare me because you are "afraid of joins", and that means you don't believe the database should be asked to do heavy lifting and that is exactly what the database is about.

I can show you 25 table joins that outperform a single table query.

I can show you the opposite.

The number of tables referenced in a query has no bearing whatever on its performance.


#4 is cool if you want to avoid an outer join and there is a change that a row in emp won't have a mate in dept. It has other uses as well.

The "how can I avoid an outer join" should be obvious. You use that technique (#4) to pick up the dname from dept instead of outer joining EMP to DEPT -- thats that.


Its other users might be to get the first rows of a complex query back faster -- to run little correlated subqueries for each row instead of a massive sort/aggregate.

I've used it when I don't know what table I want to join to -- consider:

select object_type, object_name,
decode( status, 'INVALID', '*', '' ) status,
decode( object_type,
'TABLE', (select tablespace_name from user_tables where
table_name = object_name),
'TABLE PARTITION', (select tablespace_name from
user_tab_partitions where partition_name = subobject_name),
'INDEX', (select tablespace_name from user_indexes where
index_name = object_name),
'INDEX PARTITION', (select tablespace_name from
user_ind_partitions where partition_name = subobject_name),
'LOB', (select tablespace_name from user_segments where
segment_name = object_name),
null ) tablespace_name
from user_objects a
order by object_type, object_name
/

(oh yes, I know I could have used user_segments -- I just found the performance of that to not be quite as good as this)

Use your imagination.

yes.

Not that I am aware of. Forms has a utility to let you pick an existing file (get_filename or something like that) but I'm not sure if it lets you pick a non-existent one.

try </code> http://otn.oracle.com/ <code>under discussion forums -> developer. I myself haven't written a form since 1995...

Not even we know the answer to that.

Sure, we can "estimate" it -- if you were to explain the query and you were using the cost based optimizer -- we attempt to guesstimate the cardinality of the result set:

scott@ORA920.LOCALHOST> analyze table emp compute statistics;
Table analyzed.

scott@ORA920.LOCALHOST> analyze table dept compute statistics;
Table analyzed.

scott@ORA920.LOCALHOST> set autotrace traceonly explain
scott@ORA920.LOCALHOST> select * from emp;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=14 Bytes=448)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)



scott@ORA920.LOCALHOST> select count(*) from emp;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14)



scott@ORA920.LOCALHOST> select * from emp, dept;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=56 Bytes=2800)
1 0 MERGE JOIN (CARTESIAN) (Cost=10 Card=56 Bytes=2800)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)
3 1 BUFFER (SORT) (Cost=8 Card=14 Bytes=448)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)



scott@ORA920.LOCALHOST> set autotrace off




So, you could explain the query and use the estimated total CARD but it could be way off.

The bottom line, until you fetch the last row -- neither one of us knows the total number of rows you'll be getting!

sorry -- don't do windows myself. the only thing i know is an open systems standard called SYLK that is not MS proprietary. It is a generic file format. Do not know if it does "work sheets" or not.

as i've said before -- i don't do windows.

i don't use excel

i quite simply "don't know"

it would be a virus at that stage.

or the clients filesystem needs be mounted to the unix machine so it appears as a local filesystem.

there are lots of utilities with the latest versions of forms that permit many more things -- you can ask about them on otn.oracle.com in the discussion forums (it has been a DECADE since I've touched forms -- this html thing came along and I've not looked back)