srakaflicks.blogg.se

• #Create syn code

It’s not a user: SQL> select user_id,username from dba_users where username like 'DEMO%' īut it’s another entry in user$ with type 3 which is for schema synonyms: SQL> select user#,name,type#,ctime,spare2 from sys.user$ where name like 'DEMO%' Which is type 1 in user$ (type 0 is for roles): SQL> select user#,name,type#,ctime from sys.user$ where name like 'DEMO%' Īnd I create a synonym DEMO2 for it: SQL> create schema synonym DEMO2 for DEMO So I have a DEMO schema: SQL> select user_id,username from dba_users where username like 'DEMO%' SQL> alter system set "_enable_schema_synonyms" = true scope=spfile Ok, let’s go: SQL> connect sys/oracle as sysdba But there is an undocumented parameter: SQL> select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like '%schema%synonym%' Let’s give a try to the syntax: SQL> create schema synonym DEMO2 for DEMO ĭoesn’t work. But that’s not a reason not to try it – in lab only of course. But that feature is not yet supported: its implementation has probably been postponed again. There are a lot of internal references to 12g’ written when the ‘c’ was not yet decided. So this blog post is not about mulitenant but about Schema Synonyms. insert into audit_actions values (224, 'DROP SCHEMA SYNONYM') insert into audit_actions values (222, 'CREATE SCHEMA SYNONYM') I’ll blog about it soon, but in the meantime when checking its definition in cataudit.sql it came upon the following: /* SCHEMA SYNONYMS will be added in 12g */

#Create syn code

Then you can only ever refer to it using: "schema1"."function1"Īnd (assuming you have the permissions to create a synonym) then your code should work.Ivica Arsov ( has made an interesting comment about AUDIT_ACTIONS object link table. CREATE FUNCTION "schema1"."function1" /* (.) */ RETURNING NUMBER If you have created the schema and the function both using lower-case quoted identifiers (don't do this, its bad practice) then you will have to ALWAYS refer to them using quoted identifiers with exactly the same case.

However, "schema1"."function1" would fail as the object is called "SCHEMA1"."FUNCTION1" and, by using quoted identifiers, you are forcing case-senstivity in the identifiers (rather than the default behaviour of implicitly converting non-quoted identifiers to upper case and then case sensitivity is not an issue). provided that quoted identifiers are in upper-case and non-quoted identifiers can have any case. You can then refer to your function using: "SCHEMA1"."FUNCTION1"Įtc. Then your function uses non-quoted identifiers and its identifier will be converted to upper-case in the data dictionary. If you have created your function using: CREATE FUNCTION schema1.function1 /* (.) */ RETURNING NUMBER

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees Quoted identifiers are case sensitive.īy enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: "employees" Nonquoted identifiers are not case sensitive.