Akdora’s Blog

Programming, Oracle, Life, Fun

Calling SQL and PL/SQL codes from Shell March 6, 2011

Filed under: Linux,Oracle — Akdora @ 11:24 am
Tags: , , , , , , , , ,

#################################################################################
#!/bin/ksh
##################################################################################
# Filename: test.sh
# Name    : Özay Akdora (https://akdora.wordpress.com)
# Date		: 04.03.2011
# Purpose : Oracle - Shell Integration (SQL Operations & PL/SQL Execution )
# Env			: This example is uses Oracle 11G with "/export/home/oracle/app/product/11.2c" path and korn shell script

# First of all we need to define some variable to access Oracle Database
export ORACLE_HOME=/export/home/oracle/app/product/11.2c
export LD_LIBRARY_PATH=/export/home/oracle/app/product/11.2c/lib
export PATH=/usr/local/bin:/usr/local/sbin:/usr/bin:/export/home/oracle/app/product/11.2c/bin:/export/home/ubtool/ubSQL_5.0.1:

# Then, we need to define username/password (@ optional another database TNS definition).
export db_connection_url=scott/tiger@MYDB
# I needed to connect to another database to query a table that has my configuration data.
# Your shell code may locate in an another machine that you want to connect.
# Anyway, You have to define TNS info of MYDB database to /export/home/oracle/app/product/11.2c/network/admin/tnsnames.ora

# You have to give permission to execute this file. For example : chmod 777 test.sh
# You also have to give permission, if you put this file to a folder that you created new.

# Run as : "./test.sh"

#######################################################################
# Let's see what we got here;
# Example 1: Access db and query SYSDATE:

echo "########## Example 1: SYSDATE ############"
RETVAL=`sqlplus -s $db_connection_url <<EOF
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') my_date from dual;
exit;
EOF`

echo $RETVAL
# As you see, we connected to database with sqlplus command ( We defined it's path to PATH above.
# Then we set username and password with $db_connection_url variable
# After that, we tell the command line execute what you see between EOF words 🙂 (Note: You can use ENDASDKAJSDK instead of EOF ) 🙂
# Finally, we set the date to $RETVAL variable and prints it to screen.
# Let me explain that : Think like this way. We are using SQLPLUS (in any environment) and executing some commands and getting some result on the screen
# In this example; We set everything to $RETVAL that we see the results of sqlplus screen. Everything!

#######################################################################
# Example 2 : SQLCODE
echo "########## Example 1: SQLCODE ############"
RETVAL=`sqlplus -s $db_connection_url <<EOF
whenever sqlerror exit sql.sqlcode;
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') my_date from dual;
exit;
EOF`

ERROR_CODE=$?

#Let me check the error code.
if [ $ERROR_CODE != 0 ]
then
  echo "There are some errors. ErrorCode: $ERROR_CODE";
else
  echo "Command executes successfully. ErrorCode: $ERROR_CODE";
fi

echo $RETVAL
#######################################################################
# Example 3 : SQL(Select) with WHERE clause
echo "########## Example 3: SQL with WHERE clause ############"

#Declare the variable
feed_id=1;

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select * from extract_external_feeds where feed_id='$feed_id';
exit;
EOF`

#echo $RETVAL
v_result=`echo -n $RETVAL|awk '{printf $4}'`
echo $v_result

# Let's see what we done here. 
# We added "set feed off echo off head off " line to command. So, I do want to see column names as result.
# In (feed_id='$feed_id') section do not forget to use (').
# We writes (awk '{printf $3}') the data of third column in my table.
# Note: We assumes that the data in the columns do not have any space like " " space  🙂
#######################################################################
# Example 4 : SQL(Select) with WHERE clause & Multiple Variables
echo "########## Example 4: SQL with WHERE clause ############"

#Declare the variable
feed_id=1;

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select ROW_LINE from EXTRACT_EXTERNAL_FEEDS_VW where feed_id='$feed_id';
exit;
EOF`

#echo $RETVAL
var1=`echo -n $RETVAL|awk -F\# '{printf $1}'`
var2=`echo -n $RETVAL|awk -F\# '{printf $2}'`
var3=`echo -n $RETVAL|awk -F\# '{printf $3}'`
echo $var1
echo $var2
echo $var3

