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 Repliescan 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 RelatedI am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string
M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';
Now trying to execute it. using below
M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;
Now what i want is M_STR vairable to executed as
SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
instead of
SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';
I have a form in which the user selects values above from a LOV and then selects a run query button which then uses the values selected in the LOV to refine the query and output the appropriate values.
What i have done in the pre-query trigger is: (the block i am querying is called cars where the block i am selecting values from the LOV is called selection)
:cars.make := :selection.make; (repeated for other fields)
this worked fine untill i introduced a from and to date in my form in which i passed the data through to the query like this:
declare
v_dates_between varchar2(1000);
begin
v_dates_between := 'reg_date between ''' || :selection.from_date || ''' and ''' || :selection.to_date;
Set_Block_Property( 'cars', DEFAULT_WHERE, v_dates_between ) ;
Now i have added the above bit of code the from and to date work fine, but when a car make is selected it still bring back every car make and not just the one selected.
is it possibly because i am setting the where clause in the block property? perhaps i need to now incoperate my other selections into this where clause? are the two blocks exclusive to eachother? and if i put the whole code into the block property I assume i will get problems. E.G. the user leaves a selection criteria blank (if they want to query all car models they wouldnt select one from the list) i assume the query would only return back values which contain no car model which would be 0 records.
I have created a Data block - 'CONTACTS' (Database data block) and has database item - 'Code', 'Descr'
The number of records displayed is set to 5.
Value When checked - 'Y'
Value When Unchecked - 'N'
Check box mapping of other values - 'unchecked'
The requirement is when i check one or multiple checkboxes, i should pass the 'Code' item values to the WHERE clause.
Right now whenver i am trying to do so, only the current record value is copied to the WHERE clause.
I have tried using basic loop as well as while loop but things havmt worked. Below is a basic code which will work for one record, request to guide me with muliple checkbox ticked.
IF :contacts.cb = 'Y' THEN
IF p_where is null then
p_where := :contacts.code;
else
p_where := p_where ||','||:contacts.code;
end if;
end if;
p_where:= 'where code in ('||p_where||')';
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a form which has few textboxes, and few dropdown, and 3 links. Data is entered in few boxes, and then when a link is clicked, it goes to another page, but i want to pass the text in the text boxes, and the selected value from the dropdown also, to be passed as parameters to this link... But since these values are not yet saved, its not getting passed.. like it is something like this in the address bar add_new_event?&event_ id= &event_ name= & event_dt=
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 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 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.
I need to know how to pass the ref cursor as INOUT parameter to a procedure. I have the following procedure and I need to execute it.
PROCEDURE get_site_setup_detail (
p_study_id IN SITES.study_id%TYPE,
p_proj_act_date IN VARCHAR2,
[code]....
I had created drill down reports. rep_1 dispalying various Location_cd. by clicking location_cd The parameter form of rep_2 is displayed in which location_cd is displayed which is already selected and accept dept_cd from user. While running the rep_2 the parameter location_cd is passing null value.
View 1 Replies View RelatedI 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?
get rid of the below error
CREATE OR REPLACE FUNCTION fn (
p_salesrep_id IN jtf_rs_salesreps.salesrep_id%TYPE,
p_org_id IN jtf_rs_salesreps.org_id%TYPE,
p_cnf_date IN emcint_ord_headers_all.creation_date%TYPE
[Code]....
Invoking Functions
select fn(-3,293,'1/1/1952'), resource_id from jtf_rs_salesreps
where rownum < 5
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
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
I prepared a report in Oracle reports and trying to run the report through command line by creating a batch file. but i am getting the following error message when i run the batch file
REP-0069: Internal error
REP-57054: In-process job terminated:Executed successfully but there were some errors when distribute the output
REP-50159: Executed successfully but there were some errors when distribute the output
the batch file is as follows
D:DevSuiteHome_1BINRWRUN p:spannawazeitspanna_wa_zeit.Rep userid=alrayatec/alrayatecprod@alraya.world
DESFORMAT=HTMLCSS DESTYPE='mail' DESNAME='sarfraz_it@hnd.com'
I have developed a report using apex shared components (report query and report layout) along with BI Publisher for report printing in pdf format. the Report template is built using Template Builder for Word. I have configured my Apex4.1 env with BI Publisher 11. This report is working fine for hardecode ID ( like REQUEST_ID=10 ). to make report dynamic i have made following additions in the report
I have added where clause in report query as WHERE REQUEST_ID = :REQUEST_ID (:REQUEST_ID is the text field in my page).
Added a text field names :REQUEST_ID to get user input at run time.
Create a button and add Repot Query URL in its properties. When i run the report with some valid value in :REQUEST_ID text field i did not get expected result infect there is no record getting displayed in the PDF file. i think this is not the write way to do this but interestingly im not getting any error. how can i get this functionality in apex this way or the other ?
I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :
where account_code = P_CODE.
Eg. The value of :P_CODE is 'CS'.
I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.
I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0
I have two page , from first page to second page I am passing some values using url parameter passing but some values contains comma ex :- P1_ NATION text field contains INDIA,USA,UK
but apex treated that as these are separate values and assign it to separate items
suppose my intention is like P2_NATION,P2_EMPLOYEE,P2_EMPID :INDIA,USA,UK,SAGAR ,123
but apex treating it as P2_NATION = INDIA :P2_EMPLOYEE=USA :P2_EMPID = UK
I am using apex4.1 , db 11g , ie , chrome , ff.
I have a batch file Menu.bat which should pass accept the input parameters from the end users and pass the entered values to a different batch file.
After entering option 2 the user should be prompted with an option to enter the path. E.g. /shared/folder.
Now '/shared/folder' should be passed to a different batch file named R.bat
Below is the Menu.bat file.
@echo off
color 00
title Security Audit Adaptor
:main
cls
echo //MAIN MENU\
echo.
echo 1. Got to Google
echo 2. Enter path
[code].......
APEX 4.2 - Base code from Denes examples
CODE ON HTML HEADER
<script language="JavaScript" type="text/javascript">
function f_setglp(pThis,p_app_item,p_other_record_level_item)
{
var curr_id = $x(pThis).id; // OK !
var curr_glp = $x(p_app_item).id; // returns undefined
var curr_orli = $x(p_other_record_level_item).id; // returns undefined
[Code]...
The Javasxcript function fires OK on click in the "Product" attribute but: PMO_GLP_PK is an Application Item that Javascript can´t reference as I called YES_NO is another tabular form attribute and the reference returns also "undefined"
what is the best way to implement this ?