Home » SQL & PL/SQL » SQL & PL/SQL » Global Temp Tables and PL/SQL (10)
Global Temp Tables and PL/SQL [message #322257] Thu, 22 May 2008 23:18 Go to next message
Vidyalakshmi
Messages: 4
Registered: May 2008
Junior Member
Here is what the structure of my code:
Create Global Temp Table 1 on commit preserve rows
as
select <>
from tables;

create Global Temp Table 2 on commit preserve rows
(
);
PL/SQL Block;
declare
Var
begin
Process data from Table 1 and Insert into table 2;
end;
select * from table 2;
In SQL Developer, I run the above 4 SQLs it runs succ and gives me the data in TABLE 2 when I do the last select stmt.

When I put the above in a .SQL file and use my scheduler which uses SQLPLUS to run the SQL script from the unix server. The last select stmt gives 0 rows. It sees the table but does not see the data processed by the previous PL/SQL block. Any ideas?
Shouldn't it still be the same session even if SQLPLUS was running the file and in that case, it should see the data just like SQL DEveloper was doing it right?

What is the issue here?
Re: Global Temp Tables and PL/SQL [message #322258 is a reply to message #322257] Thu, 22 May 2008 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
privs acquired via ROLE do not apply within PL/SQL procedures

to prove/validate this is sqlplus do

SET ROLE NONE;
-- Then invoke SQL statement

[Updated on: Thu, 22 May 2008 23:20] by Moderator

Report message to a moderator

Re: Global Temp Tables and PL/SQL [message #322259 is a reply to message #322258] Thu, 22 May 2008 23:21 Go to previous messageGo to next message
Vidyalakshmi
Messages: 4
Registered: May 2008
Junior Member
What privileges? the same user Id is running in SQL Developer tool, it is running succ.

How is SQLPLUS different?

I apologize I am not clear.
Regards,
Re: Global Temp Tables and PL/SQL [message #322260 is a reply to message #322257] Thu, 22 May 2008 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
Believe me or not.

Reality is what reality is.

Re: Global Temp Tables and PL/SQL [message #322261 is a reply to message #322258] Thu, 22 May 2008 23:25 Go to previous messageGo to next message
Vidyalakshmi
Messages: 4
Registered: May 2008
Junior Member
anyways, added the SET ROLE none to the script. IT did not help.

Thanks,
Re: Global Temp Tables and PL/SQL [message #322264 is a reply to message #322257] Thu, 22 May 2008 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


You can claim whatever think is your reality may be;
but if you really knew what you were doing you would not be asking for assistance here.

I tend to believe CUT & PASTE.
Re: Global Temp Tables and PL/SQL [message #322269 is a reply to message #322261] Thu, 22 May 2008 23:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add some logging to the pl/sql program unit to see if you really insert records in batch-mode
Re: Global Temp Tables and PL/SQL [message #322270 is a reply to message #322269] Thu, 22 May 2008 23:42 Go to previous messageGo to next message
Vidyalakshmi
Messages: 4
Registered: May 2008
Junior Member
Yeah I did that, and it does insert records into the table but the select outside the block cannot see it.

Thank you for your time and interest. Appreciate it.

Is doing the select inside the PL/SQL code the only option here.

Regards,


Re: Global Temp Tables and PL/SQL [message #322275 is a reply to message #322270] Thu, 22 May 2008 23:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If the statements are all in the same script, and the script runs fine from sqlplus, then the script should run in the same way when you run it (connected as the same user) unattended in sqlplus.
How would Oracle know whether you are watching it or running it unattended?
The only options I can think of are:
- difference in data between daytime and nighttime
- different userid (sure you run it against the same db? default sids can be different, tnsnames might point to different instance)
- your sqlplus settings differ from the server's (e.g. you use dbms_output.put_line, but you have serveroutput off)
- a when others exception handler without a raise, so there might occur an error without you seeing it.
Previous Topic: Database link not working for count(1) but works for count(*)
Next Topic: problem with storing large data in nclob
Goto Forum:
  


Current Time: Fri Dec 02 12:11:04 CST 2016

Total time taken to generate the page: 0.13253 seconds