I have some SQL scripts that I'm trying to automate. In the past I have used SQL*Plus, and called the sqlplus binary manually, from a bash script. However, I'm trying to figure out if there's a way to connect to the DB, and call the script from inside of the bash script... so that I can insert date and make the queries run relative to a...
I started using sqlplus for emacs. It works fine except one thing - very often I get a message "Buffer ... is not talking to anybody". In a file sqlplus.el there is the following code which verifies (get-buffer-process process-buffer-name). How can I keep alive the sql process? (defun sqlplus-verify-buffer (connect-string) (let ((outp...
I really should know this, but would someone tell me how to change the default database on Linux? For example: I have a database test1 on server1 with ORACLE_SID=test1. So, to connect to test1 I can use: sqlplus myuser/password Connects to the default database, test1 I would now like the default sqlplus connection to go to database...
Hi, I'm using SQL Plus 11.1.0.6.0 to run a script that performs batch inserts into an Oracle 10g db. The problem i noticed is when inserting some code string into a clob field that has some lines with trailing spaces such as: ....public void myMethod().... --trailing space here ....{ ........int myVar = 1; ........ -- empty line with t...
Is it possible to do something like this? $ sqlplus -s user/pass "select 1 from dual" or $ echo "select 1 from dual" | sqlplus -s user/pass I know I can put select 1 from dual in a file and do this: $ sqlplus -s user/pass @myFile.sql but I'm wondering if it's actually necessary to create a file just to satisfy sqlplus ...
I have a PL/SQL statement that uses EXECUTE IMMEDIATE to execute a query. However, I'm having difficulty figuring out how to even get the text of the query that's being executed. I can't use dbms_output as the query is greater than 255 characters. Is there any way to make sqlplus echo the string that's passed in to EXECUTE IMMEDIATE? ...
I need to format csv output file from a sqlplus query. The char and varchar output fields come out in the max column size in the table, which means a lot of columns have extra blanks, plus the comma seperator. How can I get rid of the extra blanks in my csv file? Also how can I get "" around the numeric fields? ...
I am having a prior knowledge of shell scripting. I am trying to connect to a table using SQL*Plus and fetch the data in a Perl script and store that output in a Perl variable. Normally in a shell script I would do like below: SQL_RESULT=`sqlplus -s ${CONNECT_STRING} << EOF ${SQLPLUS_SETTINGS} select foo||'|'||bar ||'|'|| xy...
Note the table below. I am wanting to set the default value for the newly created BEST_SELLER column to "N". How do I go about doing that? Create Table Mystery (Book_Code Char(4) Primary Key, Title Varchar2(40), Publisher_Code Char(2), Price Number(4,2)) ...
Hi , i have a script which simply connect to the table in sql*plus and inserts a row in the table. it is throwing an error as below: SP2-0552: Bind variable "BIND" not declared i am not able to figure out exactly what the bind variable is in the query that it is trying to insert. ...
I need to find the book code and book title for each book whose price is greater than the book price for every book that has the type 'HOR'. My table looks like this CREATE TABLE BOOK ( BOOK_CODE CHAR(4) CONSTRAINT BOOK_BOOK_CODE_PK PRIMARY KEY, TITLE VARCHAR2(40) CONSTRAINT BOOK_TITLE_NN NOT NULL, PUBLISHER_CODE CHAR(2) CON...
I'm producing an HTML report from a query using: set markup html on table "WIDTH='100%' BORDER='1' cellpadding='2px' cellspacing='0px'"; Is there a way of including a doctype declaration such as: <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> I've searched but I can't ...
OK, I am using SQL Plus and I am trying to view the table and one of the columns I was to view in lower case. This shold be very easy but for some reason it is not work. The code I am using is SELECT CUSTOMER_NUM, CUSTOMER_ADD (LOWER)CUSTOMER_FIRST, (UPPER)CUSTOMER_LAST FROM CUSTOMER; The error I am getting is ORA-00904: "CUSTOMER_...
Hi All, Please tell me what is the use of TNS_ADMIN parameter in Oracle? I am working on Unix using oracle database. Is this parameter is required to locate the sqlplus. I am executing a script in which a update query is executed on Oracle Database. The script fails with 127 error code when executed with crontab. The script content...
Hello I am trying to display the constraints in one of my tables but for some reason I get the message no rows selected. Noted below is the table I have created. Create table Teams ( TeamID varCHAR2(4) constraint Teams_TeamID_PK Primary Key, TeamName VARCHAR2(40) ); This is the code I am using to show my constraints. SELECT c...
I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: select * from table_name where column_having_index="some value"; The query takes too long, and you find out that the index is not being used. If you think the performance of the query will be bett...
Hi, there, I just installed Oracle, and I am working with SQL*Plus because the GUI tool, SQL Developer, won't work. I need to run some scripts, and I found that after starting SQL*Plus I couldn't navigate from a session to another directory. The instructions that I did find after I searched on the internet all talk about a gui interfac...
I have several scripts that I would like to start from a menu presented to the SQLPlus user. Something like: Please make a selection: 1: Do script a 2: Do script b 3: Do script c I just need a point in the right direction, not a quick answer. ...
hello, i am trying to count the number of riders in my data. and i am having trouble figuring this out. sample of my output is noted below. The data comes from many different tables and I had to join the tables which is not the problem I am having. I am trying to get the count number of RIDERS by EVENT by DESCRIPTION. And still disp...
Greetings, I am having problems with a line of code. I am trying to create a count function for a view that I created. I have done this a bunch of different ways but below is the format that I have most recently used. CREATE VIEW ERP_REPORT(EVENTNAME, DESCRIPTION, COUNT(RIDERS) AS SELECT EVENTNAME, RACES.DESCRIPTION, RIDERS_F...