# Since, I do want to make query for each column value, I return them with and deliminater like "#". The we parse it with awk command in shell.
# SELECT FEED_ID, FILE_PATH, MAIN_PATH, DB_VIEW_NAME FROM EXTRACT_EXTERNAL_FEEDS
#       
# CREATE OR REPLACE VIEW EXTRACT_EXTERNAL_FEEDS_VW AS
#  SELECT FILE_PATH || '#' || MAIN_PATH || '#' || DB_VIEW_NAME AS ROW_LINE, FEED_ID
#    FROM EXTRACT_EXTERNAL_FEEDS

#######################################################################
# Example 5 : SQL (Select) with dynamic column names
echo "########## Example 5 : SQL (Select) with dynamic column names ############"

feed_id=1;
column_name='DB_VIEW_NAME';

RETVAL=`sqlplus -s $db_connection_url <<EOF
set feed off echo off head off 
select $column_name from EXTRACT_EXTERNAL_FEEDS where feed_id='$feed_id';
exit;
EOF`

echo $RETVAL
# We can pass variables to SQLPLUS commands like ($column_name)
#######################################################################
# Example 6 : Calling PL/SQL 
echo "########## Example 6 : Calling PL/SQL ############"

RETVAL=`sqlplus -s $db_connection_url <<EOF
var x number ;
exec  pk_my_package.prc_my_proc(1,1,:x);
print x;
exit;
EOF`

echo $RETVAL
# We can call PL/SQL codes as we work in SQLPLUS and read the outputs
#######################################################################
# Example 7 : Calling Dynamic PL/SQL 
echo "########## Example 7 : Calling Dynamic PL/SQL ############"
dynamicCode1="declare v_date date; begin select sysdate into v_date from dual; dbms_output.put_line(to_date(v_date,'dd.mm.yyyy hh24:mi:ss') ); end; ";
dynamicCode2="
var x varchar2(2000); 
exec prc_my_proc;
print x;";

RETVAL=`sqlplus -s $db_connection_url <<EOF
set serveroutput on;
$dynamicCode2
exit;
EOF`

echo $RETVAL
# We can also set dynamic variable that contains PL/SQL commands, but you have to set the variable with new lines to execute them successfully.
# If you set the variable like $dynamicCode1. The code does not work. You have to do it as $dynamicCode2. You may read this code from an config file or oracle table.
#######################################################################

# Finally, if we want to do fancy. The following is a way one can describe this script as Oracle job. 
# 
#	BEGIN
#		DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME        => 'akdora_test',
#									  PROGRAM_TYPE        => 'EXECUTABLE',
#									  PROGRAM_ACTION      => '/export/home/bonjovi/ozay/test.sh',
#									  NUMBER_OF_ARGUMENTS => 0,
#									  ENABLED             => TRUE,
#									  COMMENTS            => 'Fantasy Program');
#	END;
#	/
#
#
#
# I hope this entry is helpful for you.
# Cheers

Advertisements
 

ORA-06508 PL/SQL: could not find program unit being called September 11, 2009

When you search this error on the net, you will find out these :

Cause: An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.
Action: Check that all referenced programs, including their package bodies, exist and are compatible.

Yes, that’s true. But if you have lots of objects depends to a lot of object 🙂 You will need to more information to fix this error. If you face up with this error, you probably use SQLCODE and SQLERRM in your exception block of your PL/SQL code. Something  like this:

BEGIN
 NULL;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 INSERT_LOG(SQLCODE, SQLERRM);
END;

I do not advise to handle errors in this way. Because this error description does not give you enough information abour your error as ORA-06508 error. You don’t really know what triggered this exception. Operation of this code on a production database is also not easy. At this point, Oracle’s DBMS_UTILITY package will be included in out lives. 🙂

If you write something like this:

(more…)

 

DBMS_APPLICATION_INFO package February 11, 2009

Filed under: PL/SQL — Akdora @ 7:15 am
Tags: , , , , , ,

