PL/SQL Insert Scripts with Variables not working

Question

I'm new to PL/SQL and now I'm working on my insert scripts. They are dealing with variables, fetching from select into statements.

And I tried so hard! But I did not get it work. Every fixed error brings another error. But I compared and copied out from online examples and it SHOULD work.

Here's the script:

/* Inserts */
VARIABLE v_nutzer_gustav NUMBER(5);
VARIABLE v_artikelid NUMBER(5);
VARIABLE v_gustavspaypal VARCHAR(80);
DECLARE
    v_nutzer_gustav nutzer.id%TYPE;
    v_artikelid artikel.id%TYPE;
    v_gustavspaypal zahlungsdaten.id%TYPE;
BEGIN
    INSERT INTO artikel (name, interpret, genre, erscheinungsjahr, typ, preis, bestand) VALUES ('Dark Side Of The Moon', 'Pink Floyd', 'Rock', 1978, 5, 9.99, 5);
    INSERT INTO nutzer (login, passwort, vorname, nachname) VALUES ('footballfan2010', 'geheim123', 'Peter', 'Pfennig');
    INSERT INTO nutzer (login, passwort, vorname, nachname) VALUES ('t.maier', 'agrar5$', 'Thorsten', 'Maier');
    INSERT INTO nutzer (login, passwort, vorname, nachname) VALUES ('KingGustav', 'gustav1997', 'Gustav', 'Gauner');
    SELECT id INTO v_nutzer_gustav FROM nutzer WHERE login = 'KingGustav';

    UPDATE nutzer SET stammkunde = 'Y' WHERE id = v_nutzer_gustav;
    /* Stammkunde tätigt Kauf */
    SELECT id INTO v_artikelid FROM artikel WHERE name = 'Dark Side Of The Moon' AND interpret = 'Pink Floyd';
    INSERT INTO bestellungen (nutzer_id, artikel_id) VALUES (:v_nutzer_gustav, :v_artikelid);
    INSERT INTO zahlungsdaten (nutzer_id, art, details) VALUES (:v_nutzer_gustav, 'PayPal', '[email protected]');
    SELECT id INTO v_gustavspaypal FROM zahlungsdaten WHERE nutzer_id = :v_nutzer_gustav AND art = 'PayPal';
    INSERT INTO rechnungen (zahlungsdaten_id, betrag) VALUES (:v_gustavspaypal, GET_PRICE(:v_artikelid, :v_nutzer_gustav));
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('No record avialable');
  WHEN too_many_rows THEN
   dbms_output.put_line('Too many rows');
END;
/

And when I play:

Verwendung: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ] 
Verwendung: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ] 
Verwendung: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ] 
SP2-0552: Bind-Variable "V_GUSTAVSPAYPAL" nicht deklariert.

There must be many errors, because when I remove the v_gustavspaypal line, I still get error after error. I believe the variables from the SELECT INTOs get NULL, which they shouldn't.

Can someone please correct :(

i think you do not need use :(colon) with variables. this DML are not dynamic. Example: INSERT INTO rechnungen (zahlungsdaten_id, betrag) VALUES (v_gustavspaypal, GET_PRICE(v_artikelid, v_nutzer_gustav)); — hekko
– hekko, Commented Feb 11, 2018 at 3:25

Kaushik Nayak · Accepted Answer · 2018-02-11 06:20:40Z

1

It is not clear why do you need bind variables here. You are selecting the values into equivalent PL/SQL variables and using them in your DMLs.

As it is clearly shown in the error messages,

you cannot set size for NUMBER bind variables
VARCHAR is not supported as a bind variable, you may use VARCHAR2 instead (optionally with size ).

So, if you want to use these variables, change it to

VARIABLE v_nutzer_gustav NUMBER
VARIABLE v_artikelid NUMBER
VARIABLE v_gustavspaypal VARCHAR2(80)

you said,

There must be many errors, because when I remove the v_gustavspaypal line, I still get error after error.

Please post the error messages or try to figure out why they should be occurring.More often than not when you do a simple google search with the appropriate errors, it is enough to find the required solution quickly.

answered Feb 11, 2018 at 6:20

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

William Robertson · Accepted Answer · 2018-02-11 10:00:19Z

You have three host variables with the same names as three PL/SQL variables, which is confusing and it doesn't seem to be necessary. There are several select into constructions which look as though they are just capturing a generated key value and so could be replaced with returning into, which would also remove the need for the exception handlers.

I think you just need something like this (untested as I don't have your tables):

declare
    v_nutzer_gustav nutzer.id%type;
    v_artikelid artikel.id%type;
    v_gustavspaypal zahlungsdaten.id%type;
begin
    insert into artikel (name, interpret, genre, erscheinungsjahr, typ, preis, bestand) values ('Dark Side Of The Moon', 'Pink Floyd', 'Rock', 1978, 5, 9.99, 5)
    returning id into v_artikelid;

    insert into nutzer (login, passwort, vorname, nachname) values ('footballfan2010', 'geheim123', 'Peter', 'Pfennig');

    insert into nutzer (login, passwort, vorname, nachname) values ('t.maier', 'agrar5$', 'Thorsten', 'Maier');

    insert into nutzer (login, passwort, vorname, nachname) values ('KingGustav', 'gustav1997', 'Gustav', 'Gauner')
    returning id into v_nutzer_gustav;

    update nutzer set stammkunde = 'Y' where id = v_nutzer_gustav;

    /* stammkunde tätigt kauf */
    insert into bestellungen (nutzer_id, artikel_id) values (v_nutzer_gustav, v_artikelid);

    insert into zahlungsdaten (nutzer_id, art, details) values (v_nutzer_gustav, 'PayPal', '[email protected]')
    returning id into v_gustavspaypal;

    insert into rechnungen (zahlungsdaten_id, betrag) values (v_gustavspaypal, get_price(v_artikelid, v_nutzer_gustav));
end;

thanks, i didn't know about that returing thing. it works finally :D

Collectives™ on Stack Overflow

PL/SQL Insert Scripts with Variables not working

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related