jump to navigation

Simple example of using a cursor to update July 4, 2006

Posted by maxmil in : Oracle , add a comment

CREATE OR REPLACE
procedure alterpedidos as

denom productos.denominacion%type;
cursor c_pedidos is
select idproducto, nombreproducto from pedidoslinea where to_number(idpedido)>113 and to_number(idpedido)<697 for update; idproducto_ped pedidoslinea.idproducto%type; nomproducto_ped pedidoslinea.nombreproducto%type; begin open c_pedidos; loop fetch c_pedidos into idproducto_ped, nomproducto_ped; exit when c_pedidos%notfound; begin select denominacion into denom from productos where idproducto=idproducto_ped; exception when no_data_found then denom:= null; dbms_output.put_line(‘Ref no encontrada: ‘ || idproducto_ped); end; if (denom is not null) then update pedidoslinea set nombreproducto=denom where current of c_pedidos; end if; end loop; close c_pedidos; end;

Example of reading from a file and inserting April 29, 2006

Posted by maxmil in : Oracle , add a comment

This is a simple script that uses the UTL_FILE package to read a text file with values separated by ; and inserts them into a test table.

DECLARE
    VARCHAR(100);
    i1 NUMBER;
    i2 NUMBER;
    idCnt Test.ID%TYPE;
    namefield VARCHAR(100);
    datestr VARCHAR(100);
    datefield DATE;
    f utl_file.file_type;
BEGIN
    f := utl_file.fopen('SAMPLEDATA', 'sample1.txt', 'R');
    SELECT TEST.ID INTO idCnt FROM TEST WHERE ROWNUM = 1 ORDER BY ID DESC;
    LOOP
        BEGIN
            utl_file.get_line(f, l);
            i1 := INSTR(l, ';');
            i2 := INSTR(l, ';', i1+1);
            namefield := SUBSTR(l, 0, i1-1);
            dateStr := SUBSTR(l, i1+1, i2-i1-1);
            idCnt := idCnt + 1;
            INSERT INTO TEST
                (ID, NAME, START_DATE) VALUES
                (idCnt, namefield, TO_DATE(dateStr, 'dd-mm-yyyy'));
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
    END;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Exception caught:' || SQLCODE || SQLERRM);
END;
/  

PL/SQL Articles

Posted by maxmil in : Oracle , add a comment

This guy writes with clarity…

http://www.devshed.com/cp/bio/Jagadish-Chatarji/ 

Oracle XE – Reading from files April 27, 2006

Posted by maxmil in : Oracle , add a comment

First define directories where input files can be stored:

CREATE DIRECTORY sampledata AS 'e:tmp';

Then Grant read and write access to all

GRANT read, write ON DIRECTORY sampledata TO PUBLIC;

Note that in a production environment this would be more restrictive.

In Oracle XE there is a missing grant that needs to be issued for utl_file to work

Connect as sysdba. For me in sqlplus this is acheived by:

connect system/password as SYSDBA

Or in sql developer i set up a new connection using the user sys/password with rol SYSDBA on localhost with SID xe.

Once inside issue

GRANT EXECUTE on SYS.UTL_FILE TO PUBLIC;

You can know start to read files. Heres a simple procedure that reads the first line from sample1.txt in e:/tmp

create or replace procedure read_file as
f utl_file.file_type;
s varchar2(200);
begin
f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
utl_file.get_line(f,s);
utl_file.fclose(f);
dbms_output.put_line(s);
end;

See the article here http://www.devshed.com/c/a/Oracle/Reading-Text-Files-using-Oracle-PLSQL-and-UTLFILE

Oracle Express Edition (XE)

Posted by maxmil in : Oracle , add a comment

Notes about the installation of oracle xe and sql developer

In debian i needed more swap space, added it via creating a swap file. But then i also needed to install extra packages that were in the unsable sources so i decided that for the moment i would just install it in windows.

The installation in windows was easy peezy, for sql developer i had to install the jsdk1.5.

The administrator user that is created is called system and an hr user is created for non-admin stuff.

To connect from sql developer i had to use:

username: hr
password: password
hostname: 127.0.0.1
SID: XE

To be able to debug using the hr user i had to give it certain priveleges. As the system user i executed:

GRANT debug any procedure, debug connect session TO hr;