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


ADVERTISEMENT

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 :: 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

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 View Related

SQL & PL/SQL :: Google Translator - How To Pass Special Characters In URI Type

Aug 30, 2012

I am using http url for Google translator to convert into different languages.

While passing the string, to escape the white spaces, I am using utl_url.escape . But many of my words contain special characters like '+ , @ , $ , ) ,. ,; [, ] etc.

Not sure how to pass these special characters in uri type.

httpuritype
('http://translate.google.com/?hl=' ||
p_from || '&layout=1&eotf=1&sl=' || p_from ||
'&tl=' || p_to || '&text=' ||
utl_url.escape (p_words) || '#').getclob();

few of my words for translation are

VALERIANA OFFICINALIS L. + HYPERICUM PERFORATUM L. + MELISSA OFFICINALIS L.
VALERIANA OFFICINALIS L. + HYPERICUM PERFORATUM L. + [ MELISSA OFFICINALIS] L.

View 16 Replies View Related

SQL & PL/SQL :: How To Pass Parameter In A View

Nov 16, 2012

How to pass parameter in a view.

create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60' then
case when a.ccn <> '091' then
[code]........

in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding? here i need to pass the date as a parameter from a select query to view the data from the above view!

View 4 Replies View Related

SQL & PL/SQL :: Pass Date Parameter

Jun 22, 2010

In a procedure i need to pass date parameter, to check how to do this. I create a simple procedure, but it giving

PLS-00049: Bad Bind Variable 'FD'
PLS-00049: Bad Bind Variable 'TD'

How to resolve .......

CREATE PROCEDURE pass_date_parameter
AS
fd VARCHAR2 (10) DEFAULT '01-01-2010';
td VARCHAR2 (10) DEFAULT '01-06-2010';
ffd VARCHAR2 (10) DEFAULT '01-01-2010';
ttd VARCHAR2 (10) DEFAULT '01-06-2010';

[Code]....

View 19 Replies View Related

Forms :: How To Pass Two Parameter For Graphs

Mar 5, 2010

i had a chart developed by graphics builder in which I am passing one parameter(ie year) and displaying the chart.Now i want to pass two parameter into chart.How to do this here i have attached my chart view and procedure which executes and display the chart from forms.

coding when button pressed

PROCEDURE CHART_DISPLAY IS
pl_id ParamList;
pl_name VARCHAR2(100) := 'tempdata';

[Code]....

In the above procedure i had one parameter directly am passing value of year '2009' now i want to pass two parameter like between 'year1' and 'year2' for getting chart between two years.

View 1 Replies View Related

Forms :: Best Way To Pass 100+ Parameter In Form A To B?

Dec 18, 2012

If I want to pass lots of informations (100 parameter) from standard HR assignment form to my custom form. I am using fnd_function.execute('called form') to launch new form.

I thinking of creating 100 parameter object but not sure it has limitation of number or not. Another ways I googled are shared PL/SQL library or parameter list which I still doubt.

View 3 Replies View Related

PL/SQL :: How To Pass Value From A Table Into Parameter Of A Function

Nov 8, 2012

I have written as function :

create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is
TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v_x ty_varchar_assoc;
v_y ty_varchar_assoc;
v_inter ty_varchar_assoc;

[Code]....

Now i need to pass the values to the parameter from a table? How can i do that?

View 2 Replies View Related

SQL & PL/SQL :: How To Pass Parameter To Cursor And Print Details

Aug 15, 2010

Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a parameter of DATE data type to the cursor and print the details of all the employees who have joined after that date.

DEFINE P_HIREDATE = 08-MAR-00

Test the PL/SQL block for the following hire dates: 08-MAR-00, 25-JUN-97, 28-SEP-98, 07-FEB-99.

I don't know how to pass parameters. So far I have this:

SET serveroutput ON;
DECLARE
p_HIREDATE DATE := 08-MAR-00;
CURSOR c_DATE_CUR(P_HIREDATE DATE) IS
BEGIN
OPEN c_DATE_CUR();

View 6 Replies View Related

Forms :: How To Pass Parameter From Form To Report

Feb 23, 2011

I have created a simple form & report based on 2 tables Cust & cntry.I just want to display all customers of region selected in the criteria form. I am uploading the files.

Other than selection criteria report is fine.

View 4 Replies View Related

Forms :: How To Pass Parameter From Report To Form

Jan 23, 2013

How can i pass the parameter from reports to form. means how can i open particular form from my report. means if i write query

select * from emp

this emp table report open now i just want open only emp no 10 form from this report so which parameter i have to pass.

View 2 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

PL/SQL :: How To Pass Year And Month As Parameter Into A Block

Apr 26, 2013

I am populating a time dimension table. One job is to assign business day with sequence number on monthly basis and by year. Business day does not include weekends and all federal holidaies.

