Home » SQL & PL/SQL » SQL & PL/SQL » Schema name (Oracle 10.2)
Schema name [message #572198] Fri, 07 December 2012 06:12 Go to next message
pointers
Messages: 336
Registered: May 2008
Senior Member
Hi,

Sometimes we share couple of scripts to DBA to run over production.

Because of the schema name is not mentioned explicitly in the script and dba runs as a super user, the script get fail with the object is not found.
e.g
DECLARE
   v_job_id   NUMBER;
BEGIN
   IF pkg_test.f_job_id_lesen IS NULL
   THEN
      v_job_id := pkg_test.f_starten ('BB');
   END IF;
END;
/
Above, the schema name is not mentioned for pkg_test package

I know, eiether I need to give the schema name or ask the DBA to run the script in the specific schema.

But I am looking for any commnad (sqlplus or sql) which can be mentioned in the script so that the script assumes the schema name by default.

Upon searching I found, the below command.
ALTER SESSION SET CURRENT_SCHEMA="scott"; 


Is there any best approach for this.

Regards,
Pointers
Re: Schema name [message #572199 is a reply to message #572198] Fri, 07 December 2012 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the best a approach.
Another one is to use proxy user and authentication but it is more complex and not better.

Regards
Michel
Re: Schema name [message #572200 is a reply to message #572199] Fri, 07 December 2012 06:27 Go to previous messageGo to next message
pointers
Messages: 336
Registered: May 2008
Senior Member
Hi Micheal,

Thank you very much for the details.

Is it ok to use this command (ALTER SESSION SET CURRENT_SCHEMA="scott";) in the scripts which will be run over production, I dont know if there is any side effects or anything that i need to consider in the production environment.

Regards,
Pointers
Re: Schema name [message #572201 is a reply to message #572200] Fri, 07 December 2012 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is
ALTER SESSION SET CURRENT_SCHEMA=SCOTT; 


Quote:
I dont know if there is any side effects


All commands after have SCOTT schema as scope.

Quote:
anything that i need to consider in the production environment.


1/ Go back to the current user schema as soon as you no more the other one
2/ Test the script in a test environment before.

Regards
Michel
Re: Schema name [message #572202 is a reply to message #572201] Fri, 07 December 2012 06:37 Go to previous message
pointers
Messages: 336
Registered: May 2008
Senior Member

Thank you very much Michel.

Regards,
Pointers
Previous Topic: Different plans on select query
Next Topic: Difference in num_rows and COUNT function.
Goto Forum:
  


Current Time: Sun Apr 20 13:24:14 CDT 2014

Total time taken to generate the page: 0.15127 seconds