How to Programming with Sqlplus

All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function. Change query to: SELECT d.D#,DNAME,sum(budget) as "TOTAL BUDGETS" FROM DEPARTMENT d,PROJECT p WHERE d.D# = p.D# GROUP BY d.D#, DNAME; Update for the comment: SELECT d.D#, d.DNAME, NVL(SUM(p.budget), 0)...

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...

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....

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...

Executing PL/SQL functions from a file

sql,database,oracle,relational-database,sqlplus

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...

Handling duplicates while updating records in SQL using where clause

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); ...

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...

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 ...

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...

SP2-1504: Cannot print uninitialized LOB variable

oracle,error-handling,sqlplus,createuser

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...

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...

Ignoring User Exists Error in Oracle

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 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....

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.

Dropping all tables sqlplus

sql,aggregate-functions,sqlplus

Posting as a comment for better visibility.. Credits to @Tomás for pointing me in the right direction. I had to do purge recyclebin; instead of purge dba_recyclebin since I have insufficient privilege(I am a student logged in via ssh). If you want to turn your recycle bin off, Use ALTER...

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...

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...

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...

Issues with Group functions

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...

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...

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.

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...

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...

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';...

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; ...

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"}]}...

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%'/ ...

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...

User_tables Oracle

oracle,plsql,sqlplus,plsqldeveloper

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 ------------------------------...

Fetching SQL execution plan. PL/SQL Developer = strange behaviour. SQL*Plus = no rows selected

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...

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...

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]"; ...

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...

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...

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...

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 do I get rid of this extra grouping_id column when writing this query?

oracle,sqlplus,database-administration

You could do this using a subquery: SELECT DNAME, GENDER, SUMSALARY FROM (SELECT DNAME, GENDER, GROUPING_ID(DNAME,GENDER) AS "Group_id", SUM(SALARY) AS SUMSALARY FROM AZDEPARTMENT JOIN AZCONSULTANT ON AZDEPARTMENT.DID=AZCONSULTANT.DID GROUP BY CUBE(DNAME,GENDER) HAVING GROUPING_ID(DNAME,GENDER) > 0 ) T ...

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

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...

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 ...

Join table with itself

c#,oledb,sqlplus,oledbconnection,oledbcommand

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; ...

Display hebrew language sqlplus

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 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...

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 <<-)....

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...

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...

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 ('|', '-')...

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....

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...

Select the Database name in iSQL plus

oracle,oracle11g,sqlplus,describe

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 ...

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...

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;...

Cannot print to command line in sql plus

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

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:...

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...

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...

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...

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...

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...

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....

Compile Error for Oracle Procedure

oracle,environment-variables,sqlplus

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...

Can SQL*Plus read environment variables from the machine is it running on?

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...

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 & ...

Multiple select statement in Oracle

sql,oracle11g,subquery,sqlplus

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...

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...

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...

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...

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...

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 ];...

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; ...

Use a variable as a table name in a Alter Table Statement

oracle,plsql,oracle11g,sqlplus

declare l_newtablename varchar2(50); begin l_newtablename := 'TABLE_LERO_' || TO_CHAR (SYSDATE, 'MM_DD_YYYY'); execute immediate 'ALTER TABLE TABLE_LERO RENAME TO ' || l_newtablename; end; / ...

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...

Run SQL*Plus Script That Runs Other Script files

sql,oracle,sqlplus,ddl,database-metadata

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...

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....

Get table definition in oracle sql plus [duplicate]

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

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 ...

ORA-06502 Error Only in SQLPLUS

oracle,plsql,oracle11g,sqlplus

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

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...

Complex SQL query to join two tables

This is a strange data structure. You need a left outer join with the conditions in the on clause: select a.id, a.name, a.city, b.attr_value from table_A a left join table_B b on a.id = b.attr_id and b.attr_name = 'Attr_1' and b.attr_type = 'TableA'; I added the attr_type condition, because that...

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'); ...

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...

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)....

Returning errors as with Group By