Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to fix this error ORA-00922: missing or invalid option

GadoJan 18 2017 — edited Jan 21 2017

I'm trying to create a  script that generates sequences for each table in my data base.

But it keeps givving me this error:

ORA-00922: missing or invalid option

The code:

DECLARE

V_OWNER_NAME   VARCHAR2(100) := 'HAZARDV2';

V_TABLE_NAME VARCHAR2(500) ;

V_PK_COLUMN  VARCHAR2(500) ;

V_MAX_ID     NUMBER ;

  CURSOR ALL_TABLES IS

    SELECT table_name

            FROM all_tables

            WHERE OWNER = V_OWNER_NAME

            ORDER BY TABLE_NAME ;

BEGIN

  FOR I IN ALL_TABLES LOOP

 

  --STORING THE TABLE NAME RETURNED FROM CURSOR

  V_TABLE_NAME := I.TABLE_NAME;

 

  IF V_TABLE_NAME LIKE 'PS_TXN' THEN

NULL;

ELSE

-- GETTING THE PK COLUMN NAME  

SELECT 'SEQ_'||NVL(column_name,999)

INTO V_PK_COLUMN

FROM all_cons_columns

WHERE

OWNER = V_OWNER_NAME AND

constraint_name = (

  SELECT constraint_name FROM user_constraints

  WHERE TABLE_NAME = V_TABLE_NAME AND

  CONSTRAINT_TYPE = 'P'

);

 

----- THIS IS THE LINE THAT THE ERROR POINTS TO -------

  

  execute immediate

 

  'CREATE OR REPLACE SEQUENCE ' || V_PK_COLUMN || '

START WITH (SELECT NVL(MAX('||V_PK_COLUMN||'),0)+1 FROM '||V_TABLE_NAME||')

INCREMENT BY 1

MAXVALUE 9999999999999999999999999999

MINVALUE 1

NOCACHE

ORDER';

  

  END IF;

  END LOOP;

   

END;

Thank you for your time.

Gado

Comments

513949
Answer
For example:
set serveroutput on

begin

   delete  dept;
   
   dbms_output.put_line(sql%rowcount);
   
end;
/  
Miguel
Marked as Answer by AAlsofyani · Sep 27 2020
32685
No time to google either I guess.
http://www.google.co.uk/search?q=number+of+rows+deleted+oracle&rls=com.microsoft:en-gb&ie=UTF-8&oe=UTF-8&startIndex=&startPage=1&rlz=&redir_esc=&ei=Qi5qToGyGYqw8QOGzf3nAg
SQL> create table dt_del_ex(id number);

Table created.

SQL> set serveroutput on
SQL> BEGIN
  2
  3      INSERT INTO dt_del_ex VALUES(1);
  4
  5      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows inserted');
  6
  7      INSERT INTO dt_del_ex select rownum from dual connect by level <=10;
  8
  9      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows inserted');
 10
 11      UPDATE dt_del_ex SET id = id + 3 WHERE id >= 9;
 12
 13      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows updated');
 14
 15      DELETE FROM dt_del_ex WHERE id <= 10;
 16
 17      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows deleted');
 18
 19  END;
 20  /
1 rows inserted
10 rows inserted
2 rows updated
9 rows deleted

PL/SQL procedure successfully completed.
AAlsofyani
I Do Thanks!
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 18 2017
Added on Jan 18 2017
26 comments
29,769 views