SQL & PL/SQL

EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.

GADO wrote:
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

What version of oracle are you using?

I don't see "OR REPLACE" clause in sequence creation.

Hi,

Whenever you do dynamic SQL, put the entire SQL statement into a string variable, so that, during debugging, it's easy to display the dynamic text before you execute it.  For example:

...
    sql_text := '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';
    dbms_output.put_line (sql_text || ' = sql_text');
    EXECUTE IMMEDIATE  sql_text;
...

That way, if you get an error, you can see exactly what the command was, and test that command separately to make sure that it works.

In this case, you'll find that the command does not work.  Review the syntax of the CREATE SEQUENCE command to find out why.  (CREATE SEQUENCE has no OR REPLACE option, and the START WITH value must be a literal).

Actually, this is just the reverse of what you should do.  You should test the statement outside of PL/SQL first, to make sure you know exactly what the dynamic test should be, and only then should you start writing the PL/SQL code.

Thanks guys for your quick response

I fixed my code as you suggested :

set serveroutput on format wrapped;
DECLARE
V_OWNER_NAME   VARCHAR2(100) := 'HAZARDV2';
V_TABLE_NAME VARCHAR2(1000) ;
V_PK_COLUMN  VARCHAR2(1000) ;
V_MAX_ID     NUMBER ;
SQL_TEXT     VARCHAR2(1000) ;
V_START_WITH 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; 
  
  DBMS_OUTPUT.PUT_LINE('TABLE_NAME ='||V_TABLE_NAME);
  
  IF V_TABLE_NAME LIKE 'PS_TXN' THEN 
NULL;
ELSE
-- GETTING THE PK COLUMN NAME   
SELECT 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'
);
---- Next statement returns error: PL/SQL: ORA-00942: table or view does not exist
SELECT NVL(MAX(V_PK_COLUMN),0)+1 
INTO V_START_WITH
FROM V_TABLE_NAME;
SQL_TEXT := 'CREATE SEQUENCE SEQ_'
                       || V_PK_COLUMN 
                       || ' START WITH '
                       || V_START_WITH
                       ||')INCREMENT BY 1
                       MAXVALUE 9999999999999999999999999999
                       MINVALUE 1
                       NOCACHE
                       ORDER';
  
  DBMS_OUTPUT.PUT_LINE('SQL TEXT ='||SQL_TEXT);
   
  EXECUTE IMMEDIATE SQL_TEXT;
   
  END IF;
  END LOOP;
    
END;

I have anew error now :

PL/SQL: ORA-00942: table or view does not exist

returned by this statement :

SELECT NVL(MAX(V_PK_COLUMN),0)+1

INTO V_START_WITH

FROM V_TABLE_NAME;

I tried this statement outside it runs if i typed the table name literally but it gives this error if the table name is stored in a variable ... why??

Thanks for your time.

Gado

GADO wrote:
I tried this statement outside it runs if i typed the table name literally but it gives this error if the table name is stored in a variable ... why??

Because table name/column name can't be passed as variable to a static SQL statement:

SELECT NVL(MAX(V_PK_COLUMN),0)+1

INTO V_START_WITH

FROM V_TABLE_NAME;

means select column named (literally) V_PK_COLUMN from table named (literally) V_TABLE_NAME. You have to use dynamic SQL.

SY.

I suggest you leave this idea, there are number of problems. Why ?

declare

  v_owner_name varchar2(100) := 'HAZARDV2';

  v_table_name varchar2(1000) ;

  v_pk_column  varchar2(1000) ;

  v_max_id     number ;

  sql_text     varchar2(1000) ;

  v_start_with 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;

    dbms_output.put_line('TABLE_NAME ='||v_table_name);

    if v_table_name like 'PS_TXN' then -- This is equivalent to if v_table_name = 'PS_TXN', you may want if v_table_name like '%PS_TXN%'

      null;

    else

      -- GETTING THE PK COLUMN NAME

     -- What will happen if PK not exists OR more than one cloumn ??

      begin

      select 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'

        );

        exception

         when no_data_found then

          dbms_output.put_line('PK not exists ='||v_table_name);

         when too_many_rows then

          dbms_output.put_line('More than one column in PK ='||v_table_name);

        end; 

      ---- Next statement returns error: PL/SQL: ORA-00942: table or view does not exist

     --This should fix your error.

      -- BUT What will happen if more than one column in PK ? If no OK at all ? If datatype of PK is NOT a number ?

    execute immediate ' select nvl(max(v_pk_column),0)+1

      --into v_start_with

      from '||v_table_name into v_start_with ;

      sql_text := 'CREATE SEQUENCE SEQ_' || v_pk_column || ' START WITH ' || v_start_with ||')INCREMENT BY 1                      

                    MAXVALUE 9999999999999999999999999999                      

                    MINVALUE 1                      

                    NOCACHE                      

                    ORDER';

      dbms_output.put_line('SQL TEXT ='||sql_text);

