SQL & PL/SQL :: Validating Input Parameter
Aug 11, 2010I'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
ADVERTISEMENT
SQL & PL/SQL :: Can Have Input Parameter In Procedure
Oct 11, 2010i 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 RelatedSQL & PL/SQL :: How To Use Date As Input Parameter
May 13, 2013How 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
SQL & PL/SQL :: Order By Based On Input Parameter?
Jun 29, 2011I 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]....
SQL & PL/SQL :: How To Collect Input Parameter In Procedure
Mar 4, 2010I 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?
PL/SQL :: Input Parameter - Date Format
Aug 5, 2013i 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 RelatedSQL & PL/SQL :: Debugging The Procedure With XML As Input Parameter?
Jun 23, 2011I 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.
SQL & PL/SQL :: Passing Input Parameter To Like Variable In Procedure
Dec 27, 2012I 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.
SQL & PL/SQL :: Ordering The Result Set Based On Input Parameter
Aug 2, 2013I 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.
SQL & PL/SQL :: Passing CLOB Data As Input Parameter?
May 24, 2010I 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?
PL/SQL :: How To Pass Input Parameter For Nested Table
May 10, 2013I 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
PL/SQL :: How To Concatenate Quote To Procedure Parameter Input Value
Dec 6, 2012create 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.
SQL & PL/SQL :: Removal Of Time In Input Parameter In Stored Procedure?
May 15, 2011I'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?
SQL & PL/SQL :: Convert As Procedure Based On Input Date Parameter
Dec 12, 2012i 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.
SQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?
Feb 22, 2011i 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;
/
Input Parameter From Arraylist To Stored Procedure In Oracle?
Nov 7, 2013i 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].....
PL/SQL :: Input Parameter Of Table Type And Null Assigned To It
Jul 17, 2012I have few questions regarding PL/SQL tables
1) If I don't specify the "INDEX BY" clause, it is indexed by PLS_INTEGER by default, right?
2) Consider this package specification
CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
END Testxyz;So I created a table (I hope it is defaultly indexed by pls_integer data type) and I am passing it as a parameter to a procedure. Because I want this parameter to be optional I am assigning null into it.
Now I change the definition of the table to:
CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
[code]....
3) Because I need this parameter to be optional, I use the first declaration:
CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
END Testxyz;
Now I create an anonymous block and want to assign something into the table
DECLARE
vtab_Numbers TESTXYZ.tab_Numbers;
BEGIN
vtab_Numbers(1) := 5;
END;
/When trying to run this, I got:
06531. 00000 - "Reference to uninitialized collection"
Is it possible to have input parameter of PL/SQL table type and have defaultly null assigned to it?
SQL & PL/SQL :: Column Access In Table Based On User Parameter Input
Nov 10, 2010Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?
View 5 Replies View RelatedSQL & PL/SQL :: Pass Multiple Values As Single Input Parameter Into Pipeline Function
Dec 23, 2012My 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)
Client Tools :: How To Pass Ambersand (&) As Input Parameter To A Stored Procedure
Oct 26, 2010i 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;
SQL & PL/SQL :: Fail While Validating Email ID
Jul 3, 2012I am creating function for validating email id :
create or replace function f_is_valid_email(p_email in varchar2) return varchar2 is
begin
if REGEXP_LIKE(p_email, '^[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+.{1}[a-zA-Z]$') then
return 'success';
ELSE
RETURN 'Failure' ;
END IF;
end f_is_valid_email;
But this function fail when
i did
SELECT f_is_valid_email('aaa..ss@AA.com') from dual
or
SELECT f_is_valid_email('aaa.ss@AA..com') from dual
PL/SQL :: Validating XML Against A Schema Registered In DB
Oct 2, 2012I have an XML schema registered in the database (user_xml_schema table has a rows for the schema). I have another table in which XML data is stored in CLOB column.
I want to validate this XML data against the XML Schema [ SCHEMA_URL = 'MY_TEST_SCHEMA' ].
Forms :: 6i / Validating Text Items?
Nov 24, 2010I am working on form 6i.
i need to write validation on text fields like first name,last name,phone no,email id
i have to not allow special char.
Forms :: Validating Item Vehicle_Reg_No
Feb 5, 2011I am currently having trouble validating the item Vehicle_Reg_No..I have to ensure that it starts with a letter and ends with a number. The code below gives me the TRUCK_START but for every other scenario the form throws an unhandled exception.
DECLARE
TRUCK_START VARCHAR2(100);
TRUCK_END VARCHAR2(100);
NUM_ALERT NUMBER;
BEGIN
IF :M_ORDER_ITEMS.VEHICLE_REG_NO IS NOT NULL THEN
truck_start := substr (:M_ORDER_ITEMS.VEHICLE_REG_NO,1,1);
truck_end := substr (:M_ORDER_ITEMS.VEHICLE_REG_NO,-1,1);
truck_start:= to_number(truck_start,0);
truck_end := to_number(truck_end,0);
[code]....
PL/SQL :: Validating Connection From Batch File?
Sep 3, 2012I am trying to create a .bat file where I need to take inputs for below variables
DNAME
UNAME
PSWD
So I have done like this
SET /p DNAME="Enter the Database Name "
SET /p UName="Enter the Username "
SET /p Pswd="Enter the Password "
Now I need to validate whether I am able to establish the connection
sqlpus -s "%UName%/%Pswd%@%DNAME%"
if the above connection is valid one then perform certain action else need to echo user that Access is denied.
(ex: I am trying to say if cond is valid
stmt-1
else
echo message to user. )
Forms :: Validating Field With Data In Database?
Aug 6, 2010I currently have a field in which the user inputs a number, but before this number is allowed to be saved to the database it has to be checked to see if the number is already stored in a different field within the database. For example, if the number is not in the database the user cannot put that number into the field.
I do want it to be user input so I don't want a LOV linked to the appropriate field in the database.
Is below anyway close to the way it should be done? For some reason seq_rec.seq seems to give me 3 and doesn't seem to compare it with each of the values in the database.
DECLARE
cursor seq_curs IS SELECT SEQ FROM ZZJOB;
seq_rec seq_curs%rowtype;
none_existing_seq EXCEPTION;
invalid_seq EXCEPTION;
return_alert NUMBER;
[code]....
Just say the field in my database has numbers: 1 2 3 4 and 5 in the field that i allow the user to input data they can only enter 1 2 3 4 or 5. Hence the check needs to be made with te database field to check if the input number is already in the database.
P.S. they are separate fields for different things, the numbers just need to be in one field before they can be added in the other.
PL/SQL :: Validating Date Without Using Inbuilt Oracle Functions
Jan 2, 2013writing a manual date validation function without using inbuilt oracle functions.I am planning to write this function in PL/SQL.
View 17 Replies View RelatedForms :: Running Oracle Report Through 6i And Validating Between Two Dates
Feb 16, 2010I want to call a report through forms 6i and i want to send a parameter from forms6i to report 6i. in which i want to take records between two dates.
View 26 Replies View RelatedForms :: Personalization Validating A Multi-Record Block?
Sep 5, 2012I have a problem validating a multi-record block. I need to put the validation after the user clicks save. The custom validation should run through all the records in the multi-record block. But instead of validating all records, it will only validate the record where the cursor is located before the user hits save.
View 1 Replies View RelatedJDeveloper, Java & XML :: Capture Invalid Nodes While Validating XML With Schema
Jan 6, 2011I wonder is there any way to capture all the invalid nodes in the xml when the xml is validated with an xsd (schema).
I am using java's xml validation package to perform validation (javax.xml.validation packages in java 1.5)