I want to ask that how can I use parameters in forms? I have created 2 parameter pm_rollnum and pm_classname but I don't that how I use it in form that when end user enter its value in these parameter result should be open.
View 6 RepliesI'm migrating forms 6i to forms 10g. I have a mune which has parameters ,which are working in forms 6i. But when i try to comile in forms 10g its poping up error's.
View 3 Replies View Relatedi want to pass parameters from xyz form to abc form when i press a button. how to pass parameters.....
View 5 Replies View RelatedI need to pass two parameters (STUD_ID) and (TERM) from Form A to Form B. I've read a lot but I'm not familiar with terms and oracle jargon yet! So I really need your straight forward clarification
From the Calling form I'm using when_button_pressed trigger...what shall i write in here?! In the Called form, I'm declaring the following under (When_new_form-instance):
declare
p1_id paramlist;
stud_id number(8);
term number(5);
begin
p1_id := get_parameter_list(stud_id);
p1_id := get_parameter_list(term);
:TEST_STUD_CRSE_REG.stud_id :=:parameter.stud_id;
:TEST_STUD_CRSE_REG.term :=:parameter.term;
next_block;
end;
-----------------
on the calling form:when_button_pressed I am using:
DECLARE
p1_id paramlist;
stud_id number(8);
term number(5);
BEGIN
call_FORM('test16SEP',NO_HIDE);
GO_BLOCK('TEST_STUD_CRSE_REG');
set_block_property('TEST_STUD_CRSE_REG',default_where,'STUD_ID=:TEST_STUDENT_INFO.STUD_ID');
execute_query;
END;
I have one interface form where user will choose radio buttons and based on radio button value different report will run, in one radio button i want to check parameters , for example if there are three radio buttons, a - will run a.rdf ,b- will directly run b.rdf , if user chooses c - then prompt will appear asking him to input from date and to date and based on that value c.rdf will run.
I want this date parameters to run only for this report c.rdf only.One way is to display from date and to date on forms and make it applicable to only this report , but i want to use this parameters only while choose c radio button.
Is it possible to pass parameters from menu file to form when calling a form from menu item?
View 4 Replies View Relatedwhat are the parameters expected for app_window.set_window_position() function?
View 4 Replies View RelatedI have created a simple function in database that take 2 arguments, the last one is optional.
Now i call the function in Forms 6i PL/SQL Trigger and omitt the default (optional) parameter the form builder crashes when i compile the form (CTRL + SHIFT + K)
i have tried to used both words "DEFAULT" and ":=" in the function's signature for making an optional argument.
I want to create table from query with passing parameters
create table as temp
select * from emp
where hiredate between :sdate and :edate
and deptno = :dpt
when I tried in Toad after passing parameters it gives me error
ORA-01036: illegal variable name/number
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=
Oracle Version:11.2.0.3/ OEL 6.1
For diagnosing a performance issue related to a batch run, our application teams wants to know all session/system level parameter that affects SQL performance. I can't simply give an output of v$parameter.name and v$parameter.
value as there will lots of parameter not related SQL performance like
diagnostic_destlog_archive_dest_1deferred_segment_creationcontrol_filesbackground_core_dump
I am looking for only those parameters which are relevant to SQL performance, like
optimizer_modestatistics_levelmemory_targetsga_target
How can you use pivot with a parameter? If you attempt to use a parameter then an ORA-500: bind variable not supported is given
Below is a simplified version of the sql to illustrate teh problem.
create table test_pivot
(
ord_no number,
qty number,
loc varchar2(10)
):
insert into test_pivot values (123, 3, 'LOC1');
insert into test_pivot values (123, 2, 'LOC1');
insert into test_pivot values (234, 4, 'LOC1');
insert into test_pivot values (234, 5, 'LOC1');
-- this one is fine
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In ('123', '234')
)
Pivot (Sum(Qty) For Ord_No In ('123', '234') )
-- how to use pivot with parameters
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In (:Ord1, :Ord2)
)
Pivot (Sum(Qty) For Ord_No In (:Ord1, :Ord2) )
the same query i put below but in oracle.
I'm trying to declare and set parameters in oracle
I'm trying to do something similar as the follow SQL Server Sentence:
------------------------------------------------------
DECLARE
@Year SMALLINT,
@City varchar(255)
SET @Year = 2010
SET @City = ''NY','NJ''
[Code]....
I just upgraded my version of my oracle from 9 to 11.2g.When I tried to execute some queries in PL/SQL i keep getting an
annoying error : "ORA-01843 not a valid month."
I realized that if I execute the following query:alter session set NLS_DATE_LANGUAGE=AMERICAN;before every query that I want to execute, then it's executed fine.
I preformed the next queries:
1. select * from nls_database_parameters;
2. select * from nls_instance_parameters;
3. select * from nls_session_parameters;
and I saw that the fields in the "nls_session_parameters" vieware not derived from "nls_database_parameters" "nls_instance_parameters".
How can I derive the parameters from the instancedatabase view into the session view?I want that the "nls_date_language" parameter will always be 'AMERICAN'.
How to how to get this sql to work?
select a.in_house_ref
from media_item a
where
((&rack is null) or a.in_house_ref in (&rack));
--a.house_media in ('CH127600','BF101042'), example values
I've tried the a.in_house_ref in (:rack) by itself and that doesn't work for the values above and hence changed it to (&rack) and that works. The issue I run into now is that the the paramater can be null, ie., no values need be entered by users and hence I put the is null statement. When I run the full statement above, oracle returns an error, i.e. , ORA-00936: missing expression.
what is the purpose of kernel parameter while creating database.
View 1 Replies View RelatedI have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.
Example:
in excel the column has : 'ABC','GEH','HGT',LTP'
create or replace procedure abc(temp_col varchar2)
.
....
....
...
SELECT COLA, COLB, COLC
FROM TABLE_TEMP
WHERE TCOL IN temp_col;
This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.
i have a function which takes in two variables and return a varchar.
ex: Function(var1,var2) return as varchar2.
in the function,i query a table for var1 and var2 and concatenate the result set to return a varchar. But if either var1 or var2 is null,then my query in the function fetches the result set for the other variable.
My question is,how would i pass a null value through the function and handle it in the function.
The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.
The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?
I need to extract DDL's without storage parameters. if i use the export and import using indexfile or if i try to extract using the DBMS_METADATA.GET_DDL package , in both ways my output is with the storage parameters
eg:
CREATE TABLE "SEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
[Code].....
I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.
I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.
The code for the procedure is
CREATE OR REPLACE PROCEDURE add_new_classes
(p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number
p_course_id classes.course_id%TYPE,
p_period classes.period%TYPE,
p_frequency classes.frequency%TYPE,
[code]....
I then use this to test it
BEGIN
add_new_classes(1002,'first','daily',3002);
END;
and the error I get is
Quote:ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
1. BEGIN
2. add_new_classes(1002,'first','daily',3002);
3. END;
When I replace the params manualy with dates in format of:
'27-feb-2011 08:00:00'
I get the real output.
But when I call the function with the TEST button in the PLSQL I don't get any output (Empty table).
Here is the original function (Bad output) and attaching a file with manuly added dates (Good output):
FUNCTION WhatRoomsTaken(dStartTime date, dEndTime date, dEventDate date)
RETURN Genrefcursor IS
Retval Genrefcursor;
BEGIN
open Retval for
[code].....
Why do I need to change in order to get the good output by calling the function with params?
In my task I am trying to pass a timestamp datatype as one of the input parameter to a procedure in the package.But I am not sure how to give data for it while executing and testing it from anonymous block.
CREATE OR REPLACE
PACKAGE body ac
IS
PROCEDURE ac_ex_wbdb_keycats(
In_Sale_Location_Id IN NUMBER,
In_Start_Datatime IN TIMESTAMP,
In_Stop_Datatime IN TIMESTAMP,
v_refcur OUT sys_refcursor)
[code]....
i created one procedure with 4 in parameters and 1 out parameters(return the value) but whenever execute this procedure i got the error i.e pl/sql: Compilation unit analysis terminated.
PLS-00201:identifier T_USER_PLDATA' must be declared.
CREATE OR REPLACE PROCEDURE GET_USER_PLDATA1( V_PROD_LINE_CD IN VARCHAR2,
V_BUS_GROUP_CD IN VARCHAR2,
V_BUS_UNIT_CD IN VARCHAR2,
V_COUNTRY_CD IN VARCHAR2,
V_USER_PLDATA OUT T_USER_PLDATA)[c
[code]....
declare
type ref_cur is ref cursor;
r ref_cur;
enam emp%rowtype;
dno dept.deptno%type;
begin
dbms_output.put_line('The Employee details are');
open r for select deptno from dept;
loop
fetch r into dno;
[code]....
Error at line 1
ORA-06550: line 12, column 28:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
. ( % ;
if i need to use ref cursor to send parameters, is it possible? if yes how to use it?
I want to calculate two parameters A and B which are interdependent. Unfortunately, my code is not working correctly:
create table temp_res1 as
select *
from temp_res
model
dimension by (rn)
measures ( 0 as A,
0 as B
[code]....
The correct result should be:
create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 7, 12);
insert into temp_res values(4, 13, 8);
insert into temp_res values(5, 9, 14);
insert into temp_res values(6, 15, 10);
insert into temp_res values(7, 11, 16);
insert into temp_res values(8, 17, 12);
but I get:
create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 1, 12);
insert into temp_res values(4, 1, 2);
insert into temp_res values(5, 1, 2);
insert into te
Database version: 10.2.0.4 Patch 45 on Windows 2003
1. Documentation says to set "_first_spare_parameter" to resolve bug 6904068. What should be the value if we actually see this bug occurring after applying the patch. How can we determine that we need this parameter to be turned on?
2. How can we determine we need to set "_third_spare_parameter"? How can we check if there is a regression on log files?
I want to set both nls_language and nls_date_language, and set them as different languages. fnd_global.set_nls_context() doesn't work. So I think maybe I can use the EXECUTE IMMEDIATE, and add them together into one statement.
View 7 Replies View RelatedI would like to create a procedure which have 3 parameters. suppose in Date_birth, in Dept , out number_of_records
if i pass null value to one of the parameter it should return all values . if a pass value to the parameter it should return as per parameter values
Create or replace Procedure Emp_Test (P_dop in Date,P_Dept in number , P_noof_records out Number,
p_recordset Out Sys_Refcursor) as
Begin
OPEN p_recordset FOR
Select Emp_cd,Emp_name, Date_of_Birth,Dept,Count(emp_Cd) noof_records
From Emp_Master Where Date_of_birth =P_date_of_Birth
and Dept=P_dept ;
End ;
)How do you view the value of the parameter that is being used by instance? show parameter..?
2)How do you get the information of hidden parameters
3)What is the database object that stores information related to various types of db connections over network
4)How do you verify since when the db session is running
5)How do you verify the Originating machine details of the database session
6)How do you verify the name of program that the db session is running
7)What is the naming convenion of Base tables. Where is the information of base tables stored?
8)How are dynamic views created. Whre is the information of dynamic views stored?