SQL & PL/SQL :: Usage Of Loop - Creating A Procedure With 4 Input Parameters

Jun 6, 2012

I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.

create or replace procedure PRC_add_data(
P_Emp_No varchar2,
P_Member_Name varchar2,
P_IDvarchar2,
P_UHID varchar2
)
is
BEGIN
INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID)
END;
/

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Stored Procedure That Takes List Of IDs As Input Parameters

Oct 22, 2010

I need a stored proc that takes list of ids as input parameters. For all these Ids. the proc will send out data from another table as out ref cursor. Sounds very simple yet I am stuck with how do I pass the input list of ids.

View 3 Replies View Related

SQL & PL/SQL :: Collections Fetch - Pass List As Input Parameters To Procedure

May 11, 2012

I have a record type and table of records as follows

type rec is record
(
empid employee.empid%type,
ename employee.ename%type
);

type tab_rec is table of rec;

Suppose data from employee table is fetched into this collection

Can you pls clarify if we can refer to all the rows of empid in the above collection ?

something like tab_rec.empid without using the subscript for referring to the nth row

My requirement isto pass this list as input parameters to a procedure(PL/SQL).

View 3 Replies View Related

Windows :: Calling Stored Procedure With Input And Output Parameters From Batch File?

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

Scheduler :: DBMS_SCHEDULER.CREATE_PROGRAM With Optional Input Parameters

Feb 27, 2013

I have a procedure that has a number of "optional" parameters.

procedure get_files(
file_name_in in varchar2 default 'dummy_file',
layout_in in number default 1,
client_in in number default null,
data_supplier_in in number default 99999
);

This procedure can be called with any combination of the input parameters.I can set up program(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'PLSQL_BLOCK' like this:

begin
sys.dbms_scheduler.create_program(
program_name => 'GET_MY_FILES',
program_action => '
declare
begin
get_files( layout_in => 11111, client_in => 2222 );
end ;',
program_type => 'PLSQL_BLOCK',
number_of_arguments => 0);
end;

My question is: Can I set up programs(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'STORED_PROCEDURE' when I have "optional" parameters? It appears that ALL of the program input parameters must be defined and there is no way to indicate that a parameters is "optional".

View 8 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Creating Loop - Sequence Should Add A Number In The End Of Variables

Jun 12, 2013

SELECT
DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,

[Code]....

My problem, I have the query which works correctly. However as you can see there is always a sequence number in the end of variables and I am not sure the total number of cases, so I have to create a loop for the same query.

I am doing the same things on BREINV1 BREINV2 BREINV3 BREINV4... AND BRENUMINV1 BRENUMINV2... so the sequence should add a number in the end of variables...

View 39 Replies View Related

SQL & PL/SQL :: Usage Of Cursors In Stored Procedure

Feb 22, 2012

i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below

create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;

[Code]...

What should i do to return mulitple values in a single variable??

View 4 Replies View Related

SQL & PL/SQL :: What Is A Global Procedure In Oracle And Its Usage

Aug 6, 2010

what is a global procedure in oracle and its usage

View 5 Replies View Related

SQL & PL/SQL :: Can Have Input Parameter In Procedure

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

SQL & PL/SQL :: Variables In Loop Procedure

Feb 15, 2010

PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2,
P_Txn_Ccy IN VARCHAR2,
P_Allocation IN VARCHAR2,
l_lty_id IN VARCHAR2 ) IS
l_balance_fmt NUMBER := 0;
BEGIN
[code]....

it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..

View 3 Replies View Related

PL/SQL :: Calling Procedure In Loop

Oct 2, 2013

I am calling a procedure with the following parameters 

DECLARE     PROFIT_CENTER NUMBER;   BEGIN   PROFIT_CENTER:= 1109 --( Similarly I am running the proc for 5 more profit_centers 1123,1132, 1122,3211, 1111

one by one by passing values manually, it is taking almost a minute for each profit center

)Prc_test_calc (  PROFIT_CENTER);  COMMIT;   END;
For each profit_center proc is taking 1 mins (Approx) time.  

But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center. Anything wrong with the below loop  

DECLARE     PROFIT_CENTER NUMBER;  cursor c_profit is select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111); --