In a project I had to retrieve some data from about 30 different tables and put them together to a new table. I had a main query and retrieve others data by eacy row of this query. It’s a kind of a master query.
This process will be worked per day.
Whhile I was testing my code, I had some problems. Because, We will read a thousand row and I do not know which row is processing. If an error occures, It may lock my PL/SQL development tool.
(The row that retrieving current data may locks in some queries because of performance problems or other problems). It was irritating.

I cannot use dbms_output.put_line because it shows us the result after completing the job. But the test procedure also do not response 🙂
In this moment I met with DBMS_APPLICATION_INFO package.

If you work with a complex code and get the current information to debug in any environment (not only dev), you can use dbms_application package.

you just set any value in anywhere of the code:
dbms_application_info.set_client_info(client_info => crow.case_history_id);

For example:

DECLARE
     CURSOR c1 IS
          SELECT * FROM all_source;
BEGIN
     FOR lrowc1 IN c1
     LOOP
          dbms_application_info.set_client_info(client_info => lrowc1.text);
     END LOOP;
END;

You can see the current client value in two method:

1.
SELECT t.client_info, t.* FROM v$session t --WHERE t.username = USER;
2.
DECLARE
     x VARCHAR2(100);
BEGIN
     dbms_application_info.read_client_info(x);
     dbms_output.put_line(x);
END;
You may see the other uses of this package
http://tonguc.wordpress.com/2006/12/29/expanded-supplied-packages-with-10g-part-2/
http://www.psoug.org/reference/dbms_applic_info.html
 

Oracle VARCHAR2 vs. CHAR February 9, 2009

Filed under: Oracle,PL/SQL — Akdora @ 8:21 am
Tags: , , , , , , ,

In Oracle, If we want to store some string, we uses VARCHAR2 type or its subtypes.

SQL> set serveroutput on
SQL> desc sys.standard;
Element                          Type     
-------------------------------- ---------
......
VARCHAR2                         TYPE     
VARCHAR                          SUBTYPE  
STRING                           SUBTYPE  
LONG                             SUBTYPE  
RAW                              SUBTYPE  
"LONG RAW"                       SUBTYPE  
ROWID                            SUBTYPE  
CHAR                             SUBTYPE  
CHARACTER                        SUBTYPE  
......
SQL>

Most commonly used ones of these types are VARCHAR2 and CHAR types. In this post, we will compare them.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
 
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE prc_compare_varchar2_char IS
  2       v_char_ex1     CHAR(4) := 'TEST';
  3       v_varchar2_ex1 VARCHAR2(4) := 'TEST';
  4 
  5       v_char_ex2     CHAR(40) := 'TEST';
  6       v_varchar2_ex2 VARCHAR2(40) := 'TEST';
  7  BEGIN
  8   (more...)
 

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…)

 

Search In Oracle Tables :) February 5, 2009

Filed under: PL/SQL — Akdora @ 3:06 pm
Tags: , , , , , , , ,

After I write searching code in PL/SQL blocks. I also  want to add this post.

If you want to find a specific data in Tables, you may check this page:

http://www.adp-gmbh.ch/ora/misc/find_in_tables.html

SQL> set serveroutput on
SQL>
SQL> begin
  2    dbms_output.put_line(find_in_schema(val => ‘147 Spadina Ave’));
  3  end;
  4  /
 
  rowid: AAAC88AAEAAAAAnAAI in LOCATIONS
Success
 
PL/SQL procedure successfully completed
 
SQL>

 

Non-Predefined Exception Example

Filed under: PL/SQL — Akdora @ 11:47 am
Tags: , , , ,

One of my friend asked me following question.

“I want to drop two columns of a table. So I wrote a basic drop command.

alter table t_test drop (col_name1, col_name2)

However, If this command is called more than once. I do not want an error message. What can I do for this?”

It’s a strange request, but this will learn us how to write a non-predefined  exception to solve this kind of requests. We can handle ORA-XXXXX exception as we want. We name them this numbered exceptions and handle them.  I will write a post about exception types as soon as possible. 

 When we try to drop a column that does not exists on the table. We get following error: ORA00904: invalid column name. It’s error number “-904”. We will use this number to define the exception.

(more…)