CODEWITHSUNDEEP
oracle-databasefunctionplsqloracle-apex
Tikkaty
Tikkaty

Reputation: 792

How do I call a function from within a PL/SQL process within Oracle APEX 4.2

I've created a new function called GET_FILENAME inside my Oracle APEX database. I'd like to call this function from within a APEX PL/SQL code block and pass it the filename. Below is the function I created in my APEX DB with SQL Dev.

create or replace function get_filename
   (p_path IN VARCHAR2)
   RETURN varchar2

IS
   v_file VARCHAR2(100);

BEGIN

   -- Parse string for UNIX system
   IF INSTR(p_path,'/') > 0 THEN
      v_file := SUBSTR(p_path,(INSTR(p_path,'/',-1,1)+1),length(p_path));

   -- Parse string for Windows system
   ELSIF INSTR(p_path,'\') > 0 THEN
      v_file := SUBSTR(p_path,(INSTR(p_path,'\',-1,1)+1),length(p_path));

   -- If no slashes were found, return the original string
   ELSE
      v_file := p_path;

   END IF;

   RETURN v_file;

END;

APEX side of things....

Below is the code I'd like to put in when calling the process. 

TO_DATE(SUBSTR(GET_FILENAME(file_date),21,8),'YYYY-MM-DD')

Below is the apex process code and the comments are where I'd like to put it in.

BEGIN

APEX_COLLECTION.ADD_MEMBER
(
        p_collection_name => 'PARSE_COL_HEAD',
        p_c001            => 'C031',
        p_c002            => 'FILE_DATE');

FOR UPLOAD_ROW IN (SELECT SEQ_ID FROM APEX_COLLECTIONS 
                   WHERE COLLECTION_NAME = 'SPREADSHEET_CONTENT')
LOOP

  APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
   p_collection_name  => 'SPREADSHEET_CONTENT',
   p_seq              => UPLOAD_ROW.SEQ_ID,
   p_attr_number      => '31',
   p_attr_value       => :P25_FILE_NAME -- I want to call the process here
  ); 

END LOOP;

END;

Upvotes: 2

Views: 828

Answers (1)

Tikkaty
Tikkaty

Reputation: 792

I don't normally answer my own question. But this was easier than I thought :). Below is the code I used to get this working....

BEGIN

APEX_COLLECTION.ADD_MEMBER
(
        p_collection_name => 'PARSE_COL_HEAD',
        p_c001            => 'C031',
        p_c002            => 'FILE_DATE');

FOR UPLOAD_ROW IN (SELECT SEQ_ID FROM APEX_COLLECTIONS 
                   WHERE COLLECTION_NAME = 'SPREADSHEET_CONTENT')
LOOP

  APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
   p_collection_name  => 'SPREADSHEET_CONTENT',
   p_seq              => UPLOAD_ROW.SEQ_ID,
   p_attr_number      => '31',
   p_attr_value       => TO_DATE(SUBSTR(GET_FILENAME(:P25_FILE_NAME),21,8),'YYYY-MM-DD')
  ); 

END LOOP;

END;

Upvotes: 1

Related Questions