It's not completely clear to me. You want to reference a schema other than the parsing schema and want to use substitution strings for it?
You can not write something like
select * from :some_schema.dual
because :some_schema is a bind variable - it doesn't work like that.
Substitution strings (#...#) work because apex replaces those before the text gets parsed.
If all you want to do is to create more substition string then you can do so on application level by going to the application defition ("Edit application properties") and scrolling down to substitutions.
I have been trying to find a similar thread but had no luck. I want to avoid hard-coding schema names in my reports. I have tried to use a Substitution string but I don't seem to be able to reference it by just doing :default_schema. I already reference the parsing schema like this: #OWNER# which is saving me a lot of time. Is there any way to create additional variables like that one instead of those substitution strings that can't be parsed in a query?
Will you actually get any benefit from using substitution strings? They are also hard-coded at design time. You could use application items if you need something dynamic.
Bind variable syntax (:default_schema) will not work for lexical substitution of substitution string and application item values in report queries. You have to use static text (&DEFAULT_SCHEMA.) references (and be aware of the potential for SQL injection that this exposes).
I wouldn't recommend using schema qualifiers directly in APEX applications. I would use grants, synonyms and views at the database level.
Thanks for your quick answer! That's exactly what I was trying to do. I want to replace the schema name with some sort of global variable other than the parsing schema visible to all pages like in the following example:
SELECT ID, NAME, AGE
I use "..FROM #OWNER#.employees when the table is in the parsing schema but if I need to reference any other I have to hard-code it. Btw, the use of #OWNER# there is correct, isn't it?
I have already created more substitution strings in application properties but the query won't parse. Is there any workaround?
If your table is owned by the parsing schema there's is need to prefix it with something like :default_schema or #owner#
If your table is owned by another schema, it is better to create, as the parsing schema, a (private) synonym for that table. And again you won't need to prefix anything in your report queries.
I think it would be helpful to the group to understand why you need to derive/set schema name for non-parsing schemas in your application.
Things I have had to deal with in the past
1. you have multiple identical schemas (schema1.employees , schema2.employees) and need to have one report that will run against any of your schemas.
2. you have different schema names between your dev, test and prod databases and you want your application to be portable.
3. you are building a commercial piece of software where your users can name their schemas whatever they want.
No matter what, please heed the warning of security risks fac586 mentioned and protect yourself accordingly.
Look at the answer posted by fac586. As he says, you can't use bind variables for substitutions, you'd have to use &SCHEMA. references. This creates the risk of sql injection, which can be a major security threath to both your application and your database.
This is really something you should handle at a database level.