| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Programming problem
On Wed, 26 Feb 1997 13:47:05 -0330, Silvercup <dpierce_at_morgan.ucs.mun.ca> wrote:
>I am trying to write a PL/SQL program that does the following:
>
> 1) accepts user input
see the accept command in sql*plus..... for example:
accept x prompt 'Enter a value for x: '
> 2) does validity checks on the user input
send &x to a pl/sql routine
> 3) depending on a particular field the user enters, I want
> to run a specific query:
> For example:
> if the user enters a record type 'C' then
> run select query1
> else
> run select query2
If statement....
> 4) output the selected records to a user specified file
>
spool
> Does anyone out there know how I can do this? I can't seem to
>get any help from the Oracle SQL*Plus documentation.
>
Heres a full example, run as scott/tiger. It'll prompt for input, validate it (must be in a,c) and run a query, capturing the output in a file. query will be determined by input....
set serveroutput on
set feedback off
set verify off
set linesize 80
set termout off
spool tmp.sql
begin
if (upper('&x') in ( 'A', 'C' )) then
dbms_output.put_line( 'spool data' );
if ( upper('&x') = 'A' ) then
dbms_output.put_line( 'select * from emp;' );
else
dbms_output.put_line( 'select * from dept;' );
end if;
dbms_output.put_line( 'spool off' );
dbms_output.put_line( 'set termout on' );
dbms_output.put_line( 'prompt your data is in data.lst' );
else
dbms_output.put_line( 'set termout on' );
dbms_output.put_line( 'prompt Input is wrong, try again' );
end if;
> I am running SQL*Plus on a Win 95 PC, that connects to an Oracle
>database stored on a Windows NT server. My version of Oracle is
>Oracle7 version 7.1.3.3.3
>
> If anyone is really familiar with this version of Oracle, and
>you do programming in C/C++ (not OWL), could you tell me how to
>use C/C++ to also solve the above problem (ie. I need to know the
>headers, and libraries that I should use in my C/C++ program).
>
> Any help would be greatly appreciated.
>
>--
>Thanks, | "We live in a society of laws.
>David J. Pierce | Why do you think I took you
>Programmer Consultant | to see all those Police Academy
>Computing And Communications | movies!!!"
>Memorial University Of Newfoundland | - H. Simpson
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |