prompt and dbms_output.put_line 1

[beechill4]

Hi,

I am in the process of automating a set of sql's into one script.

one sql i run is to check for the existance of primary keys on all tables :

select table_name from dba_tables
where owner = 'AIBBIS' AND
table_name not in(select table_name from dba_constraints
where constraint_type = 'P'
and owner ='JOE')

I want this script to write an output/log file that is readable.

What is the best way to produce such a file. I want headings etc in the report and be able to wrap text around table names

Is dbms_output.put_line the right thing to use?

J

 

[stefanhei]

Have a look at the SQL*Plus commands:
spool ... write a report to a file
set ... modify apperance of the report
column ... define apperance of a column in the report

Stefan

 

[SantaMufasa]

Beechill,

Although you can create the output you wish using PL/SQL's DBMS_OUTPUT.PUT_LINE, as Stefan mentions, you can more simply use SQL*Plus's report formatting capabilities. Also, to produce the result set of rows that you want, Oracle's "set operators" are much faster/more efficient than using the "IN" comparison operator:

ttitle "Tables Sans PKs"
set linesize 30
spool temp.txt
select table_name from dba_tables where owner = 'TEST'
minus
select table_name from dba_constraints
where constraint_type = 'P'
  and owner = 'TEST'
/
spool off

Tue Jun 21           page    1
       Tables Sans PKs

TABLE_NAME
------------------------------
A
ACCOUNT
ANSWER
...
Tue Jun 21           page    2
       Tables Sans PKs

TABLE_NAME
------------------------------
MYTABLE
MYTABLE1
MY_TABLE
...

SQL*Plus prints a new page heading whenever it exceeds the value of "SET PAGESIZE..."

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[beechill4]

Thanks Dave,

that looks like the kind of track i need to take.

now, if i wanted to put in a piece of text below the list of tables how would i go about that. I would be llokking to display :
'all tables in this database should have a primary key'

J

 

[beechill4]

hi dave, been doing a bit of work and have decided to use utl_file_dir to output my results.

am getting an error though:

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 fileID UTL_FILE.FILE_TYPE;
3 cursor c1
4 is
5 select user_name from aibbis.cd_users;
6 BEGIN
7 fileID := UTL_FILE.FOPEN ('/tmp', 'emp.dat', 'W');
8 FOR emprec IN c1
9 LOOP
10 UTL_FILE.PUT_LINE
11 (emprec.USER_NAME);
12 END LOOP;
13 UTL_FILE.FCLOSE (fileID);
14* END;
SQL> /
UTL_FILE.PUT_LINE
*
ERROR at line 10:
ORA-06550: line 10, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 10, column 7:
PL/SQL: Statement ignored


SQL>

any ideas?

 

[beechill4]

hi,

i have now gotten the pl/sql to execute but the file is not being created. am i missing something in my code?

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 fileID UTL_FILE.FILE_TYPE;
3 cursor c1
4 is
5 select user_name from aibbis.cd_users;
6 BEGIN
7 fileID := UTL_FILE.FOPEN ('/tmp', 'emp.dat', 'W');
8 FOR emprec IN c1
9 LOOP
10 UTL_FILE.PUT_LINE
11 (fileID, emprec.USER_NAME);
12 END LOOP;
13 UTL_FILE.FCLOSE (fileID);
14* END;
SQL> /

PL/SQL procedure successfully completed.

 

[SantaMufasa]

You are pretty close, Beechill. When you use the UTL_FILE.PUT_LINE packaged procedure, since you can output to many files simultaneously, you must specify which file handle to which you would like to output. There fore, your UTL_FILE.PUT_LINE call should read:

...
UTL_FILE.PUT_LINE (fileID, emprec.USER_NAME);
...

Also, if you want to see how to print the message 'all tables in this database should have a primary key' without using PL/SQL, you can say either:

prompt all tables in this database should have a primary key

or

SELECT 'all tables in this database should have a primary key'
FROM DUAL;

Let us know if this provides you with success.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

Sorry, Beechill, we cross-posted above.

If the results of the procedure execution are, "PL/SQL procedure successfully completed," then there are two things I would check for:

1) What are the results of the query:

SELECT count(*) from aibbis.cd_users;

If the results are zero, then that is your problem. If the result are non-zero, then confirm the content of "/tmp" on your SERVER. Remember, "UTL_FILE..." procedure manipulate files only on your server, not on your client-side machine.

Check those two issues and post your findings here.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[beechill4]

hi,

there are 14 rows in the cd_users table.

no files have turned up in the /tmp directory

beechill

 

[SantaMufasa]

But are you looking in the "/tmp" directory of your client connection or the "/tmp" directory of your server?

Specifically, when you connect to Oracle, match the alias entry of your "host service" (or username/password@alias) with the entry of the same spelling in your tnsnames.ora file. Then look at the "HOST=<some name>" definition. Whatever is in the "<some name>" entry is the machine whose "/tmp" you must look at for the 'emp.dat' file.

Also, just for fun, do this query:

col a heading "Writable Paths|On Server machine" format a50
select '['||value||']' a
from v$parameter
where name like 'utl%';

Please post both sets of findings here.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[beechill4]

Hi Dave

SQL> col a heading "Writable Paths|On Server machine" format a50
SQL> select '['||value||']' a
2 from v$parameter
3 where name like 'utl%';

Writable Paths
On Server machine
--------------------------------------------------
[/tmp]

Also, the file has now started to appear on the server, which is very strange. unfortunately, i do not have permission to read this file though!

thanks very much for your help. Brilliant again.