How To Transfer Parameter Between Procedure And Shell
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
ADVERTISEMENT
Mar 29, 2013
I'm working on trying to write a shell script to parse a parameter file, but at the same time I want to be able to overwrite the parameter file settings with other command line settings. For instance if my par file had export/import settings for the username, password, schema, etc;
I wanted to run the same export/import with those settings for a different schema. I want to be able to put the schema=<different_than_par_file> after the parfile=<parfile.par and have the parfile be read and applied for everything except the different schema.
Right now I'm storing the cmd line and parsing it again looking for other parameters besides the parfile.
View 4 Replies
View Related
Feb 10, 2012
I've a requirement of running shell script in pl/sql procedure. I thought of doing this by using dbms_scheduler package. I believe while executing the jobs using dbms_scheduler the owner has to be part of DBA group, but as per my company policy (security issue) only DBA users has to be part of this group and i'm not able to . So is there anyway i can execute shell script in pl/sql procedure other than using dbms_scheduler.
View 2 Replies
View Related
Jul 22, 2013
I have a procedure that checks in the table if the daily refresh was successful or not for the previous day. If it failed I would like to receive an email, which I accomplish with calling my EMAIL() procedure. Otherwise call my program_scheduler that will run a shell script.
Below is my procedure:
create or replace
PROCEDURE RD_ODS_REFRESH_LOG
IS
BEGIN
FOR i IN
(
SELECT RESULT FROM RD_REFRESH_LOG
[code].....
And here is CALL_SHELL job that my procedure calls:
BEGIN
SYS.dbms_scheduler.create_job (
job_name => 'CALL_SHELL',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/load_semantic.sh',
[code].....
My ELSE statement never gets executed, why?
View 18 Replies
View Related
May 30, 2012
I have one procedure , which calls the DBMS_scheduler.run_job , which calls the Shell Script. now based on the shell execution i need to return the message lines to procedure.
View 1 Replies
View Related
Nov 26, 2006
I am trying to call a shell script through external procedure. To summarize the steps that I followed:
Placed the library file,i.e., the .so file in bin folder of the database server. I have not compiled the .c file and created this. I have taken this from another application.
Then I logged into the sqlplus. Created one library pointing this .so file.
create or replace library ext_proc as '/ngs/app/ivndrptd/bin/execute_cmdlib.so';
After this created the external procedure. This got compiled without any errors.
SQL> create or replace procedure ext_proc_wrapper(in_script IN varchar2)
as external
library ext_proc
NAME "execute_cmd"
parameters (in_script string);
/
Procedure created.
I am trying to execute a script test.ksh which contains a touch command to create a test.txt file.
However irrespective of the procedure running successfully the script is not getting called. I tried several ways of calling the script which are as follows
SQL> exec ext_proc_wrapper('sh /ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.sh');
PL/SQL procedure successfully completed.
SQL>
SQL> exec ext_proc_wrapper('/ngs/app/ivndrptd/test.ksh');
PL/SQL procedure successfully completed.
SQL>
View 5 Replies
View Related
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
View Related
Sep 5, 2012
I am writing a procedure in which I have a input string parameter in the following way..('NYC,ATL,OKC,KAC,LA'). I need to use that string to search values in the table.
Example
create or replace procedure search_city(p_string varchar2)
/*
paramater will be like ('NYC,ATL,OKC,KAC,LA')
*/
is
v_city varchar2(40)
begin
for i in (select city_name from cities
where city_code in (p_string )
) loop
v_city := i.city_name;
end loop;
end ;
View 2 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
Nov 1, 2010
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have this in one of the packages WCL_LIB:
TYPE vc2_255_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
and i use it in one of the procedures as
PROCEDURE "WCL_EVENTS"
(p_event_id IN NUMBER,
p_event_arr IN Wcl_Lib.vc2_255_arr,
p_model IN VARCHAR2 DEFAULT NULL,
p_model_code IN VARCHAR2 DEFAULT NULL
but the calling procedure doesnt have any array.... Can I declare something like in the procedure to be called
p_event_arr IN Wcl_Lib.vc2_255_arr DEFAULT NULL,
I tried, but doesnt seem to work? so how to call the procedure, which has a array as mandatory, but calling one doesnt have any?
View 5 Replies
View Related
Jan 12, 2012
I have the procedure with out parameter is ref cursor.
l_sql VARCHAR2(32767);
BEGIN
l_sql := 'select query with appending procedure IN aparameters';
OPEN rc_rpt FOR l_sql;
Here procedure IN parameter is a string with comma separated value which is appended in the dynamic query IN clause.So some time the size exceeded more then 32767 and getting error.If i am using normal parametrized cursor this issue is not there,but i want to return only ref cursor for some java purpose.My oracle version is 10g.
View 4 Replies
View Related
Oct 11, 2010
i am trying to create a procedure that will take an input and based on that input it will delete or update a row, if the input is wrong we will dbs_ouput a line telling the user to enter the input again. can i have a input parameter in a procedure and insert multiple rows with a single insert on a procedure?
View 6 Replies
View Related
Aug 31, 2012
I have a requirement when i will pass the table name as a parameter in the procedure then the series of stametmemt is performed on the table whose name has been passed.
code look like below
PROCEDURE PROC_CREATE_PARTITION(TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);
V_TABLE_NAME VARCHAR2(30);
[code]....
I am getting a error PL/SQL: ORA-00933: SQL command not properly ended
this error is show in the first select statement line where i have used the variable in the from list of table.
View 5 Replies
View Related
Jan 4, 2011
I have got this procedure:
CREATE OR REPLACE PROCEDURE GET_NUM_ROWS(TABLE_NAME VARCHAR2) AS
NUM_ROWS NUMBER;
BEGIN
SELECT COUNT(*) INTO NUM_ROWS FROM TABLE_NAME;
DBMS_OUTPUT.PUT_LINE(NUM_ROWS);
END;
When I try to compile it, the compiler says:
ERROR at line 4: PL/SQL: ORA-00942: table or view does not exist.
View 3 Replies
View Related
Mar 10, 2012
I have a pl/sql procedure having IN, OUT and IN-OUT parameters, this procedure in called from front end application. Now I need create a script to run this procedure from back end (sql prompt) and the result must be same as the front end application call to this procedure.
For the procedure I don't want to pass IN parameter instead to pick the value from the package where the derivation is defined. how to run this procedure from sql prompt without passing value for IN parameter.
View 6 Replies
View Related
Sep 6, 2013
I see example for a dbms_scheduler setting in parameter values but what about if it is an out parameter ?
create or replace procedure vd_tst (v_id out number) asbegin v_id := to_number(to_char(sysdate,'YYYYMMDDHH24MISS'));end vd_tst
how do I set a hook to do an alert let say for dbms_ schedulerlet sayif v_id mod(9)==0 then <alarm>
View 9 Replies
View Related
Jul 4, 2012
I have requirement to create a procedure that accepts an Array as IN parameter , Query a table using this array and return the result as refcursor . I tried to get it as below but not working .
CREATE OR REPLACE TYPE ARR_ID AS TABLE OF VARCHAR2(20);
/
CREATE OR REPLACE
PROCEDURE TEST_ARRAY
(P_ARR_ID IN ARR_ID,
P_CUR_OUT OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_CUR_OUT FOR
SELECT * FROM EMPLOYEE WHERE EMP_ID IN (SELECT * FROM TABLE(P_ARR_ID ));
END;
/
View 3 Replies
View Related
Jan 30, 2009
I am wanting to write a procedure which takes in a TIMESTAMP datatype as a parameter. I only really want the time, not the date part.I am struggling to understand however what format the inserted TIMESTAMP would take. E.g
function_name( 'timestamp')
--would this be;
function_name('12:00');
--or something along those lines?
View 1 Replies
View Related
Feb 7, 2011
I knew procedure can be run like 'EXEC (****);' and can be written like
'Create or replace procedure(**** IN VARCHAR2)
BEGIN
END
/
But the requirements are 'Parameter can be come from referencing DB table column?')
If table has value like PARMTAG PARMVAL, I read LOGSTORETYPE data and if exist, fetch the PARMVAL value 7 to my parameter? LOGSTORETYPE 7
Is it possible?
The reason is batch process was written by ProC and DB upgrade time with exadata, we want to change batch to Procedure about our DB delete.
View 2 Replies
View Related
Mar 4, 2010
I have a procedure, which is getting called from ado.net with a input parameter named p_inp_oids which is a character string like '(122,323,434,444)',
My procedure body contains a query whose WHERE clause contains
AND OID IN p_inp_oids
I cannot post the whole procedure. Also OID is number.How to collect that input parameter in my procedure?
View 24 Replies
View Related
Feb 20, 2010
I want to have a dynamic collection as a parameter to procedure.This collection should access dynamic column set.
while calling this proc user may not aware of what column set is going to be sent to this proc.
View 8 Replies
View Related
Jun 2, 2010
I am trying to pass a PL/SQL table as a parameter to a procedure and then using that table, update the records, but I am getting an error like:
ORA-06550: line 30, column 10:
PLS-00306: wrong number or types of arguments in call to 'UPDATE_STATUS'
Find the code below:
CREATE TABLE test_pl(empno VARCHAR2(20), empname VARCHAR2(40), empsts VARCHAR2(10));
INSERT INTO test_pl
VALUES ('0001', 'A', 'Y');
INSERT INTO test_pl
VALUES ('0002', 'B', 'N');
INSERT INTO test_pl
VALUES ('0003', 'C', 'Y');
INSERT INTO test_pl
VALUES ('0004', 'D', 'Y');
[code]....
View 4 Replies
View Related
Feb 2, 2011
I have a query like this:
*************************
SELECT vendor_id, summary_flag
FROM ap_suppliers
WHERE vendor_id = :param; --'4551'
The Results:
************
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N
Then I create the procedure:
*****************************
CREATE OR REPLACE PROCEDURE myproc4 (
p_vendor_id IN ap_suppliers.vendor_id,
p_summary_flag OUT ap_suppliers.summary_flag
)
AS
BEGIN
[code]....
Warning: compiled but with compilation errors.
I want to create a procedure that call vendor_id (parameter) and the output like the this:
VENDOR_ID SUMMARY_FLAG
-----------------------
4551 N
View 4 Replies
View Related
Dec 28, 2011
how to use ref_cursor as out parameter for insert procedure
This is the procedure iam having and when compiling it is througing error is
create or replace procedure proc_insert_policies_temp1(
i_policy_id in int,
i_corporate_name in varchar,
i_corporate_address in varchar,
i_divisionid in number,
[code]....
View 3 Replies
View Related
Sep 1, 2010
I want to pass a table as a parameter to a procedure.
As an example:
TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;
However, I want to give this parameter a default of null... is this possible?
procedure myproc(p_param1 in varchar2, p_tab in my_tab default null)
View 11 Replies
View Related
May 23, 2012
Can i use lexical parameter within my procedure
select '' employee_no, ptdv.fiscal_year, ptdv.trans_month, ptdv.location_code, ptdv.acc_code,ptdv.acc_desc,sum(ptdv.EARNING)-sum(ptdv.DEDUCTION) debit,0 credit,1 tag
from pr_trans_dtl_v ptdv
where ptdv.trans_month=nvl(mtrans_month, ptdv.trans_month)
[code].......
see the highlighted row. can i use and ptdv.trans_type_row in &vtype instead of and ptdv.trans_type_row in ('R','A')
where the vtype is varchar2(100) and vtype:='(''R'',''A'')';
View 1 Replies
View Related
Jun 23, 2011
I have a procedure where it contains an XML as input parameter.And i have to debug this procedure in pl/sql developer to check whether this is the correct XML and inserting the XML data in to my table. But when debugging, I am getting an Error "EXPRESSION IS OF WRONG TYPE". Below is my
declare
px_xml xmltype;
begin
px_xml:='Xml content';
--call the procedure
package_name.procedure_name
(parameter1 => :parameter1,
px_xml => px_xml,
pvo_out_mesg => :parameter3);
end;
Is there any other way to debug XML in plsql developer.
View 3 Replies
View Related
Oct 4, 2013
I have an procedure with four in parameters in it. Now, I want to add an out parameter to it. The issue is the procedure is called from many other procedures and triggers. Since we can't set default value to out parameter as we can for in parameter, how we can achieve the requirement without having to update all the places where this procedure is being called.
View 3 Replies
View Related
May 13, 2013
I've the following package having a procedure with OUT parameter as refcursor .The following is the sample example having same structure with different table name,exception handling in my dev environment
CREATE OR REPLACE PACKAGE TEST_PACK
IS
Type refCursor is REF CURSOR;
PROCEDURE TEST_PROC (out_data out refCursor);
END;
[code]...
i am getting the follwoing errror
ORA-00932: inconsistent datatypes: expected - got.
View 3 Replies
View Related
Dec 27, 2012
I have a procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input
i.e.
The following sql when executed at sqlplus prompt gives correct results.
select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');
How can I transform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.
i.e I should be able to call the procedure as follows
sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;
Should the cursor inside the procedure be built as follows
cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';
Iam unable to build the sql for the cursor.
View 3 Replies
View Related