All posts tagged with 'PL/SQL'

Simple tokenizing with Oracle PL/SQL


I was in need of tokenizing some comma delimited data within an Oracle Database. A pity, there’s no split for a varchar2 like java.lang.String.split. I could have used Java in the database, but that would be lame, too.j I found this little function which uses pipelined results, that is, it returns his results while being […]

Read the complete article »

On writing binary data from within Oracle Forms 6i


There is this nice project of ours from 2002…. developed with Oracle Forms 6i and database PL/SQL Procedures. This project runs fine with an estimated 6gig database. Plain old client server model. We have some XML / XSLT based HTML reports that are processed – thanks to Oracle 9i – directly in the database with […]

Read the complete article »

IS_NUMERIC, IS_NUMBER oder ähnliches…


Kann sein, dass es in einem großen Datenbankprodukt namens Oracle keine Methode isNumeric oder ähnliches gibt, die mir sagt, ob ein String eine Zahl repräsentiert? Es kann… Deswegen hier eine kleine Methode: CREATE OR REPLACE FUNCTION f_makeNumber(inval IN VARCHAR2) RETURN NUMBER IS rv NUMBER; numCharSet VARCHAR2(32); BEGIN SELECT VALUE INTO numCharSet FROM nls_session_parameters WHERE parameter […]

Read the complete article »

Haufenweise: Bulk Insert, Update, Delete


Bulk Methoden in PL/SQL sind praktisch und schnell. Auch recht einfach anzuwenden. Allerdings ist mir in Oracle ein sehr komisches Verhalten aufgefallen: DECLARE TYPE id_table IS TABLE OF NUMBER; objekt_ids id_table; BEGIN SELECT id BULK COLLECT INTO objekt_ids FROM irgendwas WHERE irgendwasanderes = ‘wasweißich’;   FORALL idx IN objekt_ids.first .. objekt_ids.last DELETE FROM table1 […]

Read the complete article »