sql,oracle,sqlplus,ddl,database-metadata
Check the function: DBMS_METADATA.GET_DDL http://psoug.org/reference/dbms_metadata.html...
Run this in SQL*Plus first: set serveroutput on; ...
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...
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...
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...
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 <<-)....
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 ];...
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...
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 ...
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...
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...
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...
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;...
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%'/ ...
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...
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...
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 ...
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....
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...
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...
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....
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...
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...
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...
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...
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...
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...
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 ...
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...
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...
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...
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....
oracle,plsql,oracle11g,sqlplus
Problem solved. Was a internal error of the package. Thanks for all the answers.
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); ...
In the snippet you post, set the command1 var for example to String command1= "echo 'SELECT 1 FROM DUAL;' | sqlplus user/[email protected]"; ...
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';...
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.
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....
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)...
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...
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...
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...
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....
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...
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'); ...
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...
oracle,command-line,sqlplus,toad
You need to enable the output in SQL/PLUS before running your stored procedure: SET SERVEROUTPUT ON ...
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)...
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...
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...
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; ...
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...
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)....
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...
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...
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...
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...
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...
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....
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 ...
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:...
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...
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; ...
what about commit. Is autocommit on? or add 'commit' after your insert statement...
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 ('|', '-')...
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...
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...
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"}]}...
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 & ...
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...
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...
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...
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...
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...
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 ||.
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...
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...
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,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...
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.
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...
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; ...
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...
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 ------------------------------...
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...
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...
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...
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...
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); ...