Code Frost

Coming from VB background, I tend to look for functions that I used most frequently back then. When working with Excel sheet for example, I would like to format the date properly (e.g., dd-mmm) but store it as text to prevent conflict when just using it as a label.

The Excel’s equivalent to Format is the TEXT function.

Syntax
TEXT(value,format_text)

This function can take most of the available custom formatting found in Excel. I’m not sure with that but I think it is.

I often encounter the need to flatten out a set of rows of data to a single row by contatenating the string values of one of the columns. This code does the job well.

Original code came from here
I just did some minor modification especially on how the function is called.

CREATE OR REPLACE FUNCTION f_concatenate_list (p_cursor IN sys_refcursor) 
       RETURN VARCHAR2
   IS v_return                  VARCHAR2(32767);
       v_temp                    VARCHAR2(4000);
   BEGIN
      LOOP
         FETCH p_cursor
          INTO v_temp;

EXIT WHEN p_cursor%NOTFOUND;
         v_return := v_return || ', ' || v_temp;
      END LOOP;

    RETURN LTRIM (v_return, ', ');
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END
	f_concatenate_list

This is how the function should be called:

SELECT      a.Id,
                   f_Concatinate_List
                       (CURSOR (
                               SELECT  TO_CHAR(b.strValue)
                               FROM    table1 b
                               WHERE   b.Id = a.Id
                               )
                       ) AS concatenate_str
FROM        table1  a
WHERE       a.Id = 7374;

Since I’m using Oracle 10g XE, I’m a little bit conscious about my database size because it’s limited. That’s why although Oracle supports storing BLOBS on tables, I prefer not to in order to save space.

What I did was created a file folder in my Oracle server then store all the files I needed there with the file path stored on my table. I’ve used the blob_wrapper code from this article: On reading blobs from and writing blobs to a file

I then wrote a separate package to utilize the blob_wrapper function by using the APEX_APPLICATION_FILES to retrieve the file, write to a directory in my server as a file then delete from APEX_APPLICATION_FILES.

The steps are pretty much straightforward on the above article so I will not duplicate it here.

Since there is no built-in function equivalent to IsNumeric in Oracle, we can easily create a custom function as follows:

create or replace
function isnumeric ( p_string in varchar2) 
return NUMBER
as
  l_number number;
begin
  l_number := p_string;
  return 1;
exception
  when others then
    return 0;
end;

I’ve used NUMBER as the return type but you can use any type that suits your requirements.

For Oracle 8i and above, one can get the Top N rows using an inner-query with an ORDER BY clause:

SELECT *
  FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
 WHERE ROWNUM < 10;

More info here.