Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Linked Server Query with EXEC () AT

915593Feb 6 2012 — edited Feb 7 2012
Hi Folks,

I am facing some issue .The scenario is like this : I've created a LINKED SERVER in SQL Server 2005
to connect to ORACLE database(so far so good) .After that i had to call their Stored Procedures, in which i was facing some problems.
So i decided to create stored procedures in SQL with the script similar to what the Oracle Stored Procedure looks like.
I used EXEC (' ') AT <SERVER NAME> syntax to create the SP. The main issue is the WHERE clause which is illustrated below

SYNTAX in SQL SP
----------------
WHERE (LOG_FUN_ID =? OR LOG_FUN_ID=NULL)
AND (LOG_STATUS_CODE=? OR LOG_STATUS_CODE=NULL)
AND (LOG_CHNL_ID=? OR LOG_CHNL_ID=NULL)
AND (T1.LOG_CREATION_TMSTMP LIKE ? || ''%'' OR T1.LOG_CREATION_TMSTMP=NULL)
AND (T1.LOG_CREATION_TMSTMP LIKE ? || ''%'' OR T1.LOG_CREATION_TMSTMP=NULL)

group by LOG_FUN_ID,LOG_STATUS_CODE

',
@LOG_FUN_ID,@LOG_STATUS_CODE,@LOG_CHNL_ID,@LOG_CRET_FRM,@LOG_CRET_TO) AT <servername>


----------------------------------------

SYNTAX in ORACLE SP
-------------------

WHERE (LOG_CREATION_TMSTMP >= to_timestamp(p_LOG_CRET_FRM, 'DD-MM-YYYY HH24:MI:SS') or p_LOG_CRET_FRM is null)
AND (LOG_CREATION_TMSTMP < to_timestamp(p_LOG_CRET_TO, 'DD-MM-YYYY HH24:MI:SS') or p_LOG_CRET_TO is null)
AND (LOG_CHNL_ID = p_LOG_CHNL_ID or p_LOG_CHNL_ID is null)
AND (LOG_FUN_ID = p_LOG_FUN_ID or p_LOG_FUN_ID is null)
AND (LOG_STATUS_CODE like p_LOG_STATUS_CODE ||'%' or p_LOG_STATUS_CODE is null)

group BY log_fun_id,log_status_code ;

The parameters are preceded by "p" in the above Oracle script.


So the SQL Script is not returning any records even though data is present in Oracle DB.

Edited by: 912590 on Feb 6, 2012 11:00 PM

Comments

662454
Result of my own research is, that utl_file.fcopy isn't meant for copying binary files as xls or pdf files are. Copied file's length had changed a bit which can be explained by fcopy putting CR+LF characters after each "line". Whatever line it finds in a binary file. Thus, it's not suitable for my needs.

The DBMS_FILE_TRANSFER package won't do either, because it requieres directory objects as source and destination. Because physical file structure provides a special folder for each file and its versions, I would have to dynamically (re-)create directory objects for each copy process. This could be done by dynamic DDL but can't be done because of the whole process being started by a trigger which doesn't allow the auto commit of DDL statements. Yes, I could wrap that in an autonomous transaction but, well, that seems to be no good practise. And more, the copy method of that package relies on a very specific file size in exact multiples of 512 bit (I didn't quite get the reason for that).

Seems, I will have to put all that files (which are more than 1000 an more than 2GB) in BLOBs. Wonder, what problems that will give to me...

Yet, any hints and opinions are still welcome.

Regards
Claus.
662454
Problem solved by using DBMS_SCHEDULER ablilities.

Claus.
Rnr-Oracle
To expand on what I assume the solution was (for reference).

The poster probably used the dbms_scheduler get_file and put_file functions which are new in 11g.

They allow files to be copied to/from the operating system as a particular user. The username and password of the operating system must be stored in the database as a credential object using dbms_scheduler.create_credential.

Hope someone finds this useful,
Ravi.
662454
Hi there

We are driving a 10g version, so I wasn't able using the get_file/put_file methods. No, I just created an executable type job and provided a rather lengthy string as job action.
This action string contains calling cmd.exe witch again calls copy.bat.
Like that:
"c:\winnt\system32\cmd.exe /q c:\copy.bat path\to\sourcefile path\to\destinationfile > nul"

No other parameters for cmd.exe should be provided, in contradiction to what other examples say. Copy.bat is a wrapper batch file for native copy command.

That's all.

Claus.
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 5 2012
Added on Feb 6 2012
#oracle-provider-for-ole-db, #windows-and-net
0 comments
427 views