Auto complete for tables are not happening when we querying other schema objects

[jpvalapad]

Dear Team,

After we execute "ALTER SESSION SET CURRENT_SCHEMA = ",

Auto complete feature on SCHEMA_XYZ objects are not working.

Is this feature is available in the latest version of PL/SQL developer?

Regards,
John

 

[Marco Kalter]

It should work fine. Can you provide an example and also let me know your PL/SQL Developer version?

 

[Wharton]

Is this the correct execution of the schema btw?

 

[jpvalapad]

I am using the latest version of PL/SQL developer IDE (13).

1. I have a schema called "JOHN" where I have to login and support our production schema "XYZ_PROD".Whatever I am allowed for to do in "XYZ_PROD" objects are granted to "JOHN" Schema.

2.I modified "AfterConnect.sql" as below,

SET FEEDBACK OFF;
BEGIN
IF USER ='JOHN' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=XYZ_PROD';
END IF;
END;
/
SET FEEDBACK ON;

3. Now when I logged in "JOHN" user, I can able to access "XYZ_PROD" objects without using "XYZ_PROD." (.(dot)) , but auto complete feature is not working.

 

[Marco Kalter]

This seems to work fine for me. I modified the script so that user SYS uses current schema SCOTT. After logging on in PL/SQL Developer as SYS, I can see the correct [schema] suffix in the connection list:

sys@chicago as sysdba [SCOTT]

I can now type the following:

select * from emp e where e.|

At this point the Code Assistant shows the columns of the SCOTT.EMP table.

Can you show an example that does not work for you?

 

[jpvalapad]

Thank you Marco

The method which you are explained is working fine with me (i.e. Calling a column name with an 'Alias'). I am describing here autocomplete without an alias name. If you logged in 'Scott' schema and start typing a table name of the same schema, (after '_') the full table name will available in the option list. But this is not happening When we logged in 'Scott' schema and trying to access an 'XYZ' schema.
(Please note that , We are changing alter session current_schema to 'XYZ' from 'Scott' before accessing 'XYZ' schema objects ) .

Regards,
John.

 

[Quantum]

Given the following:
Schema: KT is the owner of dB objects: TEST (Table), KT (Table), V_TBS (View), OBJ (Procedure)
Schema: STUDENT is the owner of dB objects: STUDENT, COURSE, GRADE,... (all tables)

I start a Command Window in R13 and login as KT.
From the SQL prompt I type: SELECT COUNT (*) FROM KT.
After the dot, the Coding Assistant does not pop up. No response from pressing F6. I drop the table KT and try again. This time, the Coding Assistant pops up and offers me the selection: TEST, V_TBS and OBJ
The above test against Oracle's SQL*Developer: after the dot, the coding assistant shows all 4 objects (TEST, KT, OBJ and V_TBS)

In R13: From the SQL prompt I type: SELECT COUNT (*) FROM STUDENT.
After the dot, the Coding Assistant pops up and offers me a selection of dB objects belonging to schema STUDENT. This is a correct behaviour.

I issue the following command: ALTER SESSION SET CURRENT_SCHEMA = STUDENT
I type: SELECT COUNT (*) FROM KT.
The Coding Assistant pops up and correctly displays the objects belonging to KT schema.

I type: SELECT COUNT (*) FROM STUDENT.
After the dot, the Coding Assistant pops up and offers me a selection of column names of the table STUDENT.
This is incorrect. Inside the FROM clause the object preceding the dot is a schema not a table.
The above test (altering session test) against Oracle's SQL*Developer: The Coding Assistant does not pop up after the dot. However, if I just type the schema name STUDENT (without the dot) then its Coding Assistant displays:
1. STUDENT.student (note that it shows me the schema name in upper case)
2. TGT.student (TGT is just another schema in my dB which has a table named student)
3. student (I guess this is a user with a user icon to its left)

Hope this helps

 

[jpvalapad]

Dear Quantum ,

Thanks for the detailed response,

My issue here is different,I will try to explain it through the simulation steps here,

1. I have two schema (a) ABC and (b) XYZ.
2. I created a table EMP_TEST on ABC schema
3. After creation I just run the select query multiple times on the same session (SELECT * FROM EMP_TEST)
4. I grant all the privileges to XYZ of table EMP_TEST.
5. I close the session and IDE
6. I login PL/SQL with ABC credentials again and launch SQL window
7. I start typing emp (first 3 letter) and then IDE brings the rest in the option list and I can see EMP_TEST name in the option list and I can choose it from there.
8. Logout ABC and login XYZ
9. Run ALTER SESSION SET CURRENT_SCHEMA = ABC from XYZ
10.Follow step (7) and the intended output of step(7) is not getting from the IDE. However I am getting auto complete on table columns after I am using a "." following table name.
11. I am expecting here an option list of table names after I am type three or four letter of a table name.

Regards,
John.