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.
begin
testproc('abc &'||'def');
end;
View 9 Replies
ADVERTISEMENT
Feb 22, 2011
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;
/
View 10 Replies
View Related
May 9, 2012
I am fairly new to oracle, here's what I am doing.
Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));
[Code]....
I need to test the procedure how do I pass the inputs for the type object csc_info?
View 2 Replies
View Related
Mar 28, 2012
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?
View 1 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
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
Apr 5, 2011
To keep things simple: lets say that this is my procedure:
create or replace procedure testSp(x out sys_refcursor )
is
begin
open x for
select 1 from dual;
end;
how would i be able to execute this from with in sqlTools to see the result ??? i have tried everything...
my goal is in the end to execute this from Magic 8 (to those who herd of it). p.s this needs to work in oracle 8
View 4 Replies
View Related
May 10, 2013
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
Msg_codes Language_id Language_name msg_text_details
101 1 ENG "msg_test" "Bundle_msg_text" 1
"msg_test_1" "Bundle_msg_text_2" 2
View 5 Replies
View Related
Mar 29, 2010
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;
[code]...
View 1 Replies
View Related
Dec 23, 2012
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)
View 2 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 3, 2010
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.
View 1 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
Oct 19, 2011
I have the following error when I try to pass a Procedure parameter carrying db_link name to a stored procedure:
SQL> CREATE OR REPLACE PROCEDURE AFESD.P_DM_VCONTRACT_ITEM (CON_CONNECTION VARCHAR2)
2 IS
3 T_CONNECTION VARCHAR2(50);
4 T_SQL VARCHAR2(500);
5 BEGIN
6 T_CONNECTION := 'X_DM_TEST@' || CON_CONNECTION;
[code]....
The insert statement succeeds when I try to run DBMS_OUTPUT.put_line output.
View 4 Replies
View Related
Jun 2, 2010
I am trying to pass a PL/SQL table as a parameter to a procedure and then using that table, update the records, but I am getting an error like:
ORA-06550: line 30, column 10:
PLS-00306: wrong number or types of arguments in call to 'UPDATE_STATUS'
Find the code below:
CREATE TABLE test_pl(empno VARCHAR2(20), empname VARCHAR2(40), empsts VARCHAR2(10));
INSERT INTO test_pl
VALUES ('0001', 'A', 'Y');
INSERT INTO test_pl
VALUES ('0002', 'B', 'N');
INSERT INTO test_pl
VALUES ('0003', 'C', 'Y');
INSERT INTO test_pl
VALUES ('0004', 'D', 'Y');
[code]....
View 4 Replies
View Related
Sep 1, 2010
I want to pass a table as a parameter to a procedure.
As an example:
TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;
However, I want to give this parameter a default of null... is this possible?
procedure myproc(p_param1 in varchar2, p_tab in my_tab default null)
View 11 Replies
View Related
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
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
Feb 26, 2012
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].......
View 2 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
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
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
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
Apr 6, 2011
Using pl/sql block , i tried to have a certain input values from Sql prompt but it doesn't work when i invoke it.
Here the simplest
declare
a number(4);
begin
for i in 1..10 loop
a := &a;
end loop;
end;
/
View 9 Replies
View Related
Aug 27, 2011
I want to put some line in my .sql file. The below is my issue :-
SQL>spool /tmp/Tablespace_Remove.ksh
SQL>echo >#!/bin/ksh---- I want to enter this line to my Tablespace_Remove.ksh
SQL>spool off;
View 3 Replies
View Related
May 23, 2012
Can i use lexical parameter within my procedure
select '' employee_no, ptdv.fiscal_year, ptdv.trans_month, ptdv.location_code, ptdv.acc_code,ptdv.acc_desc,sum(ptdv.EARNING)-sum(ptdv.DEDUCTION) debit,0 credit,1 tag
from pr_trans_dtl_v ptdv
where ptdv.trans_month=nvl(mtrans_month, ptdv.trans_month)
[code].......
see the highlighted row. can i use and ptdv.trans_type_row in &vtype instead of and ptdv.trans_type_row in ('R','A')
where the vtype is varchar2(100) and vtype:='(''R'',''A'')';
View 1 Replies
View Related
Oct 9, 2012
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).
View 11 Replies
View Related
Jan 26, 2012
create table a_user
(
u_name varchar2(30)
, grp varchar2(30)
)
;
INSERT INTO a_user VALUES ('abc', 'new');
[code].....
sql:
SELECT
a.grp
, a.g_tot
FROM a_user b
,
(
SELECT
[code].....
getgrpname is a function to get grp when I input the u_name
Now I want to create a proc to display the output from the sql when I give the p_u_name as input. I am thinking some thing like this:
create or replace procedure get_u_name_data (p_u_name IN VARCHAR2, rc_data OUT SYS_REFCURSOR) IS
IS
BEGIN
OPEN rc_data FOR
SELECT
a.grp
[code].....
how to print output in SQL developer
View 2 Replies
View Related
Feb 8, 2012
In SQL*Plus i am accepting value like below examples
1] ACCEPT v_number prompt 'Enter Number ='
2] column db_owner new_value db_owner;
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 ?
View 1 Replies
View Related