Recent content by slicendice

I have a database that I'm using to extract/generate data that includes a whole bunch of tables, queries, VBA, etc and the output of this is a set of data that will be used by a customer. The data that is extracted has to be in a separate database so that it only contains the data and nothing...

Hi everyone I have a situation where I'm processing the data in a large table using a PL/SQL routine. The processing has to happen in batches and when a record has been processed, a field in the table is updated to indicate this. The field in question (called PROCESSED) is a simple NUMBER(1)...

Hi I have a bunch of XML files that need reading/processing and I've constructed something that uses xmltype, dbms_xmlschema, etc. I've loaded the associated XSD schema into the database using the following: -- Read the XSD file and load it into an xmltype -- xtXSD is declared as...

Well, just in case anyone's interested, a restart of the database made this issue go away! I have no idea why and it's a little disconcerting, as I really don't want this to just start happening again - because once it's installed at a customer site, it won't be possible to just shut the...

I have a package that compiles and works fine - no issues or errors and the package status is VALID. There is a main function in the package and this in turn calls various other functions within the package as part of the processing. All functions are fully internal to the package and no...

Sorry if the subject doesn't explain things too well, but it's difficult to get across in one line! Anyway, we have a query which returns a data set that contains two columns - an ID and optionally a date. The query used to generate the data is as follows: WITH evt AS (SELECT aceu_cnh_id...

I have a requirement to use the built in REPLACE function on a CLOB variable as part of a larger PL/SQL process. I'm using Oracle 11g R2 and the function works OK, in that it does the replace as required, but as the procedure runs (there are around 2.5 millions records to process), it slows down...

I have been looking at external tables but I use an Oracle function on one of the data items as it is imported, i.e. in SQL*Loader control file: LOAD DATA INFILE 'udf_data2.txt' BADFILE 'udf_data2.bad' APPEND INTO TABLE udf_test FIELDS TERMINATED by ',' (ptau_id "seq_udf_test.nextval"...

As far as I'm aware the WHEN clause only deals with strings...though I stand to be corrected on that one. I tried it anyway, just in case, but it simply caused another error: SQL*Loader-350: Syntax error at line 5. Expecting quoted string or hex identifier, found "-". WHEN (ptau_per_id <>...

Hi Does anyone know if it's possible to check for a negative number using the WHEN clause in SQL*Loader? The column being checked is defined in the database table as NUMBER(8). I can use the following OK: WHEN (col1 <> '1') The above works fine, but if I change it to: WHEN (col1 <> '-1')...

OK, I just realised that I forgot to include a return type for the Java function....doh! Altered to: CREATE OR REPLACE FUNCTION get_dir_list_array(p_directory IN VARCHAR2) RETURN typDirList AS LANGUAGE JAVA NAME 'GetDirListArray.GetList( java.lang.String ) return String[]'; Compiles OK...

Hi I've got a function in Java that returns a string array but I'm not sure how to reference this in PL/SQL (I am a total beginner when it comes to Java!). The Java function is as follows: CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "GetDirListArray" AS import java.io.*; import...

Yep, I think that's the best/easiest way to do it. Thanks Dagon

Hi I have two variables that are involved in processing some appointment data - one is the start time of the appointment and the other is the duration (in minutes). I need to calculate the end time of the appointment using those two items - which in itself is not a complex concept! I know I can...

OK I've done it slightly differently and instead of returning a cursor, it's returning a TABLE data structure. It's working up to a point - but I can't execute it using dynamic SQL and successfully retrieve the data returned by the function. CREATE OR REPLACE PACKAGE qi_test IS TYPE...