El proximo SQL devuelve la informacion jerárquicade los registros en una table basando en el código del padre. Este script es muy útil para determinar la estructura de jerarquía: SELECT   ID,   ORGANIZACION,   ID_PADRE,   LEVEL, – PALABRA RESERVADA  SYS_CONNECT_BY_PATH(ORGANIZACION, ‘/’) – USADO PARA LA CONSTRUCCIÓN DEL PATH/MIGA-DE-PAN (PUEDE SER REMOVIDO) FROM  ORG_UNITS where   ACTIVE = 0 START WITH ID = XXX – CÓDIGO DEL PADRE, RAÍZ DE LA ESTRUCTURA

Problema: Inserir un cambio de linea(enter) en un campo de texto, varchar2, clob, blob etc. Solución: Concatenar chr(13) || chr(10):  p_log clob;  p_log := p_log || ‘blah blah’ || chr(13) || chr(10);

Problem:  begin dbms_mview.refresh(‘MY_VIEW’,‘C’); end; ORA-12008: error in materialized view refresh path ORA-01476: divisor is equal to zero create or replace MY_VIEW(F_FIELD_1, (…), F_CREATED_BY, F_CREATED_ON, F_UPDATED_BY, F_UPDATED_ON) as select (…) CREATED_DATE, to_date(CREATED_BY, ‘DD/MM/YYYY’), UPDATEDD_DATE, to_date(UPDATED_BY, ‘DD/MM/YYYY’),  from (…) ; Caused by: to_date(CREATED_BY, ‘DD/MM/YYYY’), to_date(UPDATED_BY, ‘DD/MM/YYYY’),

Solución: select * from where between to_date(‘0101’ || to_char(trunc(sysdate, ‘YEAR’)-1, ‘YYYY’), ‘DDMMYYYY’) AND to_date(‘3112’ || to_char(trunc(sysdate, ‘YEAR’)+1, ‘YYYY’), ‘DDMMYYYY’)

