Oracle Syntax for SELECT INTO a Temp Table? 4

[kjv1611]

Is it possible to use a SELECT INTO statement in Oracle SQL and get the same/similar results as said statement in MS SQL? If not, then do I have to first create the table, and then use INSERT INTO?

Thanks for any info/references anyone can give me. I have practically no experience whatsoever on Oracle SQL, and I'm learning rather quickly that what I learned in MS SQL does not really carry over - some things, yes, but a lot - no.

 

[SantaMufasa]

KJV,

First, Welcome to the World of Oracle ! (May you live long and prosper here.)

Oracle usually provides about a dozen ways to accomplish things that there are either only 1 (or zero) ways to do it in other environments.

So, if you can explain your objectives and "wish list" for this task, we'll try to offer some solutions for you.

In direct response to your question, you can do the following (as one of the several ways to do what [I believe] you wanted):

create table kjv as select * from s_emp;

Table created.

describe kjv

Name                          Null?    Type
----------------------------- -------- -------------
ID                            NOT NULL NUMBER(7)
LAST_NAME                     NOT NULL VARCHAR2(25)
FIRST_NAME                             VARCHAR2(25)
USERID                                 VARCHAR2(8)
START_DATE                             DATE
COMMENTS                               VARCHAR2(255)
MANAGER_ID                             NUMBER(7)
TITLE                                  VARCHAR2(25)
DEPT_ID                                NUMBER(7)
SALARY                                 NUMBER(11,2)
COMMISSION_PCT                         NUMBER(4,2)

select count(*) from kjv;

  COUNT(*)
----------
        25

Let us know if we're getting close to what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

I'll have to test, but that looks EXACTLY like what I was wanting to do!

Thanks!

I'll post back with whether it worked or not, and what questions I run into.

 

[kjv1611]

Okay, just like that, i went and created a temp table off one of the system tables I'm trying to query - I went with one I knew was fairly small - most of them are way too large to just toss them into a table for a test.

So, with such a temp table, is there any way to link to it, or import it into an Access database? Thanks again for any suggestions.

 

[kjv1611]

One other question that is related, but I suppose really could be it's own thread... though would be short, I think.

If I create a temp table, when is said table deleted? Should I make sure to manually delete it, or is it deleted once the current connection to the server is lost/severed, or I suppose, just what is the scenario when it can be deleted - I imagine I can find a reference for this one later-on if I need to.

 

[SantaMufasa]

KJV,

If you want the contents of the table to be available to use in Access, then you will not want to use an Oracle temporary table, per se, since the data will not persist beyond your Oracle session (for Access to access <grin>).

So, by creating, populating, and COMMITting the contents of a standard Oracle table, you can make the table's contents visible to Access via, for example, ODBC (Open Database Connectivity).

If I create a temp table, when is said table deleted?

Interestingly, in Oracle temporary tables are PERMANENT, i.e., the table structure does not go away until the owner (or a user with privileges to do so) performs a "DROP TABLE <table_name>;" on the temporary table.


What is temporary in a temporary table are the data in the table. By default, the data in a temporary (Oracle) table persist only during the transaction that operates on the data. The data disappear upon COMMIT or ROLLBACK, or upon disconnection (normal or abnormal) of the responsible session. And the data are never visible/available to another session.

Did this answer your question, KJV?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

Yes, indeed, it does answer my question. Thanks for your time.

Now, it however brings me to a new question. How would I create a standard Oracle table as apposed to temporary? I'm used to in SQL Server, specifying the name, #MyTempTable... but in Oracle, it was just .. MyTempTable.

My guess is that if you specify the database or instance name before the table name, then it's a standard table?

If that's the case, I just tested and created one....

I created it like this:

CREATE TABLE MyDb.mytesttable20110121
(testid numeric (10));

And that created a table.

Now I'm saying "database" and "instance" because when I tried to research this, I found folks talking about there being databases and instances in Oracle as different things. From the sound of it, the "database" is really the same as the "server" on SQL Server, and the "instance" would be more like the individual "datbases" on MS SQL Server.... or at least that's my stab at it... I may be totally off.

Anyway, if that's how you create the table, then I have already done so with one.. so I'd just need to drop that table specifically after a script is run, to be sure they don't built up, I suppose..

Yeah, I can see right now that I'm going to HAVE to test this.

 

[SantaMufasa]

Your questions, KJV, are very well taken. They represent self-inflicted ambiguities that Oracle created for itself virtually from the company's inception.

Here is a brief treatment of some of the terms in the Oracle World that you mention, above:

Instance: The memory structures and background processes that manage the contents of an Oracle database -- generally, the pieces of Oracle that disappear if electricity is no longer flowing to the machine where Oracle is running. The memory structures include components such as:[ul][li]Shared Pool[/LI][LI]Database Buffer Cache[/li][li]Log Buffer[/li][/ul]Background processes are numerous, but include processes such as:[ul][li]System Monitor[/li][li]Process Monitor[/li][li]Database Writer(s)[/li][li]Log Writer(s)[/li][li]Checkpoint process(es)[/li][li]individual connection processes[/li][li]and many, many more[/li][/ul]

Database: The (generally) disk-stored data structures of Oracle. This includes all tables (owned by all users/schemas). Among the tables owned by user/schema SYS are the tables and objects that make up the Oracle Data Dictionary.

The equivalent of Oracle's Instance plus the Database typically has the label Database Server in other database-vendor environments.

Schema or User or Owner: Oracle uses these terms virtually synonymously. A single Oracle database contains multiple schemas/users/owners. Each table, index, view, et cetera, is owned by a single Oracle schema/user/owner. (In other database-vendor environments, they use the term, database, where Oracle uses the terms schema/user/owner.)

I hope this explanation helps de-mystify some of the apparent descrepancies/ambiguities that exist between Oracle and other database vendors' terminologies.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry, KJV. I forgot to compare/contrast the syntax of standard Oracle table versus a temporary Oracle table. I shall use minimal syntax in each case:[ul][li]Oracle standard table syntax:

SQL> create table x (col_a number, col_b varchar2(20));

Table created.

SQL> insert into x values (1,'KJV');

1 row created.

SQL> insert into x values (2,'SantaMufasa');

1 row created.

SQL> select * from x;

     COL_A COL_B
---------- --------------------------------------------
         1 KJV
         2 SantaMufasa

SQL> commit;

Commit complete.

SQL> select * from x;

     COL_A COL_B
---------- --------------------------------------------
         1 KJV
         2 SantaMufasa

[/li][li]Oracle temporary table syntax:

SQL> create global temporary table y (col_a number, col_b varchar2(20));

Table created.

SQL> insert into y values (1,'KJV');

1 row created.

SQL> insert into y values (2,'SantaMufasa');

1 row created.

SQL> select * from y;

     COL_A COL_B
---------- ------------------------------------------------------------
         1 KJV
         2 SantaMufasa

SQL> commit;

Commit complete.

SQL> select * from y;

no rows selected

[/li][/ul]See that the only significant difference between the behavior of the two types of tables is the persistence of the data: standard-table data, once COMMItted, persists indefinitely; temporary-table data, once COMMITted, disappears (by default).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[dkyrtata]

kjv1611

Since you are new to Oracle, I should mention one thing.

If you want to DROP the table structure or do any other DDL (Data Definition Language) command, do so after you have COMMITted or ROLLBACKed your work. DDL commands do an implicit COMMIT. Not a problem if you end up doing a COMMIT anyway.

However, in the event that you need to do a ROLLBACK, but issue a DDL command first, your work will be committed. Therefore there will be nothing to rollback.

 

[kjv1611]

Thanks, Santa, I feel like I've been to a college-level database class (and I mean that in the best way possible - no sarcasm).

