Tag Archives: sqlplus

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, sqlplus, unix

Oracle – SQL*PLUS does not show the SQL being executed from a script file.

Leave a comment

Oracle SQL*PLUS utility provides several options to customize the display of information from your database session. In this article we will review how to display the DML and DDL statements which are stored in an OS layer file upon execution. By default SQL*PLUS will not display the DML or DDL statement from a script file.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called test.sql with the following SQL.

select * from v$instance;

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Apr 23 22:00:09 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Execute the SQL file test.sql with the SQL*PLUS command: @./test.sql

SQL> @./test.sql

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL>

NOTE: The SQL contained within the file test.sql is not displayed, thus the end user executing such a file may not be aware of the output they are looking at.

5. To remedy this situation we will have to setup our SQL*PLUS environment to display the commands within our file. To do this we will have to turn two options on: ECHO and FEEDBACK.

SQL> set echo on
SQL> set feedback on
SQL>

6. Now re-execute the file test.sql with the SQL*PLUS command: @./test.sql.

SQL> @./test.sql
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
mylinux
10.2.0.4.0 23-APR-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

1 row selected.

SQL>
SQL>

As show above, SQL*PLUS is now displaying the SQL contained within our file, before the output. This will allow the end user to know exactly how the output of our script file was produced, thus turning our script into more usefully information.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, script, SQL, sqlplus

Oracle – Viewing errors generated when writing a PL/SQL program unit.

Leave a comment

When you create a PL/SQL block of code within Oracle in the form of a procedure, function, or package and a compilation error occurs; Oracle will not immediately give you the error details. This article details the procedure to view errors generated by a PL/SQL block within SQL*PLUS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:00:09 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Set FEEDBACK and ECHO to on and execute the file create_file.sql.

SQL> set echo on
SQL> set feedback on
SQL> @./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Warning: Procedure created with compilation errors.

SQL>

5. The procedure was created with compilation errors, however it does not show you the errors. To see the errors generated in your procedure creation statement, use the SQL*PLUS command: show errors

SQL> show errors
Errors for PROCEDURE TEST_FILE:

LINE/COL ERROR
——– —————————————————————–
10/1 PL/SQL: Statement ignored
10/1 PLS-00201: identifier ‘V_WRITE’ must be declared
12/1 PL/SQL: Statement ignored
12/19 PLS-00201: identifier ‘V_WRITE’ must be declared
14/1 PL/SQL: Statement ignored
14/17 PLS-00201: identifier ‘V_WRITE’ must be declared
SQL>

6. Now you can see that the procedure creation statement is missing the definition for the variable V_WRITE. To correct this error replace the contents of your create_file.sql file with the following text, where the variable v_write is defined as utl_file.file_type.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

7. Re-enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file create_file.sql.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Apr 22 19:43:17 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

The procedure now compiles without error, through the use of the SQL*PLUS command ‘show errors’ which allowed us to see the detailed error message provided by the Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

linux, Oracle, Oracle 10g, oracle 9i

SQLPLUS does not allow connect as sysdba:

Leave a comment

In Oracle configuration files and system parameters there exist certain setting which will prevent the Oracle software owner from connecting to the database with sysdba privileges. In this article we will discuss a SQLNET.ORA setting which will result in this error. This article was written using a linux environment; however the result will be very similar on any OS.

1. Logon to your Oracle server as the Oracle software owner and attempt to logon to sqlplus with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Oct 19 19:00:46 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

2. Exit out of sqlplus by hitting return twice.

3. Change directory to $ORACLE_HOME/network/admin.

mylinux:>cd $ORACLE_HOME/network/admin
mylinux:>

4. Open the file sqlnet.ora and comment out the line sqlnet.authentication.

mylinux:>vi sqlnet.ora
“sqlnet.ora” 2 lines, 59 characters
#sqlnet.authentication_services=none

5. Save your changes and exit your editor.

6. Now try to connect to sqlplus with sysdba privileges as the oracle software owner.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Oct 19 19:05:01 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

You have successfully connected with SYSDBA privilege.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, unix

UNIX CRON – Shell script errors with [10]: sqlplus: not found

Leave a comment

In normal administration of an Oracle RDBMS on UNIX or LINUX operating systems, the DBA will be required to develop shell and SQL scripts to maintain his/her RDBMS. In fact, to be considered a mid to senior DBA you will need to be well versed in scripting at the OS layer. In this article we will review a common error which occurs in a shell script called by CRON due to a lack of properly set variables.

1. In a normal scenario, the DBA has developed a shell script which calls and executes a SQL script to perform a certain maintenance task. In testing, the DBA executes the script as the oracle software owner and the script performs perfectly. However, when placed in the OS CRON for execution, the following error occurs.

[10]: sqlplus: not found

2. The error occurs because the environmental variables are not being read. Thus, the OS does not know where to find the executable: sqlplus.

3. The solution is to update your shell script and hard code the directory structure for the command sqlplus. An example would be if sqlplus is located in the following path /u01/opt/app/oracle/bin then perform the following:

Replace:

sqlplus

With:

/u01/opt/app/oracle/bin/sqlplus

This completes resolving the error message [10]: sqlplus: not found in a shell script called through CRON.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle 10g, oracle 9i, SQL, sqlplus

Display the time at your SQL*PLUS Prompt

Leave a comment

Oracle SQL*PLUS provides the ability to change the default prompt in your session. This allows you to easily determine information about your current session without having to query the database. In this article we will review displaying the current system time at your SQL*PLUS prompt.

1. Connect to your database through SQL*PLUS.

SQL>connect ljcatt/xxxx@mydb
Connected.
SQL>

2. Execute the following SQL*PLUS command: set time on.

SQL>set time on
19:53:56 SQL>
19:53:58 SQL>

3. The time will now be displayed in your SQL*PLUS prompt.
4. To turn off the time execute the following SQL*PLUS command: set time off

19:53:58 SQL>set time off
SQL>
SQL>

5. This completes displaying the time at your Oracle SQL*PLUS prompt.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com