I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:
Original script for the report:
set serveroutput on size 1000000 set pages 10000 set lines 1000 set arraysize 1 set trimspool on set heading off [code]....
The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.
I usually work in oracle using the different editors like toad, plsql developer etc. I felt command line is good utility because of various supporting points. I am not sure what kind of command line tools is available/used. I am using pretty straight command line (run->cmd, then enter username and password).
The best command line tools available. What is generally used command line tools.
I have a question regarding SQL Developer. When opening a SQL Developer worksheet, there are two different panes or work areas; the upper pane contains the query code and the bottom pane contains the query results (output). Using the
Tools===Preferences tab, I was able to enable line numbering for the top pane (code) but I haven't figured out how to enable line numbering for the bottom pane (results area). how to enable line numbering for the bottom pane?
I am trying to run PL/SQL Developer from command line, So it can open with an opened SQL WINDOW by default, with a script (which I specify in the command ) already in it:
When I execte the command from command line .Codeexprt run and stop after some time and it make entry in the error log as "ora-00942 table or view does not exist".
The way to create a short cut on desktop to have an sql*plus (command line).
i.e. I want to have an short cut on desktop when I click that short cut that would connect me to a specific database with a username and password (which i supply while creating the shortcut-I would provide the database name, username and password)
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME ------------------------------------------------ STATUS ------------------------------------ BUP OPEN
Another database has following characterset settings:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME STATUS ---------------- ------------------------------------ HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
I'm a student researching SQL, Oracle queries. I'm currently writing a oracle:JDBC to query by id, then receive all tuples that belong to id and compute GPA. I convert letter-grade to # in query how to count rows pertaining to id, then average GPA of all tuples. I understand Count(*) where sid=?, but i need to count the rows for id, then convert letter to number, finally compute gpa for all pertaining tuples belonging to id.
Everything works find but the last query for my JDBC prog.Here is my query as of now...
"SELECT grade, CASE WHEN grade = 'F' THEN 0 WHEN grade = 'D' THEN 1 WHEN grade = 'C' THEN 2 WHEN grade = 'B' THEN 3 WHEN grade= 'A' THEN 4 END AS letter_grade FROM Grades"
How do I count all tuples then compute the gpa pertaining to id.For all IDs?
I have a problem with getting '|' sign in TOAD Editor. It is default for Croatian keyboard layout that I get '|' sign when I press Alt_gr+W key combination.
But in Toad, when I press Alt_gr+W combination Watches dialog is opened.I want to get '|' sign in Toad Editor when I press Alt_gr+W.
I wrote an sql script (spooler) which runs a select query and produces results into a file.
I want to have the filename with the year attached. Can i pass a parameter when i run the script from SQL*PLUS
@'B: Year Of FinancingPLSQLspoolersSpooler_File_Creation' 01/01/2010 2010;
the first parameter 01/01/2010 is passed to the select query and taht works fine.
Since the output file contains 2010 data, I want the spooled file to mention in its name the 2010, so User's can know the file contains 2010 data in it.
So i wrote the statement within the spooler script
SPOOL X:HARSOrganizationsFinancialDataFor&2.txt
&2 should take 2010 has its value and produce the file FinancialDataFor2010.txt
how to use Substitution variables to produce the Filename i want.
My query is suppose to capture the student's number, date of birth, and number of students which that students mentors. I am suppose to show only the 5 oldest students
SELECT s.std_num, s.birth_date, COUNT(s2.mentor_num) From Student s left outer join Student s2 on s2.mentor_num=s.std_num GROUP BY s.std_num, s.birth_date ORDER BY s.birth_date ASC;
This function works but the problem is in displaying the five records because whenever I apply the rownum<=5 function the counter returns incorrect results. I tried writing another query to fix it but I could not dispay the counter anymore
SELECT ROWNUM as SENIOR, s.std_num, s.birth_date, COUNT(s2.mentor_num) FROM (SELECT s1.std_num, s1.birth_date, COUNT (e2.super_id) From Student s left outer join Student s2 on s2.mentor_num=s1.std_num GROUP BY s1.std_num, s1.birth_date ORDER BY s1.birth_date ASC) S WHERE row_num<=5;
This code only works if I remove the count in the first line but I need to display the number in my result.
is there any way to force SQL*Plus to provide the actual line number in the source file whenever there is an error? I often put at the first line of my script SET SQLBLANKLINES ON so that I may be able to put several consecutive blank lines lines in my code (I do this sometimes, when I find it to be appropriate in order to make my code more readable or to group a sequence of instructions which I believe make their logical link more understandable for the reader) Now the problem is that SQL*Plus ignores these blank lines and whenever there is an error, the line number provided in the error message, doesn't correspond to the actual line number in the source file but it seems to be the last non blank line in the file. Consider the following example:
SET SQLBLANKLINES ON;DECLARE var PLS_INTEGER := 10;BEGIN var := 20END;/ In the above code at line 9
(by counting also the blank lines) there is an error (no semicolon at the end of var := 20) but when I run the script inSQL*Plus here is the error message that I get
SQL> @myscript.sql;END;*ERROR at line 8:ORA-06550: line 8, column 1:PLS-00103: Encountered the symbol "END"
when expecting one of the following:* & = - + ; < / > at in is mod remainder not rem<an exponent (**)> <> or != or ~= >= <= <> and or like like2like4 likec between || multiset member submultisetThe symbol ";" was substituted for "END" to continue.SQL> So as you can see the error message indicate that the error was found at the line 8 whereas in the file it is really at the line 9 As long as the number of lines in the script is limited, this may not be a problem and one can find rapidly the actual line number in the code raising the error but for a code including hundreds (or even thousands) of lines and with many comments and blank lines, finding the actual line number given by SQL*Plus error message becomes tricky. Is there any way to make SQ*LPlus trace correctly the line numbers as they appear in the source file?
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.
we can't use the Exadata Plugin for Cloud Control but we need some monitoring of the Cell Servers.Does OS Watcher is the right tool or do we need ADRCI for incidents and so on.
What do have to install and what information do we get.
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.
I want to use BR*Tools to administer an Oracle database. I have installed BrGUI local, set the JAVA Home variable and edited the brgui.properties file accordingly.I am able to login, but the menues are mising. The login screen is incomplete.
