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 stored procedure in SQL Server Management Studio

I have a database that I want to add stored procedures but when I go to save the stored procedure it asked me to save it as a .sql file and doesn;t add it to the database.

This is what I'm doing:

right click on database > programability > stored procedures

select "new Stored Procedure"

writting the procedure

then save - this is when i get the save dialogue rather than seeing the sp added to the database.

There must be something very simple that I'm doing wrong but I just can't figure it out, any help would be greatly appreciated.

Damien

You need to execute this command (that begins with CREATE PROCEDURE) instead of saving to disk.

So, you need to press F5 or press the Execute Button from program bar.

|||Thanks

Creating a MODEL

Is there any possibility to save a REPORT like a MODEL ? I mean for using the
same layout,fonts,etc..
--
TabacaruYes, you can save a report off to the side with basic layout and formatting
and use it as the starting point for creating additional reports.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tabacaru Teodor" <TabacaruTeodor@.discussions.microsoft.com> wrote in
message news:4B494061-035C-4E75-9280-CE3E09E98D46@.microsoft.com...
> Is there any possibility to save a REPORT like a MODEL ? I mean for using
> the
> same layout,fonts,etc..
> --
> Tabacaru|||And you can add it to the VS template directory to add it to the 'add new
item' list. Several articles have been published which demonstate this.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uYXEk4m%23EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Yes, you can save a report off to the side with basic layout and
> formatting and use it as the starting point for creating additional
> reports.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Tabacaru Teodor" <TabacaruTeodor@.discussions.microsoft.com> wrote in
> message news:4B494061-035C-4E75-9280-CE3E09E98D46@.microsoft.com...
>> Is there any possibility to save a REPORT like a MODEL ? I mean for using
>> the
>> same layout,fonts,etc..
>> --
>> Tabacaru
>

Creating a DTS package from a BAS file thru VB

Hello. ok, if I open a DTS package, and save it as a VB
BAS file, make some alterations to the BAS file, how do I
re-create the DTS package from that updated BAS file? Is
there any short sample code to do this that someone could
point me to? I'm not a VB coder, so just need a shell of
the VB code to run. THanks, BruceThe generated VB code will contain code to either execute or save the
package. The initial code will have the SaveToSqlServer line commented out.
You can remove the comment, specify the correct 'sa' password (and server or
other security credentials), comment out the Execute line and then run the
program. Code snippet example below. Note that you'll need to first delete
the package if you want to save it under the same name to the original
server.
You can find more information on the SaveToSQLServer method in the Books
Online.
'--
' Save or execute package
'--
goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
'goPackage.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
> Hello. ok, if I open a DTS package, and save it as a VB
> BAS file, make some alterations to the BAS file, how do I
> re-create the DTS package from that updated BAS file? Is
> there any short sample code to do this that someone could
> point me to? I'm not a VB coder, so just need a shell of
> the VB code to run. THanks, Bruce|||You da MAN!! actually the DAN, but.........
THanks, I'll try it out Monday... Bruce

>--Original Message--
>The generated VB code will contain code to either
execute or save the
>package. The initial code will have the SaveToSqlServer
line commented out.
>You can remove the comment, specify the correct 'sa'
password (and server or
>other security credentials), comment out the Execute
line and then run the
>program. Code snippet example below. Note that you'll
need to first delete
>the package if you want to save it under the same name
to the original
>server.
>You can find more information on the SaveToSQLServer
method in the Books
>Online.
>'--
>' Save or execute package
>'--
>goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
>'goPackage.Execute
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
VB[vbcol=seagreen]
do I[vbcol=seagreen]
Is[vbcol=seagreen]
could[vbcol=seagreen]
of[vbcol=seagreen]
>
>.
>

Creating a DTS package from a BAS file thru VB

Hello. ok, if I open a DTS package, and save it as a VB
BAS file, make some alterations to the BAS file, how do I
re-create the DTS package from that updated BAS file? Is
there any short sample code to do this that someone could
point me to? I'm not a VB coder, so just need a shell of
the VB code to run. THanks, Bruce
The generated VB code will contain code to either execute or save the
package. The initial code will have the SaveToSqlServer line commented out.
You can remove the comment, specify the correct 'sa' password (and server or
other security credentials), comment out the Execute line and then run the
program. Code snippet example below. Note that you'll need to first delete
the package if you want to save it under the same name to the original
server.
You can find more information on the SaveToSQLServer method in the Books
Online.
'--
' Save or execute package
'--
goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
'goPackage.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
> Hello. ok, if I open a DTS package, and save it as a VB
> BAS file, make some alterations to the BAS file, how do I
> re-create the DTS package from that updated BAS file? Is
> there any short sample code to do this that someone could
> point me to? I'm not a VB coder, so just need a shell of
> the VB code to run. THanks, Bruce
|||You da MAN!! actually the DAN, but.........
THanks, I'll try it out Monday... Bruce

