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]

Lel,

The first thing you need to do is upgrade from Oracle 8.0.x to Oracle 8.1.x or above (Since execute immediate doesn't work in 8.0.x). After you do that, then the following simplified code should work just fine:

accept start_dt PROMPT "Enter start Date: "
accept end_dt prompt "Enter end Date: "
PROMPT
BEGIN  
    -- Create tables
    EXECUTE IMMEDIATE('CREATE TABLE purchase_order AS
                       SELECT * 
                         FROM purchase
                         WHERE purchase_dt NOT BETWEEN   
                         ''&START_DT'' AND ''&END_DT''');
    --
    EXECUTE IMMEDIATE('CREATE TABLE supplies_order AS
                       SELECT *
                         FROM supplies
                        WHERE supply_dt NOT BETWEEN  
                        ''&START_DT'' AND ''&END_DT''');
END;
/

Let me know if my assertions are correct.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:37 (08Dec03) GMT, 14:37 (08Dec03) Mountain Time)

 

[lel75]

Thanks Dave. We have Oracle 8.1.5 here but it doesn't seem it work. Could it be that the execute immediate only works on 8.1.7 or greater? If I can't upgrade, I guess I'm stuck with dbms_sql? I've never used this before and below is what I've come up with. I tried executing it but got errors. Can you please help?

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

DECLARE
v_start_dt DATE;
v_end_dt DATE;
dummy integer;
stmt integer;
BEGIN
--
--Read parameters
v_start_dt := '&START_DT';
v_end_dt := '&END_DT';
--
stmt := dbms_sql.open_cursor;
-- Create tables
dbms_sql.parse(stmt, 'CREATE TABLE purchase_order AS
SELECT *
FROM purchase
WHERE purchase_dt NOT BETWEEN
v_start_dt AND v_end_dt');
--
dbms_sql.parse(stmt, 'CREATE TABLE supplies_order AS
SELECT *
FROM supplies
WHERE supply_dt NOT BETWEEN
v_start_dt AND v_end_dt');

dummy := dbms_sql.execute(stmt);
dbms_sql.close_cursor(stmt);


END;
/


Thanks!!

 

[SantaMufasa]

Lel,

Sorry, I have many 8.1.6 and above installations, some 8.0.4 and 8.0.5, but no 8.1.5 installation. I successfully tested the code I posted earlier on an 8.1.6 installation. If your installation supports EXECUTE IMMEDIATE at all, then the code I posted at 21:37 GMT should work without errors.

Whenever you use Tek-Tips and you say, "I tried executing it but got errors," it is VITAL that you also post the error message(s) along with the offending code.

Please update,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:20 (08Dec03) GMT, 15:20 (08Dec03) Mountain Time)

 

[gunjan14]

Hi,
Try this
dbms_sql.parse(stmt, 'CREATE TABLE purchase_order AS SELECT * FROM purchase WHERE purchase_dt NOT BETWEEN ' || v_start_dt || ' AND ' || v_end_dt);
You have written the variables (v_start_dt, v_end_dt) inside the single quote.

Gunjan

 

[gunjan14]

ooops sorry, my mistake
dbms_sql.parse(stmt, 'CREATE TABLE purchase_order AS SELECT * FROM purchase WHERE purchase_dt NOT BETWEEN ''' || v_start_dt || ''' AND ''' || v_end_dt || '''');

 

[sem]

If you still plan to use DBMS_SQL you should refine your PARSE call to include one more parameter (version, probably DBMS_SQL.NATIVE) and get rid of EXECUTE, because DDL statements are executed during parse time. And of course be more carefull with quotes and values, because quoted variable is no more a variable but rather string literal:

dbms_sql.parse(stmt,
'CREATE TABLE purchase_order AS
SELECT * FROM purchase
WHERE purchase_dt NOT BETWEEN ''&start_dt'' AND ''&end_dt''',DBMS_SQL.NATIVE );

Regards, Dima

http://seminihin.narod.ru

 

[lel75]

Thanks everyone for your help. I followed both gunjan14's and sem's suggestions using dbms_sql and both worked....thanks again!

--lel75

 

[parbhani]

Hi all ,

Why are we putting brackets after execute immediate.
Have a look at the following code and it will work, for simple things better not get into dbms_sql

DECLARE
v_stmt varchar2(1000) ;
v_start date ;
v_end date ;
BEGIN
-- Create tables
v_start := '&date1' ;
v_end := '&date2' ;

v_stmt := 'CREATE TABLE purchase_order AS ' ||
'SELECT * FROM purchase WHERE '||
'purchase_dt NOT BETWEEN '||
chr(39) || to_char( v_start ) || chr(39) || ' AND ' ||
chr(39) || to_char( v_end ) || chr(39) ;

EXECUTE IMMEDIATE v_stmt ;
--
END;

Cheers

 

[SantaMufasa]

Parbhani,

You may have missed the fact that Lel is using a version of Oracle that does not support "EXECUTE IMMEDIATE", so is using what is available, "DBMS_SQL..."

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:01 (09Dec03) GMT, 10:01 (09Dec03) Mountain Time)

 

