SQL & PL/SQL :: How To Use Default Value
Feb 21, 2013
I want to use default value in pl/sql , i'm executing below code
SET serveroutput ON
DECLARE
l_deptno INTEGER DEFAULT 10;
v_dname VARCHAR2(200);
[Code]...
SQL> @test.sql
Enter value for deptno: 40
dname value=OPERATIONS
I tried with NULL to use its default value but it uses NULL(which does make sense)
SQL> @test.sql
Enter value for deptno:
error
How to use the default value of the passed argument?
expected O/P
dname value=ACCOUNTING
View 4 Replies
ADVERTISEMENT
Nov 16, 2010
I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]
View 11 Replies
View Related
Mar 24, 2011
I wanted to know the consequences of adding a DEFAULT value to an existing column in a table.I understand that when you add a DEFAULT value to a column which is Nullable, Oracle updates all the null values for the column to the DEFAULT value, generating a lot of undo/redo data.
Is adding a DEFAULT value to a NOT NULL column a problem? As the column is NOT NULL, an update would not be done, so no undo/redo data will be generated.But will this cause a whole table scan? Is this advisable?
View 6 Replies
View Related
Nov 4, 2011
During the Table Creation if it possible to Use the SYSDATE is Default Value for a Column.
View 3 Replies
View Related
Mar 27, 2013
I'm using apex 4.2 with Weblogic server. Right now our url to the application is
[URL].......
How can we make it much more simpler like
[URL].......
View 1 Replies
View Related
Jul 11, 2007
how do I add a column to an existing table which when rows are added to the table this column will have the same default value?
alter table thetable add (new_column_name varchar2(100) default bbubu);
View 1 Replies
View Related
Aug 30, 2010
How to set default value of particular column of a table ?
View 5 Replies
View Related
Nov 2, 2011
I am working on forms 6i. I have set where condition in pre-query of a trigger, my requirement is, in post-query, i want to delete the where condition.
pre-query:
set_block_property('HEADER_S',default_where,'STATUS IN (select meaning from XXSMCQSS_LOOKUP_VALUES where lookup_type=''CONCESSION STATUS CODES'' and primary_flag=''N'' )');
post_query: I want to delete this where condition, i just want make execute query normal by removing the where condition.
View 8 Replies
View Related
May 10, 2012
I have a table with Date Field . While selecting the records its display like below format.
TO_CHAR(CHAR_DATE,'DD-MM-RRRRHH:MI:SSAM')
10-05-2012 12:00:00 AM
10-05-2012 03:26:16 PM
1 row doesnt have time, but in default it shows 12:00:00 AM, how to eliminate it. Display should be
10-05-2012
10-05-2012 03:26:16 PM
create table time_test (char_date date);
INSERT INTO TIME_TEST ( CHAR_DATE ) VALUES (
TO_Date( '05/10/2012', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TIME_TEST ( CHAR_DATE ) VALUES (
TO_Date( '05/10/2012 03:26:16 PM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
select TO_char(CHAR_DATE,'dd-mm-rrrr HH:MI:SS AM') from time_test;
i need in to_char only, im using it in reports
View 4 Replies
View Related
Apr 18, 2013
In my point of view, deferral FK (DFK) are stronger than non-deferral FK (NDFK). In other words, every NDFK can be moved to DFK. Is there a performance issue ?
View 2 Replies
View Related
Apr 30, 2013
What is By Default Buffer size in Oracle ......?
View 4 Replies
View Related
Aug 3, 2013
I want to make default 'y' for COL1 how should i make it.1) "COL1" NCHAR(10) DEFAULT N'y'or 2) "COL1" NCHAR(10) DEFAULT 'y' yours sincerly
View 5 Replies
View Related
Oct 2, 2013
I am trying to find out if there is a definite way to find out (by querying database) which database users have been created by Oracle (either during installation or as part of patching or adding new feature) and which database users have been created by DBAs.
I have looked into the documentation but could not find anything relevant. Ideally, I would like to know if this can be done for any versions starting from 9iR2.
View 7 Replies
View Related
Jul 12, 2013
Query that I want to run:
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', false);
result: E;;45;45 ;45 ;45 ;S 45
I am not sure but may be I need to set long size before running above query. But when I try to set long size gives below error. "The output from DBMS_METADATA.GET_DDL is a LONG datatype. When using SQL*Plus, your output may be truncated by default. Issue the following SQL*Plus command before issuing the
DBMS_METADATA.GET_DDL statement to ensure that your output is not truncated:"SQL> SET LONG 9999error: Unhandled SET statement: "SET LONG 9999"
View 5 Replies
View Related
Aug 10, 2010
I would like to know the reason why the database is on unencrypted format by default,there must be some reason behind this, i hear from someone that encrypted data degrade performance thats the reasons its on unencrypted format by default.
View 6 Replies
View Related
Apr 27, 2010
I have the following procedure...
CREATE OR REPLACE Procedure Updatetdef_normal_ranges (
i_tdef_range_id in number,
i_user_change_id in number default -1,
i_norm_range_low in number default NULL,
[Code]....
EXCEPTION
WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END Updatetdef_normal_ranges;
I am trying to execute it using the command
Execute Updatetdef_normal_ranges (i_tdef_range_id => 1, i_norn_range_high => 100.34, i_high_reflex_tdef_id => 100, i_low_reflex_tdef_id => 17, i_range_low_sched => -1)
and I am getting the following error...
SQL> Execute Updatetdef_normal_ranges (i_tdef_range_id => 1, i_norn_range_high => 100.34, i_high_reflex_tdef_id => 100, i_low_reflex_tdef_id => 17, i_range_low_sched => -1);
BEGIN Updatetdef_normal_ranges (i_tdef_range_id => 1, i_norn_range_high => 100.34, i_high_reflex_tdef_id => 100, i_low_reflex_tdef_id => 17, i_range_low_sched => -1); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'UPDATETDEF_NORMAL_RANGES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
If I replace the update statement with dbms_output statement this procedure works fine.
View 25 Replies
View Related
Oct 22, 2008
I am using Forms 6i.I have a Radio Group, where in I have specified Intial value for it. Previously it was populating properly, But when I have added some code to fix a bug. The radio group starts behaving strange. It doesnt show defaulted value.The code what i put is not even related to the radio group.
View 1 Replies
View Related
Oct 26, 2009
I would like to add a constraint "default value" for a table column, with a name.
I know how to do it for a constraint "not null" : ALTER TABLE tablename MODIFY columnname CONSTRAINT constraintname NOT NULL;
But I don't know how to do it for a constraint "default value".How can I do ?
View 2 Replies
View Related
Sep 14, 2003
I Just got Oracle 9i installed. But can not login . What are the Default username and password for "Oracle9i Discoverer Administrator" and other Developers Suite apps?
Also What URL should i type to access the "iSQL *PLUS" through my browser, so that i can execute my SQL statements.
View 3 Replies
View Related
Mar 22, 2011
We are trying to add 2 columns in a partitioned table.but we are getting below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
But, it is not a compressed table.
select table_name, compression from user_tables
where table_name ='SVC_ORDER_CODE_FACT'
TABLE_NAME COMPRESSION
------------------------------ -----------
SVC_ORDER_CODE_FACT
actually we are trying to add 2 columns as below:
ALTER TABLE SVC_ORDER_CODE_FACT
ADD (MKT_FEATURE_KEY NUMBER default '-2', PREV_MKT_FEATURE_KEY NUMBER default '-2');
But, if we add column without default value,
View 2 Replies
View Related
Apr 3, 2012
I have the following Stored Procedure:
CREATE OR REPLACE PROCEDURE AFESD.TEST_PROC (I_NUM IN NUMBER,
I_NUM2 IN NUMBER DEFAULT 3, D_DATE IN DATE DEFAULT sysdate,D_OUT OUT DATE)
IS
[Code]....
I dont know what I need to pass in order to give it the default value.
View 3 Replies
View Related
Jun 24, 2010
How can we find the temporary tablespace and default tablespace names.
View 4 Replies
View Related
May 25, 2010
Is there any property that can be set at design time or dynamically altered during runtime about the default mode in which a form opens? I have created a new FORM in Forms 6i and it opens in the Enter-Query mode. I do not want to open it in the Enter-Query mode. This is because I will be doing my own execute_query on some click of a button in the form. How do I alter this Enter-Query mode in which the form opens?
View 2 Replies
View Related
May 28, 2012
I have a table as below. This table is not partitioned.
create table t1
(
d1 date,
n1 number not null
);
[Code]....
I took an export dump of the above table and after that I renamed the table t1 to t1_old. Then I recreated the table as below with a default constraint on d1 field.
create table t1
(
d1 date default to_date('01/01/1100','DD/MM/YYYY','NLS_CALENDAR=GREGORIAN'),
n1 number not null
[Code]....
But the problem here is the data import is taking too much time than what I expected. I can't afford a maxvalue partition here as of my DBA team mentioned if you add maxvalue partition adding partition later in a stage is difficult on this table
apply in this scenario and make the import faster. I am using oracle 10.2.0.1.0 version.
View 2 Replies
View Related
Nov 12, 2013
if when you are querying a table in 11g and you use the order by clause and there is more than one occurrences with the same values in the order by, if the 11g default is different than from 10g.
For instance.
DECLARE MHBulk CURSOR FOR select invoice_nbr, customer_nbr, post_century, post_yymmdd, from CUSTOMERS where customer_nbr = 1234 order by post_century, post_yymmdd;
If you have more than one occurrence of the same customer_nbr, post_century, and post_yymmdd
it looks like the default order for how 11g retrieves the records is a bit different than the 10g default.
View 9 Replies
View Related
May 30, 2013
I frequently need to run queries to do things like sum space used by each schema, but excluding all of the default system users. Is there a better way to identify the default users than listing all 30 of them in a "not in" expression? For example in sql server to query user databases you just say "with database_ id > 4". Is there something simple like that in Oracle to identify all default users?
View 6 Replies
View Related
Jul 18, 2012
Is there any way to use * along with the default value while selecting on a table...I need something as follows:
select *, 'JUNK' from table_name;
(or)
select 'JUNK', * from table_name;I know it will not work.
View 4 Replies
View Related
Jul 15, 2012
I'm using Oracle 11g and I'm trying to set the sequence nextval as default to Primary Key row, when I create the table, Oracle throws an exception that columns are not allowed. Here is an example.
CREATE TABLE EMP (
EMP_ID VARCHAR2(50) PRIMARY KEY DEFAULT MYSECUENCE.NEXTVAL)
View 10 Replies
View Related
Oct 18, 2013
What are the default features available along with 10g R2 Enterprise Edition , Especially for RMAN Backup ? What are the features required for licensing in 10g R2 EE version .
View 2 Replies
View Related
Aug 18, 2011
i am practicing to get Dynamic columns as i have learnt from Orafaq SQL/PLSQL forum .i am using Default EMP table...first one is running smoothly as below:
1 DECLARE
2 v_sql VARCHAR2(32767);
3 v_refcur SYS_REFCURSOR;
4 BEGIN
5 v_sql := 'SELECT deptno ';
[code]....
View 11 Replies
View Related