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.
I have a HTML region with two select lists and one button. The select lists are P103_SPORT and P103_YEAR. I want the user to make choices from the select lists and click the button to pass their choices as parameters in a URL. I set the button to redirect to a URL. In that URL I am using the notation &P103_SPORT. and &P103_YEAR. to include the user values. Currently when a user clicks the button, a new window opens with the URL, but nulls are being passed for the 2 items (sport and year). When I click the DEBUG button for the page, these items have nulls for values, even though the user selects values from the select list.
what I'm missing here? Why does DEBUG show null for the items when I can see values in these items?
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
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 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;
I have an issue with rather complicated function.Basically it is using DBMS_SQL to execute a very long statement with many parameters (~6000 of them) and binding them with DBMS_SQL.BIND_VARIABLE. Variables are called :1,:2,...,:6000.
When this arguments set is too large - I am receiving error "ORA-00939: too many arguments for function".
Currently I am thinking about dividing the query into subqueries and executing them all with performance decrease.
There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.
But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size group by Condition, Size;
COUNT(*) CONDITION SIZE -------- ---------- -------- 3 MINT L 2 FAIR L 4 FAIR M 1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE -------- ---------- -------- ---------- 3 MINT L ISNULL 2 FAIR L NOTNULL 2 FAIR M NOTNULL 2 FAIR M ISNULL 1 MINT S ISNULL
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate); insert into test values (1, sysdate - 1); insert into test values (1, null); insert into test values (1, null); -- ----- insert into test values (2, sysdate); insert into test values (2, sysdate - 1); insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
SQL> Describe Stu_Table Name Null? Type ----------------------------------------- -------- ---------------------------- STU_ID VARCHAR2(2) STU_NAME VARCHAR2(10) STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null; ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null * ERROR at line 1: ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL; ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL * ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column
Suppose that, I have two tables: emp, dept emp records the empid, emp_name, deptid dept records the deptid, dept_name
Here is a record, it's a president or some special position in company, so it's deptid is set to NULL. Here comes the question, how can I print all the emp_name with their deptartment name?
I know how to print all the emp_name with their department name if they have dept_id, but is that possible that I merge the record with dept_id NULL?
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
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.
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'.
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.