Access frontal interface for Oracle database

#41

On Mon, 11 Aug 2008 05:52:43 GMT, "gym dot scuba dot kennedy at gmail"
<kennedyii (AT) verizon (DOT) net> wrote:

Quote:

I was trying to use a stored procedure (so, an UPDATE query) in order to
copy the data to a temporary table, so that the user does not lock the
table, but opens a copy instead. I this conception erroneous ?

UPDATE has nothing to do with stored procedures
Also readers don't block writers, so the temporary table is redundant.
If you follow this concept, the application will be very unstable and
buggy.

Finally humans happen to read from top to bottom. Only the species
infested with the Mickeysoft virus can't be bothered to acknowledge
that.

--
Sybrand Bakker
Senior Oracle DBA

#42

On 11 Aug, 06:52, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Gloops" <glo... (AT) invalid (DOT) zailes.org> wrote in message

news:g7oj3j$ne3$1 (AT) aioe (DOT) org...
Hello Timmy,

I read very quickly before urging to the office, I shall read more
carefully tonight.

Well, the user name is repeated at the beginning of the ODBC connexion
name, and on my machine another connexion has the user name, so I
presume there should be no problem with that.

I see you give details about using a view, I was rather quick when
studying this point, I think I remember a view shows the same data as in
the tables it presents, so if you modify something in a view it is
modified in the tables ? In this case I fear that a view does not avoid
to lock the table, so several users cannot open the same view, I presume ?

I was trying to use a stored procedure (so, an UPDATE query) in order to
copy the data to a temporary table, so that the user does not lock the
table, but opens a copy instead. I this conception erroneous ?

I read the answers again tonight to see if a proposal allows me
something quick to develop, in the meanwhile I am going to use
independant forms and controls, and copy the data from the tables during
the Form_Open, and backwards during the AfterUpdate. That is very heavy
as a model, but I think it should have the advantage to work.

Anyway thank you for the time spent.

_____________________________________
Timmy! a écrit, le 11/08/2008 06:31 :

Salut, Gloops.

I don't do this (running stored procs) very often not because one
can't but because I have little experience in PL/SQL. *However, you
should be able to do this. *If I can execute DDL, I'm sure stored
procs won't be a problem.

You need a pass through query, though you've probably figured that out
already if you've tried and failed to execute.

Take the syntax that you type into SQLPlus to execute the query and
copy it into the PTQ query SQL View.

If the Oracle user that is being used for your ODBC connection string
is not the (Oracle guys may need to help me out, I'm not 100% sure of
the terminology) main user, ie, the user that makes up the DSN you are
using was made under another "main" (again, bad term, sorry) user, you
are going to need to prefix the proc name with the main user and a
dot.

Here's an example using a create view statement (not Pl/sql), I know,
but hopefully you will see what I am getting at:

Main user (again apologies the Oracle folks): Timmy
User with specific privileges created under user Timmy: Apples

Apples is the user in the DSN.

Now, the following will run in SqlPlus when logged in as Timmy:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Fruits
WHERE Fruit_type = 'Apple'

If you were to run this in the Access environment as a PTQ using the
DSN that uses the user name Apples, the syntax you need in Access is:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Timmy.Fruits
WHERE Fruit_type = 'Apple'

Note the from clause. *You would need to do something similar to your
stored proc.

If you are trying to do this via VBA code, let me know. *There is one
small trick to avoid an error message when VBA runs a PTQ that is a
stored proc or update/delete/insert SQL.

Hopefully some of the cdo flks will *correct some of my poor
terminology above.
--
Tim *http://www.ucs.mun.ca/~tmarshal/
^o
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Oracle!=sqlServer.
Readers do NOT block writers in Oracle.(writers do not block readers either)
You do not need a temp table.
Jim- Hide quoted text -

- Show quoted text -

http://download.oracle.com/docs/cd/B...ist.htm#i13945

HTH

-g

#43

On 11 Aug, 06:52, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Gloops" <glo... (AT) invalid (DOT) zailes.org> wrote in message

news:g7oj3j$ne3$1 (AT) aioe (DOT) org...
Hello Timmy,

I read very quickly before urging to the office, I shall read more
carefully tonight.

Well, the user name is repeated at the beginning of the ODBC connexion
name, and on my machine another connexion has the user name, so I
presume there should be no problem with that.

