Home » SQL & PL/SQL » SQL & PL/SQL » not urgent - problem writing into text file in oracle 8i
not urgent - problem writing into text file in oracle 8i [message #241889] Wed, 30 May 2007 19:00 Go to next message
pavan27
Messages: 50
Registered: December 2006
Location: bangalore
Member
hai to everyone,

i am kumar. working as oracle DBA. now i have to write one procedure to send query results into text files. in oracle 8i

my query contain nearly 6 table s. the result would be send to text files..

how can i do this task. can any one send sample procedure to me..

it is very urgent . i know how to do it in oracle 10g.
but i dont have idea in oracle 8i..


thanks


[Updated on: Thu, 31 May 2007 11:20] by Moderator

Report message to a moderator

Re: urgent problem writing into text file in oracle 8i [message #241890 is a reply to message #241889] Wed, 30 May 2007 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>it is very urgent .
It is not urgent to me.
>i know how to do it in oracle 10g.
Then do the same in 8i
>but i dont have idea in oracle 8i..
See above.

Did you EVEN try it before posting?
In the future PLEASE follow posting guidelines as included in the #1 STICKY post at the top of this forum.
Re: urgent problem writing into text file in oracle 8i [message #242029 is a reply to message #241890] Thu, 31 May 2007 07:49 Go to previous messageGo to next message
pavan27
Messages: 50
Registered: December 2006
Location: bangalore
Member
i am very sorry for my message. i Apologize to the forum administrator for my previous posting.

the reason why i send this message is lot of pressure. i started my career from oracle 91, and now i am working on 10g. even i searched
entire site for that procedure/ hints . i did not find it.

i did some work on 8i but still i am getting some errors.

this is my code

create or replace PROCEDURE Ex_ENR_PARTIC_sp
AS
v_row_count number := 0;
Begin
Declare Cursor
CSR_ENR_PARTIC IS
select
||e.empno
||e.ename
||e.sal
||d.deptno
||d.dname
from
emp e, dept d
where e.deptno=d.deptno;
csr_Columns CSR_ENR_PARTIC%ROWTYPE;
Begin
Ben_Extract_Package.O_SqlCode := 0;
Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
Ben_Extract_Package.I_FileName := 'ENR_PARTIC.txt';
Ben_Extract_Package.I_FilePath := 'C:\pavan\';
I_FileType := utl_file.fopen (I_FilePath,I_FileName,'W');
Ben_Extract_Package.O_RowCount := 0;
-- Fetch rows and write records. The Cursor Open, Fetch and Close is dynamic
For csr_Columns IN CSR_ENR_PARTIC
Loop
v_row_count := v_row_count + 1;
utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
End Loop;
utl_file.fclose (Ben_Extract_Package.I_FileType);
end;
END Ex_ENR_PARTIC_sp;
/
after executing this i am getting this error..

--------------------------------------------
Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE EX_ENR_PARTIC_SP:

LINE/COL ERROR
-------- -----------------------------------------------
7/12 PL/SQL: SQL Statement ignored
8/12 PL/SQL: ORA-00936: missing expression

i don't know what is the problem.. where i am doing wrong..

that is the reason i send it very urgent message..

once again i am very sorry..

[Updated on: Thu, 31 May 2007 07:50]

Report message to a moderator

Re: urgent problem writing into text file in oracle 8i [message #242040 is a reply to message #242029] Thu, 31 May 2007 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and appy How to format your posts.
Post exactly what you execute with line numbers.

Have a look at your select clause.
It begins with "select ||..."
Don't you think something is missing before ||?

Regards
Michel
Re: urgent problem writing into text file in oracle 8i [message #242060 is a reply to message #241889] Thu, 31 May 2007 09:12 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You have a number of problems. You can't start a concatenated string with the ||. Your next problem is that I do not see anything in your cursor with the alias of "unload_string".

Re: urgent problem writing into text file in oracle 8i [message #242067 is a reply to message #241889] Thu, 31 May 2007 09:22 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

Begin
Declare Cursor
CSR_ENR_PARTIC IS ...



Correct me if I'm wrong but as far as I know, cursor declarations go in the ... declarations section (i.e. after the IS|AS and BEFORE Begin

(Along with all the other stuff that's not right
Re: urgent problem writing into text file in oracle 8i [message #242070 is a reply to message #241889] Thu, 31 May 2007 09:30 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Try the following. Please be aware that utl_file will ONLY write to a pre authorized directory on the database server. If the "c:\pavan" path is not on the server and has been configured as a directory object by your dba, this will NOT work.

create or replace PROCEDURE Ex_ENR_PARTIC_sp AS
  v_row_count number := 0;
  I_FileType utl_file.file_type;

  Cursor CSR_ENR_PARTIC IS
  select e.empno||e.ename||e.sal||d.deptno||d.dname unload_string
  from emp e, dept d
  where e.deptno=d.deptno;

csr_Columns CSR_ENR_PARTIC%ROWTYPE;
Begin
  Ben_Extract_Package.O_SqlCode := 0;
  Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
  Ben_Extract_Package.I_FileName := 'ENR_PARTIC.txt';
  Ben_Extract_Package.I_FilePath := 'C:\pavan\';
  I_FileType := utl_file.fopen (I_FilePath,I_FileName,'W');
  Ben_Extract_Package.O_RowCount := 0;
  -- Fetch rows and write records. The Cursor Open, Fetch and Close is dynamic
  For csr_Columns IN CSR_ENR_PARTIC Loop
    v_row_count := v_row_count + 1;
    utl_file.put_line(Ben_Extract_Package.I_FileType,
                      csr_Columns.Unload_String);
    Ben_Extract_Package.O_RowCount :=  ben_Extract_Package.O_RowCount + 1;
  End Loop;
  utl_file.fclose (Ben_Extract_Package.I_FileType);
END Ex_ENR_PARTIC_sp;
/
Previous Topic: Functions problem in 9i
Next Topic: Error in Executing Spool file
Goto Forum:
  


Current Time: Mon Dec 05 13:18:29 CST 2016

Total time taken to generate the page: 0.12681 seconds