Oracle SQL Developer

[eggy168]

I am a newbie in Oracle. I just want to do a simple sql script, but it drives me insane. Can someone help me?

I have two tasks for this stored procedure:
1) My purpose for the Oracle sp procedure is to create a column that is exact same as the column, "as_of" in SQL version which is a system date every time I run this sp.
2) I would like to know is there anyway I can insert the data into a csv file? I know something in Oracle called spooled. If someone can give me some guideline, it would be great. However, I need to have this sp code ready, so I can complete one of the tasks.

In SQL, I would do the followings:

Create Procedure sp_test
AS
Truncate table tablexx
Insert into tablexx
SELECT
CONVERT(CHAR(10),GETDATE(),101) as as_of,
column 1,
column 2
FROM tableYY

I want to create a stored procedure in Oracle with the same function in SQL, but I have trouble.

create or replace procedure sp_test
AS
Execute immediate truncate table tablexx;
INSERT INTO tablexx --somehow this will be a csv file that need to upload to a ftp
BEGIN
SELECT
as_of default sysdate,
column 1,
column 2
from tableYY
commit;
END;

After I compile the above sp, I got this message,
Error(4,19): PLS-00103: Encountered the symbol "TRUNCATE" when expecting one of the following: := . ( @ % ; not null range default character

Can someone help or guide me?
Thank you very much

 

[garani]

Hi

please use quotes in EXECUTE IMMEDIATE

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
END;



Regards
garan

 

[Andrzejek]

> is there anyway I can insert the data into a csv file?
A csv (comma separated value) file is just a simple text file. Create a text file, something like:[tt]
123,abc,xyz
678,klm,po9
645,rye,rt7[/tt]
Name it something like MyFile.csv and you are done.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[eggy168]

Hi all,
Garani ~ I tried the line, EXECUTE IMMEDIATE 'TRUNCATE TABLE tablexx'. it doesn't allow me to do anything.
Andrzejek ~ Is there anyway you can help me to put a code inside the stored procedure to create the csv file? I created the csv file in the c drive, and I don't know how to write it in the stored procedure.
Thanks for all your help.

 

[Andrzejek]

To find out how to create a text file in Stored Procedure in Oracle I would try this first

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[garani]

Hi User eggy168

You have missed BEGIN KEYWORD in your stored procedure


STEP 1:


DROP TABLE EMP;
CREATE TABLE EMP(ID NUMBER);
INSERT INTO EMP VALUES(10);
COMMIT;
SELECT COUNT(*) FROM EMP; -- Will give 1 row

STEP 2:
CREATE OR REPLACE PROCEDURE SP_TEST
IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
END;
/

STEP 3: exec SP_TEST

STEP 4: Verify count is 0

SELECT COUNT(*) FROM EMP;

garan

 

[garani]

In other words the BEGIN KEYWORD should immediately follow AS/IS and you have put BEGIN at the wrong place

 

[Beilstwh]

The head developer for Oracle has a nice little procedure to dump the results of a query to a flat file ON THE DATABASE SERVER. See

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:68212348056

Bill
Lead Application Developer
New York State, USA

 

[eggy168]

Great, it works great after I copy and paste the exact code below. I didn't know I need to include the "/" at the end.
Thanks for Garani AND Andrzejek

CREATE OR REPLACE PROCEDURE SP_TEST
IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
END;
/

Beilstwh, I will take a look of the link you provided.

Thanks everybody. It helped my first task.