Oracle schema names: sqlplus "v" Perl

This is Oracle V 10.02.0010 for Windows.

Under sqlplus I can log in as system/seekrit, and can do:

SQL> select table_name, tablespace_name from user_tables where table_name like
'%STATE%';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STATE SYSTEM
LOGMNR_DICTSTATE$

but in Perl, if I do:

my($table_sth) = $dbh -> table_info(undef, 'SYSTEM', '%', 'TABLE');

I don't get back any table data at all.

I assume the problem is the 'SYSTEM', i.e. the value of the schema parameter.

So, any ideas as to what schema I should use in Perl?

Is TABLESPACE_NAME the same as schema?

More info:
I used table_info() to get a list of schema names, and from that can get tables
per schema for some of the schema, but nothing for SYSTEM:
Schema => Tables
----------------
TSMSYS => SRS$
OUTLN => OL$ OL$HINTS OL$NODES
HR => COUNTRIES LOCATIONS DEPARTMENTS JOB_HISTORY EMPLOYEES REGIONS JOBS
FLOWS_FILES => WWV_FLOW_FILE_OBJECT$
SYS => No tables
SYSTEM => No tables
XDA => ^C (Took so long)
MDSYS => ^C
FLOWS_020100 => ^C
DBSNMP => ^C
CTXSYS => ^C

--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

Re: Oracle schema names: sqlplus "v" Perl

>
> Is TABLESPACE_NAME the same as schema?
>
>

No, table_space refers to the set of physical files (one or several
files) that a particular table is stored in. One table space may
house several schemas (such as USERS) and/or one schema may span
several tablespaces, though each schema has a default tablespace where
its tables are created unless otherwise specified in the DDL.

In Oracle and (at least) DB2, schema is synonymous to the username.
If in Oracle you select from user_tables you will only see your own
tables. If you select from all_tables, you will see all tables for
which you have permissions, and there will be an OWNER column which
equates to schema.

So a similar query to what you are looking for in table_info is:

select table_name, owner from all_tables where table_name like '%STATE%';

--
------------------------------------------------------------ --------------------------------------------------
The darkest places in hell are reserved for those who maintain their
neutrality in times of moral crisis.
Dante Alighieri (1265 - 1321)

They who would give up an essential liberty for temporary security,
deserve neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers,
and effects, against unreasonable searches and seizures, shall not be
violated, and no warrants shall issue, but upon probable cause,
supported by oath or affirmation, and particularly describing the
place to be searched, and the persons or things to be seized.

Amendment IV to the Constitution of the United States
------------------------------------------------------------ --------------------------------------------------

Re: Oracle schema names: sqlplus "v" Perl

On Fri, 9 Jun 2006 01:17:06 -0400, Jeffrey Seger wrote:

Hi Jeffrey

> So a similar query to what you are looking for in table_info is:
> select table_name, owner from all_tables where table_name like
> '%STATE%';

Hmmm.

SQL> select table_name, owner from all_tables where table_name like '%STATE%';

TABLE_NAME OWNER
------------------------------ ------------------------------
SCHEDULER$_STEP_STATE SYS
WRI$_SQLSET_STATEMENTS SYS
WWV_FLOW_TREE_STATE FLOWS_020100
STATE SYSTEM
LOGMNR_DICTSTATE$ SYSTEM