Para substituir texto em PL/SQL es necesario usar la funcion REPLACE Atención: REPLACE devuelve la string con la substituicion echa, por lo tanto el resultado de su llamada debera ser guardado en una variable: … text_aux varchar2(1024); text clob; … text := text || replace(text_aux, ‘ xmlns=’ || chr(34) || ‘http://www.aaa.com' || chr(34), “); … (En este caso chr(34) es el carácter de escape para: “)

Teniendo un fichero XML: <?xml version=“1.0” encoding=“ISO-8859-9”?> 1 TESTE ESTO ES SOLO UN TEST Una tabla: CREATE TABLE XML_TEST (id number, title varchar2(200), description varchar2(200)); Para ler el fichero en la consola: SELECT * FROM XMLTABLE(‘/Row’ PASSING XMLTYPE(BFILENAME(’’, ‘’), NLS_CHARSET_ID(‘CHAR_CS’)) COLUMNS ID NUMBER PATH ‘F_ID’, TITLE VARCHAR2(200) PATH ‘F_TITLE’, DESCRIPTION VARCHAR2(200) PATH ‘F_DESCRIPTION’ ); Otra alternativa es hacer el insert de un fichero XML en una tabla: INSERT INTO xml_test (ID, TITLE, DESCRIPTION)

Problem: ORA-00955: name is already used by an existing object 00955. 00000 - “name is already used by an existing object” Solution: Verificar que tipo de object esta a utilizar el nombre:  select * from all_objects where object_name = ‘XXX’; Si el object existing no es necesario se puede realizar el drop:  drop “tipo_de_objecto” “nombre_del_object”;

Listar los Oracle jobs existentes: select * from dba_jobs; Obtener la próxima fecha de ejecución: select to_char( next_date, ‘YYYYMMDD:HH24MI’) from dba_jobs where job = 181; Testar la próxima fecha de ejecución: select to_char( trunc(SYSDATE) + ((16*60)+50)/1440 , ‘YYYYMMDD:HH24MI’) from dual; Aplicar los cambios: exec DBMS_JOB.next_date ( , trunc(SYSDATE) + ((16*60)+50)/1440); Commit a los cambios: Commit;

El siguiente script transfiere datos del esquema AAA de una base de datos usando el database link BBB. Porque deseamos guardar las trazas de la operación en el fichero de nombre CCC tendremos que informar Oracle que debe hacerlo en el directory object DDD: impdp system/system_user_password schemas=AAA directory=DDD network_link=BBB logfile=CCC remap_schema=AAA:AAA remap_tablespace=AAA:AAA Nota: 1- El script arriba parte del principio que el tablespace tiene el mismo nombre que el esquema

The following script transferes the schema AAA from a database using the database link BBB. Because we want to store the log of this operation in file CCC we have to inform oracle to save it int the directory object DDD: _impdp system/system_user_password schemas=AAA directory=DDD network_link=BBB logfile=CCC_ remap_schema=AAA:AAA remap_tablespace=AAA:AAA Nota: 1- The above script takes into consideration that the tablespace has the same name as the schema 2- In order to import the data, instead of only the databale elements (tables, views, etc) droping the schema might also be necesary.

In order to concatenate the values of the same column across several records do: _ select replace( wm_concat(), ‘,’, chr(13)) from ; _ Examples: Add all distinct values in the same cell separated by “:”  select distict(wm_concat(), ‘,’, ‘:‘) from ; Add all values in the same cell separated by a new line “chr(13)” _ replace(wm_concat(), ‘,’, chr(13))_

Seguidamente un ejemplo de como juntar en una misma celda el contenido de varios registros. Ejemplo de una table estudiantes con varios periodos StudentId FromDate ToDate 28 01-Oct-08 31-Dec-07 28 01-Jan-09 31-Mar-09 34 01-Feb-11 30-Jun-11 35 01-Feb-11 30-Jun-11 36 01-Nov-10 31-Dec-10 36 01-Mar-11 31-May-11 Si queremos ensenar todos los periodos de cada uno de los estudiantes en una misma celda: -- Juntar las varias celdas de las varias lineas en una misma celda

De modo a convertir una columna tipo varchar2 para clob es necesario: paso 1 - adicionar una nueva columna con del tipo clob: alter table add( clob); paso 2 - copiar los datos de la columna tipo varchar2 para la nueva tipo clob: _update <_tabla_> set <nombre_nueva_columna> = ;_ paso 3 - finalmente, renombrar el nombre de la nueva columna para el nombre de original: alter table <__tabla>_ rename column_ to ;

In order to convert a column datatype from varchar2 to clob do: step 1 - add a column with clob datatype: _alter table add( clob); _ step 2 - copy data from the source column to the new one: _update set \= ; _ step 3 - rename the new column created in the step 1 in order to complete the process: _alter tablerename column_ to _; alter tablerename column_ to ;

Script para conceder permisos de acceso a una tabla/view a un User/Schema: grant select on to ; Con opcion “grantable” grant select on to ; with grant option;

Thank you. that post is very useful. Ahmet DEMIR - Mar 2, 2017 Thank you. that post is very useful.

Problem: Error: PLS-00323: subprogram or cursor ‘UPD_COSTELEMENTX’ is declared in a package specification and must be defined in the package body Error: PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted Error: PL/SQL: Item ignored  procedure UPD_COSTELEMENT (p_commit in number, p_out_msg out varchar2) is (…) l_logfile_name varchar2(250); p_out_msg varchar2(4000);  lf utl_file.file_type; begin dbms_output.enable(null); (…)  Cause: Double initialization of the variable p_out_msg.

Problem: Connecting to the database APPS_MANPOWER_PLUS_APPS. ORA-29282: invalid file ID ORA-06512: at “SYS.UTL_FILE”, line 878 ORA-06512: at “APPS_MANPOWER_PLUS.PKG_UPD_BULK”, line 421 ORA-06512: at line 6 -——————————————————————————- Process exited. , generated by: (…)  _ begin dbms_output.enable(null); dbms_output.put_line(RPAD(‘-’,80,‘-’)); utl_file.put_line(lf, RPAD(‘-’,80,‘-’)); l_logfile_name := ‘UPD_XXX’ || ‘_’ || to_char(sysdate, ‘YYYYMMDDHH24MISS’) || ‘.log’;____ lf := utl_file.fopen(‘OPERATION_LOGS’, l_logfile_name, ‘W’);__ dbms_output.put_line(‘BEGIN: PKG_UPD_BULK.UPD__XXX_‘); utl_file.put_line(lf, ‘BEGIN: PKG_UPD_BULK.UPD_XXX’);_ (…)  Cause: Write in the file before first open it. The correct code should be:

Procedure PL/SQL to send a email using UTL_SMTP package: _Declare l_mail_conn UTL_SMTP.connection; BEGIN l_mail_conn := UTL_SMTP.open_connection(“, 25); UTL_SMTP.helo(l_mail_conn, ‘__‘); UTL_SMTP.mail(l_mail_conn, “); UTL_SMTP.rcpt(l_mail_conn, ‘ UTL_SMTP.data(l_mail_conn, “ || UTL_TCP.crlf || UTL_TCP.crlf); UTL_SMTP.quit(l_mail_conn); END;_

Take the following Oracle function as example: sql>create or replace function “func_get_project_id” (p_project_code varchar2) return varchar2 is param_value varchar2(255); begin select project.id into param_value from project, costelement where project.costelementid = costelement.id and code = p_project_code; return param_value; exception when others then return sqlerrm; end func_get_project_id; 1- The first option is to call the function using a select: sql>select func_get_project_id(‘e/0900-04’) from dual; 2- The second option is to set the activate the server output:

El script/procedimiento abajo testa si una dada Organización es padre de una segunda pasada como argumento: declare dummy varchar2(500); f_unit varchar2(50); f_parent_unit varchar2(50); begin f_unit := ‘AA-ABC’; f_parent_unit := ‘AA-AB’; begin select * into dummy from dual where f_unit like f_parent_unit || ‘%’ and f_unit != f_parent_unit; dbms_output.put_line(‘DATA FOUND’); exception when others then dbms_output.put_line(‘NO DATA FOUND’); end; end; / En una forma aun mas general solo necesitéis: begin select * into dummy

The following script/procedure tests if a given Organization is the father of a second: declare dummy varchar2(500); f_unit varchar2(50); f_parent_unit varchar2(50); begin f_unit := ‘AA-ABC’; f_parent_unit := ‘AA-AB’; begin select * into dummy from dual where f_unit like f_parent_unit || ‘%’ and f_unit != f_parent_unit; dbms_output.put_line(‘DATA FOUND’); exception when others then dbms_output.put_line(‘NO DATA FOUND’); end; end; / In a more general form you will only need: begin select * into dummy

You can easily generate a random number by: select dbms_random.normal from dual; Unfortunately, Oracle does not provide a solution if you require a random number between a interval of values. To do it you might wand to do: create or replace function RANDOM_NUMBER(lower IN int,higher IN int) return number is begin return trunc(abs(( GREATEST(lower, higher) - LEAST(lower, higher) + 1 ) * abs(dbms_random.value)) + LEAST(lower, higher)); end; My own solution involved a last steep:

Unfortunately it is not possible to rename a Oracle external table, the solution is to drop the column and create it from scratch: alter table drop column alter table add

List of existing jobs: select * from dba_jobs; Get the actual Oracle Job next execution date: select to_char( next_date, ‘YYYYMMDD:HH24MI’) from dba_jobs where job = 181; Test the next execution time: select to_char( trunc(SYSDATE) + ((16*60)+50)/1440 , ‘YYYYMMDD:HH24MI’) from dual; Execute the job update: exec DBMS_JOB.next_date ( , trunc(SYSDATE) + ((16*60)+50)/1440); Commit the changes: Commit;

This helped me out, thank you! Regards, Paul. [Anonymous]( “noreply@blogger.com”) - Nov 5, 2011 This helped me out, thank you! Regards, Paul.

Too user UTL_FILE.fopen first create a directory in ORACLE _ CREATE OR REPLACE DIRECTORY AS ‘/xxx/yyy/zzz’;_ Grant the permissions to it GRANT WRITE ON DIRECTORY TO APPS_NPI; Use the UTL_FILE.fopen in your PL/SQL block … _l_blob_len := DBMS_LOB.getlength (p_data); l_out_file := UTL_FILE.fopen (“, file_name, ‘wb’, 32767); WHILE l_pos < l_blob_len LOOP DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer); IF l_buffer IS NOT NULL THEN UTL_FILE.put_raw (l_out_file, l_buffer, TRUE); END IF; l_pos := l_pos + l_amount; END LOOP; UTL_FILE.

Para ejecutar una instrucción con caracteres especiales es necesario usar caracteres de escape. La siguiente una instrucción de Update hará el replace de “&” por “” update set = replace( , CHR(38), “); La siguiente una instrucción de Insert de “TESTE ‘ TESTE’: insert into  _( f_description) values( ‘TESTE’ || CHR(39) || ‘TESTE’);_ Un metodo mas comodo, que no necesita definicion de los caracteres de escape, es:

La creación de un indice en una tabla es la solución para aumentar el tiempo de respuesta a una consulta, desde cuando se haga sobre el campo indexado. Para crearlo necesitamos : CREATE INDEX on ();

Create indexes on a table to optimize data queries, always when the query is made using the indexed field CREATE INDEX on ();

La llave primaria de una tabla es una regla que especifica que el valor del campo de la tabla identifica de modo único su linea en la tabla, es decir, el valor sirve como llave para el acceso a una linea en la tabla. Como consecuencia estos valores no se puede repetir ni ser nulos en estos campos. Es posible especificar mas de un campo como llave primaria Abajo, se indica un script para definir una llave primaria en una tabla ya existente.

The primary key its a constraint that specifies that the value of the field fully identifies the row in the table, that is, the value is the “key” to he table. And so this constraint will not permit duplicate or null values int that field. Its possible to specify more than one field as primary key. Next, there’s a script for defining a primary key in a already existing table.

Buen dato Unknown - Feb 6, 2014 Buen dato Buen dato Unknown - Feb 6, 2014 Buen dato Gracias! Jeremias - Mar 2, 2014 Gracias!

Script Oracle “Alter Table” para cambiar el nombre de una columna: alter table rename column to;

The following script changes the name of an already existing field in a table alter table rename column to