creating a text file from the contents of the database?

please help!!

i'm working on a project right now using Oracle Forms 6.0 and Oracle9i.

after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and upload the data in the text file to another database (also Oracle).

i will also need to create the text file for multiple records.

can someone help me please?? i read something about SELECT INTO OUTFILE... how exactly does this work?Hello,

use UTL_FILE package to spool records into a file via PL/SQL.
In AlligatorSQL you can use a template "How to spool a ...".

See at http://www.alligatorsql.com/download/alligator116.zip

But if you wish I can post an example again (it has been already posted in this forum)

Hope that helps ?

Manfred Peter
(Alligator Compay Software GmbH)
http://www.alligatorsql.com|||oh i see! thanks, i found the thread on extracting. will post again if i have any problems!|||sir manfred,

would it be possible to use TEXT_IO instead of UTL_FILE? Oracle Forms does not have the UTL_FILE package. i had a bit of difficulty following your examples (sorry!) as i am just a beginner with pl/sql.

this is what i have to do:
- save the information that was entered in Oracle Forms (this is finished)
- when a button is pressed, update the REQUEST_SENT flag and create the text file (of that same form which was just saved)

This is what i have done so far:

/*WHEN-BUTTON-PRESSED trigger*/

DECLARE

CURSOR cuProcess IS
SELECT *
FROM SIR
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;

rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);

N_FILE VARCHAR2(2000);

BEGIN

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
||rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT;
END LOOP BeginLoop;

CLOSE cuProcess;

CREATE_TEXT('filename', cOut);

EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;

END;

then i have a simple procedure that creates the text file:

PROCEDURE CREATE_TEXT (pfilename IN VARCHAR2, selected IN VARCHAR2) IS

N_FILE text_io.file_type;

BEGIN
N_FILE := TEXT_IO.FOPEN(pfilename||'.TXT', 'W');
TEXT_IO.PUT_LINE(N_FILE, selected);
TEXT_IO.FCLOSE(N_FILE);

END;

my problem is that i have to press the button twice for the update to happen. is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * ?
also, after the text file is created, how can i load it using sqlloader?

also, how can i specify the path where the text file will be saved? the TEXT_IO.FOPEN accepts only 2 parameters, the filename and the mode unlike UTL_FILE.FOPEN

i appreciate the help! thanks again!|||Hello,

sorry, but I am not so familiar with Oracle forms. But I know, that you can call PL/SQL routines from Forms.

Sorry again.

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Creating a text file

Any one can show me how to dump the entire content of a SQL Table into either a text file or FoxPro file.
Thanks.Thanks I got it...|||Hi
I am relatively new to working with MS SQL. I need to do the same, dumping data from a table in a text file. You seem to have learned the trick. Could you please tell me how to do that?

Thanks..|||For SQL 2000:

Expand database
Right click tables
Select "All tasks" --> "Export Data..."
Then follow the wizard

For SQL 2005:

Dont have a frickin clue...

Creating a SQL script file from a stored procedure

I'd like to create a SQL script file from within a stored procedure. How do I:

1. Open the text file
2. Write to it
3. Close it when done

Thanks in advance,


Barrysounds like a job better accomplished through application code. filesystem object in vbscript perhaps.

but if you insist, perhaps I would write my sql string to a table and then fire a dts package from my sp to export the field to a text file.

what are you trying to accomplish? I can't imagine how this might be particularly useful.

more details would be helpful.

are you trying to open an application on the server side. Not a good idea. Saw someone trigger Access to open on the server side from the web once. If the the user closed the browser or something that instance of Access just stayed open on server and the mess this caused until I fixed it.|||You can use master..xp_cmdshell extended stored proc to run DOS command. It should look like

Exec master..xp_cmdshell 'Echo Whatever > MylogFile.Txt'

Cheers.|||Thrasymachus and mkostadinovic, thank you for your replies.

I ended up using osql to output the text from the SQL code:

osql -U xx -P xxxxxx -S (local) -d xxxx -h-1 -w 4000 -n -i "C:\...\ExtractUpdatePlateData.sql" -o "C:\...\Insert plate data.sql"

In case you are still wondering why, the reason for doing this was to script changes made to the contents of a number of database tables from ~20 updates that had built up over the last 18 months or so. I needed to be able to extract out the data, but had a constraint that I couldn't just use a file containing the data, and a script to import it. Here is part of the SQL from ExtractUpdatePlateData.sql:

SELECT InsertScript = 'INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES (' + '''' + vv_secttype + '''' + ', ' + CAST(vi_proctype AS NVARCHAR) + ', ' + CAST(vi_numwells AS NVARCHAR) + ', ' + '''' + vv_descrip + '''' + ')'
FROM tblUPDsecttype

This is how it appears in Insert plate data.sql:

INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES ('ANO2B', 1, 96, 'ANAEROBE SYSTEMIC Man MiC')

