Prev: How to remove BOTH duplicates in sort
Next: Data step Do loop to change the value of Macro Variable
From: Nancy Brucken on 21 Feb 2010 03:48
Hi Denise,

It has been a few years since I have poked around inside of OC, and I'm a
bit fuzzy on some of the terminology, but have you tried:

proc sql;
connect to oracle(path=MYPATH user='MYuser' pass=XXXX);
create table view as select * from connection to oracle

(select * from RXC$DCI_BOOK_PAGES);

quit;

instead? There are several different schemas(?) inside of OC where the
various internal tables are stored, and you may need to reference them
differently coming in from SAS than you would from an internal query.

Hope this helps,
Nancy

Nancy Brucken
brucken at provide dot net

On Fri, 19 Feb 2010 21:17:33 -0500, SUBSCRIBE SAS-L Dan
<deniseyu001(a)GMAIL.COM> wrote:

>Dear SASLers:
>
>I am new of Oracle Clinical data base. The DBA gave me a code as
following:
>
>SELECT RXC_DCI_BOOK_PAGES.DCI_ID,
>FROM RXC_DCI_BOOK_PAGES;
>
>That is the code they ran directly inside OC data base. Now I want to get
>the table RXC_DCI_BOOK_PAGES
>
>proc sql;
>connect to oracle(path=MYPATH user='MYuser' pass=XXXX);
>
>create table view as select * from connection to oracle
>
>(select * from RXC_DCI_BOOK_PAGES);
>
>quit;
>
>Then the log returned saying that table does not exist.
>
>That brings me two questions:
>
>1. How to get the table RXC_DCI_BOOK_PAGES, I am not sure if the name is
>the correct one? or a similar name, an alias?
>
>2. How to write a code to look into OC data base to see what tables are
>available.
>
>Thanks for your help.
>
>Dan
From: SUBSCRIBE SAS-L Dan on 22 Feb 2010 09:46
Hi. Nancy:

You suggestion DID work. Thank you very much.

Having solved my current problem. I want also to look through the OC
system. Do you have some good refs for me to look at? Many people on the
SAS-L talek about Schema, what is the Schema.

I have following codes:

proc sql;
connect to oracle(path=MYPATH user='MYuser' pass=XXXX);
create table here.view as select * from connection to oracle

(
select *
from MYSTUDY$current.rdcms_view );
quit;

proc sql;
connect to oracle(path=MYPATH user='MYuser' pass=XXXX);

create table here.DCI_BOOK_PAGES as select * from connection to oracle

(
select *
from RXC.DCI_BOOK_PAGES);
quit;

My guess is that: CURRENT and RXC are two Schema?

The first piece of code finds rdcms_views from schema CURRENT, from MYSTUDY
(among many studies)

The 2nd piece of code finds DCI_BOOK_PAGES from schema RXC from all the
studies.

My questions is:
How do I find out all the studies in OC?
How do I find out all the schemas (RXC?) in OC?
How do I find out all the schemas (current?) under one study (MYSTUDY)?

Thanks.

DAN
From: Tom Abernathy on 22 Feb 2010 10:24
The schema in the Oracle database is the part of the table name before
the period.
So in your examples RXC and MYSTUDY$CURRENT are the two schema's that
you have accessed.
You can then use the schema in a LIBNAME statement and use SAS tools
to query the metadata about tables and variables in your Oracle
database.

libname LIBREF oracle schema='SCHEMA'
user='USERID' password='PASSWORD'
path='PATH'
;


On Feb 22, 9:46 am, deniseyu...(a)GMAIL.COM (SUBSCRIBE SAS-L Dan) wrote:
> Hi. Nancy:
>
> You suggestion DID work. Thank you very much.
>
> Having solved my current problem. I want also to look through the OC
> system. Do you have some good refs for me to look at? Many people on the
> SAS-L talek about Schema, what is the Schema.
>
> I have following codes:
>
> proc sql;
> connect to oracle(path=MYPATH user='MYuser' pass=XXXX);
> create table here.view as select * from connection to oracle
>
>           (
>              select *
>                from MYSTUDY$current.rdcms_view );
> quit;
>
> proc sql;
> connect to oracle(path=MYPATH user='MYuser' pass=XXXX);
>
> create table here.DCI_BOOK_PAGES as select * from connection to oracle
>
>           (
>              select *
>                from RXC.DCI_BOOK_PAGES);
> quit;
>
> My guess is that: CURRENT and RXC are two Schema?
>
> The first piece of code finds rdcms_views from schema CURRENT, from MYSTUDY
> (among many studies)
>
> The 2nd piece of code finds DCI_BOOK_PAGES from schema RXC from all the
> studies.
>
> My questions is:
> How do I find out all the studies in OC?
> How do I find out all the schemas (RXC?) in OC?
> How do I find out all the schemas (current?) under one study (MYSTUDY)?
>
> Thanks.
>
> DAN

From: Mary on 22 Feb 2010 14:22
Dan,

"Schema" is an Oracle word (actually it is an old relational database theory word) for what is commonly referred to in other database management systems as a "database". Thus in MS Access or MS SQL Server, you have tables within a database, whereas in Oracle you have tables within a schema. So you can think of "schema" as being the same as a database. According to Oracle 9i for Dummies (a wonderful little book):

"When you create a set of related tables and all the databse things that go with these tables, you create a schema."

It probably would be helpful for you to pick up a regular Oracle database book, not just an Oracle Clinical, just to get some of the terminology down, such as the "for Dummies" books, but there are others as well. Also the forums on the Oracle web site are quite good. A table in Oracle is more fully named by the notation schemaname.tablename.

You can also download Oracle Express as a free version if you want to try to set up your own "schema"!

-Mary

--- deniseyu001(a)GMAIL.COM wrote:

From: SUBSCRIBE SAS-L Dan <deniseyu001(a)GMAIL.COM>
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: Oracle Clinical: table RXC_DCI_BOOK_PAGES
Date: Mon, 22 Feb 2010 09:46:11 -0500

Hi. Nancy:

You suggestion DID work. Thank you very much.

Having solved my current problem. I want also to look through the OC
system. Do you have some good refs for me to look at? Many people on the
SAS-L talek about Schema, what is the Schema.

I have following codes:

proc sql;
connect to oracle(path=MYPATH user='MYuser' pass=XXXX);
create table here.view as select * from connection to oracle

(
select *
from MYSTUDY$current.rdcms_view );
quit;

proc sql;
connect to oracle(path=MYPATH user='MYuser' pass=XXXX);

create table here.DCI_BOOK_PAGES as select * from connection to oracle

(
select *
from RXC.DCI_BOOK_PAGES);
quit;

My guess is that: CURRENT and RXC are two Schema?

The first piece of code finds rdcms_views from schema CURRENT, from MYSTUDY
(among many studies)

The 2nd piece of code finds DCI_BOOK_PAGES from schema RXC from all the
studies.

My questions is:
How do I find out all the studies in OC?
How do I find out all the schemas (RXC?) in OC?
How do I find out all the schemas (current?) under one study (MYSTUDY)?

Thanks.

DAN
 | 
Pages: 1
Prev: How to remove BOTH duplicates in sort
Next: Data step Do loop to change the value of Macro Variable