I see you give details about using a view, I was rather quick when
studying this point, I think I remember a view shows the same data as in
the tables it presents, so if you modify something in a view it is
modified in the tables ? In this case I fear that a view does not avoid
to lock the table, so several users cannot open the same view, I presume ?

I was trying to use a stored procedure (so, an UPDATE query) in order to
copy the data to a temporary table, so that the user does not lock the
table, but opens a copy instead. I this conception erroneous ?

I read the answers again tonight to see if a proposal allows me
something quick to develop, in the meanwhile I am going to use
independant forms and controls, and copy the data from the tables during
the Form_Open, and backwards during the AfterUpdate. That is very heavy
as a model, but I think it should have the advantage to work.

Anyway thank you for the time spent.

_____________________________________
Timmy! a écrit, le 11/08/2008 06:31 :

Salut, Gloops.

I don't do this (running stored procs) very often not because one
can't but because I have little experience in PL/SQL. *However, you
should be able to do this. *If I can execute DDL, I'm sure stored
procs won't be a problem.

You need a pass through query, though you've probably figured that out
already if you've tried and failed to execute.

Take the syntax that you type into SQLPlus to execute the query and
copy it into the PTQ query SQL View.

If the Oracle user that is being used for your ODBC connection string
is not the (Oracle guys may need to help me out, I'm not 100% sure of
the terminology) main user, ie, the user that makes up the DSN you are
using was made under another "main" (again, bad term, sorry) user, you
are going to need to prefix the proc name with the main user and a
dot.

Here's an example using a create view statement (not Pl/sql), I know,
but hopefully you will see what I am getting at:

Main user (again apologies the Oracle folks): Timmy
User with specific privileges created under user Timmy: Apples

Apples is the user in the DSN.

Now, the following will run in SqlPlus when logged in as Timmy:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Fruits
WHERE Fruit_type = 'Apple'

If you were to run this in the Access environment as a PTQ using the
DSN that uses the user name Apples, the syntax you need in Access is:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Timmy.Fruits
WHERE Fruit_type = 'Apple'

Note the from clause. *You would need to do something similar to your
stored proc.

If you are trying to do this via VBA code, let me know. *There is one
small trick to avoid an error message when VBA runs a PTQ that is a
stored proc or update/delete/insert SQL.

Hopefully some of the cdo flks will *correct some of my poor
terminology above.
--
Tim *http://www.ucs.mun.ca/~tmarshal/
^o
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Oracle!=sqlServer.
Readers do NOT block writers in Oracle.(writers do not block readers either)
You do not need a temp table.
Jim- Hide quoted text -

- Show quoted text -

http://download.oracle.com/docs/cd/B...ist.htm#i13945

HTH

-g

#44

On 11 Aug, 06:52, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Gloops" <glo... (AT) invalid (DOT) zailes.org> wrote in message

news:g7oj3j$ne3$1 (AT) aioe (DOT) org...
Hello Timmy,

I read very quickly before urging to the office, I shall read more
carefully tonight.

Well, the user name is repeated at the beginning of the ODBC connexion
name, and on my machine another connexion has the user name, so I
presume there should be no problem with that.

I see you give details about using a view, I was rather quick when
studying this point, I think I remember a view shows the same data as in
the tables it presents, so if you modify something in a view it is
modified in the tables ? In this case I fear that a view does not avoid
to lock the table, so several users cannot open the same view, I presume ?

I was trying to use a stored procedure (so, an UPDATE query) in order to
copy the data to a temporary table, so that the user does not lock the
table, but opens a copy instead. I this conception erroneous ?

I read the answers again tonight to see if a proposal allows me
something quick to develop, in the meanwhile I am going to use
independant forms and controls, and copy the data from the tables during
the Form_Open, and backwards during the AfterUpdate. That is very heavy
as a model, but I think it should have the advantage to work.

Anyway thank you for the time spent.

_____________________________________
Timmy! a écrit, le 11/08/2008 06:31 :

Salut, Gloops.

I don't do this (running stored procs) very often not because one
can't but because I have little experience in PL/SQL. *However, you
should be able to do this. *If I can execute DDL, I'm sure stored
procs won't be a problem.

You need a pass through query, though you've probably figured that out
already if you've tried and failed to execute.

