Escaping single quote in PLSQL

I do this sort stuff a fair bit (usually generating insert/update statements). You just need to use the replace function to turn all the ‘ into ”. i.e. Change it to: str_comment:=’COMMENT ON COLUMN ‘||rec.table_name||’.’||rec.column_name ||’ IS ”’||REPLACE( rec.description,””,”””)||”’; ‘ ;

Grant Select on all Tables Owned By Specific User

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;

How do I get column datatype in Oracle with PL-SQL with low privileges?

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

How to generate a GUID in Oracle?

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