Run a Select statement on an alternate OCI tablespace

[Farrell]

Hi everyone,

I hope somebody here can help me, I'm not new to php but I'm a total noob to
Oracle and the OCI8 stuff in PHP.

I have an instance in Oracle that has 2 tablespaces, 1 of which is the
default tablespace for the user.

I have tables in both tablespaces but cannot seem to run queries on the
non_default one.

Example

$query = 'select * from administrator.mytable';
or
$query = 'select * from mytable';

works fine where mytable is located in the default tablespace for the
administrator user

wheras

$query = 'select * from administrator.myothertable';
or

$query = 'select * from myothertable';

doesn't work

FYI, after creating the queries, i do the following...

$stmt = OCIParse($db_link, $query);
$exec = OCIExecute($stmt);
$current_row = 0;

while ( OCIFetch($stmt) )
{
print "fetch<br>";
$ncols = OCINumCols($stmt);
for ( $i = 1; $i <= $ncols; $i++ )
{
$column_name = OCIColumnName($stmt,$i);
$column_value = OCIResult($stmt,$i);
$resultset[$current_row][$i-1]=$column_value;
$resultset[$current_row][$column_name]=$column_value;
}
$current_row ++;
}
print "made it here";

the "fetch" never gets printed out but the "made it here does"

Now I can almost see y'all thinking its something to do with the second
tablespace query or something but when i copy paste the query text into
SQL*Plus, i get values returned.

Anyhoo, any help on this would be much appreciated because theres nothing
specific in the oracle book on fully specifying the tablespace to be used
when writing a query (ie no select * from tablespace1.table - theres just
select * from schema.table) and i get a million naff links returned on
google due to the general nature of the words i'm typing in.

Thanks in advance

David

[Andy Hassall]

On Wed, 21 Aug 2002 16:42:19 +0100, "Farrell" <csn...@droogie.co.uk> wrote:

>I have an instance in Oracle that has 2 tablespaces, 1 of which is the
>default tablespace for the user.
>
>I have tables in both tablespaces but cannot seem to run queries on the
>non_default one.
>

>$query = 'select * from administrator.mytable';
>or
>$query = 'select * from mytable';
>
>works fine where mytable is located in the default tablespace for the
>administrator user
>
>wheras
>
>$query = 'select * from administrator.myothertable';
>or
>
>$query = 'select * from myothertable';
>
>doesn't work

"doesn't work" is not a useful description of what happens in any computer
program :-p

>FYI, after creating the queries, i do the following...
>
> $stmt = OCIParse($db_link, $query);

You've not bothered to check whether it succeeded or not.

"OCIParse() parses the query using conn. It returns the statement identity if
the query is valid, FALSE if not. The query can be any valid SQL statement or
PL/SQL block. "

> $exec = OCIExecute($stmt);

You've not bothered to check whether it succeeded or not.

"OCIExecute() executes a previously parsed statement. (see OCIParse()). The
optional mode allows you to specify the execution-mode (default is
OCI_COMMIT_ON_SUCCESS). If you don't want statements to be committed
automatically specify OCI_DEFAULT as your mode.

Returns TRUE on success, FALSE on failure. "

Odds are one or more have failed for some reason; probably permissions, or
typos.

In case of any failure, check the error message. It'll tell you why.

http://www.php.net/manual/en/function.ocierror.php

> $current_row = 0;
>
> while ( OCIFetch($stmt) )
> {
> print "fetch<br>";
> $ncols = OCINumCols($stmt);
> for ( $i = 1; $i <= $ncols; $i++ )
> {
> $column_name = OCIColumnName($stmt,$i);
> $column_value = OCIResult($stmt,$i);
> $resultset[$current_row][$i-1]=$column_value;
> $resultset[$current_row][$column_name]=$column_value;
> }
> $current_row ++;
> }
> print "made it here";
>
>the "fetch" never gets printed out but the "made it here does"
>
>Now I can almost see y'all thinking its something to do with the second
>tablespace query or something but when i copy paste the query text into
>SQL*Plus, i get values returned.

The tablespace that a table is in is irrelevant to a query.

Which user are you logging in as, anyway? 'administrator'? Or another user?

>Anyhoo, any help on this would be much appreciated because theres nothing
>specific in the oracle book on fully specifying the tablespace to be used
>when writing a query (ie no select * from tablespace1.table - theres just
>select * from schema.table)

That's because it's completely meaningless to specify the tablespace - it's
irrelevant. Tablespaces specify the storage of the data; they are not in any
way part of the name of the object.

Tables are part of a schema, and schemas are part of a database - together
they uniquely identify a table.

--
Andy Hassall (an...@andyh.org) icq(5747695) http://www.andyh.org
http://www.andyhsoftware.co.uk/space | disk usage analysis tool

[Farrell]

First of all, thanks for the response.

You're right, I wasn't specific enough.

By not working, i mean i don't get any results - but neither do i get any
errors.

i left my error checking out of the code i posted to make it simpler to read
but i do error check!

after the :

$stmt = OCIParse($db_link, $query);

i do

$oerr = OCIError($stmt);

then print the error but the var $oerr is blank.

to check for errors i do

print "<p>Error code = [" . $oerr['code'] . "] - message = [".
$oerr['message']."]<br>";
$exec = OCIExecute($stmt);

print "exec = $exec stmt = $stmt";

i get the following output
Error code = [] - message = []

Error code 2= [] - message 2= []
exec = 1 stmt = Resource id #4

which as you can see doesn't show an error.

Now, to test my error checking if i mess up the query so that instead of
'select' it says 'selecdt' , i get the errors:

Error code = [] - message = []

Warning: OCIStmtExecute: ORA-00900: invalid SQL statement in E:\ocitest.php
on line 22

Error code 2= [900] - message 2= [ORA-00900: invalid SQL statement ]
exec = stmt = Resource id #4
Warning: OCIFetch: ORA-24374: define not done before fetch or execute and
fetch in E:\ocitest.php on line 31

.

so there is no error shown, it just doesn't return any rows (wheras same
query in sql*plus does)

any ideas?

[Farrell]

I figured it out.

My SQL*Plus window has been open for days now. In effect there has been 1
session active in SQL*Plus

The php was connecting using the same connection information (user etc) as
the SQL*Plus window but crucially - in a new session.

Being a mysql user, i assumed that if i can see data in one window using one
user then i should be able to see the same data in another window using the
same user. but alas, i did not know the intricacies of commit.

I'm sure y'all know this but for those who (like me) are hard of thinking.

When you make changes to an oracle db, they don't go permanent until you
commit - fair 'nuff, i got that but what i didn't get was that those changes
are VISIBLE for the session regardless of commit.

So, in my SQL*Plus window, i had made some changes (inserted data) and could
see that data returned but because php connected as a new session, it
couldn't see the data.

I also explains why i got no errors, the table was found by php, it was just
empty.

anyways, thanks for anyone who gave this some thought (and A.H who replied).

D

[Andy Hassall]

On Thu, 22 Aug 2002 15:17:57 +0100, "Farrell" <csn...@droogie.co.uk> wrote:

>Being a mysql user, i assumed that if i can see data in one window using one
>user then i should be able to see the same data in another window using the
>same user. but alas, i did not know the intricacies of commit.

Welcome to a _real_ database ;-)