Take the syntax that you type into SQLPlus to execute the query and
copy it into the PTQ query SQL View.

If the Oracle user that is being used for your ODBC connection string
is not the (Oracle guys may need to help me out, I'm not 100% sure of
the terminology) main user, ie, the user that makes up the DSN you are
using was made under another "main" (again, bad term, sorry) user, you
are going to need to prefix the proc name with the main user and a
dot.

Here's an example using a create view statement (not Pl/sql), I know,
but hopefully you will see what I am getting at:

Main user (again apologies the Oracle folks): Timmy
User with specific privileges created under user Timmy: Apples

Apples is the user in the DSN.

Now, the following will run in SqlPlus when logged in as Timmy:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Fruits
WHERE Fruit_type = 'Apple'

If you were to run this in the Access environment as a PTQ using the
DSN that uses the user name Apples, the syntax you need in Access is:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Timmy.Fruits
WHERE Fruit_type = 'Apple'

Note the from clause. *You would need to do something similar to your
stored proc.

If you are trying to do this via VBA code, let me know. *There is one
small trick to avoid an error message when VBA runs a PTQ that is a
stored proc or update/delete/insert SQL.

Hopefully some of the cdo flks will *correct some of my poor
terminology above.
--
Tim *http://www.ucs.mun.ca/~tmarshal/
^o
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Oracle!=sqlServer.
Readers do NOT block writers in Oracle.(writers do not block readers either)
You do not need a temp table.
Jim- Hide quoted text -

- Show quoted text -

http://download.oracle.com/docs/cd/B...ist.htm#i13945

HTH

-g

#45

On 11 Aug, 06:52, "gym dot scuba dot kennedy at gmail"
<kenned... (AT) verizon (DOT) net> wrote:

Quote:

"Gloops" <glo... (AT) invalid (DOT) zailes.org> wrote in message

news:g7oj3j$ne3$1 (AT) aioe (DOT) org...
Hello Timmy,

I read very quickly before urging to the office, I shall read more
carefully tonight.

Well, the user name is repeated at the beginning of the ODBC connexion
name, and on my machine another connexion has the user name, so I
presume there should be no problem with that.

I see you give details about using a view, I was rather quick when
studying this point, I think I remember a view shows the same data as in
the tables it presents, so if you modify something in a view it is
modified in the tables ? In this case I fear that a view does not avoid
to lock the table, so several users cannot open the same view, I presume ?

I was trying to use a stored procedure (so, an UPDATE query) in order to
copy the data to a temporary table, so that the user does not lock the
table, but opens a copy instead. I this conception erroneous ?

I read the answers again tonight to see if a proposal allows me
something quick to develop, in the meanwhile I am going to use
independant forms and controls, and copy the data from the tables during
the Form_Open, and backwards during the AfterUpdate. That is very heavy
as a model, but I think it should have the advantage to work.

Anyway thank you for the time spent.

_____________________________________
Timmy! a écrit, le 11/08/2008 06:31 :

Salut, Gloops.

I don't do this (running stored procs) very often not because one
can't but because I have little experience in PL/SQL. *However, you
should be able to do this. *If I can execute DDL, I'm sure stored
procs won't be a problem.

You need a pass through query, though you've probably figured that out
already if you've tried and failed to execute.

Take the syntax that you type into SQLPlus to execute the query and
copy it into the PTQ query SQL View.

If the Oracle user that is being used for your ODBC connection string
is not the (Oracle guys may need to help me out, I'm not 100% sure of
the terminology) main user, ie, the user that makes up the DSN you are
using was made under another "main" (again, bad term, sorry) user, you
are going to need to prefix the proc name with the main user and a
dot.

Here's an example using a create view statement (not Pl/sql), I know,
but hopefully you will see what I am getting at:

Main user (again apologies the Oracle folks): Timmy
User with specific privileges created under user Timmy: Apples

Apples is the user in the DSN.

Now, the following will run in SqlPlus when logged in as Timmy:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Fruits
WHERE Fruit_type = 'Apple'

If you were to run this in the Access environment as a PTQ using the
DSN that uses the user name Apples, the syntax you need in Access is:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations
FROM Timmy.Fruits
WHERE Fruit_type = 'Apple'

Note the from clause. *You would need to do something similar to your
stored proc.

