CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
SQL developer
SQL developer

Reputation: 11

Getting invalid identifier ORA-00904

I have a procedure

Create or replace procedure schoolstudents(
    LS1 OUT NUMBER,
    LS2 OUT NUMBER,
    val in VARCHAR2)
IS
 val1 VARCHAR2(128);
 schoolId NUMBER;

BEGIN
    val11 := val;
    schoolId := function(val1);
    insert := 'Insert into sname values("a","b") RETURNING '||schoolId||' into :0, '||schoolId||' into :1';
   Execute immediate insert USING OUT LS1,OUT LS2;

   COMMIT;

I am getting an error

ORA-00933 : SQL command not properly ended.

Upvotes: 0

Views: 370

Answers (3)

MT0
MT0

Reputation: 168720

  • You need an END; statement to complete the PL/SQL block.
  • You have not declared the insert variable and insert is a keyword and you should not use it as a variable name.
  • String literals need to be enclosed in single quotes (rather than double quotes).
  • The syntax for RETURNING INTO is RETURNING column1, column2 INTO :1, :2.

If you have the setup:

CREATE TABLE sname (
  col1 VARCHAR2(20),
  col2 VARCHAR2(20)
);

CREATE FUNCTION function( tablename IN VARCHAR2) RETURN NUMBER
IS
BEGIN
  RETURN 1;
END;
/

Then your procedure would be:

Create or replace procedure schoolstudents(
    LS1 OUT NUMBER,
    LS2 OUT NUMBER,
    tablename in VARCHAR2)
IS
 lname     VARCHAR2(128);
 schoolId  NUMBER;
 insert_sql VARCHAR2(200);
BEGIN
  lname := tablename;
  schoolId := function(lname);
  insert_sql := 'Insert into sname values(''a'',''b'') RETURNING '||schoolId||', '||schoolId||' into :1, :2';
  EXECUTE IMMEDIATE insert_sql RETURNING INTO LS1, LS2;

  COMMIT;
END;
/

However, you can rewrite your procedure without the dynamic SQl as:

Create or replace procedure schoolstudents(
    LS1 OUT NUMBER,
    LS2 OUT NUMBER,
    tablename in VARCHAR2)
IS
 schoolId NUMBER;
BEGIN
    schoolId := function(tablename);
    Insert into sname values('a','b');
    LS1 := schoolId;
    LS2 := schoolId;

   COMMIT;
END;
/

db<>fiddle here

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

As others have pointed out there are quite a few problems with this code. What no one pointed out is that there doesn't appear to be any reason to use an EXECUTE IMMEDIATE here. I suggest:

Create or replace procedure schoolstudents(LS1 OUT NUMBER,
                                           LS2 OUT NUMBER,
                                           tablename in VARCHAR2)
IS
  schoolId  NUMBER;
BEGIN
  schoolId := some_function(tablename);

  Insert into sname values('a', 'b');

  COMMIT;

  LS1 := schoolId;
  LS2 := schoolID;
END schoolstudents;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

Quite a few errors. When fixed, it compiles (can't tell will it do what you planned).

  • you used insert variable, but never declared it. Anyway, you have to change its name, insert is for inserting
  • function(lname) is what, exactly? You can't name a function function (similarly to insert I mentioned previously). I have no idea what it is so I commented it out
  • don't use double, but single quotes for strings. However, in dynamic SQL, you have to escape them (by doubling them), or - simpler - use the q-quoting mechanism

Finally:

SQL> CREATE OR replace PROCEDURE schoolstudents(
  2    ls1        OUT  NUMBER,
  3    ls2        OUT  NUMBER,
  4    tablename  IN   VARCHAR2
  5  )IS
  6    lname VARCHAR2(128);
  7    schoolid NUMBER;
  8    l_insert varchar2(200);                             --> not INSERT, it is reserved
  9  BEGIN
 10      lname := tablename;
 11      schoolId := lname; --function(lname);             --> what is FUNCTION?
 12      l_insert := q'[Insert into sname values('a','b')  --> q-mecanism; single, not double quotes
 13        RETURNING ]'||schoolId||' into :0, '||schoolId||' into :1';
 14     Execute immediate l_insert
 15       USING OUT ls1, OUT ls2;
 16    COMMIT;
 17  END;
 18  /

Procedure created.

SQL>

Upvotes: 1

Related Questions