Client Tools :: How To Pass Ambersand (&) As Input Parameter To A Stored Procedure
Oct 26, 2010
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.
i need to pass table as input parameter in stored procedures. during the run time, i am getting error
CREATE OR replace TYPE emp_type IS OBJECT ( id NUMBER(4), ename VARCHAR2(10)); CREATE OR replace TYPE emp_type_tab IS TABLE OF EMP_TYPE; CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB) AS BEGIN FOR i IN 1.. in_emp_type.COUNT LOOP dbms_output.Put_line(in_emp_type.Id(i)); END LOOP; END; /
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 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 a doubt how to pass input parameter for nested table which is declared as input parameter in procedure.
CREATE TYPE t_example AS OBJECT(msg_text VARCHAR2(100), bundle_msg_text VARCHAR2(100), version NUMBER(10)) / create type t_msg_details ia table of t_example /CREATE TABLE table_nested_sample (msg_codes NUMBER(10), language_id NUMBER(10),
[Code]...
How to call this procedure I want to insert data like this
I am trying to execute the procedure from toad, where the type is like an object.How can I do that?
CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype; CREATE OR REPLACE TYPE list_objtype AS OBJECT ( Emp_ID NUMBER(5), EMp_Name VARCHAR2(32 BYTE) );
In the following procedure I am calling list_t, but how would i pass the values to it?
PROCEDURE modify_settings ( p_List in list_t, p_info out varchar2) DECLARE p_List TABLE;
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as ( select 1 as t from dual union all select 2 as t from dual union all select 3 as t from dual union all select 4 as t from dual union all select 5 as t from dual ) select * from data where t in (2,3)
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 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?
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.
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.
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].......
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.
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 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).
select a.SETTINGVALUE db_owner from ABC a, DEF b where a.application_id = b.application_id
I want to confirm after executing this .sql that this variables v_number and db_owner should not have NULL value. I did searched a lot for SQL*PLUS commands. Only way i can check this is by writing anonymous PL/SQL block but i dont want to go for that option. How to restrict input of NULL values ?