Bug #33774 oracle details i am using
Submitted: 9 Jan 2008 16:06 Modified: 10 Jan 2008 13:18
Reporter: karthik ram Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[9 Jan 2008 16:06] karthik ram
Description:
Hi i have a probelm in creating temporary table.

I am creating the GTT(global temporary table) in my procedure.

when i execute the procedure i am getting an PRAGMA error.Then i include execute immediate command to create the GTT. it got created.

I need to insert some values into this table inside the procedure and also select the values from this table.

I am getting an Exception/error when the controls comes to select stmt.

I am pissed off with these error.anyone can please help me out in solving this probelm.

Also is it necessary to give the Execute permissions to this package/procedure to achieve the code without errors

And also is it necessary to give grants on  Select/insert commands for this GTT.

How to repeat:
any answers please reply me to dkarthik@techmahindra.com
[10 Jan 2008 12:31] Susanne Ebrecht
Many thanks for writing a bug report.

Please let us no, which version of MySQL you use.
Also please add an example, that we can use for reproducing your issue.
[10 Jan 2008 12:40] Hartmut Holzgraefe
As MySQL does not have global temporary tables or a PRAGMA keyword: could it be that you are actually using Oracle, not MySQL?
[10 Jan 2008 13:11] karthik ram
Oracle Client Version : 9.2.0.1.0
Oracle Client DLL : E:\oracle\ora92\bin\oci.dll
TNSNAMES.ora Location : E:\oracle\ora92\Network\Admin\tnsnames.ora

Oracle Server Version : Oracle9i Enterprise Edition Release 9.2.0.6.0 
Oracle Server NLS_CHARACTERSET : WE8MSWIN1252
Oracle Server NLS_NCHAR_CHARACTERSET : AL16UTF16
Current Oracle Session User : ORHUB (Normal)

************************************************************************
SYSTEM INFORMATION
************************************************************************
Operating System : Windows XP (Build 2600) Service Pack 2
Terminal Server? No
[10 Jan 2008 13:15] karthik ram
execute immediate 'CREATE GLOBAL TEMPORARY TABLE TEMP_FAULT_HISTORY ( column1 varchar2(200) )';

execute immediate 'CREATE Insert into TEMP_FAULT_HISTORY values (ps_srv_id)';

		
	        OPEN csr_asset for select OR_FAULT_HISTORY_PCK_ORH_BT.utc_to_bst(sr.X_CLOSE_DATETIME),s.ATTRIB_15 from s_SRV_REQ sr,S_SRV_REQ_X s where s.par_row_id=sr.row_id and sr.X_fault_srv_id in (select * from TEMP_FAULT_HISTORY) 								                   
					select count(1) into V_Count from 
siebel.S_SRV_REQ_X s where s.par_row_id=sr.row_id and sr.X_fault_srv_id in (select * from siebel.TEMP_FAULT_HISTORY). 
			
 
I am able to create the temporary table inside the procedure but while insertion & selection am getting the errors.

Please tell me the exact syntax how to insert the values into the temp table and also selecting the values from that table.
[10 Jan 2008 13:18] Hartmut Holzgraefe
Sorry, but this is the MySQL bug database, not the Oracle one.
For problems with Oracle please refer to oracle.com.