How to Programming with Sqlplus

The given answer is correct. Create a directory with two files: control.sql second.sql make control.sql contain: set serveroutput on prompt "Start of control" / @second.sql / prompt "End of control" / make second.sql contain: prompt "Start of Second" / prompt "End of Second" / Then run control.sql...

UPDATE: How to fix SQL error in procedure statement : NEW ERROR [duplicate]

oracle,date,plsql,sqlplus,auto-populate

ime_kraja := ||' '|| TO_CHAR(st); should be ime_kraja := TO_CHAR(st); or you forgot something before the first ||.

Select Database details and table names in iSQL plus

oracle,oracle10g,sqlplus,oracle-xe

You can find most details of the database and the client using the queries against metadata tables or USERENV variables. For example: select * from global_name; -- will give you the name select * from v$version; -- will give you the oracle version and other details. Oracle Database 11g Enterprise...

Why do C-style comments make insert statement run twice?

/* This is a comment */ Just make sure you have a space after /* , So it is treated as a single/multi line comment. And not mean to execute the last stored PL/SQL or SQL To put it in detail. What ever SQL*Plus interprets after / is ignored and...

How to specify tablespace_name in SQLPlus Oracle select

database,oracle,batch-file,command-line,sqlplus

You are selecting from tablespace, which is not the same as your Owner/Schema name. Thats why. For example the tablespace SYSTEM has owner SYS. You do select from Sys.xxx; Ok. SELECT owner, tablespace_name, table_name FROM all_tables WHERE tablespace_name = 'MYSPACE'; And then select * from [ owner ].[ table_name ];...

Command line SQL invalid credentials

Batch File @echo off setlocal set "SqlUserName=" set "SqlPassword=" set "SqlDatabase=" :Credentials set /P "SqlUserName=Enter username: " set /P "SqlPassword=Enter password: " set /P "SqlDatabase=Enter database: " @( echo whenever sqlerror exit failure echo connect %SqlUserName%/%SqlPassword%@%SqlDatabase% echo select * from dual; echo exit ) | sqlplus.exe -s /nolog if errorlevel...

How to spool to different files from nested scripts?

sql,shell,plsql,oracle-sqldeveloper,sqlplus

When you run a script with @ the contents of that file are embedded in the parent, so from SQL*Plus' point of view you're just running one long script. SQL*Plus commands are not localised to the child script. You would have to start spooling again after the embedded script, with...

insert statement show 1 row created but data is not insert (script)

database,oracle,sqlplus,toad

what about commit. Is autocommit on? or add 'commit' after your insert statement...

how to remove newline from a text file only if it does not match this pattern ]}\n in unix

regex,json,oracle,unix,sqlplus

Riffing off this answer on a sister site, one you have your spooled file in Unix you can join lines together with awk: awk '{if (sub(/\]}$/,"")) printf "%s]}\n", $0; else printf "%s", $0}' yourfile.lis If you started with a file containing: {"id":"test","brand":"Disney","variants":[{"pid":"cup","name":"my cup"}]} {"id":"test","brand":"Disney","variants":[{"pid":"cup","name":"my cup"}]} {"id":"test","brand":"Disney","variants":[{"pid":"cup","name":"my cup"} ]} {"id":"test","brand":"Disney","variants":[{"pid":"cup","name":"my cup"}]}...

Oracle connection and running script in c#

c#,oracle,plsql,ado.net,sqlplus

You could just use a OracleCommand with the procedure name For sample Try something like this: using (OracleConnection con = new OracleConnection()) { con.ConnectionString = My_connection_string; con.Open(); OracleCommand cmd = new OracleCommand("temp.tmp_test", con); cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; var result = cmd.ExecuteScalar(); if (result != null) { string stringResult...

How can I change the output of SqlPlusresults with PLSQL?

You're simply missing a -s flag in your call to sqlplus. Example code: [email protected]***:/home/oracle/testing> cat test.sh $ORACLE_HOME/bin/sqlplus -s<<EOF XXX/XXX set serveroutput on set sqlnumber off set sqlblanklines off set feedback off BEGIN dbms_output.put_line('testing'); END; / exit EOF Example output without the -s flag: [email protected]***:/home/oracle/testing> sh test.sh SQL*Plus: Release 11.2.0.3.0 Production...

shell scripting that uses sudo su - team and perform rest command under this sudo access only

linux,shell,sqlplus,sudo,su

You could try having all of the commands on the same line and seperate them with ';'. eg. 'sudo su; somecommand; another command' Could also run that script as the intended user....

Issues with Group functions

sql,aggregate-functions,sqlplus

You were right, you need a JOIN, a COUNT, but also an HAVING to make sure there is more then one authors that written the book : select title as 'Book Title', count(authors) as 'Number of Authors' from books join authors on books.id = authors.book_id having count(authors) > 1 group...

Connect to sqlplus with another linux user than oracle

linux,oracle,database-connection,sqlplus,privileges

Most likely it is due to incorrect privileges at OS level. The Oracle file in the $ORACLE_HOME/bin directory should have following privileges: -rwsr-s--x You could check it like: cd $ORACLE_HOME/bin ls -lrt oracle If you see any difference, then do: chmod 6751 oracle ls -lrt oracle ...

Rename column named TYPE, LEVEL in sqlplus

sql,oracle11g,sqlplus

LEVEL is an Oracle keyword, though not reserved. If you want to use it as an object name then you need to represent the name of an object with a quoted identifier using double quotation marks whenever you refer to that object. SQL> SELECT keyword, reserved FROM V$RESERVED_WORDS WHERE keyword='LEVEL';...

Any SIMPLE way to fetch ALL results from a PL/SQL cursor?

sql,oracle,plsql,sqlplus,plsqldeveloper

If you're truly open to a SQL*Plus script, rather than a PL/SQL block SQL> set colsep ',' SQL> variable rc refcursor; SQL> exec :rc := SOME_PACKAGE.someFunction('some',parameters,here); SQL> print rc; should execute the procedure and fetch all the data from your cursor. You could spool the resulting CSV output to a...

How to see stored procedure output on command line via sqlplus [duplicate]

oracle,command-line,sqlplus,toad

You need to enable the output in SQL/PLUS before running your stored procedure: SET SERVEROUTPUT ON ...

Catching error code in Java from SQLPlus

java,sqlplus,error-code

So ultimately I found a hack to this entire issue. Here's what I had to do - 1) I am still iterating over all the sql scripts in a directory, but instead of directly executing them, I call another script(test.sql) with the file location of the scripts I am iterating...

A frustrating PL/SQL Error…PLS-00103

sql,oracle,plsql,sqlplus

May be it's because max and min are reserved words...so it looking for max() and min()... But it looks more that you just made a mistake. I think you need to try this : if (temp>max_avg) then max_avg := temp; elsif (temp<min_avg) then min_avg := temp; end if; ...

Executing PL/SQL functions from a file

You don't. A function is a database object; that means that it's been compiled on the database. Before you can use the functions you first need to compile them - you can do this be executing the .sql file you have from SQL*Plus, for example: sqlplus username/[email protected] @ driver.sql This...

Retrieve Column names From one table that contain 'report'

sql,database,oracle,oracle11g,sqlplus

Instead of using user_tab_columns, use all_tab_columns, provide table name and column name in upper case, provide owner name (incase you know it already).

Spool file larger than 4GB in sqlplus

linux,unix,sqlplus

1) Im not sure but according to this source there seems to be a limit on the size of spool file. Usually file system used also places limits on the size of the file. So this has something to do with file size limit perhaps. 2) For this you can...

Update, set + 90 where … (query updates wrong values)

mysql,sql,oracle,sqlplus

Try this You're missing the IN clause update book_copy set DATE_DUE = DATE_OUT +90 where OUT_TO_CID IN (select CID from CUSTOMER where CATEGORY ='Faculty'); ...

In Oracle 11g how do you time weight average data hourly between two dates?

sql,oracle,oracle11g,sqlplus

This query generated desired values: with input as ( select value, htime, to_char(htime, 'yyyy-mm-dd hh24:mi') mnt, extract(day from d)+extract(hour from d)/24+ extract(minute from d)/(24*60)+extract (second from d)/(24*60*60) tm from (select value, htime, htime-timestamp '1899-12-30 00:00:00' d from test)) select distinct mnt, round( sum(tm*value) over (partition by mnt)/sum(tm) over (partition by...

Select the Database name in iSQL plus

sql,oracle,sqlplus,ddl,database-metadata

Once connected to your RDBMS, you can issue the following command to query the database name: select ora_database_name from dual; On my system: SQL> select ora_database_name from dual; ORA_DATABASE_NAME -------------------------------------------------------------------------------- XE ...

Double quote as parameter in SQLPLUS

shell,unix,sqlplus

This does the trick sqlplus username/ @file.sql "'\"'" /home/myuser/Desktop/ Useful resources at: UNIX Shell Quotes - a simple tutorial Expansion of variable inside single quotes in a command in bash shell script...

Get table definition in oracle sql plus [duplicate]

Check the function: DBMS_METADATA.GET_DDL http://psoug.org/reference/dbms_metadata.html...

ORA-00984: column not allowed here - Dynamic SQL

sql,oracle,plsql,sqlplus

The error you're getting is not from how you're calling the procedure, but what the procedure is doing. The ORA-00984 error is reported against line 12 of the FMSSMART.INSERT_AUDIT_PROC_TBL procedure, which is: EXECUTE IMMEDIATE 'INSERT INTO AUDIT_PROC_TBL (error_number, error_message, package_name, procedure_name, start_time, end_time) VALUES (i_retcode, i_errormsg, i_package_name, i_procedure_name,i_start_time, i_end_time)'; You're...

SQL*Plus - numeric column shows hex characters

sql,oracle11g,subquery,sqlplus

Looks like the NUMBER format for column ERROR is set by default in SQL*Plus. ATTRIBUTE command shows the format of this. SQL> attribute error COLUMN ERROR ON FORMAT A65 word_wrap So, lets clear it. SQL> column error clear Now, SQL> select 12 error from dual; ERROR ---------- 12 Full Script:...

In Oracle, is there a query to find whether a function exists?

sql,sqlplus

You can try one of these. user_source dba_source all_source For example if you have access to dba_source, you can try something like this SELECT name from dba_source where type = 'FUNCTION' AND name LIKE '%PERIOD_DIFF%'/ ...

How to use IPC::Run module in perl script

oracle,perl,solaris,sqlplus

Using lexical filehandles makes life much easier in general. They aren't global, and they will automatically close when they go out of scope. open (my $wfh1, ">", "${basePath}/QueryResult4.txt"); It's possible the whole problem is that open failed, you're not checking if it succeeded. You can do this two ways. First...

SQLPlus is trying to drop package twice

sql,oracle,sqlplus,sql-drop

The rules of SQLplus command execution basically are: Execute the current text when you encounter a semi-colon. Thus if a line doesn't end with a semi-colon, the current text continues to be collected. If you encounter DECLARE or BEGIN, collect all the text and do not execute on semi-colons If...

SQL SUBQUERY in Sqlplus Oracle 11g Help please

I don't want to seem to be attacking you, but since you said you've been trying to figure this out for a long time and you're stuck, let's look at some of the problems with what you have at the moment. SELECT Consultant_Id, Name, DOB, This line has a trailing...

running sqlplus in background in unix

sql,linux,database,unix,sqlplus

& will put it in the background but if you actually want to close your terminal and leave for the day you need to use nohup nohup sqlplus USERNAME/[email protected] @test.sql & ...

Compile Error for Oracle Procedure

You can't specify the size of strings in the procedure declaration, so it should be: create or replace procedure add_employee (fname IN varchar2, lname IN varchar2, email IN varchar2, job IN varchar2) It's not a good idea to have the argument names match any table columns (email and job in...

listagg output formatting

Nothing stops you to make your query a sub-query and select again: SELECT BOOKID || ':' || AUNAMES FROM ( select BOOKID, listagg (AUNAME,' ') WITHIN GROUP (ORDER BY auname) AUNAMES FROM BOOK_AUTHOR group by bookid ) A If you want to use a space as a delimiter use it...

read from file and save to file with sqlplus on 1 line

sql,csv,command-line,sqlplus

If practical, you could add spool commands to your script. Otherwise, you could run sqlplus interactively, manually spool, and manually run your script, and manually exit. You could also probably automate the manual method, but that is beyond the scope of this question. You could specify file names at the...

How to spool cells to different files?

oracle,oracle10g,sqlplus

The pseudo code does not make it clear what you want, but here goes .... This method works by generating a script from the data to re-query the data with embedded spool commands for each distinct NAME. set lines 200 set trimspool on set head off pages 0 -- may...

Join table with itself

oracle,connection,database-connection,oracle-sqldeveloper,sqlplus

You'd want to join the table on the bookid, you don't want to compare a bookid (a numeric column) to an auname (a string column). Something like select a.bookid, a.auname, b.auname from book_author a, book_author b where a.bookid = b.bookid and a.auname != b.auname; ...

Connection parameters in SQL Developer

First of all you can check tnsnames in %ORACLE_HOME%\Network\Admin\tnsnames.ora If there is nothing helpful there - then connect in SQLPlus and do select host_name from v$instance. Port is almost always is 1521, but I don't know where to get it in the open session. If you can't connect on port...

SQL - Missing Keyword in GRANT SELECT ACCESS to User

select,sqlplus,grant

Not come accross that syntex before (grant select with a where clause) so not sure if you can do it that way, but you could create a view for the particuler DID value instead and then grant select on that.

Display hebrew language sqlplus

c#,oledb,sqlplus,oledbconnection,oledbcommand

Your database character set has to support unicode characters in order to be able to store non-ascii characters. This setting is defined by the DBA when creating the database which means your DBA most likely needs to recreate the database from scratch. This is not an easy option if the...

sql plus two tables select method

sql,table,select,sqlplus

If, as it appears, the ID field of both tables should contain the same value for the same user, then you have set up a 1-1 relationship. Each user can have one and only one avatar. If you want more than one avatar, then it has to have an independent...

sqlplus in silent mode doesn't work

shell,sqlplus

The correct format is: sqlplus -S LOGIN_INFO @SCRIPT_TO_RUN The "silent" mode doesn't prevent terminal output. All it does is: -S Sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands. If you want to suppress all terminal output, then you'll need to do something...

How to use a variable from Bash into SQLPLUS?

bash,sqlplus

Typically, you would do: echo "select first_name from customer where customer_id = $Customer;" | sqlplus username\[email protected] If you want to run multiple queries, it is common to use a heredoc: cat << EOF | sqlplus username\[email protected] select first_name from customer where customer_id = $Customer; select first_name from customer where customer_id...

User_tables Oracle

The table name has to be in upper case in user_tables. select * from user_tables where table_name='TEMP'; An example: SQL> create table bogus (name varchar2(10)); Table created. SQL> select table_name from user_Tables where table_name = 'bogus'; no rows selected SQL> select table_name from user_Tables where table_name = 'BOGUS'; TABLE_NAME ------------------------------...

write function in PL/SQL to Sum within the same table

sqlplus

The select is wrong, you select 3 values, but fetch into only one. The group by references are incorrect. You don't need to select the workerid or the project id, neither do you need the group by. Should be like below CREATE OR replace PROCEDURE Hoursworked (j IN NUMBER, n...

Cannot print to command line in sql plus

sql,oracle,batch-file,command-line,sqlplus

Run this in SQL*Plus first: set serveroutput on; ...

call sql scripts from a trigger

oracle,sqlplus,triggers

solved. the problem was to start some process (data mining process here) when I add record in database participating in my workflow system in Oracle Data Miner. I used DBMS_CHANGE_NOTIFICATION and DBMS_SCHEDULER in a PL/SQL procedure. TRIGGERS on the other hand I wrote independently.

oracle row count into shell script variable

sql,linux,oracle,shell,sqlplus

Try this, VAR="$(sqlplus -S usr/[email protected]//host:1521/db <<ENDOFSQL set head off select count(*) from table; exit; ENDOFSQL)" echo $VAR ...

SQL*Plus Spool Oracle ( Subquery /With Clause Issue)

oracle,csv,sqlplus,spool

I suspect that (a) you've run this as sqlplus -s user/pass @script and don't have an exit at the end of your script, which will cause the command window to stay open; and (b) you don't have any committed data in your table for this year's date range. You would...

How do I prompt input for database on command line?

Ampersand (&) is used for SQL*Plus substitution variables. You are supplying it on the Windows command line, where it means something else; when you exit from the client you'll see something like: 'username' is not recognized as an internal or external command, operable program or batch file. '[email protected]' is not...

DBMS_XPLAN.DISPLAY_CURSOR without unnecessary rows like EXPLAINED SQL STATEMENT or Plan hash value (without using sed or awk)

database,oracle,sqlplus

If you really want to have a nice graphical representation of the execution plan, then use the EXPLAIN PLAN window in Oracle SQL Developer. EDIT Based on @a_horse_with_no_name solution, tweaking a bit more gives the exact desired output. SQL> SELECT plan_table_output 2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC')) 3 WHERE substr(plan_table_output,1,1) in ('|', '-')...

How to execute sqlplus commands on ssh enabled unix machine from java code

java,unix,ssh,sqlplus

In the snippet you post, set the command1 var for example to String command1= "echo 'SELECT 1 FROM DUAL;' | sqlplus user/[email protected]"; ...

Determining locatation of relevant tnsnames.ora file

oracle,sqlplus,toad

Just based on your paths you have two installed clients as you suspect (Toad and dbforge are tools, not clients so your terminology is a bit off). One 32-bit, the other 64-bit. It appears that Toad is 32-bit based on its installation path, but execute it and go to Help|Support...

How do I resolve this “ORA-01109: database not open” error?

oracle,sqlplus,database-administration

As the error states - the database is not open - it was previously shut down, and someone left it in the middle of the startup process. They may either be intentional, or unintentional (i.e., it was supposed to be open, but failed to do so). Assuming that's nothing wrong...

in bash, heredoc inside function returns syntax error

bash,variables,sqlplus,heredoc,solaris-10

Your function declaration is wrong: get_instance{ should be one of function get_instance { get_instance() { Put the close bracket on a different line: dbname=$(sqlplus -s / as sysdba<<EOF ... EOF ) The terminating word of the heredoc should be the only characters on the line (except tabs when using <<-)....

ORA-06502 Error Only in SQLPLUS

oracle,plsql,oracle11g,sqlplus

Problem solved. Was a internal error of the package. Thanks for all the answers.

3 table query with count

mysql,sql,sqlplus

You want to use count distinct: MySQL COUNT DISTINCT select t.name as Team, count(distinct m.ID) as Member_cnt, count(distinct t.ID) as Task_cnt from team t left join member m on t.ID= m.TEAM_ID left join tasks t on t.MEMBER_ID= m.ID group by t.name; ...

Sqlplus command is not working via plink

shell,sqlplus,putty

We haven't enough information about your problem, but here is my guess: you have to set ORACLE_HOME env. var each time you log in (or add it in auto executable scripts), you have to extend PATH too (use export PATH=$PATH:$ORACLE_HOME/bin), also, may be you need to start Oracle instance. Check...

hebrew from the Connection

c#,oledb,sqlplus,oledbconnection,oledbcommand

I can't really test this because I don't know anything about your database (not even your column names), but you should do that command with parameters: var testString = "היי"; // Do be aware that Visual Studio displays Hebrew text right-to-left, so the actual string is reversed from what you...

update table using nested subqueries

database,oracle10g,sqlplus,ora-12560

If you want to have different periods for different categories, I would suggest: update book_copy b set date_due = (case (select category from customer c where c.cid= b.out_to_cid) when 'faculty' then date_out + 90 when 'administrator' then date_out + 120 when 'student' then date_out - 10 else date_due end); ...

Oracle 10g users are expired

Login to database server as the Oracle sofrware owner (or, in case of Windows, as user who is member of ORA_DBA group), set ORACLE_HOME and ORACLE_SID environment variables, then login as: sqlplus / as sysdba You should get logged in without having to provide a password. Hope that helps....

Can't exit out of afiedt.buf

oracle11g,sqlplus,vi

You can configure vi as SQLPlus editor with the statement DEFINE _EDITOR=vi (see the SQL*Plus User's Guide and Reference). If you get back just a ? you are probably using the old ed editor which you can quit by entering the q command (see its guide with man ed)....

Oracle set multiple system variables

Is it possible to set multiple system variables in a single command? Yes you can. Simply specify variable name/value pairs using space as a delimiter between previous variable's new value and the next variable's name. Here is an example: /* display variables' current values */ SQL> show colsep echo feedback...

