?
Solved

SQL Plus script

Posted on 2005-03-04
5
Medium Priority
?
1,944 Views
Last Modified: 2013-12-12
Morning all,

I'm trying to run a script in SQL Plus that take an input parameter, validates it, selects a record from the database for that parameter and then asks the user if whether to proceed. At the moment I am getting the following error...

ORA-06550: line 22, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

Here's the code.  I can't see why there's a problem with the Select statement. Can anyone help? Thanks.

set serveroutput on

--Set Verify on for debugging
set Verify on

-- Show user a prompt to enter the relevant ISIN and validate value entered

ACCEPT in_isin      prompt "Enter ISIN Number:"

-- Declare variables

declare
input        varchar2(12);
input2      char(1);


begin

-- Validate the ISIN value entered

input:= '&in_isin';
      
      if not (length(input) = 12) then

              dbms_output.put_line('Account Number must be 12 characters long');

            return;

      end if;


-- Select the relevant values for the ISIN number entered

SELECT ISIN, SECDESCR1 FROM SECURITY WHERE ISIN = '&in_isin';


-- Ask user if it is ok to proceed

ACCEPT in_proceed            prompt "Is it ok to proceed Y/N"

input2:= '&in_proceed';

-- Check that user has entered Y or N. If N then end, else proceed.
      
      if not (input2 = 'Y' or 'N') then

            dbms_output.put_line('Please enter Y or N');

            return;

      else if input 2 = 'N' then;

            end;

      end if;

end;
/
0
Comment
Question by:Mark_1976
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13457462
Whenever you run a sql script in sql*plus, Oracle will ask you to give values to all bind variables (&...) before starting execution. You can't control that. However You can take then as parameters to a proccedure and execute your procedure accordingly.

0
 

Author Comment

by:Mark_1976
ID: 13457493
Hi Sujit. So what do I need to do to the code to get this to work?
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13458176
0
 
LVL 13

Accepted Solution

by:
anand_2000v earned 2000 total points
ID: 13470430
1) This is a PL/SQL code. not just a sql script
2) As the error mentions you are supposed to have a INTO clause

declare
input       varchar2(12);
input2     char(1);
v_isin security.isin%type;
v_secdescr1 security.secdescr1%type;
begin
input:= '&in_isin';
     if not (length(input) = 12) then
raise_application_error(-20121,''Account Number must be 12 characters long');
     end if;
SELECT ISIN, SECDESCR1 into
v_isin, v_secdescr1 FROM SECURITY WHERE ISIN = '&in_isin';

3) An accept does not work within PL/SQL
4) PL/SQL is not meant for run time interaction. so anyhow this program will not work in Oracle
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 13565409
Since this is PL/SQL code with BEGIN and END you will have to use a INTO claise in your SELECT statement
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question