dkyrtata,

Thanks for the note on Commiting and Rolling back. However, I'm not terribly sure I understand all of that. It sounds like until you "Commit" something, it's more or less temporary, so it's stored in RAM or such. Then, once you commit it, it's stored to the disk, and is there fore "actually" permanent until deleted... which will give you greater data recovery if necessary.

I have no clue whether that's correct, I'm just trying to make sense of the definitions above.

If that is correct, then for the purposes of what I would need it for (in my current job), then it wouldn't need to be committed. I'd just need it available long enough to be grabbed by MS Access.... and then once grabbed, it can be deleted. And even this is temporary.

Okay, trying to trim up what I originally typed up... was too long..

My main goal with this is to either use a passthrough query in Access to grab what is necessary, OR use VBA statements to run the Oracle code via DAO or ADODB in VBA... I'm somewhat thinking that will be best if I want to actually build a table or tables, filter a little further, then return the data to an Access table for reporting.

Then I can keep the "Access" portion of it the same once we do switch from Oracle to MSSQL, and just modify the Oracle SQL to now match the MS SQL.

Personally, I'd like to do some further digging on my own - possibly with setting up my own system(s) at home for it, but I don't kow for sure that I'll find the time. Maybe, we'll see. Career-wise, so long as I intend on doing things with my current employer, I might as well focus on MS SQL, however. Unless there is some SUPER HUGE change(s), then we'll not be going to non-MS products ever.

Well, still went long... sorry 'bout that.

Regardless, the whole thing is interesting to me. And some of the terminology rings a bell from talking with my Oracle friend about some Oracle db issues a few times over the past couple years.

 

[Turkbear]

Hi,
Looking into some Oracle books or the docs can help you understand how Orale handles transactions;heer is an excerpt
about Commit and Rollback It is from a v9 doc set but the concepts are still valid)

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c17trans.htm#327[/URL]]
Transaction Management Overview
A transaction in Oracle begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements.

When a transaction begins, Oracle assigns the transaction to an available undo tablespace or rollback segment to record the rollback entries for the new transaction.

A transaction ends when any of the following occurs:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
A user disconnects from Oracle. The current transaction is committed.
A user process terminates abnormally. The current transaction is rolled back.
After one transaction ends, the next executable SQL statement automatically starts the following transaction.


--------------------------------------------------------------------------------
Note:
Applications should always explicitly commit or roll back transactions before program termination.

--------------------------------------------------------------------------------


Commit Transactions
Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.

Before a transaction that modifies data is committed, the following has occurred:

Oracle has generated rollback segment records in buffers in the SGA that store rollback segment data. The rollback information contains the old data values changed by the SQL statements of the transaction.
Oracle has generated redo log entries in the redo log buffer of the SGA. The redo log record contains the change to the data block and the change to the rollback block. These changes may go to disk before a transaction is committed.
The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction is committed.


--------------------------------------------------------------------------------
Note:
The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process. This writing takes place when it is most efficient for the database to do so. It can happen before the transaction commits or, alternatively, it can happen some time after the transaction commits.

--------------------------------------------------------------------------------


When a transaction is committed, the following occurs:

The internal transaction table for the associated rollback segment records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.
The log writer process (LGWR) writes redo log entries in the SGA's redo log buffers to the online redo log file. It also writes the transaction's SCN to the online redo log file. This atomic event constitutes the commit of the transaction.
Oracle releases locks held on rows and tables.
Oracle marks the transaction complete.

See Also:
"Overview of Locking Mechanisms"
"Oracle Processes Overview" for more information about the background processes LGWR and DBWn


Rollback of Transactions
Rolling back means undoing any changes to data that have been performed by SQL statements within an uncommitted transaction. Oracle uses undo tablespaces or rollback segments to store old values. The redo log contains a record of changes.

