I have a query that returns a lot of data into a CSV file. So much, in fact, that Excel can't open it - there are too many rows. Is there a way to control spool to spool to a new file everytime 65000 rows have been processed? Ideally, I'd like to have my output in files named in sequence, such as large_data_1.csv, large_data_2.csv, large...
I have a script that gets the current time, and must pass it to another script. variable v_s_time varchar2(30); exec :v_s_time := to_char(sysdate,'YYYY-MM-DD HH:MI:SS AM'); --Lots of unrelated code here variable v_e_time varchar2(30); exec :v_e_time := to_char(sysdate,'YYYY-MM-DD HH:MI:SS AM'); @"test report script.sql" :v_s_time :v_e_...
Hi I'm installing Oracle XE and sqlplus on Oracle Enterprise Linux 5. What environment variables do I need? I think that both require different ORACLE_HOME and LD_LIBRARY_PATH. Thanks ...
Hi, I am new to PL/SQL, I'm trying to execute the commands that I learned at the course. VARIABLE area NUMBER DECLARE radius NUMBER(2) := &s_radius; pi CONSTANT NUMBER := 3.14; BEGIN :area := pi * radius * radius; END; I understand that I can run this using SqlPlus, but I remember my teacher was running this from the web brows...
Whenever I perform select statements in the command line tool it doesn't use all of the space. I've modified buffer size and window size and it just doesn't work. Here is the screenshot: ...
I am trying to track performance on some procedures that run too slow (and seem to keep getting slower). I am using v$session_longops to track how much work has been done, and I have a query (sofar/((v$session_longops.LAST_UPDATE_TIME-v$session_longops.start_time)*24*60*60)) that tells me the rate at which work is being done. What I'd l...
Hi, Anybody knows how to use sqlplus in ruby? I need to do something like this: system("sqlplus username/pwd@database filename.sql" Thx /Niklas ...
Hi, Im trying to use SET SCAN ON after as follows.. SET SCAN OFF; DECLARE -- declared a variable BEGIN --update statement END; SET SCAN ON; The use of SET SCAN ON; is causing the error when i try to run the script. The error captured ORA-06550: line 16, column 1: PLS-00103: Encountered the symbol "SET" 06550. 00000 - "line ...
When I navigate through the Oracle application with my application user and the right responsibility, I see the data. I use the "record history" menu to see which table/view is used by application. So, I got PA_EXPEND_ITEMS_ADJUST2_V. When I'm connected with apps user in a sqlplus session, SELECT * FROM PA_EXPEND_ITEMS_ADJUST2_V gi...
I'm having a problem trying to run my sql script into oracle using sqlplus. The script just populates some dummy data: DECLARE role1Id NUMBER; user1Id NUMBER; role2Id NUMBER; user2Id NUMBER; role3Id NUMBER; user3Id NUMBER; perm1Id NUMBER; perm2Id NUMBER; perm3Id NUMBER; perm4Id NUMBER; perm5Id NUMBER; BEGIN INSE...
I'm trying to create a release script that can be deployed on multiple databases, but where the data can be merged back together at a later date. The obvious way to handle this is to set the sequence numbers for production data sufficiently high in subsequent deployments to prevent collisions. The problem is in coming up with a release...
I have a file to execute in Ksh written by someone. It has a set of commands to execute in sqlplus. It starts with, sqlplus -s $UP <<- END followed by a set of ddl commands such as create,drop,etc., When I execute the file in the shell, I get the error in the starting line quoted above. I understand "-s" starts the sqlplus in silent...
Hi all, I need parser the output of sqlplus, for get list of errors of compilation of sql files, using C# Any sample, please ?? Thanks in advanced. Greetings ...
I want to change the delimiter: Can someone help me to change the delimiter in sqlplus in Oracle 11g CREATE OR REPLACE TRIGGER test_trigger BEFORE INSERT ON test REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT test_sequence.nextval INTO :NEW.ID FROM dual; END; / this is the trigger I want to create. but after Select statement...
Here is the way sqlplus displays my table data: but I want to show them as Name | Address | -------+---------------+ name1 | address1 | name2 | address2 | name3 | address3 | ...
Hi all, Is there any sql*plus command to remove \r \n and\t from the result set that's going out to the spool file? That is, "trim" every record? We've used set trim on in the past, but it doesn't seem to bue what we need right now. I'm trying to avoid calling oracle's translate, chr functions in the sql query. For example, set term...
Is there a way to display the output of a sqlplus command without having to first issue the spool off command? I am spooling the results of a sqlplus session to a file while at the same time tailing the file. The reason for this is that for table with very long rows the format is easier to look at from a file. The problem is to see the...
i want to delete all the data rows in the table. but without dropping the table. delete statement has a where clause so i have to give one by one for each row. is there any other way of deleting the whole data rows in the table. this is Oracle and using sqlplus ...
Background: Oracle Forms - DB migration; 6i to 10g Hi everyone! Customer situation: We want to attemp a DB connection with SQL*Plus 8 to a Oracle 10g DB (standard installation). Unfortunately SQL*Plus always crashes without any error message. We tried the same scenario with our systems - there was no problem. Now we are confused. W...
With the aim of reducing mouse activity i was wondering if there was such a command shortcut (eg CLS or Ctrl+L) to provide the SQL*Plus (?) "CLEAR SCREEN" command functionality found by clicking the rubber-on-end-of-pencil icon in Oracle SQL Developer to clear the lower "Results" sub-tab (...incorrect terminology i'm sure). ...