I created a PL/SQL block to handle this job. It works. However, I haven't figure out how to pass the month number and year number into PL/SQL block automatically. Now I have to manually enter year and month number (on Toad) to pass to block to make it work. If I create it as stored procedure and write another block to call it, I can pass year and month number into parameter there like this:

exec my_sp_name ('1', '2013');

But all of these are not good enough. I want to use code to automatically pass yesr and month number into PL/SQL block. So that I can make it better. . here is the PL/SQL block I created.

declare i number := 1;
begin
for r in (select * from time_dim where calendar_month_number = &month and calendar_year= &year and business_day_flag = 'B' order by date_sk_id)
loop

[Code]....

View 11 Replies View Related

Reports & Discoverer :: Pass Parameter From Forms To Report

Oct 5, 2013

I can not Pass parameter from forms to Report.

I use three parameter for report. I want to show the employee who has been hired between from_date to_date,it work well in report builder. but I want to pass parameter from forms to report and pdf format. I upload my work (this is actually a zip File, change file type txt to zip just before open).

View 5 Replies View Related

Forms :: How To Pass Parameter From Criteria Form To Report

Dec 21, 2011

have a customer table with following columns.
1 - Cust_ID
2 - Cust_nam
3 - Cntry_id

I have another table country.
1 - Cntry_id
2 - Cntry_nam
3 - Region

Now I want to view report for all customers of a certain region selected by user in criteria form.How can I do this?

View 1 Replies View Related

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

PL/SQL :: How To Pass Table Of Record As Parameter To Server Page

Apr 20, 2013

Is there any way to pass table of record as parameter to the plsql server page? For instance, I can define a new type in package specification:

create or replace package tst_type
as
type tab_expr_sec is table of varchar2(50) index by pls_integer;

type rec_expr is record (
prim_expr varchar2(50),
expr tab_expr_sec
);
[code]....

View 5 Replies View Related

PL/SQL :: How To Pass List Of Values In Where Clause Of Query Parameter

Sep 18, 2012

I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:

declare
TYPE crMain_record is RECORD (
v_code             dummy.v_code%type,
n_no               dummy.n_no%type,

[Code].....

END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..

And lv_character values are commung from a setup table
lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
--And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the

View 3 Replies View Related

Forms :: Possible To Pass Records Displayed To Report As Parameter

Jul 20, 2011

In my application (forms6i) initially data will be displayed in a non-database block after a lot of calculations and validations which is time consuming.

Now, if the user want to print it as a report, we store the displayed data in a dummy table and run the report using that dummy table and then delete records from it.

Is it possible to pass the records displayed to the report as a parameter so that I can avoid Unnecessary add/delete records?

View 1 Replies View Related

JDeveloper, Java & XML :: Pass Null As Argument In Any Method Parameter?

Feb 23, 2013

why we pass null as a argument in any method parameter....What is its use in method reference..

View 1 Replies View Related

Application Express :: 4.2 - How To Pass Date Picker Parameter Into Query

Aug 19, 2013

steps to achieve this report I need a Date Column on top of the page Depending on the selected date, Result should be displayed below for e.g. 

suppose i select a date as 14-aug-2013

Enter Report Date ___________________ ( Date Picker)  Location Name      Report Date        Quantity           valueXYS                       14/08/2013          5000            1200000ABCD                     14/08/2013          2000                24000-------------------------------------------------------------------------------------------------TOTAL                                              7000            1224000 

View 9 Replies View Related

Performance Tuning :: Pass 4000 - 5000 Parameter In IN Clause?

Mar 2, 2011

In my below query example , i have to pass more than 4000-5000 paramter in "a1.num" in below query. what is the best way to handle this, also if I pass more than 2000 paramter , the query takes a long time to execute. How can we solve the performance issue as well how I can pass more parameter.

SELECT c1, c2,
TO_CHAR (c3, 'HH24:MI'),
c4,
c5,

[code]...

View 12 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

Application Express :: How To Pass Parameter / Create New Region / Set Display Point

May 3, 2013

On package application "Customer Tracking", there is good example about Modal Page. Quite simple but it work quite nicely.The step to create this Modal Page:

- Create new region, set display point: After Header and use region template: Modal Region and set the Static ID (eg. ActivityModal)
- Create the page item and put it on the above region (eyample: Name, Company etc.)
- Create a button, set the action to "Redirect to URL" and enter the URL target to: javascript:openModal('ActivityModal');

Now when you click on the button, the modal page will be display.How can I pass a parameter to the modal page, example: I want to pass text 'Johnny' to the Name page item which is on the modal region.

View 2 Replies View Related

SQL & PL/SQL :: Can We Pass REF_CURSOR To Externel Procedure

Jun 13, 2013

Can we pass REF_CURSOR to external procedure by DB_LINK..

View 2 Replies View Related

SQL & PL/SQL :: Can Pass Nested Table To Procedure

Nov 12, 2011

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...

View 4 Replies View Related







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