Nice try, but that gives the same value as before, SYSTEM, which doesn't work
:-((.

However it does give some tables with OWNER=SYS which I didn't get before...
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

Re: Oracle schema names: sqlplus "v" Perl

I

On Jun 9, 2006, at 2:08 AM, Ron Savage wrote:
> On Fri, 9 Jun 2006 01:17:06 -0400, Jeffrey Seger wrote:
>
>> So a similar query to what you are looking for in table_info is:
>> select table_name, owner from all_tables where table_name like
>> '%STATE%';
>
> Hmmm.
>
> SQL> select table_name, owner from all_tables where table_name like
> '%STATE%';
>
> TABLE_NAME OWNER
> ------------------------------ ------------------------------
> SCHEDULER$_STEP_STATE SYS
> WRI$_SQLSET_STATEMENTS SYS
> WWV_FLOW_TREE_STATE FLOWS_020100
> STATE SYSTEM
> LOGMNR_DICTSTATE$ SYSTEM
>
> Nice try, but that gives the same value as before, SYSTEM, which
> doesn't work
> :-((.
>
> However it does give some tables with OWNER=SYS which I didn't get
> before...

Just for the sake of completeness, are you logging in as 'SYSTEM' in
your perl script?
Or are you logging in as a user which might not have permission to
see the table?

-Chris
--

"Technically, you would only need one time traveler convention" -
Dorothy Gambrell, "Cat and Girl"

Re: Oracle schema names: sqlplus "v" Perl

>
> Just for the sake of completeness, are you logging in as 'SYSTEM' in
> your perl script?
> Or are you logging in as a user which might not have permission to
> see the table?
>
> -Chris

In his original post, he did show that he was logged in, at least on
sqlplus, as system. I assumed he was in DBD::Oracle as well. Perhaps
I shouldn't have?

I took a brief look at sub table_info in Oracle.pm and the base query
looks like this:

SELECT *
FROM
(
SELECT /*+ RULE*/
NULL TABLE_CAT
, t.OWNER TABLE_SCHEM
, t.TABLE_NAME TABLE_NAME
, decode(t.OWNER
, 'SYS' , 'SYSTEM '
, 'SYSTEM' , 'SYSTEM '
, '' ) || t.TABLE_TYPE TABLE_TYPE
, c.COMMENTS REMARKS
FROM ALL_TAB_COMMENTS c
, ALL_CATALOG t
WHERE c.OWNER (+) = t.OWNER
AND c.TABLE_NAME (+) = t.TABLE_NAME
AND c.TABLE_TYPE (+) = t.TABLE_TYPE
)

with some rules after to determine what chunks of the where clause to add to it.

I haven't traced it all the way through, but I suggest sticking a
debug warning in there to let you know what is actually getting
executed. Something like:

warn $Sql;

Right before:

my $sth = $dbh->prepare($Sql) or return undef;
$sth->execute or return undef;
$sth;
}

Then examine STDOUT to see what is actually getting executed, or at
least attempted to. I notice that if it fails prepare, it just
returns undef.

Also, if nothing gets written to STDOUT, then it's probably bailing on
one of the conditionals before it gets this far.



--
------------------------------------------------------------ --------------------------------------------------
The darkest places in hell are reserved for those who maintain their
neutrality in times of moral crisis.
Dante Alighieri (1265 - 1321)

They who would give up an essential liberty for temporary security,
deserve neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers,
and effects, against unreasonable searches and seizures, shall not be
violated, and no warrants shall issue, but upon probable cause,
supported by oath or affirmation, and particularly describing the
place to be searched, and the persons or things to be seized.

Amendment IV to the Constitution of the United States
------------------------------------------------------------ --------------------------------------------------

Re: Oracle schema names: sqlplus "v" Perl

On Fri, 9 Jun 2006 15:13:04 -0400, Jeffrey Seger wrote:

Hi Jeffrey et al

>> Just for the sake of completeness, are you logging in as 'SYSTEM'
>> in your perl script?
>> Or are you logging in as a user which might not have permission
>> to see the table?

I'm logging in as 'system' to create all tables, and populate some static
tables, so I /must/ be able to see them.

> In his original post, he did show that he was logged in, at least
> on sqlplus, as system. I assumed he was in DBD::Oracle as well.
> Perhaps I shouldn't have?

My apologies. I should have said this before:
I'm using the ODBC driver Oracle installs when Oracle itself is installed=
(this
in under Windows).

Also, it's noon Saturday here now, and Monday's a public holiday, so I'll=
try
sql trace Real Soon Now. Thanx for the suggestion. I don't expect it to=
help,
since I know what I'm sending, I just don't know what I /should be/=
sending...
--
Cheers
Ron Savage, ron@savage.net.au on 10/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: Oracle schema names: sqlplus "v" Perl

Comments below ...

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Thursday, June 08, 2006 10:47 PM
To: List - DBI users
Subject: Oracle schema names: sqlplus 'v' Perl

This is Oracle V 10.02.0010 for Windows.

Under sqlplus I can log in as system/seekrit, and can do:

SQL> select table_name, tablespace_name from user_tables where
table_name like
'%STATE%';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STATE SYSTEM
LOGMNR_DICTSTATE$

but in Perl, if I do:

my($table_sth) =3D $dbh -> table_info(undef, 'SYSTEM', '%', 'TABLE');
[rr] I have never used this method. Why don't you just issue the same
query you would issue in SQL*Plus?

I don't get back any table data at all.

I assume the problem is the 'SYSTEM', i.e. the value of the schema
parameter.

So, any ideas as to what schema I should use in Perl?

Is TABLESPACE_NAME the same as schema?
[rr] No, a tablespace is a logical entity, which must have one or more
datafiles assigned to it. Tablespaces are where table and index data
live.

More info:
I used table_info() to get a list of schema names, and from that can get
tables
per schema for some of the schema, but nothing for SYSTEM:
Schema =3D> Tables
----------------
TSMSYS =3D> SRS$
OUTLN =3D> OL$ OL$HINTS OL$NODES
HR =3D> COUNTRIES LOCATIONS DEPARTMENTS JOB_HISTORY EMPLOYEES REGIONS =
JOBS
FLOWS_FILES =3D> WWV_FLOW_FILE_OBJECT$
SYS =3D> No tables
SYSTEM =3D> No tables
XDA =3D> ^C (Took so long)
MDSYS =3D> ^C
FLOWS_020100 =3D> ^C
DBSNMP =3D> ^C
CTXSYS =3D> ^C

--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html



This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: Oracle schema names: sqlplus "v" Perl

On Fri, 09 Jun 2006 16:08:53 +1000, Ron Savage wrote:

Hi Ron

OK. I got this working.

I created a user thru the Oracle web interface http://127.0.0.1:8080/apex.
This allows setting:
o Username
o Password & its expiry (Y/N)
o Account status (Locked/Unlocked)
o User privileges

These are fixed:
o Default Tablespace: USERS
o Temporary Tablespace: TEMP

Then I created an ODBC DSN.

Now I can use DBI -> connect() to create and populate tables.

Then, using uc Username as the schema name, I can get table_info(),
column_info(), primary_key_info() and foreign_key_info() to work.

Case closed.
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html