sas >> Oracle DESCRIBE in Batch PROC SQL?

by Tim Berryhill » Fri, 22 Aug 2003 11:50:44 GMT

I am banging some PROC SQL from SAS 8.2 on unix against a few Oracle 9i
tables. Is there a way to pass DESCRIBE schema.object from SAS to Oracle?
I can open a new window, fire up SQLplus, and run the DESCRIBE, but I would
prefer to "cast it in code."

Suggestions appreciated,
Tim Berryhill

sas >> Oracle DESCRIBE in Batch PROC SQL?

by Richard A. DeVenezia » Fri, 22 Aug 2003 12:52:46 GMT


Can I presume you want meta-data of Oracle tables returned back to SAS
session ?

You could assign libref to oracle space and use proc sql to select stuff
from dictionary.columns.

Or you could 'roll your own' and select stuff from Oracle meta tables,
select * from all_tab_columns where table_name = ....
You can also examine USER_TAB_COLUMNS (tables you own) and DBA_TAB_COLUMNS

I have found to be quite helpful once I get the correct

Richard A. DeVenezia, subclassing Text Pad Control

Similar Threads

1. describe sql server in oracle sql plus

2. DESCRIBE <table_name> in SQL*Plus

Where does SQL*Plus get the info displayed when you do
a DESC <table> ? I thought doing  a trace would show me
selects against SYS.COL$ and stuff but it doesn't. So where
is this program getting its information from ??

3. oracle 11g sql versus sas 8.2 proc sql performance issue

4. SAS Proc SQL vs Oracle 10 SQL


I have a code developed in Oracle SQL that works just fine. It is a
relatively uncomplicated joint of several tables. However when run in SAS,
the same code doesn't work and for the life of me, I can't find the error.

Here is the code with the sas log:

1    libname SWT oracle user=clencar pw=XXXXXXXXXX path=swtp
1  ! buffsize=20000;
NOTE: Libref SWT was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: swtp
5    PROC SQL ;
6            CONNECT to ORACLE (user=clencar password=XXXXXXXXXX
6  ! path=swtp);
7            *-----------------------------------------------------------
7  ! ----------------------;
9            select * from connection to oracle (
10                select MAX(REPORTING_PERIOD_END_DT) AS MAXIMUM
11                FROM swt.swt_site WHERE PUBLISHED_YN='Y'
12                 );
NOTE: Table WORK.REPORT_DATE created, with 1 rows and 1 columns.

13           *-----------------------------------------------------------
13 ! ----------------------;
14           create table site as
15           select * from connection to oracle (
16            select max(site_id) as site_num
17            from swt.swt_site where published_yn = 'Y'
18                 );
NOTE: Table WORK.SITE created, with 1 rows and 1 columns.

19           *-----------------------------------------------------------
19 ! ----------------------;
20           select maximum  into :maximum from REPORT_DATE;
21           select site_num  into :site_num from site;
22           *-----------------------------------------------------------
22 ! ----------------------;
23           CREATE TABLE work.waiting AS
24               SELECT * FROM connection to oracle
25          (
26           select
27                  b.spr_pat_id as PHN
28                , p.BORN_DATE as Birth_Date
29                , as Gender
30                , p.NAME_LAST
31                , p.NAME_GIVEN_1
32                , p.NAME_GIVEN_2
33                , p.NAME_GIVEN_3
34                , p.post_code_prefix as Postal_Code
35                , p.CITY
36                , b.rqst_date as Date_on
38           from SWT.SWT_BOOKING_VW b
40               inner join SWT.SWT_PATIENT_VW p on
41                   p.SITE_ID       = b.SITE_ID
42               and p.spr_pat_id    = b.spr_pat_id
43               and p.rha_id        = b.rha_id
45               left outer join SWT.SWT_POST_OP_VW po on
46                   po.SITE_ID      = b.SITE_ID
47               and po.SURG_CASE_ID = b.SURG_CASE_ID
48               and po.RHA_ID       = b.RHA_ID
51           where
52                   b.SITE_ID       = &site_num
53               and floor(intck('month',p.born_date,&maximum)/12) >= 19
54               and trim(b.book_primary_prov_procedure_id)
55               not in('12001', '12002', '12003', '12004', '12005',
55 ! '12006', '12007',
56                      '12008', '12009', '40018', '40029', '40033')
57               and b.RQST_DATE <= &maximum
58               and (
59                   (trim(upcase(b.SPR_SURG_STAT_DESC)) = 'ACTIVE')
61                   OR
63                   (trim(upcase(b.SPR_IACT_TYPE_DESC)) = 'PERFORMED'
64                   and po.SURG_CMPL_DATE > &maximum)
66                   OR
68                   (trim(upcase(b.SPR_IACT_TYPE_DESC)) = 'CANCELLED'
69                   and b.SURG_IATV_DATE > &maximum
70                   and b.SPR_CANCEL_CODE not in('C7','C9','C11'))
71                   );
ERROR 79-322: Expecting a ).

