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

SQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?

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

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

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

DBMS_JOB - Pass Date As Parameter To Another Stored Procedure?

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

SQL & PL/SQL :: Removal Of Time In Input Parameter In Stored Procedure?

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

Input Parameter From Arraylist To Stored Procedure In Oracle?

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

Client Tools :: How To Execute Stored Procedure With Cursor Within PL/SQL

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

PL/SQL :: How To Pass Input Parameter For Nested Table

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

Client Tools :: Execute A Procedure With Parameter Of Object Type?

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

SQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function

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

SQL & PL/SQL :: Collections Fetch - Pass List As Input Parameters To Procedure

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

Pass Array To Oracle Stored Procedure?

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

SQL & PL/SQL :: Can Have Input Parameter In Procedure

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

SQL & PL/SQL :: Error With Execute Immediate To Pass Db_link To Stored Procedure?

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

SQL & PL/SQL :: How To Pass Table As Parameter To Procedure

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

SQL & PL/SQL :: Pass Table As Parameter To Procedure?

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

SQL & PL/SQL :: Stored Procedure That Takes List Of IDs As Input Parameters

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

SQL & PL/SQL :: How To Collect Input Parameter In Procedure

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

SQL & PL/SQL :: Debugging The Procedure With XML As Input Parameter?

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

SQL & PL/SQL :: Pass More Characters To In Mode Parameter Using Procedure

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

SQL & PL/SQL :: Passing Input Parameter To Like Variable In Procedure

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

PL/SQL :: How To Concatenate Quote To Procedure Parameter Input Value

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

SQL & PL/SQL :: Convert As Procedure Based On Input Date Parameter

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

Windows :: Calling Stored Procedure With Input And Output Parameters From Batch File?

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

Client Tools :: Having Multiple Input Values

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

Client Tools :: How To Input Line To Sql Script

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

SQL & PL/SQL :: Use Lexical Parameter In Stored Procedure

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

Client Tools :: Pass Parameters To Oracle SQL Query?

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

Client Tools :: REF CURSOR To Print Output From Given Input?

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

Client Tools :: How To Restrict Input Of NULL Values

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







Copyrights 2005-15 www.BigResource.com, All rights reserved