About EasyMorph Tutorials & Examples Web-help

Oracle Snapshot query


#1

I am attempting to run a previous snapshot in an oracle database using the dual table. Is there a way to run a short select script form the dual table before running a normal sql query? Every time I attempt this i get a ORA-00933 error.


#2

Hi Matt,

Do those two queries have to be executed as a single database “command” or it will be OK to use separate transformation for each of those queries?


#3

From previous query tool experience the set snapshot command is executed previous to, and in-line with the sql query. A transformation wouldn’t work in this case because the DBA has place parameters around what can be accessed for the sake of efficiency.


#4

Matt,

Native Oracle driver, used by EasyMorph can only execute one statement at a time. Anonymous PL/SQL Block can be used to execute several statements at once. But we can’t extract SELECT statement results from such block. So in the context of EasyMorph anonymous blocks are only useful for statements like CREATE TABLE, INSERT, UPDATE, etc.

It seems that stored function which returns a nested table type can be used here with the following statement:

SELECT * FROM (TABLE(GET_ALL_ROWS()))

where GET_ALL_ROWS is a name of a stored function.


#5

Thank you for the insight everyone. I really appreciate the feedback.