Sleep function in ORACLE

Short of granting access to DBMS_LOCK.sleep, this will work but it’s a horrible hack: IN_TIME INT; –num seconds v_now DATE; — 1) Get the date & time SELECT SYSDATE INTO v_now FROM DUAL; — 2) Loop until the original timestamp plus the amount of seconds <= current date LOOP EXIT WHEN v_now + (IN_TIME * … Read more

Using bind variables with dynamic SELECT INTO clause in PL/SQL

In my opinion, a dynamic PL/SQL block is somewhat obscure. While is very flexible, is also hard to tune, hard to debug and hard to figure out what’s up. My vote goes to your first option, EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job; Both uses bind variables, but first, for me, is more redeable and … Read more

How can I describe a table in Oracle without using the DESCRIBE command?

You’re looking for USER_TAB_COLUMNS – all the columns, and their descriptions in the schema the query is executed in – or ALL_TAB_COLUMNS – the same except for all tables that user has permission to view. A typical query might be: select * from user_tab_columns where table_name=”MY_TABLE” order by column_id column_id is the “order” of the … Read more

How to increase buffer size in Oracle SQL Developer to view all records?

https://forums.oracle.com/forums/thread.jspa?threadID=447344 The pertinent section reads: There’s no setting to fetch all records. You wouldn’t like SQL Developer to fetch for minutes on big tables anyway. If, for 1 specific table, you want to fetch all records, you can do Control-End in the results pane to go to the last record. You could time the fetching … Read more