Get table definition in oracle sql plus [duplicate]
sql,oracle,sqlplus,ddl,database-metadata
Check the function: DBMS_METADATA.GET_DDL http://psoug.org/reference/dbms_metadata.html...
Cannot print to command line in sql plus
Run this in SQL*Plus first: set serveroutput on; ...
sqlplus in silent mode doesn't work
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...
Select Database details and table names in iSQL plus
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...
SQL*Plus Spool Oracle ( Subquery /With Clause Issue)
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...
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 <<-)....
How to specify tablespace_name in SQLPlus Oracle select
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 ];...
Sqlplus command is not working via plink
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...
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 ...
How to spool cells to different files?
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...
ORA-00984: column not allowed here - Dynamic SQL
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...
In Oracle 11g how do you time weight average data hourly between two dates?
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...
Oracle SQLPlus: Select statement, where (?) and order by
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;...
In Oracle, is there a query to find whether a function exists?
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%'/ ...
Catching error code in Java from SQLPlus
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...
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...
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 ...
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....
Multiple select statement in Oracle
select * from tab1 Select * from tab2 You cannot simply have a select query in PL/SQL. It will throw PLS-00428: an INTO clause is expected in this SELECT statement error. Either you use a SELECT..INTO clause or use CURSORS. The SELECT INTO clause is used to retrieve one...
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...
shell scripting that uses sudo su - team and perform rest command under this sudo access only
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....
How to describe an ORACLE package without using DESCRIBE clause?
oracle,oracle11g,sqlplus,describe
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...
oracle sqlplus no username and database
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...
Hide SQL > statements in the spool file
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...
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...
Spool CSV file using BASH Script | Errors with Output in generated CSV
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...
sqlplus select record between merged dates
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...
Select the Database name in iSQL plus
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 ...
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...
How can I change the output of SqlPlusresults with PLSQL?
sql,shell,plsql,oracle-sqldeveloper,sqlplus
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...
Spool file larger than 4GB in 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...
What is the most simple SQL command to show CONSTRAINT_NAME column
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....
ORA-06502 Error Only in SQLPLUS
oracle,plsql,oracle11g,sqlplus
Problem solved. Was a internal error of the package. Thanks for all the answers.
update table using nested subqueries
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); ...
How to execute sqlplus commands on ssh enabled unix machine from java code
In the snippet you post, set the command1 var for example to String command1= "echo 'SELECT 1 FROM DUAL;' | sqlplus user/[email protected]"; ...
Rename column named TYPE, LEVEL in 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';...
SQL - Missing Keyword in GRANT SELECT ACCESS to User
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.
Which tables do not have indexes in Oracle? [closed]
Well, normally I wouldn't do this, but here you go: SELECT t.TABLE_NAME FROM USER_TABLES t LEFT OUTER JOIN (SELECT DISTINCT TABLE_NAME FROM USER_INDEXES) i ON i.TABLE_NAME = t.TABLE_NAME WHERE i.TABLE_NAME IS NULL; Perhaps your question should be "Why did someone just do my homework for me?". Best of luck....
Installing rlwrap on linux - without root permission
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)...
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...
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...
formatting SQLPlus terminal output (very long data_type column width)
terminal,output,sqlplus,truncate
Just format the output e.g. COL DATA_TYPE FORMAT A10 http://docs.oracle.com/cd/A87860_01/doc/server.817/a82950/ch4.htm...
I need to call another sql file within an sql file using sql plus
sql,sql-server,unix,plsql,sqlplus
The command to call other SQL files from within SQLPLUS is : start (or @) Getting feedback from the SQL script : SQLPLUS is not Bash. It is possible, but not really beginner stuff....
How to use a variable from Bash into 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...
Update, set + 90 where … (query updates wrong values)
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'); ...
Where is Oracle sqlplus located?
mysql,sql,oracle,command-line,sqlplus
You can locate the sqlplus executable in Windows by running in a CMD shell dir /s /b c:\sqlplus.exe Suppose you find the file at c:\oracle\product\11.2.0\client_1\bin\sqlplus.exe Then you have determined that your ORACLE_HOME is: c:\oracle\product\11.2.0\client_1 Assuming the above ORACLE_HOME, set your environment variables (Control Panel > System > Environment Variables). Below...
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 ...
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)...
Connection parameters in SQL Developer
oracle,connection,database-connection,oracle-sqldeveloper,sqlplus
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...
SQLPlus is trying to drop package twice
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...
A frustrating PL/SQL Error…PLS-00103
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; ...
read from file and save to file with sqlplus on 1 line
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...
Can't exit out of afiedt.buf
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)....
Double quote as parameter in 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...
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...
Can SQL*Plus read environment variables from the machine is it running on?
oracle,environment-variables,sqlplus
You can get a few client-related things from the USERENV context, but not arbitrary environment variables. If you can create a file on your local machine you could use the host command to set a substitution variable based on an environment variable: SQL > host echo define homedir=$HOME > /tmp/gethome.sql...
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...
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 10g users are expired
database,oracle10g,sqlplus,ora-12560
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....
What is causing my values to be printed as #
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*Plus - numeric column shows hex characters
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:...
sql plus two tables select method
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...
Join table with itself
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; ...
insert statement show 1 row created but data is not insert (script)
what about commit. Is autocommit on? or add 'commit' after your insert statement...
DBMS_XPLAN.DISPLAY_CURSOR without unnecessary rows like EXPLAINED SQL STATEMENT or Plan hash value (without using sed or awk)
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 ('|', '-')...
Command line SQL invalid credentials
database,oracle,batch-file,command-line,sqlplus
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...
PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following: := . ( @ % ;
oracle,plsql,oracle11g,sqlplus
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...
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"}]}...
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 & ...
Why do C-style comments make insert statement run twice?
oracle,oracle10g,sqlplus,oracle-xe
/* 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...
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...
Determining locatation of relevant tnsnames.ora file
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...
Need to pass string with newline character from Unix shell script to sql query
shell,unix,scripting,newline,sqlplus
UPDATED MY ENTIRE SOLUTION DESIGN After trying all night, i have given up. Thanks Aaron and mplf for your inputs. I have decided to change my solution from file based to table based. I will be reading the partner.txt file and inserting the partners in a dummy temporary table. Then...
SP2-1504: Cannot print uninitialized LOB variable
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...
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 ||.
How to spool to different files from nested scripts?
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...
Run SQL*Plus Script That Runs Other Script files
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...
write function in PL/SQL to Sum within the same table
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...
SQL SUBQUERY in Sqlplus Oracle 11g Help please
sql,oracle11g,subquery,sqlplus
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...
call sql scripts from a trigger
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.
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...
3 table query with count
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; ...
How do I prompt input for database on command line?
sql,oracle,batch-file,command-line,sqlplus
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...
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 ------------------------------...
Euro '€' symbol not inserted correctly during Oracle SQL LOAD
oracle,oracle11g,sqlplus,sql-loader,dbms-output
Your database is not configured to support the Euro character in a VARCHAR2 column. Your database's NLS_CHARACTERSET of WE8DEC means that it uses the old DEC MCS character set. That character set long predates the Euro character (it's even older than the ISO 8859-1 character set that also predates the...
How to use IPC::Run module in perl script
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...
How to log queries taking long time
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...
Fetching SQL execution plan. PL/SQL Developer = strange behaviour. SQL*Plus = no rows selected
oracle,plsql,sqlplus,plsqldeveloper
The sql query in my question was never needed. All I needed is to write to the admin team to grant me the SELECT premissions on: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL The answer to my question was found here. I will need a deeper understanding of the problem in the future...
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); ...