How can you tell if a value is not numeric in Oracle?
REGEXP_LIKE(column, ‘^[[:digit:]]+$’) returns TRUE if column holds only numeric characters
plsql
Convert comma separated string to array in PL/SQL
here is another easier option select to_number(column_value) as IDs from xmltable(‘1,2,3,4,5’);
Is there any way to flush output from PL/SQL in Oracle?
Not really. The way DBMS_OUTPUT works is this: Your PL/SQL block executes on the database server with no interaction with the client. So when you call PUT_LINE, it is just putting that text into a buffer in memory on the server. When your PL/SQL block completes, control is returned to the client (I’m assuming SQLPlus … Read more
Proper way of checking if row exists in table in PL/SQL block
I wouldn’t push regular code into an exception block. Just check whether any rows exist that meet your condition, and proceed from there: declare any_rows_found number; begin select count(*) into any_rows_found from my_table where rownum = 1 and … other conditions … if any_rows_found = 1 then … else … end if;
create table with sequence.nextval in oracle [duplicate]
Oracle 12c We now finally have IDENTITY columns like many other databases, in case of which a sequence is auto-generated behind the scenes. This solution is much faster than a trigger-based one as can be seen in this blog post. So, your table creation would look like this: CREATE TABLE qname ( qname_id integer GENERATED … Read more
Declaring a variable and setting its value from a SELECT query in Oracle
SELECT INTO DECLARE the_variable NUMBER; BEGIN SELECT my_column INTO the_variable FROM my_table; END; Make sure that the query only returns a single row: By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure … Read more
Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
No – you either get all fields (*) OR specify the fields you want.
Truncating a table in a stored procedure
All DDL statements in Oracle PL/SQL should use Execute Immediate before the statement. Hence you should use: execute immediate ‘truncate table schema.tablename’;
What does the colon sign “:” do in a SQL query?
What does “:” stand for in a query? A bind variable. Bind variables allow a single SQL statement (whether a query or DML) to be re-used many times, which helps security (by disallowing SQL injection attacks) and performance (by reducing the amount of parsing required). How does it fetch the desired value? Before a query … Read more
Oracle: how to INSERT if a row doesn’t exist
INSERT INTO table SELECT ‘jonny’, NULL FROM dual — Not Oracle? No need for dual, drop that line WHERE NOT EXISTS (SELECT NULL — canonical way, but you can select — anything as EXISTS only checks existence FROM table WHERE name=”jonny” )