Oracle SQLPlus Query Output to a File
| Posted by watashii | Filed under Database, Programming, Unix
Here is a simple way to output SQL query output into a file, via command line based SQLPlus.
Step 1: Nominate the output file location. In this case this is a Unix location.
SQL> spool /tmp/output.txt
Step 2: Perform your SQL query.
SQL> select * from tab;
Step 3: Close the file.
SQL> spool off
Tip: Use set lines statement to adjust output display
Tags: log, oracle, sql, sqlplus, unix
Starting / Shutdown Oracle Database in Unix
| Posted by watashii | Filed under Database, Unix
This simple guide to shows how to start /stop your Oracle database from Unix. You must have logon access to do this.
Step 1
Unix login as oracle (usually the user that installed the database)
Step 2
Make sure your environment variables are set, eg:
ORACLE_SID=MYDBSID
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1
LD_LIBRARY_PATH=/opt/oracle/product/11.2.0/dbhome_1/lib
PATH=/usr/bin:/usr/sbin:/usr/ucb:/usr/local:/usr/local/bin:/usr/local/sbin:/opt/oracle/product/11.2.0/dbhome_1/binStep 3 – Start DB Instance
On the Unix command line, run sqlplus with sysdba, and simply type startup in the prompt to start it up.
oracle@db-srv-01 % sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 30 12:06:47 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 3207790592 bytes
Fixed Size 2152368 bytes
Variable Size 2449475664 bytes
Database Buffers 738197504 bytes
Redo Buffers 17965056 bytes
Database mounted.
Database opened.Step 4 – Start Listener
To start the oracle listener (assuming listener.ora is setup), run lsnrctl start
oracle@db-srv-01 % lsnrctl start
LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 30-NOV-2010 12:00:34
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /opt/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
System parameter file is /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/db-srv-01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-srv-01)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-srv-01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
Start Date 30-NOV-2010 12:00:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/db-srv-01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-srv-01)(PORT=1521)))
The listener supports no services
The command completed successfullyStep 4 – Shutdown
Similarly, to shutdown the instance, type shudown normal within sqlplus.
Stop the listener, by running lsnrctl stop
Tags: Database, oracle, shutdown, startup, unix
Start & Stop Weblogic Without Username & Password
| Posted by watashii | Filed under Database, Web
In a Weblogic Server domain running in production mode, username and password is always prompted during startup and shutdown (admin server and managed servers). To bypass this manual step, we can store the username and password credentials in the boot.properties file (although this is somewhat the same as development mode)…
Tags: encrypt, password, security, weblogic
Encrypt Passwords With Weblogic Server
| Posted by watashii | Filed under Database, Web
In a Weblogic Server environment, sometimes passwords must be unavoidably stored in text files, for example database connection details within JDBC configuration files. When running Weblogic in production mode, only encrypted password is accepted to ensure that the password is never exposed as clear text. To generate the encrypted password, the weblogic.security.Encrypt utility can be used, which generates a hashed password with a “{3DES}” prepended.
In Unix environment:
$ cd /opt/bea/user_projects/domains/mydomain/bin
$ . setDomainEnv.sh
$ java weblogic.security.Encrypt mycleartextpasswordhere
{3DES}xLLwrOm7asb4imZOYYwxz5TcBqDBEe6S
In Windows environment:
C:\>cd c:\bea\domains\mydomain\bin
C:\bea\domains\mydomain\bin>setDomainEnv.cmd
C:\bea\domains\mydomain\bin>java weblogic.security.Encrypt mycleartextpasswordhere
{3DES}lieJgIkTqg1xSBBoy3YxnHbPuz+tMxhY
Note that running the same command on different environment (or different Weblogic server installation) will produce a different password.
Tags: encrypt, oracle, password, security, weblogic
Oracle Timezone Conversions – GMT to localtime (and back)
| Posted by watashii | Filed under Database, Programming
GMT to Local Time
Convert a datetime value (which has no timezone info, but assumed as GMT/UTC) to a local datetime (based on timezone parameter as set by SESSIONTIMEZONE, eg ‘+11:00′):
SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'GMT')
AT LOCAL) AS DATE) "Local Time"
FROM DUAL;
Local Time
--------------------------------------------------
1/12/1999 10:00:00 PMTags: gmt, localtime, oracle, timezone, utc
BusinessObjects Universe Designer – Sysdate Value Object
| Posted by watashii | Filed under BusinessObjects, Database, Software
Creating SYSDATE value objects in a Universe (via Oracle connection) can simply be done as shown above. I have simply created a single-value dimension object, which can be used anywhere within a query (result/condition/sort). Having sysdate values allows the flexibility to create dynamic time-based reports. For example you can schedule a weekly report to extract data from the past 7 days starting from the current time the report was run.
Note, when parsing the object against the database (Oracle), an error will appear. ORA-00903: Invalid table name.
You can just ignore this error and press ok.
Tags: BusinessObjects, oracle, sysdate, universe
Oracle Index Null Values
| Posted by watashii | Filed under Database, ProgrammingWhen creating Oracle indexes, Oracle ignores the NULL values. For example, the following index was created over “emp_name” column.
create index emp_name_idx on employees (emp_name);
Querying for a NULL value on the indexed column would yield a full table scan.
select emp_name from employees where emp_name is NULL;
So how can we make Oracle perform a fast indexed search?
The solution is to index NULL values with a function-based index. The following example replaces all the NULL values with a string named ‘null’ (note, this can be any arbitrary string). This allows the NULL to be included as if it was a real value.
create index emp_name_idx on employees (nvl(emp_name,'null'));
Tags: index, oracle, performance
Oracle SQL Loader – Importing CSV files to a table
| Posted by watashii | Filed under Database, Programming
This post shows how to easily insert data from a CSV file into a database table, using the Oracle SQL Loader tool. We will only concentrate on importing CSV formatted data to the Oracle table with a 1-to-1 column mapping, otherwise I will need to write a book. Users can easily reuse my code, and just customise the columns to their needs.
Read the rest of this entry »
Tags: control file, import csv, oracle, sql loader
PL/SQL – Pipelined Table Function
| Posted by watashii | Filed under Database, ProgrammingNormally when writing PL/SQL functions, they return a single value result. But what if you want to return a collection type instead? The easiest way to do this is via a pipelined function, with the PIPELINED keyword, in which rows are returned by the function iteratively. It can be created at the schema level, or inside a package. I prefer the latter since its more portable, and easier to manage.
Inside the function, you return individual elements of the collection type, instead of returning the entire collection type when function ends. This also has performance benefits, depending on your application.
Tags: function, oracle, performance, pipelined
