Akdora’s Blog

Programming, Oracle, Life, Fun

How to write,create Excel files via PL/SQL and save the file to a directory? February 6, 2009

In this post, I will write a PL/SQL package that helps us to write a EXCEL file in XML format. Then, we will save it some directory. This is a basic way to create an Excel file. There is no formatting type in the cells. We will just write something in the cells. I will try to develop this package with the formatting commands. Anyway, before we write our package, we shoul know about something oracle’s directory policy. We should tell to ORACLE, I have a directory path and I will name it. I mean, we will use create directory command of oracle.

 CREATE OR REPLACE DIRECTORY MY_ORACLE_DIR AS 'C:\oracle_test\';

To run this command, we have the following privileges;

 GRANT CREATE ANY DIRECTORY TO HR; >>>>>HR is the schema name!

On the other hand, you created an directory in another schema. You should grant  the directory to your shema.

GRANT read, write on DIRECTORY MY_ORACLE_DIR to HR;

After defining the directory to oracle, we can write our package. I passed a variable referencing the XML body of the file as CLOB in the procedures.

CREATE OR REPLACE PACKAGE pkg_excel_export IS
/**
 *  @author  : Özay AKDORA
 *  @version : 1.0
 *
 *  Name of the Application         :  pkg_excel_export.sql
 *  Creation/Modification History   :  5-Jan-2009
 *
 *  Overview of  Package/Sample     :Create Excel files via PL/SQL
 *           write the file to a directory
 * 
 **/
     PROCEDURE excel_open(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE excel_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE worksheet_open
     (
          l_xml_body      IN OUT NOCOPY CLOB,
          p_worksheetname IN VARCHAR2
     );
     PROCEDURE worksheet_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE row_open(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE row_close(l_xml_body IN OUT NOCOPY CLOB);
     PROCEDURE cell_write
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_content  IN VARCHAR2
     );
     PROCEDURE excel_get
     (
          l_xml_body IN OUT NOCOPY CLOB,
          p_filename IN VARCHAR2
     );
     PROCEDURE prc_write_file
     (
          p_filename IN VARCHAR2,
          p_dir      IN VARCHAR2,
          p_clob     IN CLOB
     );
END pkg_excel_export;
/

(more…)

Advertisements
 

Calling a web service by PL/SQL (utl_http) August 3, 2007

Filed under: Oracle — Akdora @ 1:49 pm
Tags: , , , , , , , , , ,

Last day, a mission is given to me about calling a web services by pl/sql. Firstly, i started to search ; “What is a web service?” The term Web services describes a standardized way of integrating Web-based applications using the XML, SOAP, WSDL and UDDI open standards over an Internet protocol backbone. Actually we can summarize it;

Web Services can convert your applications into Web-applications.
By using Web services, your application can publish its function or message to the rest of the world.

Web Services can be used by other applications.
With Web services your accounting department’s Win 2k servers can connect with your IT supplier’s UNIX server.

The basic Web Services platform is XML + HTTP.
Web services uses XML to code and decode your data and SOAP to transport it.

We have some input parameters and make a request. Then, we get a response from it with output parameters. All this syntax is in XML format. So, we will use XMLtype in our pl/sql package.

 

Let’s start to write a soap access package or we can use this package 🙂 (www.oracle-base.com/dba/miscellaneous/soap_api.sql)

🙂 Let’s assume we have a function “myFunction” in this page http://www.mywebservice.com/web 🙂 Then,start to write our code

 

 

 CREATE OR REPLACE PACKAGE pkg_my_webservice IS
     FUNCTION call_myfuntion
     (
          vp_parameter1 VARCHAR2,
          vp_parameter2 VARCHAR2
     ) RETURN VARCHAR2;
END pkg_my_webservice;
/
CREATE OR REPLACE PACKAGE BODY pkg_my_webservice IS
     vg_funciton_fnc VARCHAR2(256) := 'myFunction';
     vg_ws_address   VARCHAR2(255) := 'http://www.mywebservice.com/web';
     FUNCTION call_myfuntion(
                             --lets assume that it inputs two parameters called string1, string2
                             vp_parameter1 VARCHAR2,
                             vp_parameter2 VARCHAR2)
     
      RETURN VARCHAR2 AS
          ol_req  soap_api.t_request;
          ol_resp soap_api.t_response;
     BEGIN
          -- we initilize a new request
          ol_req := soap_api.new_request(vg_funciton_fnc,
                                         'xmlns="' || vg_ws_address || '"');
          -- we started to add parameters
          soap_api.add_parameter(ol_req,
                                 'string1',
                                 'partns:string',
                                 vp_parameter1);
                                 
          soap_api.add_parameter(ol_req,
                                 'string2',
                                 'partns:string',
                                 vp_parameter1);
                                 
          -- we call the web service
          ol_resp := soap_api.invoke(ol_req, vg_ws_address, vg_funciton_fnc);
          -- we get back the results
          RETURN soap_api.get_return_value(ol_resp,
                                           'result', -- result tag name
                                           'xmlns:m="' || --can be change as "xmlns:n1"
                                           vg_ws_address || '"');
     
     END call_myfuntion;
END pkg_my_webservice;
/

 

pkg_my_webservice_blog.txt

You may need to change some tag attributes. The main basic function syntax is like this. If you have more than one return xml tags, then you need to call the get_return_value function as much as result attributes. You may also store the results in a pl/sql type and return it back.

There are also other ways to do this. You may also use oracle’s “utl_dbws” package, however “utl_http” is more faster than it. You can test it.

In my next post, i am gonna explain soap_api package in more details.