PL/SQL :: How To Pass Changeable Actual Parameters Into Procedure
Mar 29, 2013
I am writing a procedure for the front-end. The end-users need to insert multiple rows of data into history tables in the database (11G). My problem is: the multiple actually parameters is not a fix amount, this time, the amount could be 5, next time, it could be 12. I currently used one string and pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8', the procedure was executed successfully, but cannot insert any data into history table.
See my procedure below (the base table has clob data, I have to consider insert ... select *), I tried to use to_number (CONTACT_MSG_ID), it doesn't work well:
PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS
BEGIN
INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST
SELECT *
FROM TRC_XREF_CONT_EMAIL_MSGS
[code].......
I have a procedure which will execute on every Monday. Same is not executed last Monday. Can I execute the Procedure on some other day with out changing the actual procedure?
In my task I am trying to pass a timestamp datatype as one of the input parameter to a procedure in the package.But I am not sure how to give data for it while executing and testing it from anonymous block.
CREATE OR REPLACE PACKAGE body ac IS PROCEDURE ac_ex_wbdb_keycats( In_Sale_Location_Id IN NUMBER, In_Start_Datatime IN TIMESTAMP, In_Stop_Datatime IN TIMESTAMP, v_refcur OUT sys_refcursor)
I'm migrating forms 6i to forms 10g. I have a mune which has parameters ,which are working in forms 6i. But when i try to comile in forms 10g its poping up error's.
I had a requirement to execute a long running sql query. But the sql query had some parameters to be passed in and at some places i need to press "Enter" . I want to use nohup command and run the sql script using shell script concept. How can i pass the parameters and run the nohup command.
[URL]
I want to keep the sql script in a shell script and run it through nohup command.
how can i keep the above sql in a shell script and run through nohup command
I have a problem here.Normally, we use &p_where inside a sql script in condition sectione.g : select name from member where name like 'a%' &p_where order by name;
may i use this kind of parameter in table section?e.g : select name from &p_table where name like 'a%' and status = 'a' order by name;
the reason i need to do is there are 2 different server. but i need retrieve same info.server ABC have table A but don't have table B and server DEF have table B but don't have table A.
I am trying to pass the parameters to query and I need to pass few parameters to query. Can I make it like like then I run it, then it come up and ask the parameter like this:
SQL> @text para1 XXXXX para2 XXXXX
And then the result of query will come up. I would like to put label for each parameter( likepara1).
declare cursor c is select employee_id from employees; type nst_type is table of employees.employee_id%type; emp nst_type; begin open C; loop exit when C%notfound; fetch c bulk collect into emp; end loop; close c; end;
Above is the sample code, and now i want to pass 'emp' as an input parameter to the procedure.
How can I do that, as emp is of nst_type type and I do not know how my procedure will recognize the datatype...
I am passing a string from vb.net through oracle store procedure. String consists of varchar values for eg '123456;I|N,2424424;O|A'. But how can i call these string values into array using oracle stored procedure? After getting these values into array , i wanted to loop through these values.
I am trying to pass many characters to the in mode parameter using procedure , but i am getting the below error.
ORA-06550: PLS-00172: string literal too long
CREATE TABLE USR_DETAILS ( LOAD_ID NUMBER NOT NULL, LOAD_DATE DATE, USER_VALUE VARCHAR2(4000 BYTE), USERID VARCHAR2(4000 BYTE) ) insert into user_details values('1','2/10/2011','PROD1','USER1'); insert into user_details values('2','2/10/2011','PROD2','USER2'); [code]......
I have written the code as below...
Procedure concept :
Here i will seperate the strings(input parameters) using comma(,) symbol . Here users may pass more than 5000 characters to single in mode parameter, so i have tried with clob datatype but no luck.
CREATE OR REPLACE procedure user_details_proc (user_value1 IN varchar2, user_value2 IN varchar2, user_value3 IN varchar2, user_value4 IN varchar2, user_value5 IN varchar2, userid IN varchar2 ) as [code].......
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.
'Can we pass a dynamic collection variable to the procedure that is called from dbms_job.submit'
I have a package my_package with:
1. record type: ocv_rec
2. collection type: varr_ocv_rec varray(100) of ocv_rec
3. record type: ext_id_rec
4. collection type: varr_ext_id_rec table of ext_id_rec index by pls_integer
5. procedure: analyze_error_152_ll
6. procedure: get_ext_ids_152( v_db_ssa_dtl in varr_ocv_rec, ext_id_type in varchar2, ext_ids out varr_ext_id_rec)
Now here is where the issue resides:Within the definition of analyze_error_152_ll (in body of my_package), I call get_ext_ids_152 using dbms_job.submit
I have a table that has 10 columns which is used to store the customer information (e.g Gender, Age, Name). And i have wrote a store procedure to compare the before and after value of column since there has a parameter to control which column need/no need to be updated while the value being changed.
For example, master table "CUST" has column (NAME, GENDER, AGE). "CUST_TEMP" is a temporary table to store the input image which has the same table structure as "CUST".
DECLARE bef_val CUST%ROWTYPE; aft_val CUST_TEMP%ROWTYPE; BEGIN SELECT * INTO bef_val FROM CUST WHERE name = 'ABC'; SELECT * INTO aft_val FROM CUST_TEMP WHERE name = 'ABC'; [code]....
For the above case, i need to type 3 times of "sp_compare_val ( bef_val.xxx, aft_val.xxx )" on the program. And if the table has more than 10 columns, i need to type more than 10 times.Thus, is it possible to pass in a dynamic variable while calling the store procedure. let say, where the 'xxx' can be definable?
I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:
CODEcreate or replace PROCEDURE "P_Load_Stamp" AS v_exists BOOLEAN; v_length NUMBER; v_blocksize NUMBER;
[code]...
The above codes works perfectly and I scheduled it using SQLPLUS as follows:
CODEvariable jobno number; variable instno number; begin select instance_number into :instno from v$instance;
[code]...
My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):
CODEcreate or replace PROCEDURE "P_Load_Stamp" (vCTIDATE OUT varchar2) AS v_exists BOOLEAN; v_length NUMBER;
[code]...
Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?
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 ;
i have a stored procedure whose input parameter is a varchar2 datatype.i created this procedure for an interface and tibco would be calling my procedure by passing input parameters.my problem is when there is a input string with & (ambersand) then its not working.
even i tried to pass the parameter with & in TOAD, it asks me to enter value for string.look at the sample code below which i wrote for testing purpose:
procedure is:
create or replace procedure testproc(p_in in varchar2) is begin null; end;
i pass parameter as given below:
begin testproc('abc & def'); end;
if i run above script, it asks me to input some string value as it sees & in the string. attached is the image that shows up in TOAD. if i run below script it works. but i dont know how many &'s will be there in the input parameter. hence i cant do. and also TIBCO cant modify the input paramter while calling the procedure.
My Oracle procedure works on two parameters, file type and file name.
exec xml_trans.load_xml('OMG','Sample.xml');
First parameter is xml file type and second parameter is xml file name. XML file is generated by by a web service and keep in a particular location.
Now the requirement is my procedure should pick up the in parameters, once the xml file is generated and my procedure should be running as per schedule. I can execute the procedure in schedule but how can i pick the file name and pass to procedure as soon the xml file is generated?
I am having an Oracle procedure which return ref cursor. I also want to result one more out parameter result. How Can I call the procedure in SQL. Below is the way I am calling my stored procedure with one parameter.
proc_Test (p_resultset=> My_cursor)
How can I call the procedure when I have one more OUT parameter. Second parameter returns 0 or 1.
CREATE TABLE t2 ( id NUMBER, ename2 VARCHAR2(20), sal2 NUMBER, job2 VARCHAR2(20), conid NUMBER
[Code]...
My requirement is like when I am calling the procedure P1 with some values then it should check the table "t2".And table "t2" is linking with table "t3".
So what ever the column "verify" is there, it should check the incoming values against it. If matches success otherwise reject it.Later the incoming values is stored different tables.I am doing it in the above way by hard coding some value.
BEGIN p1(1,'MILLER',500,'ADMIN'); --REJECT p1(1,'MILLER',5000,'ADMIN'); --ACCEPT P1(2,'MILLER',5000,'SALESMAN');--ACCEPT END;
