Akdora’s Blog

Programming, Oracle, Life, Fun

ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations March 21, 2011

Filed under: Oracle,SQL — Akdora @ 9:40 am
Tags: , , , , ,

When we try to rename  table or index name in Oracle, we use a simple command as following;

alter table [prev_table_name] rename to [last_table_name]

If we put into schema names to this command, we can deal with ORA-14047 error. This error raise when we put schema name in front of the last_table_name.

alter table hr.[prev_table_name] rename to hr.[last_table_name] >>> RAISES ORA-14047 ERROR
alter table hr.[prev_table_name] rename to [last_table_name] >> CORRECT ONE

 

It is same with Index renaming.

Advertisements
 

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

 

How to write to a path, make a FTP operation, send E-mail a SQL query result in Excel file via PL/SQL March 5, 2011

Filed under: PL/SQL — Akdora @ 10:08 pm

After I read some blog entries about exporting Excel files via PL/SQL recently, I also decided to share my Excel API code in PL/SQL. I wrote a similar article before .  This API has following features;

Most of the interface procedures accepts SQL query as parameter:

  • Creates an excel file content as CLOB variable (PRC_CREATE_EXCEL_BY_QUERY)
  • Writes any CLOB data to a specific folder that defined before with a filename. (PRC_WRITE_EXCEL_BY_MANUAL)
  • Writes any SQL query to a specific folder that defined before (PRC_WRITE_EXCEL_BY_QUERY)
  • Sends e-mail any CLOB data as attachment with TO, CC, BCC features and with also some specific words with spaces in sender name like “John Smith Company”. (PRC_EMAIL_EXCEL_BY_MANUAL)
  • Sends e-mail with SQL query result in Excel file as attachment (PRC_EMAIL_EXCEL_BY_QUERY)
  • Writes any CLOB data to a FTP path as file. (PRC_FTP_EXCEL_BY_MANUAL)
  • Writes SQL query result in Excel file to a FTP path. (PRC_FTP_EXCEL_BY_QUERY)
  • Downloads SQL query result in Excel file from APEX application using WPG_DOCLOAD package. (PRC_DOWNL_EXCEL_FILE)
  • You can also create dynamic Excel file content in CLOB variable. For example;


DECLARE
MYEXCELCONTENT CLOB;
BEGIN
-- Test statements here
PK_EXCEL_API.G_SHOW_BORDER := 'Y';
PK_EXCEL_API.EX_EXCEL_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_WORKSHEET_OPEN(MYEXCELCONTENT, 'test');

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'asdasdasd');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_ROW_OPEN(MYEXCELCONTENT);
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'INVOICE NUMBER');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'SEQ NO');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'VERSION..');
PK_EXCEL_API.EX_CELL_WRITE(MYEXCELCONTENT, 'BLA BLA BLA');
PK_EXCEL_API.EX_ROW_CLOSE(MYEXCELCONTENT);

PK_EXCEL_API.EX_WORKSHEET_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.EX_EXCEL_CLOSE(MYEXCELCONTENT);
PK_EXCEL_API.PRC_WRITE_EXCEL_FILE(P_CLOB => MYEXCELCONTENT,
P_FILENAME => 'test.xls',
P_DIR => 'UTL_FILE_DIR');
END;

Here is the all package here: PK_EXCEL_API_v1.0

(Note:Since I could not upload directly txt files to wordpress, I put the code into a odt file and uploaded here)

 

ORA-02064: Distributed Operation not Supported September 11, 2009

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

After a short investigation, I did not see this comment:

We can not run a PL/SQL block contains a DDL (Data Definition Language)  command via a db_link 🙂

ORA-02064: distributed operation not supported
ORA-06512: at "FRAUD.FN_FCMS_BTG", line 3
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
7000000c984d720        10  anonymous block

On DB A we write such a funciton:

CREATE OR REPLACE FUNCTION FN_FCMS_BTG RETURN NUMBER IS
BEGIN
 EXECUTE IMMEDIATE 'create table aaaaaaa as select * from f1_segment_1';
 RETURN 1;
END;

Then, we call it from DB B:

DECLARE
 I NUMBER;
BEGIN
 I := FN_FCMS_BTG@MY_LINK;
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK ||
 DBMS_UTILITY.FORMAT_CALL_STACK);
END;

NOTE: Since TRUNCATE is a DDL command, we cannot also use it in the same way 😉

(more…)

 

ORA-06508 PL/SQL: could not find program unit being called

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

 

Sending mail with CLOB attachment August 13, 2009

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

I needed to send an e-mail with Excel file attachment. I realize that there is not enough documentation about sending mail in CLOB filetype attachment.

Here is our procedure:

CREATE OR REPLACE PROCEDURE SENDMAIL_ATT
(
 MSG_FROM     VARCHAR2,
 MSG_TO       VARCHAR2,
 MSG_SUBJECT  VARCHAR2,
 MSG_TEXT     VARCHAR2,
 MSG_ATT      CLOB,
 ATT_FILENAME VARCHAR2
) IS

 V_MAILHOST VARCHAR2(50) := 'mailhost';
 V_PORT     NUMBER(2) := 25;
 V_HELO     VARCHAR2(50) := 'localhost';
 (more...)
 

Rules of Precedence in SQL Where Clause February 18, 2009

Filed under: SQL — Akdora @ 8:49 pm
Tags: , , ,

Expressions in where clause process in the following order:

Number Expression
1 Arithmetic Operators
2 Concatenation Operator
3 Comparision Condition
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not Equal To
7 NOT Logical Condition
8 AND Logical Condition
9 OR Logical Condition

 

For example on AND and OR conditions:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
 
SQL> set serveroutput on
 
SQL>
SQL> SELECT t.employee_id, t.first_name, t.last_name, t.salary  ,t.manager_id
  2    FROM employees t
  3   WHERE t.salary > 15000
  4         AND t.manager_id = 100
  5         OR t.manager_id = 103;
 (more...)