Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Oracle SQL Developer

Status
Not open for further replies.

eggy168

Programmer
Mar 6, 2002
220
US
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
 
Hi

please use quotes in EXECUTE IMMEDIATE

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
END;



Regards
garan
 
> 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.
 
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.
 
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.
 
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





 
In other words the BEGIN KEYWORD should immediately follow AS/IS and you have put BEGIN at the wrong place
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top