If you are trying to do this via VBA code, let me know. *There is one
small trick to avoid an error message when VBA runs a PTQ that is a
stored proc or update/delete/insert SQL.

Hopefully some of the cdo flks will *correct some of my poor
terminology above.
--
Tim *http://www.ucs.mun.ca/~tmarshal/
^o
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Oracle!=sqlServer.
Readers do NOT block writers in Oracle.(writers do not block readers either)
You do not need a temp table.
Jim- Hide quoted text -

- Show quoted text -

http://download.oracle.com/docs/cd/B...ist.htm#i13945

HTH

-g

#46

Well, here is some feedback.

I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN
MAJSES", either by ADODB.Execute, or by
Workspace.Connection.QueryDef.Execute

If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE,
INSERT, PROCEDURE, SELECT or UPDATE awaited". If I try and launch
"PROCEDURE MAJSES" I am blamed for a syntax error in the PARAMETERS
clause, and same thing if I try to save the record to define parameters
from a function.

I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
fact, the code of MAJSES), but after that the TMPCLIENTS table was still
empty.

A session is opened by the sub to execute a query, and closed before the
end of the sub. I fear that the temporary table is proper to each
session that is opened like that, so, if ten sessions are opened that
way, ten copies of the same table are created with no way to see data
from one to the other.
So, I presume that a session has to be opened public in a module when
opening the database, to be closed only when closing the database, and
all subs that have to operate on the base must refer to that session
object, but I do not have enough time to test the different syntaxes and
objects to implement this, so this will be for my next mission on
Oracle. I tried with other newsgroups before, I discovered these two
ones by browsing a book last Saturday once I could somewhat situate the
question.

One special point of attention is how to obtain that the session object
in the code refers to the same session as the user interface.

It would be tempting to launch OraDatabase.Execute "PROCEDURE MAJSES" or
"EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
OraDatabase.Execute "BEGIN MAJSES", ORA-06550 SQL execution error, line
1 column 12, PLS-00103, end of file symbol met in place of one of the
following symbols :
:= . ( @ % ;

In the same time, if you see how to correct the syntax, it could be
interesting to do some tests about it, even if I have no time to use it
for the final results. I see some examples in the help of
OraParamArrays, but as I do not exactly situate how much time I need for
this, I have to start up.

In the meanwhile, I could create temporary tables in Access. Of course,
like that, the Access interface will become very fat, but at least
several people can access at the same time.

It was a very good idea to tell me I do not need temporary tables.
Please feel free to propose a way to do like that, from Access.

#47

Well, here is some feedback.

I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN
MAJSES", either by ADODB.Execute, or by
Workspace.Connection.QueryDef.Execute

If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE,
INSERT, PROCEDURE, SELECT or UPDATE awaited". If I try and launch
"PROCEDURE MAJSES" I am blamed for a syntax error in the PARAMETERS
clause, and same thing if I try to save the record to define parameters
from a function.

I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
fact, the code of MAJSES), but after that the TMPCLIENTS table was still
empty.

A session is opened by the sub to execute a query, and closed before the
end of the sub. I fear that the temporary table is proper to each
session that is opened like that, so, if ten sessions are opened that
way, ten copies of the same table are created with no way to see data
from one to the other.
So, I presume that a session has to be opened public in a module when
opening the database, to be closed only when closing the database, and
all subs that have to operate on the base must refer to that session
object, but I do not have enough time to test the different syntaxes and
objects to implement this, so this will be for my next mission on
Oracle. I tried with other newsgroups before, I discovered these two
ones by browsing a book last Saturday once I could somewhat situate the
question.

One special point of attention is how to obtain that the session object
in the code refers to the same session as the user interface.

It would be tempting to launch OraDatabase.Execute "PROCEDURE MAJSES" or
"EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
OraDatabase.Execute "BEGIN MAJSES", ORA-06550 SQL execution error, line
1 column 12, PLS-00103, end of file symbol met in place of one of the
following symbols :
:= . ( @ % ;

In the same time, if you see how to correct the syntax, it could be
interesting to do some tests about it, even if I have no time to use it
for the final results. I see some examples in the help of
OraParamArrays, but as I do not exactly situate how much time I need for
this, I have to start up.

In the meanwhile, I could create temporary tables in Access. Of course,
like that, the Access interface will become very fat, but at least
several people can access at the same time.

It was a very good idea to tell me I do not need temporary tables.
Please feel free to propose a way to do like that, from Access.

#48

Well, here is some feedback.

I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN
MAJSES", either by ADODB.Execute, or by
Workspace.Connection.QueryDef.Execute

If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE,
INSERT, PROCEDURE, SELECT or UPDATE awaited". If I try and launch
"PROCEDURE MAJSES" I am blamed for a syntax error in the PARAMETERS
clause, and same thing if I try to save the record to define parameters
from a function.

I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
fact, the code of MAJSES), but after that the TMPCLIENTS table was still
empty.

