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;
/
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.
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
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,
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".
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>.
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...
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??
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?
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..
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;
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.
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]...
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
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.
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.
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.
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.
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;
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.
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;
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]....
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 ;
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.
