You can do this: select * from user_source where upper(text) like upper(‘%SOMETEXT%’); Alternatively, SQL Developer has a built-in report to do this under: View > Reports > Data Dictionary Reports > PLSQL > Search Source Code The 11G docs for USER_SOURCE are here
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
Well, it’s not a single statement, but it’s about as close as you can get with oracle: BEGIN FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner=”TheOwner”) LOOP EXECUTE IMMEDIATE ‘grant select on ‘||R.owner||’.’||R.table_name||’ to TheUser’; END LOOP; END;
Since the release of Oracle 12c it is now easier to rearrange columns logically. Oracle 12c added support for making columns invisible and that feature can be used to rearrange columns logically. Quote from the documentation on invisible columns: When you make an invisible column visible, the column is included in the table’s column order … Read more
Each tablespace has one or more datafiles that it uses to store data. The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile. To find out if the actual limit is 32gb, run the following: … Read more
I’m able to run an SQL query by piping it to SQL*Plus: @echo select count(*) from table; | sqlplus username/password@database Give @echo execute some_procedure | sqlplus username/password@databasename a try.
ANY (or its synonym SOME) is a syntax sugar for EXISTS with a simple correlation: SELECT * FROM mytable WHERE x <= ANY ( SELECT y FROM othertable ) is the same as: SELECT * FROM mytable m WHERE EXISTS ( SELECT NULL FROM othertable o WHERE m.x <= o.y ) With the equality condition … Read more
ALL_TAB_COLUMNS should be queryable from PL/SQL. DESC is a SQL*Plus command. SQL> desc all_tab_columns; Name Null? Type —————————————– ——– —————————- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT … Read more
You can use the SYS_GUID() function to generate a GUID in your insert statement: insert into mytable (guid_col, data) values (sys_guid(), ‘xxx’); The preferred datatype for storing GUIDs is RAW(16). As Gopinath answer: select sys_guid() from dual union all select sys_guid() from dual union all select sys_guid() from dual You get 88FDC68C75DDF955E040449808B55601 88FDC68C75DEF955E040449808B55601 88FDC68C75DFF955E040449808B55601 As … Read more
“Is there any way to avoid the mutating table error without using a temporary table for values or an autonomous transaction?” tl;dr no. The mutating table error is caused by querying the table which owns the trigger, or tables which are involved in a foreign key relationship with the owning table (at least in older … Read more