ssma/converted procedure question

Question

I've converted an oracle procedure to SQL server using the migration assistant.  I'm getting an error (see below) on the path name.  I copied procedure utl_file_fopen$impl and created utl_file_fopen$impl2, the only difference being the output is varchar(8000) instead of xml.  How do we set the local path in SQL?  In oracle, I believe you set the 'utl_file_dir' parameter in the database.  We're aiming to create a flat file on my local machine as we were doing in oracle.  Is this possible with SQL?    

Msg 59999, Level 16, State 1, Procedure utl_file_fopen$impl2, Line 52
SSMA Oracle exception emulation for [local:oracle:{sysdb|UTL_FILE_FOPEN}:INVALID_PATH_EXCEPTION:3]

Answer 1

Hi Sqlguy10,

 >>Msg 59999, Level 16, State 1, Procedure utl_file_fopen$impl2, Line 52
 SSMA Oracle exception emulation for INVALID_PATH_EXCEPTION

Regarding to the error message you provided, which seems is Oracle issue. That might be cause when accessing a file. So you can try to check that the file being accessed is not already opened by another program, and then retry to use SSMA.

For more information about how to use SSMA for Oracle please refer to this article Getting Started with SSMA for Oracle(OracleToSQL) http://msdn.microsoft.com/en-us/library/hh313198.aspx

If you have any problem, please directly to [e-mail] or you can try to link to Oracle forums.

---

Regards, Amber zhang

Answer 2

i think you specified an error file path:  would you try again as the following calling?

EXEC

@return_value = [ssma_oracle].[utl_file_fopen$impl]

@pDir = N'C:\Documents\s',

@pFileName

= N'heyssion.txt',

@pMode

= 'R',

@pSize

= 6000,

@return_value_argument

= @return_value_argument OUTPUT

Answer 3

still get same message

declare

@return_value varchar(8000)

declare

@pDir varchar(8000)

declare

@pFileName varchar(8000)

declare

@return_value_argument varchar(8000)

exec

@return_value = sysdb.[ssma_oracle].[utl_file_fopen$impl2]

@pDir = N'C:\Documents\s',

@pFileName

= N'heyssion.txt',

@pMode

= 'R',

@pSize

= 6000,

@return_value_argument

= @return_value_argument OUTPUT 

Msg 59999, Level 16, State 1, Procedure utl_file_fopen$impl2, Line 52

SSMA Oracle exception emulation for [local:oracle:{sysdb|UTL_FILE_FOPEN}:INVALID_PATH_EXCEPTION:3]

                                                                                                                                                                   

Answer 4

hi you can chench this link.

http://ssmaoracle.blogspot.in/