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

SQL & PL/SQL :: Default Values / Distinguishing Between Passed And Default Nulls

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

DEFAULT Value On Columns?

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

SQL & PL/SQL :: Default Value Is Sysdate

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

XE :: Map Default Apex URL

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

Adding Column With Default Value?

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

SQL & PL/SQL :: Set Default Value For A Column Of Table

Aug 30, 2010

How to set default value of particular column of a table ?

View 5 Replies View Related

Forms :: How To Default Where Condition

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

SQL & PL/SQL :: Eliminate Default Time 12:00 Am

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

SQL & PL/SQL :: Why Are Foreign Key Not Deferral By Default

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

SQL & PL/SQL :: By Default Buffer Size?

Apr 30, 2013

What is By Default Buffer size in Oracle ......?

View 4 Replies View Related

PL/SQL :: How To Make Default For Nchar

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

Default Oracle Users

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

PL/SQL :: How To Set The Default Long Datatype

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

Database Encryption By Default

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

SQL & PL/SQL :: Procedure With Default Columns

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

Radio Group Is Not Populating Its Default Value

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

Add Constraint Default Value For Table Column With Name?

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

Oracle 9i Default Username And Password

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

SQL & PL/SQL :: Unable To Add Columns With Default Value Due To Compression

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

SQL & PL/SQL :: Calling Stored Procedure With Default Value?

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

SQL & PL/SQL :: How To Find Temporary And Default Tablespace

Jun 24, 2010

How can we find the temporary tablespace and default tablespace names.

View 4 Replies View Related

Forms :: Default Mode While Entering?

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

SQL & PL/SQL :: Import With A Default Constraint On Table?

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

Default In (order By) Select Query Different In 11g From 10g?

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

Identifying Default Users Then Listing All 30

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

PL/SQL :: Use Asterisk Along With Default Value While Selecting On A Table

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

SQL & PL/SQL :: Set Sequence Nextval As Default To Primary Key Row

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

RMAN :: 10G Compression - Default Features

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

SQL & PL/SQL :: How To Get Dynamic Columns Using Default EMP Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved