Writing to a File using pl/sql

Thread Tools Search this Thread
Top Forums Programming Writing to a File using pl/sql
# 1  
Old 02-21-2008
Writing to a File using pl/sql

Hi I am new to using pl/sql on a unix platform and am having trouble writing to a file from within a block. Below is an example of the code that I have. I know that I need to use UTL_FILE to accomplish this; however, I keep getting errors. Can someone please help me?

I am trying to create a csv file of tables name from a schema. However, I cannot figure out how to write the table names into a csv file after getting the user input.

Any help would be appreciated.


prompt Enter Table owner;
accept user_schema char prompt 's (SYSTEM), m (WMSYS), b (Both): ';


user_input varchar2 (40) := '&user_schema';

type t_names is table of all_tables.TABLE_NAME%type;
all_table_names t_names;


if user_input = 'w' then

user_input := '''WMSYS''';
select table_name bulk collect into all_table_names from all_tables where owner = user_input;

user_input := '''SYSTEM''';
select table_name bulk collect into all_table_names from all_tables where owner = user_input;

end if;

# 2  
Old 02-22-2008
You need to read the Oracle docset, what you are asking is more than we can do in the forum:
1. Learn what %ROWTYPE is for
2. Learn about declaring a cursor
3. declare two ROWTYPE variables, one for each cursor (SELECT statement)
4. Call UTL_FILE to open an output file
5. open a cursor, fetch with a FOR LOOP into the rowtype you created, then format the ouput,
use UTL_FILE to write to the file
6 Repeat for the other cursor
7. UTL_FILE close the open file
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Help writing SQL query

Hello All, I hope I'm posting this in the right section. I have zero sql query writing skill, in fact, I've never done it before, but for some reason, a request came across my desk to get information from one of our databases. I have about 200 ticket numbers that have no information attached,... (8 Replies)
Discussion started by: bbbngowc
8 Replies

2. Shell Programming and Scripting

Column not allowed, when I am writing sql in UNIX

Please advice to rectify below error #!/bin/ksh X=$(sqlplus -s user/pass << EOSQL set serveroutput on; set heading off feedback off serveroutput on trimout on pagesize 0 INSERT INTO TEST(df) VALUES('a'); COMMIT; EXIT; EOSQL) echo $X echo $? ERROR at line 2: ORA-00984: column not... (1 Reply)
Discussion started by: mirwasim
1 Replies

3. Shell Programming and Scripting

awk - writing matching pattern to a new file and deleting it from the current file

Hello , I have comma delimited file with over 20 fileds that i need to do some validations on. I have to check if certain fields are null and then write the line containing the null field into a new file and then delete the line from the current file. Can someone tell me how i could go... (2 Replies)
Discussion started by: goddevil
2 Replies

4. Shell Programming and Scripting

Searching for Log / Bad file and Reading and writing to a flat file

Need to develop a unix shell script for the below requirement and I need your assistance: 1) search for file.log and file.bad file in a directory and read them 2) pull out "Load_Start_Time", "Data_File_Name", "Error_Type" from log file 4) concatinate each row from bad file as... (3 Replies)
Discussion started by: mlpathir
3 Replies

5. Shell Programming and Scripting

To create a file writing a SQL into it from another file

Hi, I have a file in which I have database information along with 1 SELECT statement. Only 1 statement would be there. I want to grep this SELECT STATEMENT only and write into a separate file. Input File format: Database_Name:<database> Schema_Name:<schema> Table_Name:<table> Select *... (3 Replies)
Discussion started by: ustechie
3 Replies

6. Shell Programming and Scripting

Writing sql results to file using ksh -nevermind

I'm having problems with writing my sql results to a file: sqlplus -S username/password@DB <<!! set echo off set verify off set showmode off set feedback off set timing off set linesize 250 set wrap off set pagesize 0 set newpage none set tab off set trimspool on set colsep... (1 Reply)
Discussion started by: avillanueva
1 Replies

7. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

8. Shell Programming and Scripting

writing the output of SQL into one file

Hi All, Please help me writing the below script. I have two sql queries. 1. Select count(1),Client_id from TABLE_A group by Client_id; 2. Select count(1),Client_id from TABLE_B group by Client_id; I need the output of above two sql queries in a single file. The output 2nd query should be... (4 Replies)
Discussion started by: 46019
4 Replies

9. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

10. Shell Programming and Scripting

conditional writing of sql code

Hello again... I have a request from another department to list for them all the columns and tables we use in this certain database. I have spooled the oracle stored procedured into 1 file. I need a way to write out parts of that file. The criteria is to to start the block to be written when... (0 Replies)
Discussion started by: kburrows
0 Replies
Login or Register to Ask a Question