Plng_pc is a table from where profit_center information is getting fetched  

BEGIN For pc_rec in c_profit loop   Prc_test_calc (  pc_rec .PROFIT_CENTER );  COMMIT; End loop;   END;

View 9 Replies View Related

SQL & PL/SQL :: How To Collect Input Parameter In Procedure

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

SQL & PL/SQL :: Debugging The Procedure With XML As Input Parameter?

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

Passing Sh Variable Value To Input Of PL/SQL Procedure

Aug 30, 2012

how can I pass the sh variable (.i.e file name stored in sh variable called($F)) as a input of below mention procedure (YODEL_XL_ INS_SDG_ COMMER_ PROD)

for F in *.dat; do
#
echo $F
#
#sqlldr apps/apps control=$CONTROL data=$F
# Below Part is used for Add the file name into table
[code]...

View 8 Replies View Related

Procedure Running In Infinite Loop?

Oct 1, 2012

I have a procedure with following code which runs in infinite loop.

BEGIN
LOOP
line :='Test Message';
UTL_SMTP.write_data(l_mail_conn, line || Chr(13));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(NAMESFILE);
END;

I executed the procedure and disconnected from sql developer by killing the sql developer process from task manager. Reconnected and changed the code and when i tried to compile the procedure. It is not compiling, because the procedure is already running in infinite loop by some other session.

Is is possible to stop the running procedure without killing the session.What will happen if the procedure is not stopped which is running in infinite loop.When I queried the following found that the session is still ACTIVE and CPU used showing as '0' and executing the procedure.

select
SST.VALUE CPU, S.USERNAME, S.SID, s.status,S.SERIAL#, SQL_TEXT
FROM
V$SESSION S,
V$SQLTEXT_WITH_NEWLINES T,
V$SESSTAT SST
WHERE S.SQL_ID IS NOT NULL

[code]....

Can I do force drop or alter this procedure?

View 3 Replies View Related

SQL & PL/SQL :: Procedure Cursor For Loop Don't Execute

May 13, 2010

I'm running a PL/SQL with a For Loop cursor, but when trying to execute it doesn't run. It is as if there is no data, but I ran the cursor separately in a SQL Plus session and it runs perfectly. I'm enclosing the file with the procedure.

View 18 Replies View Related

Update Store Procedure (List Of Input)

Jan 10, 2012

The current update store procedure that I have updates a list of input provided, but it there are fields that are left blank, they are being updated as null in the database.

I'm having a trouble creating a store procedure that will just update the provided fields only.

View 1 Replies View Related

SQL & PL/SQL :: Passing Input Parameter To Like Variable In Procedure

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

SQL & PL/SQL :: Procedure To Get Or Sum The Closer Numbers To On Input Number?

Aug 14, 2011

in have one input number for example 300 and in database i have 3 number as below :

1 100
2 200
3 50

i need procedure to give the closer number , for this example the output should return 2&1

example 2 :

input number 100
database number
1 50
2 100
3 140

the output should be 2 and so ....

View 2 Replies View Related

PL/SQL :: How To Concatenate Quote To Procedure Parameter Input Value

Dec 6, 2012

create or replace procedure ab(a in varchar2, b in varchar2)
is
test varcha2(8);
begin
if (a is not null) then
for i in(select c
from t
where c between ||'''||a||'''|| and ||'''||b||'''||)
loop
test:=i.c
end loop;
end if;
end;

I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.

View 7 Replies View Related

SQL & PL/SQL :: Removal Of Time In Input Parameter In Stored Procedure?

May 15, 2011

