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
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
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
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
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
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
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
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
Aug 6, 2010
what is a global procedure in oracle and its usage
View 5 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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