Thanks for your help

Nice - but there's a piece which this misses.

A view can define column headings, e.g.:
create or replace view show_people
(EMPLOYEE, MANAGER)
as SELECT emp, mgr from personnel;

The "TEXT" column values in USER_VIEWS do not
contain this part of the view:
Create or replace view show_people
(EMPLOYEE, MANAGER)
?

Hi tom,
I used your script and it's very useful. But the generated script of views contains some Empty lines and it fail the exécution of the scrip :
--Begining of generated script :
create or replace view ADDRESS_REQ_VIEW (
ADDRESS_ID
,PERSON_ID
,ADDRESS_TYPE
,ADDRESS_LINE_1_LL
,ADDRESS_LINE_2_LL
,ADDRESS_LINE_3_LL
,ZIP_CODE_ZP
,CITY_LL
,VALIDITY
,VALIDITY_CHECK_DA

,COUNTRY_ID
,COUNTRY_CODE
,COUNTRY_NAME
) as
select
A.address_id, L.person_id, L.address_ty, A.address_line_1_ll, A.address_line_2_ll, A.address_line_3_ll, A.zip_code_zp, A.city_ll,
A.validity_id, A.validity_check_da, RC.country_id, RC.kw, RC.nm
from ADDRESS A, REF_COUNTRY RC, LOCATION L
where RC.country_id = A.country_id
and A.ADDRESS_ID = L.ADDRESS_ID

/

When i exeSQL> @views\ADDRESS_REQ_VIEW.sql
SP2-0734: commande inconnue au dÚbut de ",COUNTRY_I..." - le reste de la ligne est ignorÚ.
SP2-0734: commande inconnue au dÚbut de ",COUNTRY_C..." - le reste de la ligne est ignorÚ.
SP2-0734: commande inconnue au dÚbut de ",COUNTRY_N..." - le reste de la ligne est ignorÚ.
SP2-0042: commande inconnue ") as" - reste de la ligne ignorÚ.
SP2-0044: Pour la liste des commandes connues entrez HELP
and et pour sortir EXIT.

aucune ligne sÚlectionnÚecute the script :

Any adea
thanks

 

ok thats great but ,
how can get the description of an existing
view ,dynamicly , like user_tab_columns view of sys for tables?
I 've searched all_objects to find one that does the same job for views but i couldn't find any:(
thanks..

SQL> CREATE VIEW A AS
  2  SELECT table_name, tablespace_name FROM user_tables;
SQL> SELECT COUNT(*) FROM A;
  COUNT(*)
----------
       123
SQL> SELECT TABLE_NAME,column_name,DATA_LENGTH,NULLABLE,COLUMN_ID FROM user_tab_columns
  2  WHERE table_name = 'A' ;
TABLE_NAME                     COLUMN_NAME                    DATA_LENGTH N  COLUMN_ID
------------------------------ ------------------------------ ----------- - ----------
A                              TABLESPACE_NAME                         30 Y          2
A                              TABLE_NAME                              30 N          1


Yes , now i see but don't u think it is pretty tricky to give a column such name in user_tab_columns like "table_name" also for views.!!!

Thanks...
 

Tom,

Your script is really helpful. But I have one more question: I need to write a general script to drop the tmp.sql after I run it in both UNIX and window platforms. I modified your script to put all views together and run it at the end as following:

----- getaview.sql ------
set heading off
set pagesize 0
set long 99999999
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on

column column_name format a1000
column text format a1000

prompt create or replace view &1 (
select decode(column_id,1,'',',') || column_name column_name
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt ) as
select text
from user_views
where view_name = upper('&1')
/
prompt /

set termout on
set heading on
set feedback on
set verify on

----- getallviews.sql ------
set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on

spool tmp.sql
select 'spool create_views.sql' from dual;
select '@getaview ' || view_name
from user_views
/
select 'spool off' from dual;
spool off

set termout on
set heading on
set feedback on
set verify on
@tmp
@create_views
HOST rm tmp.sql
HOST rm create_views.sql
HOST del tmp.sql
HOST del create_views.sql


Because the remove/delete command is different in both platforms I have to put both there, but it looks really ugly. How to fix it?

Thanks as always.

Tom,

What I want is a general script that can be run on both UNIX and window platforms, but access to the same database which is on UNIX server. I got the same result from the following running on both window or UNIX SQL*Plus:

SQL> select dbms_utility.port_string from dual;

PORT_STRING
-----------------------------------------------------
SVR4-be-64bit-8.1.0

The getaview.sql and getallviews.sql have to be deployed for both window clients and UNIX clients. What I mean is there any way we can detect from SQL where the user logon from (window or UNIX platform)? 

Just curiousity, I wonder why you don't use dbms_metadata. I think it work well, except just slow. We can use it in PL/SQL procedure too.

select dbms_metadata.get_ddl ('VIEW', view_name) from dual

Hallo Tom & a Reader,

what about

sqlplus_on_windos > select case when replace(process, ':') = process then 'unix' else 'windos' end as client_os
2 from v$session where sid=(select sid from v$mystat where rownum=1);

CLIENT
------
windos

sqlplus_on_unix > select case when replace(process, ':') = process then 'unix' else 'windos' end as client_os
2 from v$session where sid=(select sid from v$mystat where rownum=1);

CLIENT
------
unix

?

Matthias/Tom,

Thank you very much! It is great great! I really appreciate your help!

Hi Tom,

A couple of years ago I wrote a program that generated code to read, update & cache tables. I queried USER_TAB_COLUMNS to get the data and it worked splendid. However, for generating code for views, I had to run a script starting sqlplus and doing a desc because I could not find any trace of views anywhere in USER_TAB_COLUMNS.

Did I smoke something illegal at that time or was views not in USER_TAB_COLUMNS on a Oracle ~8.1.7?

Thanks in advance!
Marcus

"read update and cache tables" -- that is what Oracle does.  Not sure what you did?


Connected to:
Oracle7 Server Release 7.1.6.2.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.1.6.2.0 - Production
 
tkyte@DEV716> create or replace view v as select * from dual;
 
View created.
 
tkyte@DEV716> select column_name from user_tab_columns where table_name = 'V';
 
COLUMN_NAME
------------------------------
DUMMY


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
 
ops$tkyte@ORA817DEV>  create or replace view v as select * from dual;
 
View created.
 
ops$tkyte@ORA817DEV> select column_name from user_tab_columns where table_name = 'V';
 
COLUMN_NAME
------------------------------
DUMMY
 
<b>views were considered tables....even in the way back machine</b>


 

"read update and cache tables" -- that is what Oracle does. Not sure what you did?

I'm using C++ and I want a consistant interface to access the database. OCI is only for oracle, Oracle pro*c has some accent (extensions) in it and some customers want to go cheap and choose a lesser db. Other classlibs are only for windows etc or hard to find for every Unix flavour. It was easier to spend a couple of days making a generator to take care of this. Do you have any thoughts about this?

As far as caching goes, I want to avoid context switches and had to cache setup to make it fast enough. If this wasn't an issue TimesTen (f.ex) wouldn't have a market. An oracle consultat we had said we should assume each oracle call takes ~1mS which was quite accurate (1998ish). After that our department was careful to reading to arrays etc.

Anyway, thanks for the answer Tom!
Marcus