SQL & PL/SQL :: Default Values / Distinguishing Between Passed And Default Nulls
Nov 16, 2010I 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
ADVERTISEMENT
Client Tools :: How To Have Optional Parameters And Default Values In SQL*Plus Script
Jun 16, 2012Just to let you know this new section in SQL*Plus FAQ wiki page: How to have optional parameters and default values in SQL*Plus_script.
View 1 Replies View RelatedForms :: Populate Default Values In Line Level Based On Header
May 29, 2011I have two blocks.In header 2 columns are there same columns in line how to populate default values in line level based on header?
View 1 Replies View RelatedApplication Express :: Update Default Column Values In Database From Form
Sep 14, 2012Application Express ver. 4.1
I have created a form. Some of the elements are hidden such as updated_by and update_date. I have provided the default values for these attributes as pl/sql expression in the "default section" (Application->page->edit page item). I am using the Process Row process that is automatically created when a form is created.
The problem is when I click the update button, the updated_by and update_date is passed as null values which throws an error from the database.
SQL & PL/SQL :: Ref Cursor - Values To Be Passed Dynamically
Sep 26, 2011I have used ref cursor to retrun output in cursor from procedure.I have used bind variables in the v_query and passing values with using clause as given in the following code. It is working but client want to pass values dynamically.
OPEN p_cur FOR v_query
USING p_ht_nm, p_ht_treat_source, var_cycle (1),
var_cycle (2),
var_cycle (3),
var_cycle (4),
var_cycle (5),
var_cycle (6),
var_cycle (7),
[code]...
We can also use execute immediate with the above code. But in both the cases we have to pass values in using clause.
QUERY:
/* Formatted on 2011/09/24 21:13 (Formatter Plus v4.8. */
INSERT INTO z_ca_get_lot_id
SELECT DISTINCT attrbts.lot_id
FROM (SELECT DISTINCT lot_id
FROM z_alv_cert_lot_attrbts_syn
WHERE NAME = 'Heat'
AND UPPER (text_value) = :p_ht_nm) attrbts,
[code]...
SQL & PL/SQL :: Process A String Of Values That Is Being Passed From Cold Fusion?
May 7, 2010I'm trying to figure out how to process a string of values that is being passed from Cold Fusion.
procedure test (Names in varchar2) is The Names variable from the Cold Fusion page would have values like Joe1,Joe2,Joe3,Joe4.
I need to loop through the Names variable (Joe1 then Joe2 then Joe3 and so on) and insert each one into a table. how to do that within the procedure?
SQL & PL/SQL :: How To Use Default Value
Feb 21, 2013I 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
DEFAULT Value On Columns?
Mar 24, 2011I 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?
SQL & PL/SQL :: Default Value Is Sysdate
Nov 4, 2011During the Table Creation if it possible to Use the SYSDATE is Default Value for a Column.
View 3 Replies View RelatedXE :: Map Default Apex URL
Mar 27, 2013I'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].......
Adding Column With Default Value?
Jul 11, 2007how 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);
SQL & PL/SQL :: Set Default Value For A Column Of Table
Aug 30, 2010How to set default value of particular column of a table ?
View 5 Replies View RelatedForms :: How To Default Where Condition
Nov 2, 2011I 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.
SQL & PL/SQL :: Eliminate Default Time 12:00 Am
May 10, 2012I 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
SQL & PL/SQL :: Why Are Foreign Key Not Deferral By Default
Apr 18, 2013In 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 RelatedSQL & PL/SQL :: By Default Buffer Size?
Apr 30, 2013What is By Default Buffer size in Oracle ......?
View 4 Replies View RelatedPL/SQL :: How To Make Default For Nchar
Aug 3, 2013I 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 RelatedDefault Oracle Users
Oct 2, 2013I 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.
PL/SQL :: How To Set The Default Long Datatype
Jul 12, 2013Query 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"
Database Encryption By Default
Aug 10, 2010I 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 RelatedSQL & 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.
Radio Group Is Not Populating Its Default Value
Oct 22, 2008I 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 RelatedAdd Constraint Default Value For Table Column With Name?
Oct 26, 2009I 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 ?
Oracle 9i Default Username And Password
Sep 14, 2003I 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.
SQL & PL/SQL :: Unable To Add Columns With Default Value Due To Compression
Mar 22, 2011We 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,
SQL & PL/SQL :: Calling Stored Procedure With Default Value?
Apr 3, 2012I 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.
SQL & PL/SQL :: How To Find Temporary And Default Tablespace
Jun 24, 2010How can we find the temporary tablespace and default tablespace names.
View 4 Replies View RelatedForms :: Default Mode While Entering?
May 25, 2010Is 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 RelatedSQL & PL/SQL :: Import With A Default Constraint On Table?
May 28, 2012I 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.
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.