column not allowed here error in oracle SQL insert
You don't need dynamic SQL:
CREATE OR REPLACE PROCEDURE consolidate_SI(primary_SI IN NUMBER, secondary_SI IN NUMBER) IS
v_primary_si NUMBER;
v_secondary_si NUMBER;
BEGIN
v_primary_si := primary_si;
insert into error_log ( identifier, error_message) values ('Successfully updated',v_primary_si);
commit;
END;
Besides, the issue is in the way you reference the variable v_primary_si in your dynamic SQL.
ORA-00984: column not allowed here while inserting data excluding the sequence trigger
Error- Column not allowed here.
You also need to use ' single quotes for string literals (double quotes are used to signify case-sensitive identifiers, such as column names).
Error- Not enough Values.
You have 5 columns but only 4 values in the INSERT statement and you haven't told the SQL parser which 4 columns you want to use. Give the column names.
So your statement should be:
insert into mca (name,password,email,country)
values ('Ankit', 'Ankit123', 'ankit@gmail.com','India');
(Don't store the password as plain text. At the very least, store a one-way hash of it.)
CREATE TABLE mca (
id NUMBER
-- NOT NULL Not necessary for a primary key
CONSTRAINT mca__id__pk PRIMARY KEY,
name varchar2(200) not null,
password_hash varchar2(200) not null,
password_salt varchar2(200) not null,
email varchar2(200) not null,
country varchar2(200) not null
);
CREATE SEQUENCE mca_id_seq;
And the triggers:
CREATE TRIGGER mca_id
BEFORE INSERT ON MCA
FOR EACH ROW
BEGIN
:new.ID := mca_id_seq.NEXTVAL;
END;
/
and:
CREATE TRIGGER mca_hash_and_salt_password
BEFORE INSERT OR UPDATE ON MCA
FOR EACH ROW
BEGIN
IF :new.PASSWORD_HASH = :old.PASSWORD_HASH THEN
-- Assume things haven't changed (The chances of a hash collision are vanishingly small).
-- Make sure the old salt is not replaced if the password hash hasn't changed.
:new.PASSWORD_SALT := :old.PASSWORD_SALT;
ELSE
-- Regenerate a new salt and hash the password.
:new.PASSWORD_SALT := DBMS_RANDOM.STRING( 'P', FLOOR( DBMS_RANDOM.VALUE( 40, 61 ) ) );
SELECT STANDARD_HASH ( :new.PASSWORD_SALT || :new.PASSWORD_HASH, 'SHA512' )
INTO :new.PASSWORD_HASH
FROM DUAL;
END IF;
END;
/
Then:
insert into mca (
name,
password_hash,
email,
country
) values (
'Ankit',
'Ankit123',
'ankit@gmail.com',
'India'
);
Gives the value in the table:
ID | NAME | EMAIL | COUNTRY | PASSWORD_SALT | PASSWORD_HASH
-: | :---- | :-------------- | :------ | :------------------------------------------------------ | :-------------------------------------------------------------------------------------------------------------------------------
1 | Ankit | ankit@gmail.com | India | &!WAMmJkSpQgUD(BS~ub+2*Yk]]bT_IA* xm|:.[oE\z.)*u*HAEV*B | 82CF5AE586605968DA320A64E7DDC7154FD11BEF0E0680350CA9BF5D5BEEB65D8D05FF50B8DC061E698A94FDAED46A73BAD826303C90AB49352E869931DCF04E
db<>fiddle here
Column not allowed here error in INSERT statement-ORA-00984
Update your insert statement as -
BEGIN
INSERT INTO login(login_id, username , password )VALUES (1,'Sukre','1234');
INSERT INTO login(login_id, username , password ) VALUES (2,'Pal','123444');
END;
/
SQL Error: ORA-00984: column not allowed here
The Column SOURCE is a type of varchar2 but you are providing an integer
Error(13,61): PL/SQL: ORA-00984: column not allowed here IN PROCEDURE WHEN PASS IN PARAMETER
Your issue is that you have a parameter called COMMISSIONS but in your insert statement, you are passing in a value of COMMS.
Also, make sure your parameters have different names to the columns you're trying to insert into; this is (IMO) good practice across any PL/SQL code you're writing, as you can have unexpected results when name collisions happen.
Change your insert statement to use the right parameter name (or, alternatively, change the name of the parameter to match your insert statement) and it should start working.
One point, though: it is bad practice to not list the columns you are inserting into. In real production code, if someone added a column to the table, your code would break.
Your insert statement would be better written as:
insert into <table> (<list of columns to be inserted into>)
values (<list of values to insert into those columns>);
INSERT INTO gets Oracle SQL Developer Error - column not allowed here
You have to actually provide some title, e.g.
SQL> INSERT INTO movie (uniqueid,title) VALUES (SEQ_UNIQUEID.nextval,'Godzilla');
1 row created.
SQL>
[EDIT]
I created all tables whose statements you posted, and INSERT still works; therefore, you did something wrong, but I can't tell what as you didn't posted that piece of code.
SQL> CREATE TABLE CUSTOMERS
2 (
3 CustomerID NUMERIC (10) NOT NULL,
4 LastName VARCHAR (255) NOT NULL,
5 FirstName VARCHAR (255) NOT NULL,
6 MI CHAR,
7 PrimaryPhone# NUMERIC (10),
8 DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
9 PRIMARY KEY (CustomerID)
10 );
Table created.
SQL>
SQL> CREATE SEQUENCE seq_CustomerID MINVALUE 1
2 START WITH 1
3 INCREMENT BY 1
4 CACHE 10;
Sequence created.
SQL>
SQL> CREATE TABLE MOVIE
2 (
3 UniqueID NUMERIC (10) NOT NULL,
4 Title VARCHAR (255) NOT NULL,
5 ReleaseDate DATE,
6 Genres VARCHAR (255)
7 CHECK
8 (Genres IN ('action',
9 'comedy',
10 'drama',
11 'fantasy',
12 'horror',
13 'mystery',
14 'romance',
15 'thriller',
16 'western')),
17 MovieLength INT,
18 Rating VARCHAR (255),
19 Distributor VARCHAR (255),
20 DistributorID INT,
21 WholesalePrice DECIMAL (10, 2),
22 Serial# VARCHAR (255),
23 Actors VARCHAR (255),
24 Producers VARCHAR (255),
25 Directors VARCHAR (255),
26 Awards VARCHAR (255)
27 CHECK
28 (Awards IN ('ACTOR IN A LEADING ROLE',
29 'ACTOR IN A SUPPORTING ROLE',
30 'ACTRESS IN A LEADING ROLE',
31 'ACTRESS IN A SUPPORTING ROLE',
32 'ANIMATED FEATURE FILM',
33 'CINEMATOGRAPHY',
34 'COSTUME DESIGN',
35 'DIRECTING',
36 'FILM EDITING',
37 'MAKEUP AND HAIRSTYLING',
38 'MUSIC',
39 'BEST PICTURE',
40 'WRITING')),
41 StatusID INT,
42 MediaType VARCHAR (255) CHECK (MediaType IN ('DVD', 'VIDEO')),
43 PRIMARY KEY (UniqueID)
44 );
Table created.
SQL>
SQL> CREATE SEQUENCE seq_UniqueID MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
Sequence created.
SQL>
SQL> CREATE TABLE RENTAL
2 (
3 Transaction# INT NOT NULL,
4 Movies VARCHAR (255) NOT NULL, /*comma delineated list of movie id's*/
5 CustomerID NUMERIC,
6 DateAdded TIMESTAMP,
7 PRIMARY KEY (Transaction#),
8 FOREIGN KEY (CustomerID) REFERENCES CUSTOMERS (CustomerID)
9 );
Table created.
SQL>
SQL> CREATE TABLE STATUS
2 (
3 MovieID NUMERIC (10),
4 Rented CHAR CHECK (Rented IN ('y', 'n')),
5 RentalLength NUMERIC (2),
6 ReturnDate DATE,
7 Late CHAR CHECK (Late IN ('y', 'n')),
8 Rewound CHAR CHECK (Rewound IN ('y', 'n')),
9 Damaged CHAR CHECK (Damaged IN ('y', 'n')),
10 TotalFee DECIMAL (10, 2), /*add derived attribute expression*/
11 LastTransaction# INT,
12 FOREIGN KEY (LastTransaction#) REFERENCES RENTAL (Transaction#),
13 FOREIGN KEY (MovieID) REFERENCES MOVIE (UniqueID)
14 );
Table created.
SQL>
SQL> CREATE TABLE FEES
2 (
3 Late DECIMAL (10, 2),
4 StandardFee DECIMAL (10, 2),
5 Damaged DECIMAL (10, 2),
6 Rewind DECIMAL (10, 2),
7 Tax DECIMAL (10, 2)
8 );
Table created.
SQL>
Insert:
SQL> INSERT INTO movie (uniqueid, title)
2 VALUES (SEQ_UNIQUEID.NEXTVAL, 'Godzilla');
1 row created.
SQL>
Related Topics
Row-Level Trigger VS Statement-Level Trigger
Show All Rows That Have Certain Columns Duplicated
Order by Items Must Appear in the Select List If Select Distinct Is Specified
Optional Arguments in Where Clause
The Alter Table Statement Conflicted with the Foreign Key Constraint
Psql Invalid Command \N While Restore SQL
Sql: Select Rows with a Column Value That Occurs at Least N Times
How to Aggregate Over Rolling Time Window with Groups in Spark
How to Find Out What Is Locking My Tables
Sqlite Order by Date1530019888000
"Order By" Using a Parameter for the Column Name
Pseudo_Encrypt() Function in Plpgsql That Takes Bigint
Converting Select Results into Insert Script - SQL Server