• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1946
  • Last Modified:

SQL Plus script

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
Mark_1976
Asked:
Mark_1976
1 Solution
 
sujit_kumarCommented:
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
 
Mark_1976Author Commented:
Hi Sujit. So what do I need to do to the code to get this to work?
0
 
sujit_kumarCommented:
0
 
anand_2000vCommented:
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
 
sathyagiriCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now