Oracle SQL Query for listing all Schemas in a DB
Using sqlplus sqlplus / as sysdba run: SELECT * FROM dba_users Should you only want the usernames do the following: SELECT username FROM dba_users
plsql
Printing the value of a variable in SQL Developer
You need to turn on dbms_output. In Oracle SQL Developer: Show the DBMS Output window (View->DBMS Output). Press the “+” button at the top of the Dbms Output window and then select an open database connection in the dialog that opens. In SQL*Plus: SET SERVEROUTPUT ON
DBMS_OUTPUT.PUT_LINE not printing
What is “it” in the statement “it just says the procedure is completed”? By default, most tools do not configure a buffer for dbms_output to write to and do not attempt to read from that buffer after code executes. Most tools, on the other hand, have the ability to do so. In SQL*Plus, you’d need … Read more
Splitting string into multiple rows in Oracle
This may be an improved way (also with regexp and connect by): with temp as ( select 108 Name, ‘test’ Project, ‘Err1, Err2, Err3’ Error from dual union all select 109, ‘test2’, ‘Err1’ from dual ) select distinct t.name, t.project, trim(regexp_substr(t.error, ‘[^,]+’, 1, levels.column_value)) as error from temp t, table(cast(multiset(select level from dual connect by … Read more
Search All Fields In All Tables For A Specific Value (Oracle)
Quote: I’ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.* SELECT * from dba_objects WHERE object_name like ‘%DTN%’ A column isn’t an object. If you mean that you expect the column name to be like ‘%DTN%’, the query you … Read more
What is the Oracle equivalent of SQL Server’s IsNull() function?
coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for … Read more
SQL error “ORA-01722: invalid number”
An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a number. Without seeing your table definition, it looks like you’re trying to convert the numeric sequence at the end of your values list to a number, and the spaces that … Read more
Oracle PL/SQL – How to create a simple array variable?
You can use VARRAY for a fixed-size array: declare type array_t is varray(3) of varchar2(10); array array_t := array_t(‘Matt’, ‘Joanne’, ‘Robert’); begin for i in 1..array.count loop dbms_output.put_line(array(i)); end loop; end; Or TABLE for an unbounded array: … type array_t is table of varchar2(10); … The word “table” here has nothing to do with database … Read more
PL/SQL, how to escape single quote in a string?
You can use literal quoting: stmt := q'[insert into MY_TBL (Col) values(‘ER0002′)]’; Documentation for literals can be found here. Alternatively, you can use two quotes to denote a single quote: stmt := ‘insert into MY_TBL (Col) values(”ER0002”)’; The literal quoting mechanism with the Q syntax is more flexible and readable, IMO.
Number of rows affected by an UPDATE in PL/SQL
You use the sql%rowcount variable. You need to call it straight after the statement which you need to find the affected row count for. For example: set serveroutput ON; DECLARE i NUMBER; BEGIN UPDATE employees SET status=”fired” WHERE name LIKE ‘%Bloggs’; i := SQL%rowcount; –note that assignment has to precede COMMIT COMMIT; dbms_output.Put_line(i); END;