ORA-12704: character set mismatch
you should do select COALESCE (EMAIL, n’NO EMAIL’) from crmuser.accounts to convert the literal to NVARCHAR. eg http://sqlfiddle.com/#!4/73929/1 vs http://sqlfiddle.com/#!4/73929/2
you should do select COALESCE (EMAIL, n’NO EMAIL’) from crmuser.accounts to convert the literal to NVARCHAR. eg http://sqlfiddle.com/#!4/73929/1 vs http://sqlfiddle.com/#!4/73929/2
The error you are getting is either because you are doing TO_DATE on a column that’s already a date, and you’re using a format mask that is different to your nls_date_format parameter[1] or because the event_occurrence column contains data that isn’t a number. You need to a) correct your query so that it’s not using … Read more
check your casing, the name is typically stored in upper case SELECT * FROM all_source WHERE name=”DAILY_UPDATE” ORDER BY TYPE, LINE;
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,””,”””)||”’; ‘ ;
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
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;
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.
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
Something like create or replace procedure my_proc( p_rc OUT SYS_REFCURSOR ) as begin open p_rc for select 1 col1 from dual; end; / variable rc refcursor; exec my_proc( :rc ); print rc; will work in SQL*Plus or SQL Developer. I don’t have any experience with Embarcardero Rapid XE2 so I have no idea whether it … Read more