Hello,
I have an asp.net application which connects to SQL server using the
normal ado.net mechanism. One customer cannot get it to connect to the
database. his connection string is
user id=aaaa;password=bbbb;initial catalog=cccc;server=dddd
and he gets the message:
Cannot open database requested in login 'cccc'. Login fails. Login
failed for user 'aaaa'.
The customer insists that he has the values in the connection string
correct; the only unusual thing is that dddd is actually the IP address
rather than the host name of the SQL server box.
I am not in a position to do any direct investigation myself. many
other customers are using the same software without problems. does
anyone have any clues what might cause this error, other than invalid
username/password combination?
TIA
Andyajfish@.blueyonder.co.uk wrote:
> Hello,
> I have an asp.net application which connects to SQL server using the
> normal ado.net mechanism. One customer cannot get it to connect to the
> database. his connection string is
> user id=aaaa;password=bbbb;initial catalog=cccc;server=dddd
> and he gets the message:
> Cannot open database requested in login 'cccc'. Login fails. Login
> failed for user 'aaaa'.
> The customer insists that he has the values in the connection string
> correct; the only unusual thing is that dddd is actually the IP
> address rather than the host name of the SQL server box.
> I am not in a position to do any direct investigation myself. many
> other customers are using the same software without problems. does
> anyone have any clues what might cause this error, other than invalid
> username/password combination?
>
That's the only cause I know of.
if the IP address was the problem, he would be getting a "not found"
message rather than "login failed", which implies a connection to the
database was initiated, but terminated due to improper credentials.
The customer should check the sql error logs as well as the event logs
to see if any other errors or warnings are present.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||If the Sql Server is not enabled for "Mixed mode" (both SQL and Windows)
authentication, they could possibly get that error.
Peter
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com
"ajfish@.blueyonder.co.uk" wrote:
> Hello,
> I have an asp.net application which connects to SQL server using the
> normal ado.net mechanism. One customer cannot get it to connect to the
> database. his connection string is
> user id=aaaa;password=bbbb;initial catalog=cccc;server=dddd
> and he gets the message:
> Cannot open database requested in login 'cccc'. Login fails. Login
> failed for user 'aaaa'.
> The customer insists that he has the values in the connection string
> correct; the only unusual thing is that dddd is actually the IP address
> rather than the host name of the SQL server box.
> I am not in a position to do any direct investigation myself. many
> other customers are using the same software without problems. does
> anyone have any clues what might cause this error, other than invalid
> username/password combination?
> TIA
> Andy
>|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:enZRMTliGHA.4776@.TK2MSFTNGP05.phx.gbl...
> ajfish@.blueyonder.co.uk wrote:
> That's the only cause I know of.
when I looked at the error message a little closer and reproduced it, I
realised the problem was actually down to the database name being incorrect,
not the username and password
d'oh !
> if the IP address was the problem, he would be getting a "not found"
> message rather than "login failed", which implies a connection to the
> database was initiated, but terminated due to improper credentials.
> The customer should check the sql error logs as well as the event logs
> to see if any other errors or warnings are present.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:enZRMTliGHA.4776@.TK2MSFTNGP05.phx.gbl...
> ajfish@.blueyonder.co.uk wrote:
> That's the only cause I know of.
when I looked at the error message a little closer and reproduced it, I
realised the problem was actually down to the database name being incorrect,
not the username and password
d'oh !
> if the IP address was the problem, he would be getting a "not found"
> message rather than "login failed", which implies a connection to the
> database was initiated, but terminated due to improper credentials.
> The customer should check the sql error logs as well as the event logs
> to see if any other errors or warnings are present.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
Showing posts with label customer. Show all posts
Showing posts with label customer. Show all posts
Wednesday, March 21, 2012
Friday, February 17, 2012
Extracting
Dear experts,
I need to spool the results of the following extraction from PL/SQL
For each invoice no in TRANSACTION table, loop:
Txn no, customer code, date etc etc, tab-delimited
Then for each invoice no, get all invoice lines in TPRODLINE table, loop:
Txn line, product, qty, sale price etc etc, tab-delimited
end invoice lines loop
end invoice no loop
TRANSACTION table is joined to the TPRODLINE table by the transaction_id in both tables.
My questions:
1) should I do 2 cursors in the loops. 1 cursor to read the required invoice lines and pass the transaction no to the 2nd cursor to extract the transaction header infor. Then write/append this to the text file. then go back to the 2 loops? Will that work?
2) I also need help to spool this to a text file. Will UTL_FILE be able to help me? If so, anything I need to be careful about?
Any advice greatly appreciated!
Cheers,
Pete
Cheers,
Pete1) That depends on the size of table and you indexes.
I would create on SQL statement that joins all table and give me the result that I need. Look into the explain plan before executing.
2) Here is an example for using UTL_FILE
DECLARE
fHandle UTL_FILE.FILE_TYPE;
vTextIn varchar2(25);
vTextOut varchar2(25);
BEGIN
-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','w');
IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File write open');
ELSE
DBMS_OUTPUT.PUT_LINE('File write not open');
END IF;
vTextIn := 'Hello World';
vTextOut := 'World Hello';
-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR
UTL_FILE.PUT_LINE(fHandle,vTextIn);
DBMS_OUTPUT.PUT_LINE('Value write: '||vTextIn);
-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);
-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','r');
IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;
-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or READ_ERROR
-- NO_DATA_FOUND or VALUE_ERROR
UTL_FILE.GET_LINE(fHandle,vTextOut);
DBMS_OUTPUT.PUT_LINE('Value read: '||vTextOut);
-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);
DBMS_OUTPUT.PUT_LINE('Successful Completion');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');
WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20104,'Read Error');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20105,'Write Error');
WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20107,'No Data Found');
WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR(-20108,'Value Error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20109,'Unknown UTL_FILE Error');
END;
/
3) Set the option UTL_FILE_DIR to access other files on your OS
If you want to use more directories, separate them with a ;
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
Thank you so much for your help! An addition to the Oracle manual!
I am still a bit stuck with the SQL though!
Cheers,
Pete|||Hello,
when I understand your description you have two tables
One TRANSACTION table and one TPRODLINE table.
The reference is transaction_id ...
So just join with that transaction id
SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id
Here is the cursor process (a little bit old style :) )
DECLARE
CURSOR cuProcess IS
SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id
rProcess cuProcess%ROWTYPE; -- record structure
cOut VARCHAR2(2000);
BEGIN
OPEN cuProcess;
FETCH cuProcess INTO rProcess;
-- Open your file !!!
<<BeginLoop>>
WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;
-- spool your datas
cOut := rProcess.txnno || CHR(9) || .............;
END LOOP BeginLoop;
CLOSE cuProcess;
-- close your file
EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;
END;
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
The table join is simple.
But the required file consists of at least 3 lines for each invoice which makes it difficult.
Line 1:txn no, customer, address, date etc ...
Line 2 to nth:txn lineno, product code, unit price, qty, linetot
line nth+1:invoice totamt, standard greeting message
for invoice no 12345, customer = Burgerking with 2 invoice lines for products Whopper and fries, I need to product a text file of
Header,12345,burgerking,123 high street, 14-Apr-03(carriage return)
Lines,1,whopper,2.3,1,2.3(carriage return)
lines,2,fries,1.4,2,2.8(carriage return)
footer,5.1,thank you for buying from us(carriage return)
... (next invoice header)
header,12346
....
Once all these have been extracted successfully, I will need to update a date field in the transaction table to mark the invoice as "printed/extracted" ...
That's why I was thinking of using 2 cursors!
Any advice greatly appreciated (coz not many hair left on my head)!
Cheers,
Pete|||Hello,
that is something we call in germany "Gruppenwechsel" or group changing :)
You can use two cursor but you dont have to do that.
Do it in this way
DECLARE
cCompareField VARCHAR2(200);
BEGIN
cCompareField := "";
OPEN cuProcess;
FETCH cuProcess INTO rProcess;
-- Open your file !!!
<<BeginLoop>>
WHILE cuProcess%FOUND LOOP
IF cCompareField <> rProcess.taxno THEN
... do your header thing
cCompareField := rProcess.taxno;
END IF;
.. do your normal invoice stuff
FETCH cuProcess INTO rProcess;
-- spool your datas
cOut := rProcess.txnno || CHR(9) || .............;
END LOOP BeginLoop;
CLOSE cuProcess;
Hope this is clear enough otherwise please let me know ?!?!
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi
Thanks again for your help. Just to make sure I understand your coding:
1) the 1 cursor that you have used will select rows (including txn_id) from the transaction lines table.
2) first loop select records from transaction table based on txn_id in the cursor. then assign it to variable cOut and set cCompare to the txn_id.
3) then select transaction line data from transaction line table and append it to cOUT.
4) and repeat from (2) until cursor NOT FOUND.
5) then use cOUT with UTL_FILE ...
Is that correct? Thanks!
Happy easter!
Pete|||Hello,
thats completly correct ... if you need more groups like the id,
you can define more CompareField variable like it is shown in the example.
This is the classical way to program "group changing" ...
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,
You are a life saver!!
Thank you so much!!!
cheers,
Pete|||Hi
This time round I have got some question on UTL_FILE.
I used your eg. and added a loop for generating a count and writing it to a text file on my local drive using UTL.
Run the block and it just say completed successfully. But no file is generated.
I do not have Oracle client installed on my local machine. I also know the UTL_FILE_DIR parameter is set to null.
Is there anything I have not done correctly?
Thanks for any advice!
Cheers,
Pete|||Hello ...
can you please post the code ?
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi,
here is the code:
~~~~~~~~~~~~~~
DECLARE
fHandle UTL_FILE.FILE_TYPE;
cOut varchar2(25);
cCount NUMBER :=1;
BEGIN
--
-- Open file, Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION here
--
fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');
dbms_output.put_line('File opened');
--
-- Append/write cOut into file. Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR here
--
WHILE cCount < 11 LOOP
cOut := 'The Count is ' || cCount;
UTL_FILE.PUT_LINE(fHandle,cOut);
dbms_output.put_line('The Count is ' || cCount);
cCount := cCount + 1;
END LOOP;
--
-- Close file. Might get INVALID_FILEHANDLE or WRITE_ERROR
--
UTL_FILE.FCLOSE(fHandle);
dbms_output.put_line('File opened');
--
-- Update PO_ERR to indicate successful extraction
--
-- INSERT INTO PO_ERR VALUES(sysdate,'COmpleted');
-- COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20100 Invalid path');
-- COMMIT;
WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20101 Invalid Mode');
-- COMMIT;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20102 Invalid Filehandle');
-- COMMIT;
WHEN UTL_FILE.INVALID_OPERATION THEN
--
-- May signal a file locked by the OS'
--
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20103 Invalid Operation');
-- COMMIT;
WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20104 Read Error');
-- COMMIT;
WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20105 Write Error');
-- COMMIT;
WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20106 Internal Error');
-- COMMIT;
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20107 No Data Found');
-- COMMIT;
WHEN VALUE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20108 Value Error');
-- COMMIT;
WHEN OTHERS THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20109 Unknown UTL_FILE Error');
-- COMMIT;
END;
/|||Hello,
fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');
1) Remeber, that the path v:\Sweden\Post must be accessable on your server (not on your client).
2) Enter the path in your init.ora file under UTL_FILE_DIR
3) I did not test this ... but could it be that you must use the open mode
'w' for write not 'a' for append ??? Cause the file did not exist ?
4) Check that Post blank Office is a valid path ... sometimes you must reference v:\Sweden\Post~1 or something like that ...
5) For the first time ... I would use only filenames and directories that are not longer 8 characters. If this work, I try the orginal names ...
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,
I am getting some resistance from our DBA to set the parameter in the init.ora bec they will have to reboot the server and that has to go thru a lot of bureacracy red tape. i.e. I cant really test my invoice extraction scripts.
Thus, I was wondering if you know of any other tools that I can use easily.
Cheers,
Pete|||Hello,
they have to boot the server ? I though, that is is enough to start the instance and perhaps the listener ? anyway ...
Anyway, what you can do is to leave the directory blank. Oracle will put this into the oracle_home directory ...
What do you mean by ... any other tool that you can use ?
UTL_FILE is the right way to do this and I do not know any other tecniques to write in a file on the server.
You can create your own DLL or SO to program it by yourself, but your DBA has to do the same thing than when chaning the init.ora
My suggestion is ... try your example with leaving the dir blank and
Change the open mode into "w"
search for the file on the server
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
I have changed the directory to blank and the mode to 'W'. There isnt any file created on the server.
Is there something wrong with my block? I have put some dbms_output.Put_line in the block but all I got was "PL/SQL completed successfully" even thought serveroutput is on. Thus, I think the block was not entirely executed at all.
I am running this on the SQL*Plus (via Citrix) on the server's Bin directory. I do not have oracle installed on my location machine or my session of citrix. Does this have any impact?
Greatly appreciate any advice!
Cheers,
Pei Siong|||Hello,
I dont thing that this is a problem of your client configuration or your citrix.
Can you please insert a put_line in every exception block and
insert a WHEN OTHERS exception block ?
The step through the code with a debugger (f.e. AlligatorSQL) or
insert a step variable for every line and print this out in the exception block
and
insert in every exception block
dbms_output.put_line('Error=' || SQLERRM || ' - Errocode = ' || SQLCODE);
example
DECLARE
nStep PLS_INTEGER := 0;
cVar VARCHAR2(200);
BEGIN
<<Start>>
nStep := 10;
cVar := 'Hello';
.
.
.
nStep := 100;
cVar := 'Hello back';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Last executed Step = ' || nStep);
END
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
I got the invalid path error when I leave the location blank and have a filename shorter than 8 char and open_mode = 'W'.
Looks like I really do need to get the UTL_FILE_DIR parameter set to a specific location?
Cheers,
Pei Siong|||Hello,
try to define the path with "../" for unix and "..\" for windows
or only ".."
my last idea :(
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,
I have got the parameter changed and my script works brilliantly! however, I have to abandon the UTL_FILE method as my DBA team says it is not right to spool any text file to the Oracle server. so they have turned me down.
what I have done is to insert cOut (the variable that stores the text) into a temp table and then do a normal spool from the temp table.
This will have to do for the time being. But still, I have learnt many things from you... Thank you!!
Cheers,
Pei Siong|||Hello,
you are welcome.
By the way ... I have never heard, that you do not have to spool files to an oracle server. We do this in ever project ... but anyway :)
I would like to mention one thing ...
If you spool text in a temp table directly there are two inportant
things to know
1) Your application performance will go down.
2) You only see your spool text, if the application commits the transaction ... means ... if you have an exception and your normal work
will rollbacked by the application, you do not see your spool files.
To avoid this problems do the following
1) write a procedure that spools the text in an Oracle pipe.
2) write a procedure that pools against the pipe and if the pipe have datas, insert the datas in your temp table.
Your performance will not go down and you see every text that you want to spool.
But for the first step your solution will work ...
I only want to give you this information for further project.
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
I need to spool the results of the following extraction from PL/SQL
For each invoice no in TRANSACTION table, loop:
Txn no, customer code, date etc etc, tab-delimited
Then for each invoice no, get all invoice lines in TPRODLINE table, loop:
Txn line, product, qty, sale price etc etc, tab-delimited
end invoice lines loop
end invoice no loop
TRANSACTION table is joined to the TPRODLINE table by the transaction_id in both tables.
My questions:
1) should I do 2 cursors in the loops. 1 cursor to read the required invoice lines and pass the transaction no to the 2nd cursor to extract the transaction header infor. Then write/append this to the text file. then go back to the 2 loops? Will that work?
2) I also need help to spool this to a text file. Will UTL_FILE be able to help me? If so, anything I need to be careful about?
Any advice greatly appreciated!
Cheers,
Pete
Cheers,
Pete1) That depends on the size of table and you indexes.
I would create on SQL statement that joins all table and give me the result that I need. Look into the explain plan before executing.
2) Here is an example for using UTL_FILE
DECLARE
fHandle UTL_FILE.FILE_TYPE;
vTextIn varchar2(25);
vTextOut varchar2(25);
BEGIN
-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','w');
IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File write open');
ELSE
DBMS_OUTPUT.PUT_LINE('File write not open');
END IF;
vTextIn := 'Hello World';
vTextOut := 'World Hello';
-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR
UTL_FILE.PUT_LINE(fHandle,vTextIn);
DBMS_OUTPUT.PUT_LINE('Value write: '||vTextIn);
-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);
-- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
fHandle := UTL_FILE.FOPEN('c:\','utlout.txt','r');
IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;
-- Might get INVALID_FILEHANDLE, INVALID_OPERATION or READ_ERROR
-- NO_DATA_FOUND or VALUE_ERROR
UTL_FILE.GET_LINE(fHandle,vTextOut);
DBMS_OUTPUT.PUT_LINE('Value read: '||vTextOut);
-- Might get INVALID_FILEHANDLE or WRITE_ERROR
UTL_FILE.FCLOSE(fHandle);
DBMS_OUTPUT.PUT_LINE('Successful Completion');
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');
WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20104,'Read Error');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20105,'Write Error');
WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20107,'No Data Found');
WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR(-20108,'Value Error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20109,'Unknown UTL_FILE Error');
END;
/
3) Set the option UTL_FILE_DIR to access other files on your OS
If you want to use more directories, separate them with a ;
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
Thank you so much for your help! An addition to the Oracle manual!
I am still a bit stuck with the SQL though!
Cheers,
Pete|||Hello,
when I understand your description you have two tables
One TRANSACTION table and one TPRODLINE table.
The reference is transaction_id ...
So just join with that transaction id
SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id
Here is the cursor process (a little bit old style :) )
DECLARE
CURSOR cuProcess IS
SELECT t.txnno, t.customercode, t.date,
p.txnline, p.product, p.qty, p.saleprice
FROM transaction t, tprodline p
WHERE t.transaction_id = p.transaction_id
rProcess cuProcess%ROWTYPE; -- record structure
cOut VARCHAR2(2000);
BEGIN
OPEN cuProcess;
FETCH cuProcess INTO rProcess;
-- Open your file !!!
<<BeginLoop>>
WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;
-- spool your datas
cOut := rProcess.txnno || CHR(9) || .............;
END LOOP BeginLoop;
CLOSE cuProcess;
-- close your file
EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;
END;
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
The table join is simple.
But the required file consists of at least 3 lines for each invoice which makes it difficult.
Line 1:txn no, customer, address, date etc ...
Line 2 to nth:txn lineno, product code, unit price, qty, linetot
line nth+1:invoice totamt, standard greeting message
for invoice no 12345, customer = Burgerking with 2 invoice lines for products Whopper and fries, I need to product a text file of
Header,12345,burgerking,123 high street, 14-Apr-03(carriage return)
Lines,1,whopper,2.3,1,2.3(carriage return)
lines,2,fries,1.4,2,2.8(carriage return)
footer,5.1,thank you for buying from us(carriage return)
... (next invoice header)
header,12346
....
Once all these have been extracted successfully, I will need to update a date field in the transaction table to mark the invoice as "printed/extracted" ...
That's why I was thinking of using 2 cursors!
Any advice greatly appreciated (coz not many hair left on my head)!
Cheers,
Pete|||Hello,
that is something we call in germany "Gruppenwechsel" or group changing :)
You can use two cursor but you dont have to do that.
Do it in this way
DECLARE
cCompareField VARCHAR2(200);
BEGIN
cCompareField := "";
OPEN cuProcess;
FETCH cuProcess INTO rProcess;
-- Open your file !!!
<<BeginLoop>>
WHILE cuProcess%FOUND LOOP
IF cCompareField <> rProcess.taxno THEN
... do your header thing
cCompareField := rProcess.taxno;
END IF;
.. do your normal invoice stuff
FETCH cuProcess INTO rProcess;
-- spool your datas
cOut := rProcess.txnno || CHR(9) || .............;
END LOOP BeginLoop;
CLOSE cuProcess;
Hope this is clear enough otherwise please let me know ?!?!
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi
Thanks again for your help. Just to make sure I understand your coding:
1) the 1 cursor that you have used will select rows (including txn_id) from the transaction lines table.
2) first loop select records from transaction table based on txn_id in the cursor. then assign it to variable cOut and set cCompare to the txn_id.
3) then select transaction line data from transaction line table and append it to cOUT.
4) and repeat from (2) until cursor NOT FOUND.
5) then use cOUT with UTL_FILE ...
Is that correct? Thanks!
Happy easter!
Pete|||Hello,
thats completly correct ... if you need more groups like the id,
you can define more CompareField variable like it is shown in the example.
This is the classical way to program "group changing" ...
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,
You are a life saver!!
Thank you so much!!!
cheers,
Pete|||Hi
This time round I have got some question on UTL_FILE.
I used your eg. and added a loop for generating a count and writing it to a text file on my local drive using UTL.
Run the block and it just say completed successfully. But no file is generated.
I do not have Oracle client installed on my local machine. I also know the UTL_FILE_DIR parameter is set to null.
Is there anything I have not done correctly?
Thanks for any advice!
Cheers,
Pete|||Hello ...
can you please post the code ?
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi,
here is the code:
~~~~~~~~~~~~~~
DECLARE
fHandle UTL_FILE.FILE_TYPE;
cOut varchar2(25);
cCount NUMBER :=1;
BEGIN
--
-- Open file, Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION here
--
fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');
dbms_output.put_line('File opened');
--
-- Append/write cOut into file. Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR here
--
WHILE cCount < 11 LOOP
cOut := 'The Count is ' || cCount;
UTL_FILE.PUT_LINE(fHandle,cOut);
dbms_output.put_line('The Count is ' || cCount);
cCount := cCount + 1;
END LOOP;
--
-- Close file. Might get INVALID_FILEHANDLE or WRITE_ERROR
--
UTL_FILE.FCLOSE(fHandle);
dbms_output.put_line('File opened');
--
-- Update PO_ERR to indicate successful extraction
--
-- INSERT INTO PO_ERR VALUES(sysdate,'COmpleted');
-- COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20100 Invalid path');
-- COMMIT;
WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20101 Invalid Mode');
-- COMMIT;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20102 Invalid Filehandle');
-- COMMIT;
WHEN UTL_FILE.INVALID_OPERATION THEN
--
-- May signal a file locked by the OS'
--
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20103 Invalid Operation');
-- COMMIT;
WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20104 Read Error');
-- COMMIT;
WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20105 Write Error');
-- COMMIT;
WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20106 Internal Error');
-- COMMIT;
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20107 No Data Found');
-- COMMIT;
WHEN VALUE_ERROR THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20108 Value Error');
-- COMMIT;
WHEN OTHERS THEN
UTL_FILE.FCLOSE(fHandle);
-- INSERT INTO PO_ERR VALUES(sysdate,'-20109 Unknown UTL_FILE Error');
-- COMMIT;
END;
/|||Hello,
fHandle := UTL_FILE.FOPEN('v:\Sweden\Post Office\','postoffice_test.txt','a');
1) Remeber, that the path v:\Sweden\Post must be accessable on your server (not on your client).
2) Enter the path in your init.ora file under UTL_FILE_DIR
3) I did not test this ... but could it be that you must use the open mode
'w' for write not 'a' for append ??? Cause the file did not exist ?
4) Check that Post blank Office is a valid path ... sometimes you must reference v:\Sweden\Post~1 or something like that ...
5) For the first time ... I would use only filenames and directories that are not longer 8 characters. If this work, I try the orginal names ...
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,
I am getting some resistance from our DBA to set the parameter in the init.ora bec they will have to reboot the server and that has to go thru a lot of bureacracy red tape. i.e. I cant really test my invoice extraction scripts.
Thus, I was wondering if you know of any other tools that I can use easily.
Cheers,
Pete|||Hello,
they have to boot the server ? I though, that is is enough to start the instance and perhaps the listener ? anyway ...
Anyway, what you can do is to leave the directory blank. Oracle will put this into the oracle_home directory ...
What do you mean by ... any other tool that you can use ?
UTL_FILE is the right way to do this and I do not know any other tecniques to write in a file on the server.
You can create your own DLL or SO to program it by yourself, but your DBA has to do the same thing than when chaning the init.ora
My suggestion is ... try your example with leaving the dir blank and
Change the open mode into "w"
search for the file on the server
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
I have changed the directory to blank and the mode to 'W'. There isnt any file created on the server.
Is there something wrong with my block? I have put some dbms_output.Put_line in the block but all I got was "PL/SQL completed successfully" even thought serveroutput is on. Thus, I think the block was not entirely executed at all.
I am running this on the SQL*Plus (via Citrix) on the server's Bin directory. I do not have oracle installed on my location machine or my session of citrix. Does this have any impact?
Greatly appreciate any advice!
Cheers,
Pei Siong|||Hello,
I dont thing that this is a problem of your client configuration or your citrix.
Can you please insert a put_line in every exception block and
insert a WHEN OTHERS exception block ?
The step through the code with a debugger (f.e. AlligatorSQL) or
insert a step variable for every line and print this out in the exception block
and
insert in every exception block
dbms_output.put_line('Error=' || SQLERRM || ' - Errocode = ' || SQLCODE);
example
DECLARE
nStep PLS_INTEGER := 0;
cVar VARCHAR2(200);
BEGIN
<<Start>>
nStep := 10;
cVar := 'Hello';
.
.
.
nStep := 100;
cVar := 'Hello back';
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Last executed Step = ' || nStep);
END
Hope that helps ?
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Hi Manfred,
I got the invalid path error when I leave the location blank and have a filename shorter than 8 char and open_mode = 'W'.
Looks like I really do need to get the UTL_FILE_DIR parameter set to a specific location?
Cheers,
Pei Siong|||Hello,
try to define the path with "../" for unix and "..\" for windows
or only ".."
my last idea :(
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Dear Manfred,
I have got the parameter changed and my script works brilliantly! however, I have to abandon the UTL_FILE method as my DBA team says it is not right to spool any text file to the Oracle server. so they have turned me down.
what I have done is to insert cOut (the variable that stores the text) into a temp table and then do a normal spool from the temp table.
This will have to do for the time being. But still, I have learnt many things from you... Thank you!!
Cheers,
Pei Siong|||Hello,
you are welcome.
By the way ... I have never heard, that you do not have to spool files to an oracle server. We do this in ever project ... but anyway :)
I would like to mention one thing ...
If you spool text in a temp table directly there are two inportant
things to know
1) Your application performance will go down.
2) You only see your spool text, if the application commits the transaction ... means ... if you have an exception and your normal work
will rollbacked by the application, you do not see your spool files.
To avoid this problems do the following
1) write a procedure that spools the text in an Oracle pipe.
2) write a procedure that pools against the pipe and if the pipe have datas, insert the datas in your temp table.
Your performance will not go down and you see every text that you want to spool.
But for the first step your solution will work ...
I only want to give you this information for further project.
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
Subscribe to:
Posts (Atom)