oracle sqlplus no username and database

oracle,plsql,oracle11g,sqlplus

SQLplus is a command line client for the Oracle database system and the Instant Client is a slim driver from accessing it. So you haven't installed the Oracle database (server) yet, just the client. The Oracle server does not run on Mac OS X. You will need to install it...

Spool CSV file using BASH Script | Errors with Output in generated CSV

oracle,bash,shell,csv,sqlplus

The defaut linesize is 80 characters, so by default columns will wrap onto new lines when that length will be exceeded. Your 100-byte columns will cause that behaviour. You can add SQL*Plus commands to change that: -- any number at least as large as the longest possible output set linesize...

PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following: := . ( @ % ;

The INSERT statements must end with a semi-colon. insert into table (columns list) values (values list); As a good practice, always compile your code in SQL*Plus and do SHOW ERROR. Look at the line number in the error stack. Without any EXCEPTION block (or with proper exception handling), you would...

Oracle SQLPlus: Select statement, where (?) and order by

sql,oracle,sqlplus,pico

If you're interested in seeing how many days it is overdue from October 10 you can use this bit of code instead: select bookid, copy_num, Last_Name, First_Name, Date_Due - to_date( '10-OCT-2014') total_days_overdue from book_copy bc inner join customer c on bc.out_to_cid = c.cid where Date_Due >'10-OCT-2014' Order by bookid, copy_num;...

sqlplus select record between merged dates

sqlplus

You could use an anaylitic rank() call: SELECT permission_id, document_id, date_time_from, date_time_to, approved_on, permission_type FROM (SELECT permission_id, document_id, date_time_from, date_time_to, approved_on, permission_type, RANK() OVER (ORDER BY perission_id ASC) AS rk FROM document_archive_doc_perms WHERE document_id = 3 AND ecode = 1695 AND approved_on IS NOT NULL AND SYSDATE BETWEEN date_time_from AND...

PL/SQL procedure not returning expected results

oracle,shell,plsql,sqlplus

I think you'd be better off putting each table's creation in its own BEGIN...END block, as in: echo "DECLARE" > createxmodel.tmp echo " model_exists NUMBER(1);" >> createxmodel.tmp echo " excp_table_exists EXCEPTION;" >> createxmodel.tmp echo " PRAGMA EXCEPTION_INIT(excp_table_exists, -955);" >> createxmodel.tmp echo "BEGIN" >> createxmodel.tmp echo " SELECT COUNT(*) INTO model_exists...

Installing rlwrap on linux - without root permission

linux,sqlplus,rlwrap

In order to install it without root permissions, you need to configure it as follows: ./configure --prefix=$HOME after which 'make install' will install rlwrap in $HOME/bin (Above info is from INSTALL file in root folder of rlwrap)...

writing output to a file from a procedure in sqlplus - SPOOL

Ok, finally figured out what was the issue. The standard SQL PLUS switches work great ( trimspool, termout, sqlprompt, etc) however you MUST use SQL Plus DIRECTLY. As in, DO NOT USE Oracle SQL Developer for that. Those damn worksheets you open inside it just seem to behave differently in...

SP2-1504: Cannot print uninitialized LOB variable

oracle,oracle11g,sqlplus,describe

In the first code you posted you're trying to use the select ... into pattern in plain SQL, but it only works like that in a PL/SQL context. So you're right to use a block to populate a bind variable. As @JanisBaiza said, you need a / after your anonymous...

How to describe an ORACLE package without using DESCRIBE clause?

Take a look at USER_PROCEDURES. I suspect this is what DESCRIBE queries when you use it on a package, procedure, or function. EDIT: Sorry, also incorporate USER_ARGUMENTS to list the parameters for each subprogram. The logic to join the two views is not immediately obvious because of the ways different...

Write to a file in PL/SQL without spools or utl_file

sql,plsql,sqlplus,spool,utl-file

No. You will need access to init.ora to get this done the nice way through Oracle. The only other option I can think of is the use of a Java procedure to do the file writing. I couldn't find any special requirements you need to have set to use that....

What is causing my values to be printed as #

sql,oracle11g,sqlplus

aXXX formats columns as text, but you are printing numbers. You can use 9s to format a column as a number, which also lets you use commas to separate thousands: SET LINESIZE 200 COLUMN 'Pharmaceutical Companies' FORMAT a25 COLUMN 'Drug Count' FORMAT 999,999,999,999 ...

SQL Bank DB: return only people with a given property

sql,database,oracle,plsql,sqlplus

try this! select c.customerID, c.name, a.UAN, a.balance, a.overdraft From customer c join current_acc a on c.customerID = a.customerID and a ='current account' ...

Hide SQL > statements in the spool file

oracle,sqlplus,spool

reason for getting sql statements into results is:- If you copy those sql scripts and paste directly on sql prompt,and execute directly will gets the issues like this. So, to avoid this, place all the script code into one .sql file, and execute that entire file by giving...

Ignoring User Exists Error in Oracle

oracle,error-handling,sqlplus,createuser

It isn't clear how you're running your script, but assuming its via SQL*Plus you can modify the behaviour when an error is encountered with the whenever sqlerror command. If your script is setting that to exit at the moment, or you're picking that up from a startup script (login.sql, glogin.sql)...

How to log queries taking long time

sql,oracle,oracle11g,sqlplus

In simplest "Heath Robinson" terms you can write to a log table "Query X start" and "Query x end" with timestamps to measure elapsed time. There was a time back in the day when you could have used tkprof but these days the security policy tyranny of most companies dictates...

Handling duplicates while updating records in SQL using where clause

sql,database,oracle,relational-database,sqlplus

Update salary_table set salary = 9000 where username= 'abc' and usersurname= 'bak' and Last_entry_date = (select max(Last_entry_date) from SalaryTable where s.username = username and s.usersurname = usersurname); ...

What is the most simple SQL command to show CONSTRAINT_NAME column

sql,database,oracle,sqlplus

select constraint_name from user_constraints is what you're after. You select the column from the table. Now, presumably, you want more information than just the name of the constraint. You probably want to add additional columns to your SELECT list to get all the attributes you're interested in....

Multiple select statement in Oracle