I'm creating a stored procedure wherein I'm the field wherein I will use for date input parameter is having a datetime format.

The format that we need for user's to input is 'DD-MON-YYYY' (without the minutes and seconds.)

how should I proceed with the creation of stored procedure for this?

View 2 Replies View Related

SQL & PL/SQL :: Convert As Procedure Based On Input Date Parameter

Dec 12, 2012

i have the below query

select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects

[code]...

need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.

View 7 Replies View Related

Input Parameter From Arraylist To Stored Procedure In Oracle?

Nov 7, 2013

i have created a package & stored procedure which is working fine when i am passing single value to my package or stored procedure. But what approach i should take if i have to pass multiple values (ArrayList) like (from eg empid like I1001,I1002,I1003,I1004,...) in my input parameter . I am using C# & Oracle

CREATE OR REPLACE PACKAGE PKG_x AS type t_cursor is ref cursor;
procedure Proc(cur_x out t_cursor,
param_emp in varchar2 DEFAULT NULL);
END PACKAGE PKG_x;

[Code].....

View 2 Replies View Related

How To Use Parameters In Plsql Procedure

Oct 21, 2010

I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.

Example:
in excel the column has : 'ABC','GEH','HGT',LTP'

create or replace procedure abc(temp_col varchar2)
.
....
....
...
SELECT COLA, COLB, COLC
FROM TABLE_TEMP
WHERE TCOL IN temp_col;

This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.

View 2 Replies View Related

SQL & PL/SQL :: Using NVL Function With Parameters In A Procedure?

May 16, 2011

I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.

I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.

The code for the procedure is

CREATE OR REPLACE PROCEDURE add_new_classes
(p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number
p_course_id classes.course_id%TYPE,
p_period classes.period%TYPE,
p_frequency classes.frequency%TYPE,

[code]....

I then use this to test it

BEGIN
add_new_classes(1002,'first','daily',3002);
END;

and the error I get is

Quote:ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
1. BEGIN
2. add_new_classes(1002,'first','daily',3002);
3. END;

View 5 Replies View Related

SQL & PL/SQL :: Created One Procedure With 4 In Parameters

Aug 20, 2013

i created one procedure with 4 in parameters and 1 out parameters(return the value) but whenever execute this procedure i got the error i.e pl/sql: Compilation unit analysis terminated.

PLS-00201:identifier T_USER_PLDATA' must be declared.

CREATE OR REPLACE PROCEDURE GET_USER_PLDATA1( V_PROD_LINE_CD IN VARCHAR2,
V_BUS_GROUP_CD IN VARCHAR2,
V_BUS_UNIT_CD IN VARCHAR2,
V_COUNTRY_CD IN VARCHAR2,
V_USER_PLDATA OUT T_USER_PLDATA)[c
[code]....

View 7 Replies View Related

PL/SQL :: To Create A Procedure Which Have 3 Parameters

Jan 11, 2013

I would like to create a procedure which have 3 parameters. suppose in Date_birth, in Dept , out number_of_records

if i pass null value to one of the parameter it should return all values . if a pass value to the parameter it should return as per parameter values

Create or replace Procedure Emp_Test (P_dop in Date,P_Dept in number , P_noof_records out Number,
p_recordset Out Sys_Refcursor) as
Begin
OPEN p_recordset FOR
Select Emp_cd,Emp_name, Date_of_Birth,Dept,Count(emp_Cd) noof_records
From Emp_Master Where Date_of_birth =P_date_of_Birth
and Dept=P_dept ;
End ;

View 2 Replies View Related

SQL Query To Find CPU Usage And Memory Usage

Jun 28, 2013

Is their any query to find cpu usage & memory usage of all the queries currently running on DB? 

View 5 Replies View Related

PL/SQL :: Stored Procedure Taking Long Time When Updates In Loop

Feb 5, 2013

This is my stored procedure

I have below store procedure:

create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,

[Code]...

This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved