Skip to content
accelerando

Tag Archives: PL/SQL

Oracle “sleep” procedure: DBMS_LOCK.SLEEP

07-Sep-10

There’s a nice little “sleep” procedure in Oracle: A procedure that stops the execution of the current thread for n seconds.

Strangely, this method can be called in SQL*Plus like so:

EXEC dbms_lock.sleep(10);

but not in another stored procedure or function like so

CREATE OR REPLACE PROCEDURE FOOBAR AS
BEGIN
  DBMS_LOCK.SLEEP(1);
END;
/

To use “sleep” in your procedures or functions, login as administrator to your database (or ask you admin to to so) and create the following objects:

CREATE OR REPLACE PROCEDURE sleep(seconds NUMBER) AS
BEGIN
  DBMS_LOCK.SLEEP(seconds);
END;
/
 
CREATE OR REPLACE public synonym sleep FOR sleep;
 
GRANT EXECUTE ON sleep TO public;

and in your procedure just use “sleep”.

Share This

Filed in English posts, Oracle | Comments (3) »

Fun with sql

27-Oct-08

What’s all the fuss about this SQL Injection thing?

It boils down getting some malicious crafted SQL code into the SQL code of an application, destroying data or authenticate yourself without knowing any real password. xkdc has a nice explanation.

The simple cases base on wrong escaped strings and the like. But as this SQL injection cheatsheet shows there are an infinity number of possibilities.

At day most of the time my database connection is an Oracle connection and so i found this Oracle whitepaper titled How to write injection-proof PL/SQL very interesting (via Bruce Schneier found at the gay bar).

I do not have a magic recipe for avoiding attack vectors all the time but as well as the whitepaper is written, it’s not a solution to expose all queries only via pl/sql to clients. In fact, it’s a nightmare to get this to work with JPA and other ORM mappers.

I try not to use dynamic sql in the sense of “concatenate some strings with one another and mysql_real_escape_string or DBMS_Assert. them” but use prepared statements with placeholders and explicit datatypes. Also if there’s a need for computing sql queries at runtime, do not ever let user supplied input come near them. I know that i’m relying to my api in this case but there is always a point on which i must rely on i guess.

As alway, the most important thing is: Be conscious about what you are doing and try to understand that, but at this point, i leave the discussion about software development and enter the depths of common sense…

Share This

Filed in Design, English posts, Oracle | Comments (0) »

Simple tokenizing with Oracle PL/SQL

18-Jul-07

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 processed and furthermore, it can be used as a table in a from clause. Great as i need no extra code to check my values in my case.

So here it is:

CREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(32767);
/
show errors;
 
CREATE OR REPLACE FUNCTION f_split (
    p_list VARCHAR2,
    p_del VARCHAR2 := ','
) RETURN split_tbl pipelined
IS
    l_idx    PLS_INTEGER;
    l_list    VARCHAR2(32767) := p_list;
    l_value VARCHAR2(32767);
BEGIN
    LOOP
        l_idx := INSTR(l_list,p_del);
        IF l_idx > 0 THEN
            pipe ROW(LTRIM(RTRIM(SUBSTR(l_list,1,l_idx-1))));
            l_list := SUBSTR(l_list,l_idx+LENGTH(p_del));
        ELSE
            pipe ROW(LTRIM(RTRIM(l_list)));
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END f_split;
/
show errors;

This thing takes two parameters, the first one the string to be tokenized, the second an optional delimiter. It returns all tokens trimmed as a row for each token.

Kudos to Scott Stephens.

Share This

Filed in Oracle | Comments (4) »

On writing binary data from within Oracle Forms 6i

29-Jan-07

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 packages dbms_xmlgen and xmlparser. Data is collected in a mere handful of so called “object views”, that are denormalized, hierarchical views on data.

After receiving the assignment to generate pdf it was no big problem at all to write some java classes around iText and dom4j to generate new reports.
I decided agains xsl-fo as i found no inexpensive tools for easily designing adequate stylesheets. For the worse, xsl-fo lacks several important features, i.e. collapsing borders. My customers would have laughed at best if i had presented the prototype…

