I have a view connecting 4 tables CREATE VIEW BookCopyInfo AS SELECT bc.BookCopyID, b.BookTitle, m.FirstName || ' ' || m.LastName AS BorrowedBy, l.expectedReturnDate, (SYSDATE - l.expectedReturnDate) AS NoOfDaysLate FROM Book b, Member m, Lending l, BookCopy bc WHERE b.BookID = bc.BookID AND l.MemberID = m.MemberID AND l.BookCopy...
I have an Oracle 9i DB installed at remote IBM AIX server. I want to connect to it using C# app (.Net) Presently, I am able to connect to it using SQL Developer and SQLPlus from my machine. But when I try to connect from Visual Studio App, using System.Data.OracleClient. private static string GetConnectionString() { ret...
When I run a sql statement which supposed to return exact one row, sqlplus print column names many many times ? why ? ...
I am using sqlplus on a windows environment. At the moment i run sqlplus from a specific directory. I have several scripts that are in several places on my machine. Here are example commands i use within sqlplus to execute some of these scripts. sqlplus> @../../scripts/oracle/viewdbajobs.sql sqlplus> @../../../../../devhome/admin/scrip...
In the following example, variable recordId number; BEGIN SELECT MAX(recordvalue) INTO recordId FROM sometable; END; PRINT recordid; SELECT * FROM someothertable WHERE recordkey = &recordId; The select statement on the last line cannot access the value of recordId. I know i can access recordId inside the pl/sql bl...
I have PL/SQL function that returns cursor that holds 28 columns and 8100 rows. When I execute that function from SQL Plus I got the results right away and in SQL Developer I'm running script that takes looong time (about 80 seconds). The same happen from Java code. When number of columns reduced to 2 then I got response in less than 4...
I have a query that concatenates (manually by ||) a handful of columns, one of which is an XMLTYPE field (calling getClobVal() on it) . If I spool the query output using sqlplus with the following options set: set long 30000; set pagesize 0; set feedback off; I get all of the content as desired, but with cr/lf's after 80 characters, ...
Sometimes a query on SQL*Plus might yield too many rows t fit on the screen. Is there some equivalent of "piping to less/more" mechanism that I can do to navigate the results? select * from emp | less ...
For example, is there equivalent of these in SQL*Plus sqlplus 'SELECT * FROM emp' | less sqlplus 'SELECT * FROM emp' | grep Primx One way has been suggested by paxdiablo here. Is that the only way? ...
How to make sqlplus in Linux to maintain history of my queries? So that I could use up/down arrows to get my previous query. ...
Is there some way to get a list of all the indexes on a particular table using SQL*Plus? I created a table CREATE TABLE temp( id NUMBER PRIMARY KEY, name VARCHAR2(20)); There should be an implicit index created on the primary key (id). How can I see that index? SELECT * FROM all_indexes WHERE table_name = 'temp'; gives no rows se...
Is it possible to have a look at what is there inside an index using SQL*Plus? If I have a table like this: Table A ------------------------ rowid | id name 123 | 1 A 124 | 4 G 125 | 2 R 126 | 3 P where id is the primary key, I expect the index to be something like this index on A.id ------------- id ...
I am practicing SQL, and suddenly I have so many tables. What is a good way to organize them? Is there a way to put them into different directories? Or is the only option to create a tablespace as explained here? ...
Using sqlplus 11g on windows to run sql script on 11g database. Sqlplus hangs if insert line left in script. If I comment out the insert line, the script returns the records. Script exec time is < 1 sec when run in sql developer. I have tried to make the insert statment one continuous line with same resutls. using echo on, it hangs r...
I need to take information from two different data bases. select * from TABLE_ONDB2 where column_on_db2 in ( select column_on_db1 from TABLE_ONDB1 ); Problem is both are on different db instances so I am not able to figure out how to put table names and column names etc. I hope my question is clear. ...
I have a table with this structure: date colname value ---------------------------- date col1name col1's value date col2name col2's value date col3name col3's value Now I need to convert it into this structure: date col1name col2name col3name ----------------------------------------------- date col1's ...
I have write the following pl/sql program and unable to dectect the error : declare variable a number; b number:=2354; begin b:=:a; end; the error in this is SP2-0552: Bind variable "A" not declared. plz help ... ...
Hello, I am computing the sum of multiple columns on the same break, but the output only displays a dash (-) underline between the last record and the column totals for a single column only...and it appears to always be the second column upon which the sums are being computed. Is there a reason for this and a way to apply the underline...
Does SQL*Plus provide the help pages for its commands? Is there a way to access the help text from the SQL*Plus prompt, like this text for STARTUP? I tried SQL> man startup SP2-0734: unknown command beginning "man startu..." - rest of line ignored. SQL> startup -help SP2-0714: invalid combination of STARTUP options SQL> startup --help ...
After doing DEFINE_EDITOR=vi . I wrote SQL > edit sample.sql This is being stored in Jagan/Documents by default. But i want this to be stored in Jagan/Documents/plsql. Can anybody tell how to do this.. ...