>--Original Message--
>The generated VB code will contain code to either
execute or save the
>package. The initial code will have the SaveToSqlServer
line commented out.
>You can remove the comment, specify the correct 'sa'
password (and server or
>other security credentials), comment out the Execute
line and then run the
>program. Code snippet example below. Note that you'll
need to first delete
>the package if you want to save it under the same name
to the original
>server.
>You can find more information on the SaveToSQLServer
method in the Books[vbcol=seagreen]
>Online.
>'--
>' Save or execute package
>'--
>goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
>'goPackage.Execute
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
VB[vbcol=seagreen]
do I[vbcol=seagreen]
Is[vbcol=seagreen]
could[vbcol=seagreen]
of
>
>.
>

Creating a DTS package from a BAS file thru VB

Hello. ok, if I open a DTS package, and save it as a VB
BAS file, make some alterations to the BAS file, how do I
re-create the DTS package from that updated BAS file? Is
there any short sample code to do this that someone could
point me to? I'm not a VB coder, so just need a shell of
the VB code to run. THanks, BruceThe generated VB code will contain code to either execute or save the
package. The initial code will have the SaveToSqlServer line commented out.
You can remove the comment, specify the correct 'sa' password (and server or
other security credentials), comment out the Execute line and then run the
program. Code snippet example below. Note that you'll need to first delete
the package if you want to save it under the same name to the original
server.
You can find more information on the SaveToSQLServer method in the Books
Online.
'--
' Save or execute package
'--
goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
'goPackage.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
> Hello. ok, if I open a DTS package, and save it as a VB
> BAS file, make some alterations to the BAS file, how do I
> re-create the DTS package from that updated BAS file? Is
> there any short sample code to do this that someone could
> point me to? I'm not a VB coder, so just need a shell of
> the VB code to run. THanks, Bruce|||You da MAN!! actually the DAN, but.........
THanks, I'll try it out Monday... Bruce
>--Original Message--
>The generated VB code will contain code to either
execute or save the
>package. The initial code will have the SaveToSqlServer
line commented out.
>You can remove the comment, specify the correct 'sa'
password (and server or
>other security credentials), comment out the Execute
line and then run the
>program. Code snippet example below. Note that you'll
need to first delete
>the package if you want to save it under the same name
to the original
>server.
>You can find more information on the SaveToSQLServer
method in the Books
>Online.
>'--
>' Save or execute package
>'--
>goPackage.SaveToSQLServer "(local)", "sa", "mypassword"
>'goPackage.Execute
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:abc901c488a9$972e48e0$a501280a@.phx.gbl...
>> Hello. ok, if I open a DTS package, and save it as a
VB
>> BAS file, make some alterations to the BAS file, how
do I
>> re-create the DTS package from that updated BAS file?
Is
>> there any short sample code to do this that someone
could
>> point me to? I'm not a VB coder, so just need a shell
of
>> the VB code to run. THanks, Bruce
>
>.
>

Creating a database from a form.

I've made a form and now all I want to do is save everything in the form into a new database file but I don't know how. I'm using Visual Web Developer 2005. Somehow I'm guessing that I have to set something somewhere so when the submit button is hit, it knows to save it to a database file which doesn't exist yet so it would have to create it. It should be easy but I can't figure it out.

This is an excellent article to review to fully answer your question:

http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

Hope this helps.

CREATE, ALTER, why wont it save?

when i create a new stored proc, i say "CREATE PROCEDURE [sprocName]"

but the next time i edit the stored proc, it hasnt changed to "ALTER ..."

I manually change it to ALTER, and run it, and close it, and it reverts back to "CREATE..."

Is there some sort of privelage i have to enable? Has anyone encountered this before?

Help guys, please :-S

Also, if you know of a better place where i can post this thread, please let me know

The script you are editing is the crate script for the SP. To update the sp you do an alter but the create script will still be a create.

How are you doing this?

In v2000 enterprise manager clicknig on OK updates the SP even though it's a create.

In v2005 a modify from management studion generates an alter script.

It's better to maintain SPs from disk based scripts in a query window. The first statment should be a conditional drop of the SP followed by a create.

if object_id('mysp') is not null -- or something like that

drop proc mysp

go

create proc mysp

as

....

go

|||

thanx Nigel, the IF OBJECT_ID() IS NOT NULL works

now atleast query analyser doesnt tell me:

Server: Msg 2714, Level 16, State 5, Procedure [spName], Line 16
There is already an object named '[spName]' in the database.