Tuesday, September 28, 2010

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

The solutions for that error is to specify thru

DBMS_OUTPUT.ENABLE(10000).

Note that the default value is 2000 characters, and maximum is 1, 000, 000.

Oracle - Get the last inserted id how-to

Just simple, declare a variable with that type, mostly number, then   use the 'returning' syntax   for that.

set serveroutput on
declare
 nr_id number;
begin
 insert into employees( id, name, age )
 values( seq_employees.NEXTVAL, 'Jones', 33 )
 returning id into nr_id;
 dbms_output.put_line( 'nr_id = ' || nr_id );
end;
/


Hope this helps. 


- Toytoy Gogie

Oracle - Create auto-increment equivalent in MySQL

So for example, I have my table below to create

CREATE TABLE "PC2"."TMP_PRODUCT"
(
  "ID"              NUMBER(38,0) NOT NULL ENABLE,
  "ACCOUNTCODE"     VARCHAR2(20 BYTE),
  "PRICESEGMENT"    VARCHAR2(20 BYTE),
  "BRAND"           VARCHAR2(20 BYTE),
  "PRODUCTLENGTH"   NUMBER,
  "SALESID"  NUMBER(38,0),
  CONSTRAINT "PK_TMP2_WPP_1009_ID" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255,
  CONSTRAINT "FK_TMP2_WPP_1009_SID" FOREIGN KEY ("SALESID") REFERENCES "PC2"."SALES" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);

Then create a sequence which would manage the incremental part of the index key.

CREATE SEQUENCE SEQ_TMP_PRODUCT
  START WITH 1
INCREMENT BY 1;


Afterwards, create a trigger which what will do is to insert the incremented key of the table.

CREATE OR REPLACE TRIGGER "TMP_PRODUCT_ID" BEFORE
  INSERT ON TMP_PRODUCT FOR EACH ROW WHEN (NEW.ID IS NULL) BEGIN
  SELECT SEQ_TMP_PRODUCT.NEXTVAL INTO :NEW.ID FROM DUAL;
END;


Just that simple.  Just be sure with the table names, sequence names as you might confuse if you base on that sample I have.

Hope that helps.