Oracle lets you roll back an entire uncommitted transaction. Alternatively, you can roll back the trailing portion of an uncommitted transaction to a marker called a savepoint.

All types of rollbacks use the same procedures:

Statement-level rollback (due to statement or deadlock execution error)
Rollback to a savepoint
Rollback of a transaction due to user request
Rollback of a transaction due to abnormal process termination
Rollback of all outstanding transactions when an instance terminates abnormally
Rollback of incomplete transactions during recovery
In rolling back an entire transaction, without referencing any savepoints, the following occurs:

Oracle undoes all changes made by all the SQL statements in the transaction by using the corresponding undo tablespace or rollback segment.
Oracle releases all the transaction's locks of data.
The transaction ends.

See Also:
"Savepoints In Transactions"
"Overview of Locking Mechanisms"
Oracle9i Recovery Manager User's Guide for information about what happens to committed and uncommitted changes during recovery


Savepoints In Transactions
You can declare intermediate markers called savepoints within the context of a transaction. Savepoints divide a long transaction into smaller parts.

Using savepoints, you can arbitrarily mark your work at any point within a long transaction. You then have the option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction. For example, you can use savepoints throughout a long complex series of updates, so if you make an error, you do not need to resubmit every statement.

Savepoints are similarly useful in application programs. If a procedure contains several functions, then you can create a savepoint before each function begins. Then, if a function fails, it is easy to return the data to its state before the function began and re-run the function with revised parameters or perform a recovery action.

After a rollback to a savepoint, Oracle releases the data locks obtained by rolled back statements. Other transactions that were waiting for the previously locked resources can proceed. Other transactions that want to update previously locked rows can do so.

When a transaction is rolled back to a savepoint, the following occurs:

Oracle rolls back only the statements run after the savepoint.
Oracle preserves the specified savepoint, but all savepoints that were established after the specified one are lost.
Oracle releases all table and row locks acquired since that savepoint but retains all data locks acquired previous to the savepoint.
The transaction remains active and can be continued.


--------------------------------------------------------------------------------
Note:
Whenever a session is waiting on a transaction, a rollback to savepoint does not free row locks. To make sure a transaction doesn't hang if it cannot obtain a lock, use

FOR UPDATE ... NOWAIT

before issuing UPDATE or DELETE statements.

--------------------------------------------------------------------------------

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Here is a link to similar doc info for v 10g

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/transact.htm#i6564

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kjv1611]

Thanks for the references. I'll have to look over it a couple times. I did read over most of the quoted material just now, but my brain seems to be on strike... well for most of today.

 

[SantaMufasa]

It sounds like until you "Commit" something, it's more or less temporary

That is true for Data-Manipulation-Language (DML) commands. If you successfully perform Data-Definition-Language (DDL) commands (e.g., CREATE, ALTER, DROP, RENAME, et cetera) or Data-Control-Language (DCL) commands (e.g., GRANT or REVOKE), then they carry with them an automatic COMMIT both before execution and after execution.

Then, once you commit it, it's stored to the disk