A session is opened by the sub to execute a query, and closed before the
end of the sub. I fear that the temporary table is proper to each
session that is opened like that, so, if ten sessions are opened that
way, ten copies of the same table are created with no way to see data
from one to the other.
So, I presume that a session has to be opened public in a module when
opening the database, to be closed only when closing the database, and
all subs that have to operate on the base must refer to that session
object, but I do not have enough time to test the different syntaxes and
objects to implement this, so this will be for my next mission on
Oracle. I tried with other newsgroups before, I discovered these two
ones by browsing a book last Saturday once I could somewhat situate the
question.

One special point of attention is how to obtain that the session object
in the code refers to the same session as the user interface.

It would be tempting to launch OraDatabase.Execute "PROCEDURE MAJSES" or
"EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
OraDatabase.Execute "BEGIN MAJSES", ORA-06550 SQL execution error, line
1 column 12, PLS-00103, end of file symbol met in place of one of the
following symbols :
:= . ( @ % ;

In the same time, if you see how to correct the syntax, it could be
interesting to do some tests about it, even if I have no time to use it
for the final results. I see some examples in the help of
OraParamArrays, but as I do not exactly situate how much time I need for
this, I have to start up.

In the meanwhile, I could create temporary tables in Access. Of course,
like that, the Access interface will become very fat, but at least
several people can access at the same time.

It was a very good idea to tell me I do not need temporary tables.
Please feel free to propose a way to do like that, from Access.

#49

Well, here is some feedback.

I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN
MAJSES", either by ADODB.Execute, or by
Workspace.Connection.QueryDef.Execute

If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE,
INSERT, PROCEDURE, SELECT or UPDATE awaited". If I try and launch
"PROCEDURE MAJSES" I am blamed for a syntax error in the PARAMETERS
clause, and same thing if I try to save the record to define parameters
from a function.

I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
fact, the code of MAJSES), but after that the TMPCLIENTS table was still
empty.

A session is opened by the sub to execute a query, and closed before the
end of the sub. I fear that the temporary table is proper to each
session that is opened like that, so, if ten sessions are opened that
way, ten copies of the same table are created with no way to see data
from one to the other.
So, I presume that a session has to be opened public in a module when
opening the database, to be closed only when closing the database, and
all subs that have to operate on the base must refer to that session
object, but I do not have enough time to test the different syntaxes and
objects to implement this, so this will be for my next mission on
Oracle. I tried with other newsgroups before, I discovered these two
ones by browsing a book last Saturday once I could somewhat situate the
question.

One special point of attention is how to obtain that the session object
in the code refers to the same session as the user interface.

It would be tempting to launch OraDatabase.Execute "PROCEDURE MAJSES" or
"EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
OraDatabase.Execute "BEGIN MAJSES", ORA-06550 SQL execution error, line
1 column 12, PLS-00103, end of file symbol met in place of one of the
following symbols :
:= . ( @ % ;

In the same time, if you see how to correct the syntax, it could be
interesting to do some tests about it, even if I have no time to use it
for the final results. I see some examples in the help of
OraParamArrays, but as I do not exactly situate how much time I need for
this, I have to start up.

In the meanwhile, I could create temporary tables in Access. Of course,
like that, the Access interface will become very fat, but at least
several people can access at the same time.

It was a very good idea to tell me I do not need temporary tables.
Please feel free to propose a way to do like that, from Access.

#50

gazzag a écrit, le 11/08/2008 12:21 :

Quote:

http://download.oracle.com/docs/cd/B...ist.htm#i13945

Well thank you, now that I could establish concurrency thanks to
temporary tables inside the Access interface, I think this can help
ensure consistency.

Supposing I can cope with the treatments I have to finish, I presume a
good moment to read this can be on next Friday.