72            );
ERROR 180-322: Statement is not valid or it is used out of proper order.

73   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.81 seconds
      cpu time            0.25 seconds

5. SAS Proc SQL vs. Oracle 10 SQL

6. Question regarding batch, sqlplus and pl/sql

Hi all,

I have two questions regarding PL/SQL, batch jobs, sqlplus and VPD and
I'd be glad if someone could help me clarify. NB! this is not a
college project.

1. I've read that one cannot include sqlplus commands
inside a pl/sql block. I have a task to package a
number of sql scripts in a pl/sql package. Some of
these sql scripts have a number of sqlplus commands;
for exambple:


define table = &1  -- Name (wildcard)

@VPD_tabell_View_p &table

spool VPD_add_policies1.sql

select '@VPD_add_policy '||TABLE_NAME


How does one incooperate such into a pl/sql package?

2. A developer had previously created batch jobs to
manage the data warehouse, whether it was down or up
and running. This required that if the warehouse was
down, the batch file in question needed to be run to
start the affected programme. How does one convert
this into a package so that whenever the data
warehouse was down, the procedure/function would
automatically start/run the failed data warehouse??
This is how it's currently done in batch:

DM_Up.bat (DM=Datamart)
SET instance=%1
SET mart=%2

SET password=mart

sqlplus DDD/%password%@%instance% @VPD_add_policies_x

REM Re-generete Indexes if the loading actual
indexes failed.
sqlplus DDD/%password%@%instance% @create_IX_DM_x %mart%

REM Gives privilages to access users
sqlplus DDD/%password%@%instance% @Grant_Revoke_DM_p_x G
T * %mart%

SET instance=%1
SET mart=%2

SET password=mart

sqlplus DDD/%password%@%instance% @Grant_Revoke_DM_p_x R
T * %mart%

Thanks in advance for the help,

7. oracle, Word, SQL and a batch file

8. Oracle, SQL, WOrd and a batch file

I have written a SQL statement to extract data from an Oracle database with a variable &1. I have tested the sql statement and am confident that the statement is correct (in place of the variable I used an identifier for this test - otherwist, the statement would not work using SQLPLUS alone). The aim is to use a "print" button on the front end gui (client 6i engine 8i) to run a Word output. I am not interested in doing a mail merge etc. as I want to use preformatted SQL and a batch file triggered by the database to spool the SQL into a Word document.

I already have a batch file which does not suit the purpose but at least copies the sql, connects to sqlplus and opens word (I will attach the batch file a the end with it's result).


copy start.sql+%3+finish.sql temp.sql
set FILEN=dt%username%
sqlplus %4/%5@%6 @temp.sql %1 %TEMP% %FILEN%
"C:\Program Files\Microsoft Office\Office\winword.exe" /t %2 / mmerge


the variables from %2 to %6 are from the Oracle database and I presume that %1 is already set to the identifier I specify.

Do you have the solution?