[parbhani]

Yes,
I realised from the earlier discussion that it MAY BE a version problem.
Still, if u see in the initial message from Lel, its not mentioned anywhere that its on Oracle 8.0.x .
Also, nobody has pointed out the correct syntex for IMMEDIATE EXECUTE .
So , I .....

Regards

 

[avjoshi]

execute immediate '<Any SQL statement>';

Anand.

 

[karluk]

Parbhani has a good point. Metalink note 62592.1 indicates that &quot;execute immediate&quot; was available in Oracle 8.1.5, although its syntax was enhanced in 8.1.6. There's a good chance that lel75 could get the original solution to work. However, it might be academic at this point since &quot;dbms_sql&quot; is doing the job.

 

[sem]

I'm about to be sure that EXECUTE IMMEDIATE is not an option for Le75 at all. Note, that Oracle complains of IMMEDIATE term, so the whole construct seems to be unknow for it.

Regards, Dima

http://seminihin.narod.ru

 

[lel75]

Hello everyone,

I double checked the Oracle version here and it's actually 8.0.5 and not 8.1.5 (which should work with execute immediate). I apologize for all the confusion. I'll be sure to be more accurate and clear next time.

Thanks for all your help!

lel

 

[themikeaustin]

All,

When I execute this from within a stored proc, I receive an "Invalid SQL" error:

EXECUTE IMMEDIATE ('connect DMart_Phase2/dmartphase2@MXDOWL02');

Any help appreciated.

TIA,

Mike

 

[SantaMufasa]

Mike,

"EXECUTE IMMEDIATE" is for SQL commands only; 'connect...' is not SQL...'connect' just gets you into SQL.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:42 (01Sep04) UTC (aka "GMT" and "Zulu"), 12:42 (01Sep04) Mountain Time)

 

[themikeaustin]

OK, but my proc will not compile with the simple connect statement in it, so what's the solution? BTW, I am using 10g...

Thanks!

Mike

 

[SantaMufasa]

Mike,

Can you not connect to the proper user prior to your executing the script containing the PL/SQL block? Why are you trying to connect from within the procedure?

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

 

[themikeaustin]

Mufasa,

I am working on a data mart as part of a commercial package. Each customer will have its own data mart and production database, which will initially be cloned from a base version. We don't want to hard-code the logon/password/schema information into every version of the data mart. As part of setting up each data mart we:

1. Disable Constraints in the data mart
2. Truncate the data mart tables
3. Grant permissions to the data mart user in the production database
4. Create synonyms in the data mart to the tables in the production database that we will be extracting data from.
5. Enable constraints in the data mart

In order to do this, we need to connect to both of the databases. How is it possible to do this in a package without using a procedure?

Thanks!

Mike