It all seems to work fine (and has saved me a week's tedious work already).


Best regards,


Barry

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailco
m@.207.46.248.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.
Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailcom@.207.46.2 48.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailcom@.207.46.248.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

Creating a formated text file from a query

Hello all.
I'm not highly experienced with SQL programming. I must export to contents
of a table to a formated text file which is then send to the customer. Is
there a possibility to do this from within a sql statement?
TIA
RonaldHi Ronald,
You can call the BCP utility from your SQL code. The BCP utility
enables you to export a table or query results to a file. You should
consult Books Online for information about BCP and find what
switches/options are suitable to your situation.
Now, normally you would call BCP from a command line (e.g. a DOS
prompt) but you can also call it from SQL code by going:
exec master..xp_cmdshell 'bcp <rest goes here>'
Note that by default only users in the SysAdmin fixed server role can
call xp_cmdshell.
Hope that helps a bit|||Yes
1)Use BCP to create the file - make sure all permissions are set up.
2)You can send the email from within the sp - SQL server has some built in
functionality or you can install 3rd party dll such as
http://www.sqldev.net/xp/xpsmtp.htm
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:OuwKwAAdGHA.536@.TK2MSFTNGP02.phx.gbl...
> Hello all.
> I'm not highly experienced with SQL programming. I must export to contents
> of a table to a formated text file which is then send to the customer. Is
> there a possibility to do this from within a sql statement?
> TIA
> Ronald
>|||Ive read this question and i want something similar..but automatic.
I say every friday sql creates a text file with the contents from a
table, and sends to the server for the client can download it from the
internet..
It can be done with BCP too?|||encase your stored procedure AS as a JOB . Make sure you place the file in
the correct destination and naming is correct for download.
Will your client be downloading from a web site?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Pp" <josemariabarros@.gmail.com> wrote in message
news:1147255005.925240.12610@.j33g2000cwa.googlegroups.com...
> Ive read this question and i want something similar..but automatic.
> I say every friday sql creates a text file with the contents from a
> table, and sends to the server for the client can download it from the
> internet..
> It can be done with BCP too?
>|||Yes. it will put a username and password to enter in a administration
web site and then he can download it|||Thanx,
That did the trick.
"Ronald Hermans" <rhermans@.datamedicare.nl> schreef in bericht
news:OuwKwAAdGHA.536@.TK2MSFTNGP02.phx.gbl...
> Hello all.
> I'm not highly experienced with SQL programming. I must export to contents
> of a table to a formated text file which is then send to the customer. Is
> there a possibility to do this from within a sql statement?
> TIA
> Ronald
>

Creating a Auto Increment which contains Numbers & Text

Hi,
Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-
ABC000001
ABC000002
ABC000003
Etc, Etc
I am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?
Regards,
BrettI would just create a view that exposes the alpha you want plus the IDENTITY column, formatted as you wish.|||

Douglas' solution is a horrible hack. It is not scalable (what happens when you want "BCD" and "ABC") and requires having two keys -- the "real" key and the "identity" key.
You were on the right track with a stored procedure. I don't know what you're trying to model ... so I'm going with Accounts.
Your proc should look like:
PROCEDURE Create_New_Account ( @.Account_Number CHAR(8) OUT, @.Salesperson_Number CHAR(5), (... other required fields) )
I'd recommend pre-creating your identifiers, and doing a SELECT / DELETE out of the Account_Numbers table. If that won't fly, just SELECT MAX(Account_Number) from your Accounts table, parse out the string and increment the number part.

|||

I would disagree with the "Horrible hack" characterization. If there was a column [Prefix] and a column with the identity, the [Prefix] could be whatever you want. I saw a reference to Auto Increment and was thinking about Access rather than SQL Server (yes, of course I realize the post was on the SQL Server forum).

If you can ensure all access to inserting data is through the stored procedures, then of course use that. method.

What is being attempted (adding meaning of some sort to an identifier) is not often a wonderful thing.

|||You're right; it's ideal to have to have both parts of the key in the table instead of combining it into one.
But I won't concede to "Horrible Hack" being a mischaraterization. Everytime I've seen this done ... it's a horrible hack ... here's my favorite example ...
Order_Numbers were defined something as : (Order_Date + Order_Seq) + XOR Parity Shift. This was to ensure order_nums were not predictable (from end users) and were verifiable.
The programmer who implemented this requirement used this schema:
Orders_Base = TABLE (Order_ID, Order_Date, Order_Seq, ...)
Orders = VIEW( SELECT complex_generator_fn( Order_Date, Order_Seq) as Order_Num, Order_Date, ... )
Guess how fun that got when they actually used the system to place orders. And tried to query on the non-indexed Order_Num.
They first implemented decode logic in the Middle tier to get Seq + Date. Surprise surprise, that didn't quite work out so well either.
Final iteration (and still as it exists today):
Orders_Base = TABLE( Order_ID, Order_Date, Order_Seq )
Order_Numbers = TABLE( Order_ID, Order_Number )
Oh, and warehousing and analytics is a COMPLETE nightmare.

Create view with field combination

Hi there, my situation is
I have a table x with 3 filed
a nvarchar(100), b smalldatetime, c text(16)
. I want to create a view like this:
select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%

So, I always get a message error said wrong datatype, how can i do, please help me.A view is just a select statement basically, meaning if you can't get something to compile in Query Analyzer it's not going to compile as a view. The syntax for a select statement similar to yours (there's nothing like yours that will actually compile) is:

SELECT column1 + '' + column2 + '' + column3 AS all_fields
FROM x
WHERE column1 + '' + column2 + '' + column3 LIKE '%my_str%'

Notice the single quotes and the fact that the column alias was not used in the where clause.

It's kind of like the following SELECT statements:

select name as huh from sysobjects where huh like '%a%'
select name from sysobjects where name like '%a%'

Only one of those actually pretends to work. :)|||The alias (all_field) is applied to the result set at the end of execution, so you can't reference in your statement.

In addition to Derrick;s method, this would normally work:

select all_field
from (select a + ' ' + b + ' ' + c as all_field from x) CombinedColumns
where all_field like %my_str%

...but I think you are going to run into problems with the TEXT column type. Notice that it's size is only 16 bytes. That is because the TEXT column is actually just a pointer to the location where the actual column value is stored. So your statement is trying to concatenate a pointer address to the end of your nvarchar and smalldatetime fields. I'm sure that's not what you want.
You will need to concatenate the actual value of column C, not it's address.