I have a requirement where in I have to store large data in one of the database columns using stored procedure.
I have declared the column as CLOB as it can store upto 4GB and also the input parameter for the procedure as CLOB. But when I am trying to pass large data it is not allowing to store as it is throwing literal string too large error.
Is there any restriction in the data size to be passed to the stored procedure?
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.
This procedure is not working properly.
create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;
the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1
how can I pass the sh variable (.i.e file name stored in sh variable called($F)) as a input of below mention procedure (YODEL_XL_ INS_SDG_ COMMER_ PROD)
for F in *.dat; do
#
echo $F
#
#sqlldr apps/apps control=$CONTROL data=$F
# Below Part is used for Add the file name into table
[code]...
I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.
AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3
[code]....
Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.
I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.
1. What is the best to way (design)to pass input parameter for the function.
2. How to generate hierarchy in xml using DBMS_XMLGEN
Trying to set/send/input CLOB value to function.
I tried:
Clob size is about 4-5KB of data.
DECLARE clobz CLOB:=EMPTY_CLOB();
BEGIN
SELECT '<claim>'+
'<lbo_id>34</lbo_id>'+
'<claim_type_id>11</claim_type_id>'+
'<claim_subtype_id>11</claim_subtype_id>'+
[code]...
Error:
Error report:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
[code]...
I'm using Oracle 9i. How can i check for a string if it contains only numbers or letters? Otherwise i should return false.
View 6 Replies View Relatedi 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 RelatedHow to use date as an input parameter,im supposed to use varchar2 as the data type
CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 )
IS
sender VARCHAR2(30) := 'xyz@gmail.com';
mailhost VARCHAR2(100) := 'host address';
TAB VARCHAR(2) := CHR(9);
mail_conn utl_smtp.connection;
[code].....
cursor c1 is
--select activity_date,procedure_name,status_message,error_desc from staging_activity_log where rownum between 1 and 10 ;
select activity_date,procedure_name,status_message,error_desc from staging_activity_log where error_desc is not null and trunc(activity_date) >= to_date(p_date,'DD-MON-YYYY') ;
BEGIN
[code].....
when i execute i get
BEGIN mail1 ('xxx@gmail.com,yyy@gmail.com,'28-jan-2008'); END;
Error at line 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MAIL1", line 27
ORA-06512: at line 1
I have a scenario where I have to pass a paramter to "in statement". When I run the query from SQL plus its working fine.......but when I run the query at run time it doesnot come back with the results. I am sure its formating issue.
This is how I create a paramter
_sbInStatement.Append("(");
foreach (ListItem item in _listBox.Items)
{
[Code]....
oCmd.Parameters.Add(":InStatement", OracleDbType.Varchar2).Value = InStatement;
I have a procedure with 20 parameters, acutely it is to update a table and each param represents respective columns in a table. I want to update only few selected columns(random), as of now am passing Null as param values for remaining.
Is there any way to ignore the unnecessary parameters instead of passing NULL value.My Proc call looks like...
Exec MyProce(IN_ID, NULL, NULL, NULL,NULL,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);
Or
Exec MyProce(IN_ID, NULL, NULL, NULL,'SOME_VALUE' ,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);
we have a table which name is empl have two column (comapnyname,jobdate) i want output after passing parameter which name is month which value is like(feb 2011,or mar 2011 or apr 2011 or jun 2011 etc) output should come the nameof company with jobdate whose jobdate is equal to 3 or greate than three
name of company,jobdate whose job date in particular month is greater than three or equal to three the purpose of this we want to find only the name of company where we visit morethan three times in particular month table structure is
create table empl(companyname varchar2(10),jobdate date)
insert into empl values('ABC','1-feb-2011')
insert into empl values('ABC','10-feb-2011')
insert into empl values('ABC','21-feb-2011')
insert into empl values('xyz','18-feb-2011')
insert into empl values('xyz','1-feb-2012')
insert into empl values('xyz','1-apr-2011')
insert into empl values('ABC','28-feb-2012')
output:
after passing parameter month=feb 2011
companyname jobdate
abc 1-feb-2011
abc 10-feb-2011
abc 21-feb-2011
How can I pass multiple value in one parameter
example
select * from table
where table_cd in ('01','02','03','04')
here i want to put multiple value like above query by select 1 value in list
like when user select 'A'
THE VALUE PASS IN WHERE CLAUSE ('01','02','03')
FOR 'B' ('03','045','07')
FOR 'C'('044','046','078')
i have a SQL query . In the where clause of the query , there is function called dimension_intersect which takes 2 parameters.Now , when the 2 dimensions passed intersect , the function returns "Y" and the query works as expected
Function in where clause is as below
Dimension_intersect(Dimension1,select dimension2 from product where product_sys_id=1)='Y'
The above function works fine till only 1 record is returned by the inner subquery used in above function. But when "select dimension2 from product where product_sys_id=1" return 2 dimensions then the function fails as it can accept only one dimension at a time . I am not allowed to edit this function. I need to find a way to pass both the dimensions one at a time.
Query
-----
SELECT DISTINCT PROD_LONG_NAME,
P.PROD_ID_USER,
MRS.RESTRICTION_SEVERITY,
MRT.RESTRICTION_TYPE,
RESTRICTION_COMMENT RESTRICTION_DETAIL,
[code].......
I have got this procedure:
CREATE OR REPLACE PROCEDURE GET_NUM_ROWS(TABLE_NAME VARCHAR2) AS
NUM_ROWS NUMBER;
BEGIN
SELECT COUNT(*) INTO NUM_ROWS FROM TABLE_NAME;
DBMS_OUTPUT.PUT_LINE(NUM_ROWS);
END;
When I try to compile it, the compiler says:
ERROR at line 4: PL/SQL: ORA-00942: table or view does not exist.
I have a pl/sql procedure having IN, OUT and IN-OUT parameters, this procedure in called from front end application. Now I need create a script to run this procedure from back end (sql prompt) and the result must be same as the front end application call to this procedure.
For the procedure I don't want to pass IN parameter instead to pick the value from the package where the derivation is defined. how to run this procedure from sql prompt without passing value for IN parameter.
I have a table Student with two columns Rno and Name and i write following PL-Sql, it is working fine, my question is that how can i pass the parameter to cursor in the following query, e.g. if i pass the roll no. 501 then it should display only the particular Name.
declare
��� cursor st_name is
����������� select rno,name from student;
����������� studentnm st_name%ROWTYPE;
begin
�� open st_name;
[code]....
I have created the Procedure P_GET_CURRENCIES. I want to sort the cursor query based on the Input parameter P_SORT_ORDER. For example if i pass 2 for P_SORT_ORDER then my query should be sorted by 2nd column. But i'm not getting correct Output.
PROCEDURE P_GET_CURRENCIES(P_START_ROW_NUM IN INTEGER
,P_END_ROW_NUM IN INTEGER
,P_SORT_ORDER IN number
,P_CURRENCY_DATA OUT SYS_REFCURSOR
,P_RETURN_MESSAGE OUT VARCHAR2 )
AS
[code]....
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?
i have below requirement in one program date is input parameter. but that should work whatever date format like DD/mon/yyyyyyyy.mm.dd what shd i do input parameter from dateand to date
View 5 Replies View RelatedI 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 have a table empl which have three column (name,period,attendance)if we pass parameter which value is based on period column
like :January
then out should come sum of all attendence of january group by name like that
name attendance
a60.00
b20.00
c20.00
w40.00
if we pass parameter value :February then attendance should come sum of (January and February) and if we pass parameter March then attendance should come sum of(January,February and march)
create table empl (name varchar2(10),period varchar2(10),attendance number)
insert into empl values('a','January',20)
insert into empl values('a','January',10)
insert into empl values('a','January',30)
insert into empl values('a','February',20)
insert into empl values('a','March',60)
insert into empl values('b','January',20)
insert into empl values('c','January',20)
insert into empl values('w','January',40)
I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??
View 16 Replies View Relatedi AM PASSING CHARATER AS PARAMETER TO SEARCH
create or replace procedure procedure_name(p_like VARCHAR2)
is
cursor cursor_name
is select last_name from employees
where UPPER(last_name) like '%p_like%';
emp_rec cursor_name%rowtype;
begin
open cursor_name;
[code]....
WHAT SHOULD NEED TO MODIFY::::::
how do I pass the values that i get in the cursor to a select statement. If table1 values are 1,2,3,4 etc , each time the cursor goes through , I will get one value in the variable - OfferSo I want to pass that value to the select statement...the one below does not work.
drop table L1;
create table L1
(col1 varchar(300) null) ;
insert into L1 (col1)
select filter_name from table1 ;
[code]....
I need to order the result set with different data types based on the input parameter.
select * from scott.emp sc order by decode('&input_parameter',1,sc.empno,2, sc.ename);
If the input_parameter is equals to 1 then,ordering should be based on EMPNO which is Number data type.
If the input_parameter is equals to 2 then,ordering should be based on ENAME which is Character Data type.
Above query was failed for input_parameter 2,as we know that decode should return same data type.
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
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.
How to pass a values from one to another, but without using parameters or globals. I've used parameters and every time i have to list the values of the form that values have been passed to, it ask do you want to save... even if I didn't passed any values. It's because my code in where-new-form instance, where i give values to a parameters.
View 17 Replies View Relatedcan we pass parameter in a link. i.e when we open a new form clicking on the link, some values to be passed to the form so that new form get populated with that value.
View 2 Replies View Related