How To Transfer Output File From One Server To Another Using Stored Procedure
Apr 9, 2012
We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).
Functionality of the stored procedure:Generating a tab delimited file and place in a directory.
Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).
Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).
Attached File(s)
Procedure.txt ( 3.28K )
Number of downloads: 4
View 2 Replies
ADVERTISEMENT
Oct 4, 2011
when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14
code which i have written ...
DEClARE
RETCODE NUMBER;
RETMSG VARCHAR2(200);
EXEC SP_SELCT_EMPLOYEE_DATA(277080,'EMPNAME ','EMAIL@EMAIL.COM', 9028045686,111333,25000,'PUNE',35,RETCODE ,RETMSG );
EXIT
Procedure Name :
PROCEDURE SP_SELCT_EMPLOYEE_DATA (
-- A_EMPLOYEE_ID IN VARCHAR2,
--A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE,
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE,
P_EMAIL IN EMPLOYEES.EMAIL%TYPE,
[code]....
View 2 Replies
View Related
Jul 14, 2011
how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...
View 7 Replies
View Related
Oct 3, 2013
I write a file on database server by following
CREATE OR REPLACE DIRECTORY TEST_DIR AS 'c: emp'
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
SQL> select * from all_directories;
OWNER DIRECTORY_NAME
------------------------------ ---------------------
DIRECTORY_PATH
----------------------------------------------------
SYS TEST_DIR
c: emp
SYS PUBLIC_DIR
E:PUBLIC_dir
and then
PROCEDURE run_query(p_sql IN VARCHAR2
,p_dir IN VARCHAR2
,p_header_file IN VARCHAR2
,p_data_file IN VARCHAR2 := NULL) IS
[code]...
but I get error wut_118.
View 1 Replies
View Related
Jun 15, 2011
I want to call an exe file through DB Procedure. How it is possible?
1create or replace procedure proc3
2is
3begin
4 host('calc');
6* end;
from FORMS it works fine but it does not work through DB. I found same question through this link
[URL].........
but no answer is given.
View 2 Replies
View Related
May 16, 2012
I need to execute a .sql file in an oracle stored procedure.
View 9 Replies
View Related
Apr 21, 2011
I ran a scp command to transfer a file from local server to a remote server. when I am trying to kill the process, its giving an error.
ksh: kill: 19258: No such process
If I do a ps -ef | grep scp its showing the process running, but if i try to kill it, its showing me error.
View 7 Replies
View Related
Oct 24, 2013
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies
View Related
Jul 25, 2013
I created a procedure with cursor to inquiry a table. There is more than 1 million records on the table. For each record, I would like to transfer two columns on table as parameter to shell and feedback to procedure as varcahr2.
This is my sample(I only use 10 records for the testing).
create or replace procedure task_file(task_check in varchar2,file_name out varchar2) is
k number :=0;
v_task_number number :=0;
v_task_filename varchar2(30) := null;
cursor jobchk is select task_number,task_filename into v_task_number,v_task_filename from my.tasktable
where task_filename=task_check and rownum <=10;
[code]........
View 1 Replies
View Related
Dec 24, 2012
Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;
View 6 Replies
View Related
Jun 15, 2011
I am facing a issue with one of my programming application. We need to transfer a file through pl/sql programming from a unix server to another destination(target) server.
How can we do it through pl/sql programming. I learnt that oracle streams can be used for this.
I do not know what all code is required for this. send me the code for a sample text file.
I also want to encrypt with a password and zip the file
View 3 Replies
View Related
May 25, 2010
Is there anyway to get a listing of the files in a specified directory on an ftp server using webutil?
View 2 Replies
View Related
Aug 2, 2010
I am creating physical standby database through Rman duplicate command from 2 node rac cluster. rman do all its work. now am try to start the mrp process on physical standby database. I am getting following errors
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
ORA-16191: Primary log shipping client not logged on standby
------------------------------------------------------------
I copied the same pass file from primary to standby and many times verify the same but i got the same error.
View 4 Replies
View Related
May 4, 2010
after precompiling the cmpre.pc file, i got cmpre.c file. when i try compiling this .c file, am facing the following error...
servername:/.../home/usr/compile-> cc -g cmpre.c
ld: 0711-317 ERROR: Undefined symbol: .sqlorat
ld: 0711-317 ERROR: Undefined symbol: .sqlcxt
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
View 1 Replies
View Related
Jun 28, 2012
I need to transfer data from Mirco Soft SQL server to Oracle data base (EBS).
View 3 Replies
View Related
Feb 23, 2012
i want to transfer all users from server A to server B how can i do that along with their rights and password?
View 2 Replies
View Related
Oct 15, 2013
i need a script which can take input from a file and give output to other txt file, something like below :
input.txt
001
002
003
004
Query:
select name from employee where employeecode=<ENTRY FROM INPUT.TXT FILE>
Result should go to OUTPUT.TXT File.
View 7 Replies
View Related
Dec 4, 2012
When I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.
So what am I going wrong here.
set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN
[Code]....
View 7 Replies
View Related
Sep 17, 2013
this PROCEDURE for Paging.
PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;
[code]...
View 1 Replies
View Related
Dec 21, 2011
PFB code i used to schedule a job as per my requirement. And the procedure is executing fine, but when im about to run it is getting hang.
create or replace procedure scheduler_alert(frq varchar2,intrvl number) is
begin
dbms_scheduler.create_job(
job_name=>'scheduler_alert',
job_type=>'stored_procedure',
job_action=>'alertlog_error',
start_date=>SYSTIMESTAMP,
repeat_interval=>'FREQ='||frq||';INTERVAL='||intrvl,
enabled=>true,
auto_drop=>false);
end;
/
When im trying to run the job it is getting hang.
exec dbms_scheduler.run_job('scheduler_alert');
View 3 Replies
View Related
Aug 28, 2009
how to use a synonym in a stored procedure.
I have created a public synonym for a remote table on a different schema.
Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.
how to use a synonym in a stored procedure.
View 5 Replies
View Related
Jan 10, 2012
I want to call a shellscript which is in application sever through a stored procedure in database sever.i dont do this by dbms scheduler.
May i knw some sytax with examples and the settings to change to accept the external procedure call.
View 1 Replies
View Related
Mar 12, 2010
I've written a Java stored procedure that deletes some provided file. The PL/SQL procedure looks like :
procedure delete (file in varchar);
The procedure does the work correctly when the provided file name exists on the DB server, but doesn't when the file is ou of the DB server.
Is there a way to resolve this ?
View 15 Replies
View Related
Jun 11, 2013
I would like to use dynamic sql for an select query with where clause and then use the dynamic sql in pl/sql stored procedure. how to create dynamic sql (select query) and how to use it in pl/sql stored procedure.
View 4 Replies
View Related
Nov 20, 2011
I entered the following procedure code into SQLPLUS for compilation, but it just hangs. I suspect the cause is an infinite loop, but I can't locate it.
CREATE OR REPLACE PROCEDURE populate_sales_fact
AS
BEGIN
INSERT INTO sales_fact
(orderid,
prod_key,
order_day_key,
shipping_day_key,
sales_dollar_amount,
quantity,
cust_key,
emp_key)
[code]....
View 11 Replies
View Related
Apr 18, 2011
I have a sequence my_seq in schema schema1. I have granted select on this sequence to schema2. Doing :
select schema1.my_seq.nextval from dual
in schema2 work as expected. However when I try to compile a package body in schema2 using my_seq in an insert statement, it fails with:
PLS-00302: component 'MY_SEQ' must be declared
What's even stranger is that I have stored procedures that are using the exact same code that are currently compiled and working. Recompiling them yields this error. How is this possible?
View 7 Replies
View Related
Jun 9, 2010
Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....
View 3 Replies
View Related
Jun 3, 2013
I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:
CREATE OR REPLACE PROCEDURE sp_all_attribute_difference
(
i_product_id_new IN NUMBER,
o_product_attr_diff_list OUT product_attr_diff_list,
o_usage_attr_list OUT usage_attr_diff_list
)
IS
[code]....
I am not able to compile tis proc.
View 5 Replies
View Related
May 22, 2012
how to you execute a stored procedure in ORACLE..For example in SQL SERVER its just
EXEC Proc_Name ParameterValues
How the hell do you do this in oracle i just want to test if my stored procedure works.
View 1 Replies
View Related
Jul 23, 2010
connect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.
From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).
(*) for SQL statements: building of parse tree and exec.plan
In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)
According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created.
...
When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.
Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?
View 2 Replies
View Related
