I want to be able to insert a variable number of rows into a table based on user input? eg. Please enter value, enter "done" when no more values: value 1 Please enter value, enter "done" when no more values: value 2 Please enter value, enter "done" when no more values: done 2 Rows inserted successfully. I'm not sure how to store the ...
I have the following batch script: sqlplus ms/ms@orcl < drop.sql sqlplus ms/ms@orcl < create.1.0.sql This works fine when I double click on the bat file in Windows Explorer and run it. But when I type the command name from the DOS prompt I get an error: C:\>create.bat C:\>sqlplus ms/ms@orcl 0<drop.sql The handle is invalid. C:\...
How can I dump out the body of a function or a procedure when using sqlplus to connect to an oracle database? ...
When I have two sql-files, one of them in a sub-directory main_test.sql sub/sub_test.sql and sub_test.sqlcalls @../main_test.sql (or @@../main_test.sql) then this works fine when executing it from the sub-directory sub> sqlplus xxx @ sub_test.sql But when I call sub> cd .. > sqlplus xxx @ sub/sub_test.sql this results in SP2-03...
I need to export a table in the database to a tab separated values file. I am using DBI on Perl and SQL*Plus. Does it support (DBI or SQL*Plus) exporting and importing to or from TSV files? I can write a code to do my need, But I would like to use a ready made solution if it is available. ...
Hello I've got a problem accessing Oracle DB from our datacenter through a tunnel. We've got a pretty standard datacenter with one machine being accessible from the outside (I put it's IP in the /etc/hosts file as dc) and the Oracle DB inside. The IP address of our oracle database on internal network is 192.168.1.7 To create a tunnel I...
I'd like to run a sqlplus script from a cron job. I thought I could put a line like: CONNECT "myuser/mypass@mydb" within the script and then just execute it with: sqlplus @myscript However, when I do so, I get: SP2-0306: Invalid Option SP3-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus Am I misunderstandin...
I like a large linesize in SQL*Plus so that data doesn't wrap between lines. The problem is that doing a describe on an object seems to be obligated to spread itself over the entire line size. This makes it so that I can only see the name part without scrolling to the right. What I want is one linesize for describes and a different li...
Hi, I was asked in an interview,a question from oracle sql.this seemed to be a simple question but i had no clue to answer.could anybody help? if there is string like "newyork is a beautiful city" in a colum. select column_name from table_name; will result newyork is a beautiful city what is the query required to give the output...
Hi, I have a stored procedure that has an OUT parameter, indicating an error code. If the error code is not 0, then I raise an error DECLARE BEGIN foo (err_code); IF (err_code <> 0) THEN raise_application_error(...); END; So far so good, but here's my question. This piece of code (shown above) is executed by sqlplus, which is...
I have a file with the folowing script: BEGIN ... a bunch of inserts ... COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; When I execute this in sqlplus I get the following: SQL> @file.sql 382 It's as if he's not ending the block. I'm new to using pl/sql and sqlplus, so I don't know if I'm doing something wron...
I want to check the last time stats was run on my Oracle 10g server. I would normally do this via OEM, but for unrelated reasons OEM is down. Is there some way I can check this using just sqlplus? It would be extra helpful if the output was reasonably formatted. ...
I am using Solaris. I have to log into sql plus and run some queries, which give a huge result set. I want to copy all that into a file. Is there any command for it in unix or sqlplus ? ...
I have a few sql scripts that I need to run via SQL*Plus. These scripts connect several times as different users with a connect user_01/pass_01@db_01. Now, each time the script does such a connect, it confirms the successful connection with a connected. This is distracting and I want to turn it off. I can achieve what I want with a set...
Heya, I need to set up some SET options in Oracle SQLplus command line program each time I use it, such as SET HEADING OFF and the likes to beautify my results. I found that I always have to input each line separately so Set different options and this is becoming annoying since I need to access it many times a day. I found that there's...
I've got an SQL-script executed by SQL*Plus, that needs to run with Oracle 10g and Oracle 11g. That script gives grants on a package that does not exist before 11g: GRANT EXECUTE ON sys.dbms_result_cache TO my_user; I would like to avoid the exception on 10g, since I want to react to other exceptions in the script. One way is to u...
In MySQL, you can use \G to run a query: select * from mytable\G And your results will be displayed in an inverted table, kinda like this: *************************** 1. row *************************** column1: 12345 another_colum: another value yet_another: ABCD *************************** 2....
I am trying to declare g_num ,number data type with size it gives an error but in case of varchar2,char it does not. variable g_name varchar2(5);//correct accept size for varchar 2 variable g_num number(23);//Gives an error " VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) | VARCHAR2 (n [CHAR|BYTE])...
I am trying to right-align the output of some PL/SQL code, but dbms_output.put_line is trimming the leading whitespace from my strings. How do I make it stop? Or is there a better way to output strings with leading whitespace? dbms_output.put_line(lpad('string', 30, ' ')); outputs: string instead of: string...
I am running a query that returns XML data and spools to a file. I'm having a problem where the output of the query is being truncated. Here's what it looks like: XMLDATA ---------------------------------------- <?xml version="1.0" standalone="yes"?> <msgs> ...