Re: Access "magic" -- invisible Oracle rows?

CyberSaurn wrote:

All you got to do is to commit your work in SQL*PLUS. All changed you made will only be seen by your SQL*PLUS-session until you issue COMMIT.

>
> Given some of my prior experiences with fronting Oracle with MSAccess, I'll bet
> when I log on at work on Monday everything will work, requiring yet another
> application of my "Cosmic Ray Theory" (*sigh*). On Friday, however, I
> experienced the following behavior between the products and wondered if anyone
> had any clues as to what was going on as I tried a number of things to no
> avail....
>
> Products: Oracle 7.2.2 on UNIX, SQL*Plus (run directly upon UNIX, no Windows
> interface using ODBC), MS Access 2.0 running on NT client, 16-bit Oracle driver
> SQORA73N.DLL. All products have been working fine for over a month and
> interacting as expected until....
>
> Using MSAccess and SQL-Passthrough queries I (query 1) created a working view,
> (query 2) created a working table that mirrors the view. (query 3) created an
> Oracle PL/SQL stored procedure which empties the table, cursors through the
> view and inserts all the views rows into the table (sidebar: I did this because
> I need to copy views into tables for performance reasons and I didn't think
> import/export could do this for me (only moves like-object-to-like-object?) and
> there is no unload function in Oracle (true?) unlike DB2 where I can unload the
> view and load the unload file back into the table.)
>
> I ran the error-free stored procedure in SQL*Plus. It completed successfully
> (no errors). Viewing the table in Access, it was empty (Huh?). I tinkered about
> for a while rerunning things and scratching my head, wondering what was wrong,
> as I couldn't see an error in my procedure or table definition. During this
> process, I selected * from the table in SQL-Plus. Voila! Rows! Ahha! Fluky
> Access/ODBC! I'm used to this! Time to "Do the Drill"....
>
> Refreshed my table attachment in Access and looked at the table. Still empty.
> Removed the attachment and reattached. Still empty. Got out of the Access
> database and back in. Still empty. Got out of Access and back in. Still empty.
> At this point I needed to leave, so I did not try the
> "when-all-else-fails-power-down-totally-and-try-again" step. Did try SQL*Plus
> again as I was logging off -- all rows still present and accounted for.
>
> Anyone know what little ODBC glitch (or what I might be doing wrong) is
> preventing me from seeing the rows in Access?
 

-- 
Regards

Matthias Gresz    :-)