--      execute immediate sql_text;

    end if;

  end loop;

end;

What are you actually trying to do? From my perspective you are trying to create a sequence for each primary key of tables you can use. Each sequence is set to start with the maximum value of the column you can see in the table right now.

So what happens to the sequence next? What happens to insert statements into the table? Do uncommitted transactions give you cause to worry about your plans? What about transactions that take place between you creating a sequence and you changing your application to use them (assuming that's part of the process)?

GADO wrote:
I have anew error now :
PL/SQL: ORA-00942: table or view does not exist
returned by this statement :
SELECT NVL(MAX(V_PK_COLUMN),0)+1 
INTO V_START_WITH
FROM V_TABLE_NAME;
I tried this statement outside it runs if i typed the table name literally but it gives this error if the table name is stored in a variable ... why??
Thanks for your time.
Gado 

privilege acquire via ROLE do NOT apply within named PL/SQL procedure

It is a Bad Thing to CREATE object via EXECUTE IMMEDIATE

Application objects should be known & static between application version releases.

Well the idea was to use it when i'm setting the application and the DB for the first time.

You know to create all the sequences needed programmaticaly   instead of creating them manually one by one .... but i'm started to think that this is stupid.

Because thanks to @"Saubhik"  answer ... what if the PK is composite???

Which is a question that i just asked here:

I think i'm closing this discussion but would you please join in that one above.

Thanks to you all for your answers.

Gado

do NOT do in PL/SQL that which can be done in plain SQL.

There is NO requirement to have separate SEQUENCE for every Primary Key.

What do you mean?

My knowledge about sequence as PK ... is to create a sequence for each table's PK ... and then from JDeveloper create a trigger to assign that sequence value to the PK column

explain what prevents you from using single/same SEQUENCE for every Primary Key?

why do you require more than 1  SEQUENCE?

John Thorton asks a great question - just use 1 sequence.

However, let me address your approach for creating objects. Normally, I wouldn't do this as you've done in PL/SQL - what I would perhaps do is to use SQL or PL/SQL to "output" the commands I needed (using dbms_output, utl_file, or just a simple select) and spool those commands to a file. Then, I'd review the file, modify it if needed, then run it.

ANY DDL, in my humble opinion, belongs in a file, checked in to some kind of version control system, and should be run in a controlled fashion. If not, that's how we see people coming on to these forums a year or a decade later asking "how the heck do I clean up this mess?"

Yes i'm scratching my head since i read @"John Thorton" 's question ... but for once i guess that after a year of using the same sequence for every single record in all my tables the sequence number will be huge.

And i know that using sequence don't lead to gapless numbering but using one seq the gaps will be huge ... like if i'm defining a user today with id 6 ... tomorrow the next user's id might be 2258.

@"John Stegeman" i don't know if i understood you correctly ... do you mean to use the PL/SQL to print the create sequence command and then run it as sql script or something

like this :

Create sequence SEQ_WARE_ID .........................

Create sequence SEQ_EMP_ID ...................

......

Gado

Yes.

And i know that using sequence don't lead to gapless numbering but using one seq the gaps will be huge ... like if i'm defining a user today with id 6 ... tomorrow the next user's id might be 2258.

So what?

ID numbers are (usually) only used internally within the system and never seen by end users. The database doesn't care how big the numbers get (within reason), so neither should you.

Ok ... This  will destroy all my beliefs now.

ID numbers is not used internally only in my prospective ... Let's say i'm recording an invoice ... and few months later i want to review it ... so i'll search for it using the invoice_id number to print it again.

What other ways is there to do this?

Chris,

I think the Oracle database gets extra tired when there are gaps in the sequence number

GADO wrote:
Ok ... This will destroy all my beliefs now.
ID numbers is not used internally only in my prospective ... Let's say i'm recording an invoice ... and few months later i want to review it ... so i'll search for it using the invoice_id number to print it again.
What other ways is there to do this?

"so i'll search for it using the invoice_id"

Implying you have the invoice_id recorded somewhere, else how do you know what it is to search on it?  So again, what does it matter how big it is or how big the gaps may be? It's just a number.

Of course, the choice of one sequence for all tables or a sequence for each table does not alter the fundamental point, and that is don't be creating these with dynamic SQL.

Regarding your invoice example:  That is actually a good question, which deserves serious answers.

If you are designing a table, like for invoices, and there is a "natural candidate" for a PK, like an invoice number, use that as the PK - you don't need a system-generated sequence to provide a PK for that table. You just need to be 100% confident that the invoice number will never change. (This is an excellent exemple because indeed, in most cases, invoice numbers do not change!) As a counter-example, do not use a "main phone number" as PK for a "companies" table - while it is a unique identifier (perhaps), it MAY change over time, so that is a very poor candidate for a PK.

Although, even with invoice numbers, there may be one problem... what if someone mistypes the invoice number when they insert it in the database? What happens when they correct it? (Meaning, what if there are child tables that reference "this" table by invoice number?) THIS is a good reason to have a system-generated ID for ALL tables, even for an "invoice" table.

With that said: How do you think invoice numbers are generated, anyway? Perhaps by a system-generated sequence, in the database used by the vendor who produced the invoice? And - do you care if there are gaps in invoice numbers? Did THAT ever cause any problems? An "id" like an invoice number or the system-generated sequence values should NOT encode any useful information about your "objects" (if they do, that's a design flaw). In particular, the fact that the id's or PK values are "in order" with "no gaps" should have no meaning.

Cheers,   -   mathguy

And i know that using sequence don't lead to gapless numbering but using one seq the gaps will be huge ... like if i'm defining a user today with id 6 ... tomorrow the next user's id might be 2258.

So what? Who cares if one id is 6 and another is 2258? It makes no difference at all.

If you want to create a sequence for each table then do it the EASY way: use Oracle 12c and define the column as an IDENTITY column.

Oracle will create the sequence for you and even populate it for you.

https://docs.oracle.com/database/121/SQLRF/statements_7002.htm

identity_clause
Use this clause to specify an identity column. The identity column will be assigned an increasing or decreasing integer value from a sequence generator for each subsequent INSERT statement. You can use the identity_options clause to configure the sequence generator.

Julian Dykes article contains several examples

Oracle 12c - Identity Columns

@"mathguy" thank you man for your great post.

It really helped put this whole idea into perspective.

 An "id" like an invoice number or the system-generated sequence values should NOT encode any useful information about your "objects" (if they do, that's a design flaw).

This line confused me a little bit ... you mean that i shouldn't use invoice number or a phone number as an id ... because this makes a lot of sense to me ... i don't know but making the id column inserted by the user is not good ... i always use system generated ids.

Like for example if i purchased something and i'm going to record the invoice i'll use a system generated id for invoice_id ...  but i have a column to store the actual invoice_number ... the one that came from the vendor.

Because i'm not only working with one vendor ... some other vendor in the future might have the same invoice number.

and to make sure that a vendors invoice will not be recorder twice i could have a compound unique constraint for Vendor_id and vendor_invoice_id.

So in conclusion i think one sequence for most of the table starting to become a good idea to me ... I'm saying most because some tables will need an independent sequence for it's own ... because in business like a take away shop ... they are working in shifts ... so they want each shift to have it's own sequence number ... like for the sales receipt to start from 1 each shift.

Gado

GADO wrote:
So in conclusion i think one sequence for most of the table starting to become a good idea to me ... I'm saying most because some tables will need an independent sequence for it's own ... because in business like a take away shop ... they are working in shifts ... so they want each shift to have it's own sequence number ... like for the sales receipt to start from 1 each shift.
Gado

what is gained by having each shift start from 1?

why does it matter to anyone the actual value of the sequence as long as the values are unique.

I think we are coming to the same conclusion: use system generated values for primary keys.

The thing I think you are still questioning has to do with "separate sequence numbers for each shift". You suggest there may be a requirement that each shift in a shop should have its own sequence number. Two questions about that - first, WHY?, and second - does that have to be the PK value? If you need a sequential number that starts from 1 for each shift, why does that have to be the same as the PK used in your table in the database? That should, perhaps, be a separate column. How to populate it is a separate question.

My comment about the primary key not encoding any useful information... here is what I meant. Suppose you have an invoice number (and you will see from my comments that I don't actually know anything about how invoice numbers work) - so, suppose you have an invoice number that's 12 digits long. Somewhere among these digits, say the 7th and 8th digits together encode the vendor's state (as in, which state in the U.S.). The first three digits are the code of the specific employee who prepared the invoice. Etc. Different parts of the invoice number have a meaning. Well, that may be OK for whatever purposes, but is that how data should be stored in a relational database? No, absolutely not (well, "generally" not - I am sure there will be exceptions, although I can't think of any myself). That violates first normal form - each bit of information should be in a different column, with its proper name and data type, and in many cases it should simply be a code pointing to a dimension table. For example: to encode the state you would have a "states" table, with state code, state name and other relevant information about each state, and in the "invoices" table you would have a "state" column where you enter codes. This is a good example, by the way, where you shouldn't need system-generated sequences for PK: in the "countries" table, the state code could very well be PK.

Same way in your example, with several shifts. You should record which shift originated an invoice in a specific column called "shift". This would be a FK pointing to a small "shifts" table, which records shift numbers and perhaps additional information, such as start and end times for each shift. Then, given an invoice number (ANY invoice number), you could query the table to find out which shift that invoice came from. In this setup, why do you need the PK sequence numbers to begin with 1 for each shift? If, for example, you need a manager to quickly "know" which shift initiated an invoice without hitting the DB, that's fine: include a "shift" field on the invoice. Do you need the PK value (or the invoice number or whatever) to tell you how many orders have been filled by that shift so far? Why? For what purpose? (Then we can think about how to address that need... probably not through the PK value!)

I believe what we are discussing here, at a more general level, is using one thing for two very different purposes. The PK is used for establishing relationships between tables in your DB. On the other hand you want to encode useful information (like the date and time of an invoice, which shift it originated from, or other similar bits of information) Whenever possible, we try not to ask one column to serve both purposes. Often that leads to more problems than it solves. There are benefits, of course, but in many (most?) cases the cost is greater than the benefit.

Cheers,   mathguy

Great ... I understand now what you mean by PK not to hold any useful info ... i don't know why i have this idea that is stuck in my head that the user might need the PK to identify records somewhere in the application ... but after what you said so far  ... there is a difference between PKs and let's say IDs(as some unique user friendly identifier other than a PK) ... PKs is only used Internally by the DB and IDs are for users.

Let me explain the shift seq thing ... I used to do it your way ... in sales invoice there is : InvoiceId , ShiftId columns both have their own sequence ... After a year the store manager came to me and ask ... to restart the sequence for InvoiceId and make start from 1 at every shift ... it didn't make sense until he explained ... he wants to call the receipt number so the customer knows that his order is finished so he could come to the bar and grabs his coffee ... and it is so annoying for the store employee to shout "Order 546697 is ready " ... and you can find this in real life ... In Mac when i go to get my breakfast Orders start every day from 101.

But now i understand (and plz correct me if i'm wrong) that this have nothing to do with PKs ... That number shown on the screen 101 is another column may be called order_number which also may be generated by a sequence that gets dropped and created again every day or every shift ... but it has nothing to do the PK.

Gado

There is not a problem with users being shown a Pk value.

When you see things that seem like a sequence getting restarted you might be looking at something which is generated on the fly, like row_number() over (partition by day order by actual_primary_key), or mod(primary_key,1000) for your fast food order.