I wiuld like to suppress oracle messages conditionally. I am running oracle view creation scripts from command line, After successful view creation, it is showing me "view created" message. To suppress this, I have created following
set serveroutput on format wrapped;
SET TERMOUT OFF
@@test1.sql
SET TERMOUT ON
[Code]....
but still it is giving me "PL/SQL procedure successfully created" message along with "'View test831 created successfully". I need to suppress "PL/SQL procedure successfully created".
I have installed SQL navigator 5.0 version in my PC. Once it has been successfully installed, I clicked on SQL navigator icon in my PC after that it's prompted me for asking authorization Key. I have entered the following key.
3-37993-18701-28024-78347
Once I have entered the key is is prompting me for asking site message. what message I should enter.
Am using the SQL*PLus tool via a command line to execute some simple "select" scripts. Scripts work fine in what they produce - except for something that is missing occasionally.
For SOME scripts, I'm getting no "xx Rows Selected" type messages at the bottom and I can't figure out why. When no data si returned in some (different) scripts I do get a "no rows selected" message. Am looking for this message in all outputs as an "end of report"-style marker.
No complicated SQL logic and I have the following standard settings applied into each script.
set linesize 500 set pagesize 50000 set tab off set wrap off set colsep '|'
I have 20 years of coding experience so it's not a basic error....albeit I'm far more familiar with MS SQL Server product set as well as the PL/SQL Developer client front ends for Oracle.
I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-
Table_Name Column_Name --------- ---------- A C1 A C2 A C3
Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.
For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.
how to create a file in a folder based on todays date. i need to know how to define a variable in sqlplus and assign a value to it.Here is the code below. The code gets executed without creating a spool file.
DEFINE _DATE = replace('C:\_sysdate_EU001.csv', '_sysdate_', TO_CHAR(SYSDATE, 'DD-MON-YYYY')) spool _DATE set serveroutput on size 100000 select * from dual; spool off
1) A reverse engineering tool that I can point to an Oracle schema and get a "baseline" script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.
2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a "delta" script to alter the target schema so that it becomes identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. "-- Attention: data migration DML needed here?"). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the target tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping, recreating and repopulating the table).
3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.
The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.
# Host name is vmwwin7634.na.SAS.com # SID is sting2 # SERVICE_NAME is sting2.na.sas.com
PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com
I am using SQuirreL Client version 3.4.0
# Here is the tnsnames.ora entry on the server. STING2 = (DESCRIPTION =
[code]...
# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.
# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.") jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)
# "New Method" jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works) jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors) (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
I have new virtual UNIX machine and I installed oracle client on /usr/lib/oracle. Also I have a oracle database and I am able to connect to this database from my desktop sql developer.
So now I am trying to connect from new UNIX machine. Where I created tnsnames.ora file under /usr/lib/oracle/network/admin and before connecting did export the following
ORA-12545: Connect failed because target host or object does not exist. Not sure what I missed here. using same tns file I am able to connect from sql developer on windows.
I need to install Oracle client to get latest sql plus working on client pc as the problem is, i am not able to find out where i can download this , i just need the sql plus as i am not able use many commands because when i installed oracle forms 6i client tool , sql plus 8.0 got installed.
The version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi. for Unix AIX 5
I install the ODBC Driver Manager, Basic Instant Client, ODBC Instant Client packages (libsqora.so, odbc_update_ini.sh).
The error:
[oracle@d4 oracle] $ isql OracleODBC-10g user passtemp -v [01000][unixODBC][Driver Manager]Can't open lib '/home/oracle/instantclient_10_2/libsqora.so' : file not found [ISQL]ERROR: Could not SQLConnect
C:>oradim -new -sid Test100 -srvc OracleServiceTest100 -startmode a -pfile 'c:oracleora92databaseinittest100.ora' C:>orapwd file=c:oracleora92databasePWDtest100.ora password=sys entries=2 C:>set oracle_sid=test100 C:>sqlplus /nolog SQL*Plus: Release 9.2.0.1.0 - Production on Wed Mar 10 22:42:43 2010 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> conn /as sysdba ERROR: ORA-01031: insufficient privileges
SQL> conn sys/sys as sysdba Connected to an idle instance. SQL> startup nomount ORA-01031: insufficient privileges SQL> @f:oracle estdb.sql create database TEST100 * ERROR at line 1: ORA-01034: ORACLE not available
i just tried to set up a new database called Test100 manually.but i could not do anything here for these messages. my OS is winxp sp2.
I cant find any tools that can generate the DDL for all objects in Postgressql to migrate to oracle, I did sql developer however does not support postgresql, does any tool that can migrate postgresql to Oracle?
I am using dg4msql to connect to SQL server from Oracle(10.2.0.4, with AIX 5.3).
#from my laptop sqlplus (10.2.0.2)
SQL>select count(*) from table1@mssql; #works SQL>select * from table1@mssql; #ERROR
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Oracle][ODBC SQL Server Driver]String data, right truncation {01004}[Oracle][ODBC SQL Server Driver]String data, right truncation {01004}
#from my laptop, TOAD Both queries works fine.
i have compared
"select * from nls_session_parameters;" values , TOAD and sqlpus gives same result.
I'm looking for a basic password reset / unlock tool for use at a help desk. Environment is mixed 10 and 11G. Current procedure is log into specific database using SQL Plus and manually type out reset/unlock commands.
I have installed Oracle 11g R1 and have problem in connecting Oracle SQL Developer.
I have given user name: ora; pswd: ora.
In command prompt it works fine if I gave as below. uid: ora as sysdba pswd: ora
But when I am trying to connect to Oracle SQL Developer it says TNS listener does not know SID.
So I reinstalled this since i have doubt on myself whether I made mistake first time. Also I have doubt whether the un installation completely clears all the files from Registry. - What should I do to clear all the files from registry when un-installing.
1] What is SID. Is it session ID. If so how to find it. 2] Is there any way to confirm the values such HOST NAME: localhost, PORT: 1521, SID: (By default it has 'xe') which I gave for data base connection are correct.
I tried with SID value:135 (I got this value by executing [Select distinct sid from V$mystat;])
3] Is there any editor for practicing sql & pl sql queries. where I can download it.
I want to move a oracle job from one database to another database. i tried to open the job from toad by pressing f4 on it in editor , im getting message like...
"unsupported type for describe popups (Without DB admin module)
I am running TOAD 10.6 on Windows 7 (64-bit). I have two 64-bit Oracle 11g clients installed (all on the same machine). The first is Instant Client (client_1); the second is the Runtime client (client_2). The installation of Instant Client was a mistake, since it doesn't have a Network / Admin folder to drop a tnsnames.ora file into. The Runtime client is my desired client, which has the aforementioned folder that I subsequently dropped the file into.
On starting TOAD, it says that "No valid Oracle Client found. Please note that Toad only supports 32 bit Oracle Client installations". (This is odd, since my colleague is running the same Oracle Runtime client as me and has TOAD 11.6, and yet his is working fine. But, this is aside the point.) When, I click on the ellipsis button next to the "Connecting Using" field, TOAD says that I don't have any Oracle homes installed. I checked my registry key (via regedit) under Computer / HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE. It looks like I have 2 Oracle homes (because of the 2 clients).
Also, I right-clicked on My Computer and brought up Properties, Advanced system settings, Environment Variables (on the Advanced tab). My PATH variable has both of my clients.
How to make my available clients (particularly, my client_2) show up in TOAD?
Here is what I have tried thus far:
-Alter the PATH variable to just have client_2. This didn't do anything.
-Try to uninstall client_1 and client_2 via:
a) There is a Universal Installer under Oracle Installation Products in my Runtime client. I do Deinstall Products, click on any of my Oracle homes, click on Remove, and a Warning window pops up saying to run a command at some location, but the location is truncated since I can't expand the window.
b) In my client_2, there is a deinstall Windows Batch File in the deinstall folder. When I run this, the command window sits there for a little bit, saying that some files were copied and that 1 directory was removed. It then closes on its own. This doesn't seem to do anything.
I have installed Oracle 10g in Windows XP which is in 'VMware' as guest OS. share the procedure if we can connect to the oracle installed in Guest OS from the main operating system.
