CODEWITHSUNDEEP
databaseoracle-databasesqlplus
Stc5097
Stc5097

Reputation: 289

SQL script to get information to add to the DB

I am trying to create an swl script that will prompt the user for information that will be added to the database. I am prompting the user for a date and a store name. After the user enters the store name I want to run a query that will return a code associated with that store name and assign it to a variable. When I run this script, after the user enters the store name, this is what happens: 

Enter the date of the expense:01-01-13
Enter the store name for which the expense was against:MetEd
  12

I am not sure what I am doing wrong as I am new to writing SQL scripts. Below is my script file I appreciate any help/tips. Thank you. P.S., I amusing sql plus / oracle. 

ACCEPT EDate PROMPT 'Enter the date of the expense:';
ACCEPT StoreName PROMPT 'Enter the store name for which the expense was against:';

DECLARE
    v_StoreCode VARCHAR;
BEGIN
    SELECT CODE
    INTO v_StoreCode
    FROM STORE
    WHERE STORENAME = '&StoreName';
END;

INSERT INTO EXPMAST (ExpNum, EDate, StoreCode)
   VALUES(seq_EXPMAST.nextval, to_date('&EDate','mm-dd-yy'), 'v_StoreCode');

Upvotes: 0

Views: 28

Answers (1)

APC
APC

Reputation: 146239

The 12 is a line number, because you're being prompted for input. SQL*Plus expects a / on a new line to execute an anonymous block. Your script hasn't got that. Hence the prompt.

You have other problems. I presume you want to insert the retrieved value of CODE but right now you're inserting a string 'v_StoreCode'. So you need to remove those single quotes.

ACCEPT EDate PROMPT 'Enter the date of the expense:';
ACCEPT StoreName PROMPT 'Enter the store name for which the expense was against:';

DECLARE
    v_StoreCode VARCHAR;
BEGIN
    SELECT CODE
    INTO v_StoreCode
    FROM STORE
    WHERE STORENAME = '&StoreName';
END;
/

INSERT INTO EXPMAST (ExpNum, EDate, StoreCode)
   VALUES(seq_EXPMAST.nextval, to_date('&EDate','mm-dd-yy'), v_StoreCode);

In addition you could tidy up the code by using the INSERT ... SELECT construct:

ACCEPT EDate PROMPT 'Enter the date of the expense:';
ACCEPT StoreName PROMPT 'Enter the store name for which the expense was against:';

INSERT INTO EXPMAST (ExpNum, EDate, StoreCode)
   SELECT seq_EXPMAST.nextval, to_date('&EDate','mm-dd-yy'), CODE
   FROM STORE
    WHERE STORENAME = '&StoreName';

Upvotes: 1

Related Questions