INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle SQL Developer

Oracle SQL Developer

(OP)
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

RE: Oracle SQL Developer

Hi

please use quotes in EXECUTE IMMEDIATE

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP';
END;



Regards
garan

RE: Oracle SQL Developer

> 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:
123,abc,xyz
678,klm,po9
645,rye,rt7

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.

RE: Oracle SQL Developer

(OP)
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.

RE: Oracle SQL Developer

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

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.

RE: Oracle SQL Developer

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





RE: Oracle SQL Developer

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

RE: Oracle SQL Developer

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close