May 1, 2008 at 8:36 am
THIS DOES NOT WORK AS WELL
BEGIN
CREATE GLOBAL TEMPORARYTABLE ORD (ORD_NUM INT,ORD MAC INT)
ON COMMIT DELETE ROWS;
CREATE INDEX ORD_INFO ON (ORD_NUM);
SELECT ORD, ORD_MAC
FROM ORDER
WHERE ORD_DATE >='2008-02-09'
END
BEGIN SELECT U.MACNUM,O.MAC,O.ORD_NUM
FROM UNIQ U
JOIN ORD O ON O.MAC= U.MACNUM
THIS IS THE ERROR I AM GETTING
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
pipe
WHERE U.MACNUM = 1236985
May 1, 2008 at 8:45 am
First, do not type in all caps. It's shouting and rude.
Second, it's a temporary table. Using the incorrect verbage can confuse people.
Third, what doesn't work? what does "well" mean? Please include enough information and ask a specific question so people can help you.
May 1, 2008 at 8:56 am
Third, what doesn't work? what does "well" mean? Please include enough information and ask a specific question so people can help you.
inserting the result set from the query into the temporary table does not work, if i run the script as a whole. if i execute the script step by step by creating the temp table first, then executign the statement that will insert the data into the temp, then do my final select it all works fine, but running the script as a whole does not.
May 1, 2008 at 9:00 am
Is this occurring in SQL Server? It looks like an Oracle error is bubbling back through.
My guess is that the create is getting sent through to Oracle and so it causes issues. You might need to run this as two separate batches, use a GO after the create.
Actually looking again, the create isn't valid in SQL Server. Are you running this on Oracle?
May 1, 2008 at 9:05 am
i am running this from oracle
May 28, 2008 at 10:52 am
If you are running this as a script in Oracle then aftre initial Begin Oracle is waiting for anonymous block i.e. some pl/sql constructions. You don't need any BEGIN/END for your scripts if you are not using pl/sql constructrions. Second thing - after each and every sql statement Oracle needs semicolon. This is something similar to GO in SQL Server.
And third one - read more about global temporary tables these are quite different than temporary tables in SQL Server and the main idea is to create them once and use in all sessions, the definition is shared, the data are different for each session.
Gints Plivna
Gints Plivna
http://www.gplivna.eu
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply