Example of copying/renaming/deleting files on your OS using PL/SQL


DECLARE

ORIG_FILE_NM VARCHAR2(4000) := 'foo.tst';
TMP_FILE_NM VARCHAR2(4000) := 'foo.tmp';
COPIED_FILE_RENM VARCHAR2(4000) := 'foo.txt2';
SRC_DIR VARCHAR2(4000) := 'TEST_DBMS_FILE_TRNSFR';
DEST_DIR VARCHAR2(4000) := 'TEST_DBMS_FILE_TRNSFR2';
FILE_DOES_NOT_EXIST EXCEPTION;
PRAGMA EXCEPTION_INIT(FILE_DOES_NOT_EXIST, -29283);

BEGIN
-- Clean up prev tmp file in case its still there.
BEGIN
UTL_FILE.FREMOVE(LOCATION => DEST_DIR, FILENAME => TMP_FILE_NM);
EXCEPTION
WHEN FILE_DOES_NOT_EXIST THEN
NULL;
END;

-- Copy the sample file (must be a size = multiple of 512B)
DBMS_FILE_TRANSFER.COPY_FILE(SOURCE_DIRECTORY_OBJECT => SRC_DIR
,SOURCE_FILE_NAME => ORIG_FILE_NM
,DESTINATION_DIRECTORY_OBJECT => DEST_DIR
,DESTINATION_FILE_NAME => TMP_FILE_NM);

-- rename the file @ the dest
UTL_FILE.FRENAME(SRC_LOCATION => DEST_DIR
,SRC_FILENAME => TMP_FILE_NM
,DEST_LOCATION => DEST_DIR ,DEST_FILENAME => COPIED_FILE_RENM);
END;


Oracle String Aggregation - Aggregated string as CLOB

** Be warned - thought this sql aggregates strings into a clob (large strings) it also, by design of the XML functions it uses, escapes all special characters! (e.g. & will be escaped to & the xml safe equivalent)**

I needed to aggregate rows returned into a single string. Yes i know, easy! (Even easier in 11gR2 with stragg, but we're currently on 11gR1).

The resultant value exceeded the varchar2(4000) allowable limit.

Here is a method to aggregate rows with the resulting string being presented as a CLOB and the method (simple TRIM) to clean up the ending ',' from the string:

SELECT  t.TABLESPACE_NAME
,COUNT(*) CNT
,trim(TRIM(TRAILING ',' FROM 
         EXTRACT(
                DISTINCT XMLAGG(XMLTYPE('<V>'|| t.table_name||',</V>') ORDER BY                                                        t.table_name),'/V/text()'
).GETCLOBVAL()
)
) TBLS

FROM sys.user_tables T
GROUP BY t.TABLESPACE_NAME
ORDER BY 2 DESC

Return any table/query as CSV data - dynamically (Using XSLT)

Use XSLT in PL/SQL to format data returned from dbms_xmlgen.getxml as CSV (or anything else).

This method is MUCH faster than a previous solution here, using DBMS_SQL.

Example Call:

SELECT GET_CSV_FROM_CURSOR(CURSOR(select ROWNUM, a.* from all_objects a WHERE ROWNUM < 10000)) FROM dual;

CREATE OR REPLACE FUNCTION GET_CSV_FROM_CURSOR(IN_SQL IN SYS_REFCURSOR) 
RETURN CLOB AS
OUT_CLOB CLOB;
NCTX NUMBER;
STSH XMLTYPE;
res_xml xmltype;

BEGIN

/*Prepare SQL statment */
NCTX := DBMS_XMLGEN.NEWCONTEXT(IN_SQL);

/*I want null columns returned! */
DBMS_XMLGEN.USENULLATTRIBUTEINDICATOR(NCTX,TRUE);

/*Stylesheet - tells processor how to format the resultant output */
STSH := XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
<xsl:output method="text" indent="no" />
<xsl:template match="/">
<xsl:for-each select="//ROWSET/ROW">
<xsl:for-each select="*">
<xsl:choose>
<xsl:when test="position() =  last()">
<xsl:text>"</xsl:text><xsl:value-of select="."/><xsl:text>"
</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>"</xsl:text><xsl:value-of select="."/><xsl:text>"</xsl:text><xsl:text>,</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>');

/* Get your data, transform then get clob value ('Transform' returns an xmltype) 
Also, if you're working with text that have ampersands, we replace for the parse, then readd after*/
res_xml :=  xmltype(replace(DBMS_XMLGEN.GETXML(NCTX),'&', 
'&'||'amp;')).TRANSFORM(STSH);
out_clob := replace(res_xml.getclobval(), '&'||'amp;', '&');

/* No longer need the xml context stuff */
DBMS_XMLGEN.CLOSECONTEXT(NCTX);

RETURN OUT_CLOB;

END;

Return any table/query as CSV data - dynamically (DBMS_SQL)

The below code allows you to query any table and get it's data back as a csv. Nothing is hard coded here so it works well for most any table you can throw at it.

I have a MUCH faster solution now, here


CREATE OR REPLACE PROCEDURE GET_CSV_FROM_SYSREFCURSOR
(
IN_CUR IN OUT SYS_REFCURSOR
,OUT_CSV OUT CLOB
) AS

V_OUT_CSV CLOB;
V_OUT_ROW_DATA CLOB;
COL_VAL VARCHAR2(32767);

H NUMBER;
NUM_COLS NUMBER;
RET DBMS_SQL.DESC_TAB;

BEGIN

H := DBMS_SQL.TO_CURSOR_NUMBER(IN_CUR);

DBMS_SQL.DESCRIBE_COLUMNS(H
,NUM_COLS
,RET);

FOR I IN 1 .. RET.COUNT
LOOP
DBMS_SQL.DEFINE_COLUMN(H
,I
,COL_VAL
,32767);
END LOOP;

WHILE DBMS_SQL.FETCH_ROWS(H) > 0
LOOP

FOR NBR_COLS IN 1 .. NUM_COLS
LOOP
DBMS_SQL.COLUMN_VALUE(H
,NBR_COLS
,COL_VAL);

V_OUT_ROW_DATA := V_OUT_ROW_DATA || '"' || COL_VAL || '"' || ', ';
END LOOP;

V_OUT_CSV := V_OUT_CSV || TRIM(TRAILING ', ' FROM V_OUT_ROW_DATA) || CHR(13);
V_OUT_ROW_DATA := NULL;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(H);

OUT_CSV := V_OUT_CSV;

/*
Example call

DECLARE
my_cur SYS_REFCURSOR;
data_clob CLOB;

BEGIN

OPEN my_cur FOR SELECT * FROM user_tables;
get_csv_from_sysrefcursor(my_cur, data_clob);
dbms_output.put_line(substr(data_clob,1,4000));

END;

*/

END;




Regexp_replace

*** Please note I have stumbled across a better, i think most correct way, of accomplishing the below, but using REGEXP_SUBSTR here.

Just a reminder when using PL/SQL regexp_replace -

Your search string can be used as a 'filter' to get exactly what you want out of the string and nothing more.

Consider the following: your string is the following: BUTTE 211 - Color (BSS) or BUTTE 211 - B&W (BSS) or in some cases BUTTE 211 - random words.

You need to extract *only* the words between 'BUTTE 211 - ' and the parenthesized items, if they exist '(BSS)'. Sometimes, on rare occasions, words exist other than Color & B&W in the string between these two expressions.

You could write multiple regexp functions nested, or even do an case when instr('Color') > 0 but

the following does the same thing, IN ONE STATEMENT/Function call:

regexp_replace(q.queuename, '(.* - | \(.*\))', '\2')

Basically what the above expression is doing is telling the regexp_replace function to get either .* - or (.*) into backreference \1. Since we don't want those characters and specifically anything but those expressions, we ask for \2...anything *not* those strings.

Viola: you get your answer, Color and B&W (and in some cases, 'random word')

Modify AD from PL/SQL

Here is how we connect to and modify our Active Directory @ my current place of business using PL/SQL. Our setup is Oracle 11gR1/RHEL.



DECLARE

SSL_WALLET_AUTH VARCHAR2(256) := '*******************';
SSL_WALLET_LOC VARCHAR2(256) := 'file:/opt/oracle/walletloc/subfolder';

AD_SERVER VARCHAR2(256) := 'server.someaddress.edu';
AD_BASE VARCHAR2(256) := 'DC=something,DC=edu';
AD_PORT NUMBER := 636;
AD_USERNAME VARCHAR2(400) := 'DOMAIN\username';
AD_PASSWORD VARCHAR2(100) := '*******************';

SESSN DBMS_LDAP.SESSION;
MYRETVAL PLS_INTEGER;
MYMESSAGE DBMS_LDAP.MESSAGE;

MA DBMS_LDAP.MOD_ARRAY;
MAN NUMBER := 1; -- current number of attributes to populate

DN VARCHAR2(300);

NEW_ATTR_VAL_COL DBMS_LDAP.STRING_COLLECTION;

ATTRIBUTE_TO_CHANGE VARCHAR2(1000);

BEGIN

DN := 'CN=Lname\, fname,CN=Users,DC=something,DC=edu'; --distinguishedName from AD
ATTRIBUTE_TO_CHANGE := 'department';
NEW_ATTR_VAL_COL(1) := 'Network Ops';

DBMS_LDAP.USE_EXCEPTION := TRUE;

SESSN := DBMS_LDAP.INIT(AD_SERVER, AD_PORT); -- start LDAP session
MYRETVAL := DBMS_LDAP.OPEN_SSL(SESSN, SSL_WALLET_LOC, SSL_WALLET_AUTH, 2); --open ssl channel
MYRETVAL := DBMS_LDAP.SIMPLE_BIND_S(SESSN, AD_USERNAME, AD_PASSWORD); -- bind auth as user

MA := DBMS_LDAP.CREATE_MOD_ARRAY(MAN);

DBMS_LDAP.POPULATE_MOD_ARRAY(MODPTR => MA, MOD_OP => DBMS_LDAP.MOD_REPLACE, MOD_TYPE => ATTRIBUTE_TO_CHANGE, MODVAL => NEW_ATTR_VAL_COL);

-- actual modification occurs here
MYRETVAL := DBMS_LDAP.MODIFY_S(SESSN, DN, MA);

DBMS_LDAP.FREE_MOD_ARRAY(MA);
MYRETVAL := DBMS_LDAP.UNBIND_S(SESSN);

EXCEPTION
WHEN OTHERS THEN

MYRETVAL := DBMS_LDAP.UNBIND_S(SESSN);
DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;



Parsing CSV with PL/SQL

I also outline a method to generate CSV very quickly from PL/SQL here

I needed to parse a very simple CSV but wanted to avoid having to do all of the normal looping and extending involved in populating varrays in pl/sql. So I came up with the below. A single query, taking in a csv variable and returning rows on the fly. If you run it, you’ll be prompted for a string. Enter in anything separating by commas and check the results.

WITH csv_data AS (SELECT '&your_csv_string' AS CSV ,'[^ |,]+' REGEX FROM DUAL)

SELECT regexp_replace(TRIM(REGEXP_SUBSTR(A.CSV, A.REGEX, 1, LEVEL)),'[^[:print:]]', '') STR
FROM csv_data a
CONNECT BY LEVEL <= REGEXP_COUNT(A.CSV, A.REGEX)
ORDER BY 1

The only caveat with this being that the csv is limited to a number of characters (a max im not going to research at this moment.)

A more proper way to do this would be a table function/procedure using the below:



DECLARE
TYPE MYDTTP IS TABLE OF VARCHAR2(2000) INDEX BY PLS_INTEGER;
MYDTTAB MYDTTP;
MYCSV VARCHAR2(24000);
VAL VARCHAR2(4000);
REGEXPAT VARCHAR2(100) := '[^,|'||CHR(10)||'|'||CHR(13)||']+';

BEGIN

MYCSV := 'adm_admit_type_tbl,
adm_app_dat
a_dtl,
adm_app_ plan_dtl,
adm_app_program_dtl,
ads,
adm_app_recruit_category_dtl,
adm_basis_admit_dtl,
adm_basis_admit_tbl,
adm_eop_data_dtl, ,
,
,
adm_mntr_app_college_attnd_dtl,
adm_mntr_app_college_crs_dtl,
adm_mntr_app_data_dtl,
adm_mntr_app_hs_cp_crs_dtl,,, ,,,,,';

DBMS_OUTPUT.PUT_LINE(REGEXP_COUNT(MYCSV, REGEXPAT));

FOR I IN 1 .. REGEXP_COUNT(MYCSV, REGEXPAT)
LOOP
VAL := TRIM(REGEXP_SUBSTR(MYCSV, REGEXPAT, 1, I));
BEGIN
MYDTTAB(I) := VAL;
EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
END LOOP;

DBMS_OUTPUT.PUT_LINE(MYDTTAB.COUNT());

FOR I IN 1 .. MYDTTAB.COUNT()
LOOP
CONTINUE WHEN MYDTTAB(I) IS NULL;
DBMS_OUTPUT.PUT_LINE(MYDTTAB(I));
END LOOP;
END;



Otherwise, have a good weekend.

PL/SQL parsing Google XML

Google nicely provides xml with namespace nodes.

They are difficult to parse. Remember: You must include the namespace when using the .extract function with XMLType!

Here is an example though, of being able to parse the XML as it's returned:




SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<feed xmlns="http://www.w3.org/2005/Atom" xmlns:dxp="http://schemas.google.com/analytics/2009" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/">
<id>http://www.google.com/analytics/feeds/accounts/data@csuchico.edu</id>
<updated>2009-02-26T09:35:02.000-08:00</updated>
<title type="text">Profile list for data@csuchico.edu</title>
<link rel="self" type="application/atom+xml" href="http://www.google.com/analytics/feeds/accounts/default?alt=atom"/>
<author>
<name>Google Analytics</name>
</author>
<generator version="1.0">Google Analytics</generator>
<openSearch:totalResults>1</openSearch:totalResults>
<openSearch:startIndex>1</openSearch:startIndex>
<openSearch:itemsPerPage>1</openSearch:itemsPerPage>
<entry>
<id>http://www.google.com/analytics/feeds/accounts/ga:1631082</id>
<updated>2009-02-26T09:35:02.000-08:00</updated>
<title type="text">www.csuchico.edu</title>
<link rel="alternate" type="text/html" href="http://www.google.com/analytics"/>
<dxp:property name="ga:accountId" value="942024"/>
<dxp:property name="ga:accountName" value="Chico State"/>
<dxp:property name="ga:profileId" value="1631082"/>
<dxp:property name="ga:webPropertyId" value="UA-942024-1"/>
<dxp:property name="ga:currency" value="USD"/>
<dxp:property name="ga:timezone" value="America/Los_Angeles"/>
<dxp:tableId>ga:1631082</dxp:tableId>
</entry>
</feed>'

).extract('/feed/entry/dxp:tableId', 'xmlns="http://www.w3.org/2005/Atom" xmlns:dxp="http://schemas.google.com/analytics/2009" xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"').getStringVal() from dual;'




Parallel PL/SQL execution

CREATE procedure refresh_employees
authid definer
is
begin
execute immediate 'truncate table vb_emp_no';
insert into vb_emp_no
select distinct employee_number from remote_view@db_link;
commit;
execute immediate 'truncate table employees';

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P1_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P1''); end;',
comments => 'Thread 1 to refresh employees',
enabled => true,
auto_drop => true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P2_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P2''); end;',
comments => 'Thread 2 to refresh employees',
enabled => true,
auto_drop => true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P3_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P3''); end;',
comments => 'Thread 3 to refresh employees',
enabled => true,
auto_drop = true);

dbms_scheduler.create_job(job_name => dbms_scheduler.generate_job_name('VB_P4_'),
job_type => 'PLSQL_BLOCK',
job_action => 'begin refresh_employee_data_part(''VB_EMP_NO_P4''); end;',
comments => 'Thread 4 to refresh employees,
enabled => true,
auto_drop => true);

end;
/

generate time/date table Oracle

/* BEST way to generate date list as 1 column of dates between inputs */
WITH DATES AS
 (SELECT TO_DATE(NVL('&begin_dt'
                    ,SYSDATE - 365)
                ,'DD-MON-YY') BEGIN_DT
        ,TO_DATE(NVL('&end_dt'
                    ,SYSDATE)
                ,'DD-MON-YY') END_DT
  FROM DUAL)

SELECT BEGIN_DT + (LEVEL - 1) DT FROM DATES CONNECT BY LEVEL <= (END_DT + 1) - BEGIN_DT


/*two other ways: */
select to_char(x.lvl, 'HH24') || ':00:00'
from ( SELECT sysdate - (level/24) lvl
FROM dual
CONNECT BY LEVEL <= 24 ) x
order by 1

select to_char(x.lvl, 'YYYY-MM-DD')
from ( SELECT sysdate - level lvl
FROM dual
CONNECT BY LEVEL <= 30) x

/*Create a time dim for every second of the day in a few easy steps: */

create table DWPRD.Time_Dim as

WITH timegen AS ((SELECT to_char(trunc(sysdate) + (level/86400),'HH24:MI:SS') lvl_sort, trunc(sysdate) + (level/86400) lvl FROM dual CONNECT BY LEVEL <= 86400) order by 1)

SELECT ROWNUM TIME_KEY, b.* FROM (
SELECT to_char(lvl, 'HH12:MI:SS AM') TIME_STANDARD, to_char(lvl, 'HH24:MI:SS') TIME_MILITARY, to_char(lvl, 'HH12') HOUR_STANDARD, to_char(lvl, 'HH24') HOUR_MILITARY, to_char(lvl, 'MI') MINUTE, to_char(lvl, 'SS') SECOND,
to_char(lvl,'AM') as AM_PM,
case WHEN to_char(lvl, 'HH24') BETWEEN '08' AND '16' THEN 'Y' ELSE 'N' END AS IN_GENERAL_BUSINESS_HOURS_FLAG

FROM TIMEgen ) b

Oracle seconds to hours minutes seconds

This code converts seconds to hours, minutes, and seconds.

select to_char(trunc(/60/60),'09') ||
to_char(trunc(mod(,3600)/60),'09') ||
to_char(mod(mod(,3600),60),'09')
from dual;
I use SQL*Plus to show how to use it:

SQL> variable n number

SQL> exec :n := 3000;

PL/SQL procedure successfully completed.

SQL> select to_char(trunc(:n/60/60),'09') ||
2 to_char(trunc(mod(:n,3600)/60),'09') ||
3 to_char(mod(mod(:n,3600),60),'09')
4 from dual;

TO_CHAR(T
---------
00 50 00

SQL> exec :n := 45

PL/SQL procedure successfully completed.

SQL> /

TO_CHAR(T
---------
00 00 45

SQL> exec :n := 3770;

PL/SQL procedure successfully completed.

SQL> /

TO_CHAR(T
---------
01 02 50