Forum Stats

  • 3,782,585 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Execute Procedure Error

2980049
2980049 Member Posts: 22
edited Jul 15, 2015 5:30AM in SQL & PL/SQL

Hello there

I have a DB with a table PRODOTTO (products) and some specialization like SMARTPHONE etc. I need to insert a product like a new smartphone using only 1 procedure NEW_PROD

because of the specialization I create a view SPECIALIZZAZIONE joining all the spec tables then I create the procedure NEW_PROD and at the end a trigger instead of

CREATE OR REPLACE PROCEDURE NEW_PROD(

NOME1 VARCHAR2, VAL1 VARCHAR2,

NOME2 VARCHAR2, VAL2 VARCHAR2,

NOME3 VARCHAR2, VAL3 VARCHAR2,

NOME4 VARCHAR2, VAL4 VARCHAR2,

NOME5 VARCHAR2, VAL5 VARCHAR2,

NOME6 VARCHAR2, VAL6 VARCHAR2,

NOME7 VARCHAR2 DEFAULT NULL, VAL7 VARCHAR2 DEFAULT NULL,

NOME8 VARCHAR2 DEFAULT NULL, VAL8 VARCHAR2 DEFAULT NULL,

NOME9 VARCHAR2 DEFAULT NULL, VAL9 VARCHAR2 DEFAULT NULL,

NOME10 VARCHAR2 DEFAULT NULL, VAL10 VARCHAR2 DEFAULT NULL,

NOME11 VARCHAR2 DEFAULT NULL, VAL11 VARCHAR2 DEFAULT NULL,

NOME12 VARCHAR2 DEFAULT NULL, VAL12 VARCHAR2 DEFAULT NULL,

NOME13 VARCHAR2 DEFAULT NULL, VAL13 VARCHAR2 DEFAULT NULL,

NOME14 VARCHAR2 DEFAULT NULL, VAL14 VARCHAR2 DEFAULT NULL,

NOME15 VARCHAR2 DEFAULT NULL, VAL15 VARCHAR2 DEFAULT NULL,

NOME16 VARCHAR2 DEFAULT NULL, VAL16 VARCHAR2 DEFAULT NULL,

NOME17 VARCHAR2 DEFAULT NULL, VAL17 VARCHAR2 DEFAULT NULL,

NOME18 VARCHAR2 DEFAULT NULL, VAL18 VARCHAR2 DEFAULT NULL,

NOME19 VARCHAR2 DEFAULT NULL, VAL19 VARCHAR2 DEFAULT NULL,

NOME20 VARCHAR2 DEFAULT NULL, VAL20 VARCHAR2 DEFAULT NULL)

IS

SQLSTM VARCHAR2(400);

BEGIN

SQLSTM := 'INSERT INTO SPECIALIZZAZIONE('|| NOME1 ||','||NOME2||','||NOME3||','||NOME4||','||NOME5||','||NOME6||','||NOME7||','||NOME8||','||NOME9||','||NOME10||','||NOME11||','||NOME12||','||NOME13||','||NOME14||','||NOME15||','||NOME16||','||NOME17||','||NOME18||','||NOME19||','||NOME20||')

VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14; :15, :16, :17, :18, :19, :20)';

EXECUTE IMMEDIATE SQLSTM USING VAL1, VAL2, VAL3, VAL4, VAL5, VAL6, VAL7, VAL8, VAL9, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20;

END;

NOME20 AND VAL20 because when I insert a product I have to enter a maximum of 20 values

the procedure complies correctly but when I execute got this error:

EXECUTE NEW_PROD('BARCODE','1234567890','MODELLO','KINGSTON G4 DATATRAVELER 32GB','PRODUTTORE','KINGSTON','GARANZIA','IT','COLORE','BLACK','PREZZO_LIST','22','VEL_TRASF','400MBS','MEMSIZE','32GB');

ERROR AT LINE 1:

ORA-01747:  invalid user.table.column, table.column, or column specification

ORA-06512: at "PROVA2.NEW_PROD", line 27

ORA-06512: at line 1

line 27 is the EXECUTE IMMEDIATE...

what can I do?

Stockisti_rel.jpeg

Tagged:

Answers

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Jul 14, 2015 6:03PM

    Where is the table SPECIALIZZAZIONE in your ER-Model?

    bye

    TPD

  • 2980049
    2980049 Member Posts: 22
    edited Jul 14, 2015 6:06PM

    CREATE VIEW SPECIALIZZAZIONE AS

    SELECT * FROM (PRODOTTO NATURAL JOIN STORAGE NATURAL JOIN TELEVISORE NATURAL JOIN ACCESSORI NATURAL JOIN TEL_MOB NATURAL JOIN CELLULARE NATURAL JOIN TERM_MOB NATURAL JOIN SMARTPHONE NATURAL JOIN TABLET NATURAL JOIN SMARTWATCH);

    it's a view, I have to create a trigger "instead of"  for the view

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    edited Jul 14, 2015 6:40PM

    Why do you want to work against a view from  within PL/SQL?

    View have their use for hiding the real data structure to  the outside world, but what problem are you solving here?

    Why do you use dynmic SQL within your stored procedure?

    bye

    TPD

  • 2980049
    2980049 Member Posts: 22
    edited Jul 14, 2015 6:47PM

    it's a school project, what can I do to enter different products using a single procedure?

  • Unknown
    edited Jul 14, 2015 7:22PM
    it's a school project
    
    

    Really? Someone actually teaches a class on how to write the worst possible PL/SQL code? Now I've heard everything.

    Your 'execute' statement appears to have an embedded linefeed (or carriage return/linefeed pair) in it and whatever client tool you are using to enter the statement probably doesn't like that and just chopped it off.

    Post an EXACT copy of a sql*plus session that shows the code being executed using SHORT, SIMPLE values.

    You are also violating one of the cardinal rules for working with dynamic SQL: Use DBMS_OUTPUT to print out the actual query that was constructed so you can inspect it to make sure it has the proper syntax, doesn't contain any errors and can execute it manually to make sure it works correctly.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,623 Red Diamond
    edited Jul 15, 2015 5:30AM

    Why dynamic SQL?  Why don't you know the names of the columns you are inserting into?

    Part of good database design is that you know your tables and you know your columns, and your code is modularized so that it performs a specific task with known inputs, known outputs, and a known process.

    In case you hadn't guessed yet, the key here is that good design means you "know" what you're doing.

    Dynamic SQL is a clear indication (in 99.999% of cases) that you don't know what you're doing (and I don't mean that offensively).

    What's wrong with using static SQL?

    If you're inserting a new record, just insert the record with all the columns and all the necessary values.

    Your procedure should have known parameters for each element of data being passed in (or even better a known structure so you just pass a single structured variable with all the necessary data)

    Just having parameters such as NOME1, VAL1, NOME2, VAL2 etc. smacks of very poor implementation.

This discussion has been closed.