Akdora’s Blog

Programming, Oracle, Life, Fun

PL/SQL Developer User Preferences (Windows 7) March 30, 2011

Filed under: Non-technical — Akdora @ 6:56 am

If you changed your computer and want “your PL/SQL developer login history and preferences” as well in your new computer. Copy the following folder to your new computer in the same path format!

C:\Users\[username]\AppData\Roaming\PLSQL Developer

If you need only the login history, open the following file:

C:\Users\[username]\AppData\Roaming\PLSQL Developer\Preferences\[username]\user.prefs

In the file, copy [LogonHistory] part to your new user.prefs file. It’s encrypted but it does not matter. PL/SQL Developer will dencrypt it ­čÖé

I need this, because I deal with a lots of database with a lots of schema.. It is really hard to remember the passwords sometimes, so i let the PL/Developer to remember them ­čÖé (Preferences > Login History > Store with Password)

 

Advertisements
 

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.

 

How to remove spaces in a string in Shell March 8, 2011

Filed under: Shell — Akdora @ 9:45 am
Tags: , , ,

I experienced that when you connect to Oracle database from shell and get some data from the table, if the length of the column value is more than 80 letters. Shell puts space in every 80th letter automaticly. I do not know why, but it is really interesting. I counted the number 80 for my environment. It may differ in different systems.

Anyway, I need to remove that interesting spaces. So here is a script to do this.

RETVAL=`echo $RETVAL | sed 's/ //g'`

We use the SED command. Its use like ‘s/[seach_letter]/[replacement]/g’. In my example┬á[seach_letter] is ” ” space and┬á[replacement] is nothing. For example; If we want to replace ” ┬á” (space) characters with “_” (underscore) character. Here is how we do this:


RETVAL=`echo $RETVAL | sed 's/ /_/g'`

I wrote this quick entry, because if you do not remember how to do something like this and want to google it. It is not easy to find sometimes ­čÖé

 

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)

 

Reset APEX (Application Express) Password

Filed under: APEX — Akdora @ 9:19 pm

Run apxxepwd.sql file in the Root.

Check following entry:

http://www.mandsconsulting.com/resetting-oracle-apex-admin-password-video-walkthrough-tutorial

 

TROUG ─░lk Konferans─▒ Nisan Ay─▒nda! Sunum Yapmak ─░steyenler El Kald─▒rs─▒n! February 21, 2011

Filed under: Non-technical — Akdora @ 7:04 am

Uzun bir aradan sonra (Askerlik+Yo─čun Projeler) yeniden blog tutmaya karar verdim. Umar─▒m fazla bir ┼čey kaybetmemi┼čtim.

─░lk yaz─▒m─▒ Tongu├ž’un da kurucular─▒ndan oldu─ču TROUG (Turkish Oracle User Group) un Nisan ay─▒nda yap─▒lacak olan sunumu hakk─▒nda bilgilendirme i├žin yaz─▒yorum. Oracle’─▒n T├╝rkiye’de geli┼čimi konusunda Tonu├ž’un cidden epey bir eme─či var. Te┼čekk├╝rler sana.

http://tonguc.wordpress.com/2011/02/20/troug-ilk-konferans-nisan-ayinda-sunum-yapmak-isteyen-cesur-turk-oracle-uzmanlarina-duyurulur/