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
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