So i have this java classes – all in the database for sure, no need for java on the client side -, i have added code to my package and i thought it would be easy to write the resulting blob with Forms 6i to the client…

Bah! There is no such easy thing in Forms. I remembered havin’ problems reading a clob, as the whole bunch of dbms_lob functionality doesn’t work in forms… So my workaround for creating text files from clobs was on the database side:

PROCEDURE  read_export(
  p_export_id  IN     NUMBER,
  p_menge      IN OUT BINARY_INTEGER,
  p_offset     IN     BINARY_INTEGER,
  p_buffer     IN OUT VARCHAR2
) IS
 clInhalt  CLOB;
 BEGIN
  BEGIN
    SELECT processed_export
      INTO clInhalt
      FROM xmlt_exporte
     WHERE export_id = p_export_id;
    DBMS_LOB.READ(clInhalt, p_menge, p_offset, p_buffer);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_menge:= 0;
  END;
EXCEPTION
  WHEN OTHERS THEN
    p_menge := 0;
END;

and in a forms on client side:

fhDateiOut := text_io.fopen(vcPath || vcFile, 'W');   	  	   	
LOOP			
  pck_xml.read_export(p_export_id, i, offset, vcBuffer);
  EXIT WHEN i = 0;
  offset := offset + i;
  text_io.put(fhDateiOut, vcBuffer);
END LOOP;					   
text_io.fclose(fhDateiOut);

(text_io is a forms standard package).

This works quite well.. So i thought, well, d2kwutil has some functions to write binary files, so i copy the database procedure, use dbms_lob.read with blob and raw and i’m done…
Yeah, i was done. Well fucked off.

I have no idea in which way dbms_lob.read converts blob data to raw, but it’s everything but the original. So after banging my head against the wall, finding things like “use software developed in this century” in forums, i wrote the following java code:

final BASE64Encoder enc = new BASE64Encoder();
 
final CLOB rv = CLOB.createTemporary(tmp.getJavaSqlConnection(), true, CLOB.DURATION_SESSION);
 
int bufSize = 1024*1024*1;
 
final BufferedInputStream in = new BufferedInputStream(tmp.getBinaryStream(), bufSize * 2);        
final BufferedOutputStream out = new BufferedOutputStream(rv.setAsciiStream(0L), bufSize);
 
enc.encode(in, out);
out.flush();
out.close();

which runs in the database, takes a blob as parameter and base64 encodes it to a clob. This clob is written to my export table and read by forms in the above way. Then i have a little base64.exe along with my forms that is called from within forms that decodes the file….

So much trouble just for writing binary data from server to client… it’s a twoliner with c or java.

Share This

Filed in Oracle | Comments (23) »

IS_NUMERIC, IS_NUMBER oder ähnliches…

18-Oct-06

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 = 'NLS_NUMERIC_CHARACTERS';
 
  BEGIN
    SELECT DECODE(
             NVL(LENGTH(TRANSLATE(TRIM(inval),' +-.,0123456789',' ')),0),
              0,TO_NUMBER(
                CASE WHEN VALUE = '.,' THEN
                  REPLACE(inval, ',','.')
                     WHEN VALUE = ',.' THEN
                  REPLACE(inval, '.',',')
                END              
              ),
              NULL
           )
    INTO   rv
    FROM   nls_session_parameters
    WHERE  parameter = 'NLS_NUMERIC_CHARACTERS';
  EXCEPTION WHEN invalid_number THEN
    rv := NULL;
  END;
 
  RETURN rv;
 
END f_makeNumber;
/

Diese Methode gibt den Zahlenwert des Strings ‘inval’ zurück, falls es sich um eine Zahl handelt, ansonsten null. NLS_NUMERIC Characters werden berücksichtigt (2.0 und 2,0 werden beides zu Zahlen), Strings wie 2.000,0 lassen sich damit allerdings nicht verarbeiten.

Share This

Filed in Oracle | Comments (1) »
Close
E-mail It