execute immediate....please help! 3

[lel75]

Hello everyone,

I have the following sql script below using execute immediate:

PROMPT Please enter in parameters when prompted...
PROMPT start_dt
PROMPT end_dt
PROMPT

DECLARE
v_start_dt DATE;
v_end_dt DATE;

BEGIN
--
--Read parameters
v_start_dt := '&START_DT';
v_end_dt := '&END_DT';
--
-- Create tables
EXECUTE IMMEDIATE('CREATE TABLE purchase_order AS
SELECT *
FROM purchase
WHERE purchase_dt NOT BETWEEN
v_start_dt AND v_end_dt');
--
EXECUTE IMMEDIATE('CREATE TABLE supplies_order AS
SELECT *
FROM supplies
WHERE supply_dt NOT BETWEEN
v_start_dt AND v_end_dt');
END;
/


When I execute this script at the command line, I get the following error message:

ORA-06550: line 28, column 13:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:

:= . ( @ % ;
The symbol ":=" was substituted for "IMMEDIATE" to continue.


Can you please help me figure out what I'm doing wrong?

Thanks in advance!

lel75

 

[SantaMufasa]

Mike,

Whenever I need to extract data from multiple databases simultaneously, I use database links. Is there a reason why database links will not work for you? With the use of synonyms for the objects that are on a remote database, your code will virtually not notice that it is using database links.

If you have not used db links, and if you need examples, please let us know and we'll post examples for your.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:21 (01Sep04) UTC (aka "GMT" and "Zulu"), 13:21 (01Sep04) Mountain Time)