Oracle architecture can appear rather counter-intuitive (when compared, for example, to SQL Server and other-vendors' db architectures). When the Oracle Instance is up and running against a database, database data can reside either (or both) on physical disk, and/or in the Database Buffer Cache (in memory).


If your Oracle session requests to see or manipulate data, then your assigned Oracle-server process checks first to see if the data you request in "in memory" (i.e., the database buffer cache). If the data is not in memory, then your server process reads the data from disk, into the database buffer cache. At that point, your processing request continues.

Any changes you perform take place on data in the database buffer cache. Then, if/when you COMMIT, your assigned server process copies the changed data block(s) from memory to the LOG_BUFFER and the LGWR (Log Writer process) writes the contents of the LOG_BUFFER to the current On-Line Redo Log File. Because the COMMITted changes appear on the On-Line Redo Log file, Oracle does not require the COMMITted data blocks to be written immediately to the Database Data File(s). Oracle's DBWR (Database Writer process(es)) write blocks from the database buffer cache, back to the database data file(s), on a schedule/basis that does not depend upon COMMITs. In fact, Oracle's DBWR process can even write un-COMMITted changed blocks from memory back to the database data file(s)! (How's that for counter-intuitive?) The reason that this is acceptable is because the real, COMMITted data resides in the on-line (or archive) redo log files. So if disaster strikes (and a recovery occurs), Oracle recovers from a combination of backed up data files and the contents of the redo log files.

So, in summary of this topic (in the Oracle World):[ul][li]Committed data does not need to be written immediately to database data files, and[/li][li]Un-committed data can be (and often is) written to database data files.[/li][/ul](Interesting [and a little weird], huh?)

...for the purposes of what I would need it for (in my current job), then it wouldn't need to be committed. I'd just need it available long enough to be grabbed by MS Access

Ah, KJV, there's the rub...if data is not COMMITted, then it is not yet visible to ANY other session, whether it is an Access session or even another session that the changing user owns. This is a very important point to understand in the Oracle World.

[ul]Explanation: If Oracle User "A" modifies Oracle data, the assigned server process writes a pre-change copy of the data to an Oracle Rollback Segment. Next, the assigned server process makes requested changes to a copy of the data in the Database Buffer Cache. Until "COMMIT happens" <grin>, any other process (e.g. User "B", User "C", et cetera) that requests to see data from a row that User "A" has changed (but not yet COMMITted) will see only a copy of the pre-change image that resides in the Oracle Rollback Segment...The other users will not see User "A"'s changed copy of the data.

Therefore, if you want Access (or any other process, Oracle or not) to see results of INSERTs, UPDATEs, or DELETEs, you must first COMMIT the data. (The "changer" of the data is the only process that can "see" the results of their changes.) And as I mentioned in my earlier response, neither Access, nor any other process requesting to see/modify Oracle data, can see/access the contents of an Oracle (Global) Temporary Table.[/ul]

Let us know if this clarifies/answers the issues you posted, above.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

KJV, Turkbear, et. al.,

Sorry...during the (rather extended) time I was composing a classroom-style explanation for KJV, I didn't see that Turkbear posted a link to doc. (That may be one of my weaknesses is that I tend to answer questions with a "home grown" explanation versus pointing to documentation.) But perhaps my posting will act to reiterate/clarify what's in the doc.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
That's OK, Santa, your explanations are better than Oracle's docs ( and even some of their classes - not those you worked on, obviously )





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

HeHeHe.

Thanks, Turkbear, you are very kind.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
You are welcome.

Actually I really enjoyed all the Oracle classes I took ( except for a very tedious telecourse on 'Implementing OFA' where the instructor just read the book at us!)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kjv1611]

Santa,

Your explanations have been much easier to understand than those from the linked references. I'm not saying I've taken it all in already, but if I had to choose which to read, it'd be your instructions for sure.

Thanks again. I'll be doing some testing of what I want to do with the Oracle to Access pieces today, and try to remember all the above... at least some of it.

If I understand half-way correctly, then if I run a statement like:

[B]>SQL[/B] CREATE TABLE MyOracleTbl AS SELECT TOP 100 * FROM FancySystemTableOne;

Then table will be available to anybody/anything until it's deleted/dropped, b/c it is using a command that automatically commits. That would be a DDL command (Data- Definition-Language)

However, if I just had:

[B]>SQL[/B] SELECT TOP 100 * FROM FancySystemTableOne;

That data would be kept in buffer/memory, but wouldn't be committed (yet), as it would be a Data-Manipulation-Language (DML) command.

Well, I hope I'm right anyway...

Now for another question on this.. actually, I'll just post another thread, as it's related, but not the same original question.

Thanks to all for the responses and references.

Of course, if I'm wrong on my assumptions, I'd appreciate any further clarification/correction.