oracle query giving error

Question

0.00/5 (No votes)

See more:

I have declare one varaible i.e. srID
then passed value to it from select query then i am trying to pass this variable value insert
in insert statment but it giving me following error:

PL/SQL: ORA-00984: column not allowed here

below is my query---

SQL

declare srID Number;
        begin
        select max(ID)+1 as ID  into srID from table1;
        end;

         INSERT INTO table2 (ID)
         VALUES(srID)

Posted 30-Jul-13 2:24am

Pro86

Updated 30-Jul-13 2:44am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-30T02:47:00

Solution 1

Have a look here: http://www.dba-oracle.com/t_ora_00984_column_not_allowed.htm[^] and try your code without AS ID part:

SQL

declare srID Number;
BEGIN
    SELECT ID INTO srID
    FROM (
        SELECT MAX(ID)+1 AS ID
        FROM table1
        ) AS T;
END;

 INSERT INTO table2 (ID)
 VALUES(srID)

Second way:

SQL

INSERT INTO table2 (ID)
SELECT MAX(ID)+1 AS ID
FROM table1

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-select.html[^]

Posted 30-Jul-13 2:47am

Maciej Los

Updated 30-Jul-13 3:22am

v4

Comments

Pro86 30-Jul-13 8:53am

not work for me still giving same error

Maciej Los 30-Jul-13 9:02am

See my unswer after update ;)

Pro86 30-Jul-13 9:11am

this is right but actually i want to use same value in many insert statement
thats why i am trying to store in varible so i can use it in many time

Maciej Los 30-Jul-13 9:17am

See my answer now ;)

Maciej Los 30-Jul-13 9:27am

Please see note in here: http://psoug.org/definition/INTO.htm[^]

Pro86 30-Jul-13 9:41am

thanks

Maciej Los 30-Jul-13 9:47am

You're welcome.
If my answer was helpfull, please, mark it as an answer (green button) and up-vote ;)

Espen Harlinn · Answer 2 · 2013-07-30T03:45:00

Solution 2

You should use a sequence to generate keys for your table, perhaps something like this:

Create the sequence:

CREATE SEQUENCE mySequence
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Use the sequence to generate unique primary keys for the table:

INSERT INTO myTable (id, name)
VALUES (mySequence.nextval, 'Oh my ;-)');

Best regards
Espen Harlinn

Posted 30-Jul-13 3:45am

Espen Harlinn

Comments

Jörgen Andersson 31-Jul-13 17:16pm

To paraphrase Mycroft: this is the solution, not the answer.

Espen Harlinn 31-Jul-13 17:23pm

Thank you, Jörgen - and yes you're quite right ;-)