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


ADVERTISEMENT

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 :: 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 :: 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

Application Express :: How To Set Default Displayed Columns

Jun 12, 2012

Env: APEX 4.1

For example, initially I created the reprot source as select c1, c2, c3 from t; later I added c4. But i was unable to figure out how to make c1, c2, c3, c4 become default displayed columns. I always have to click Action -> Select Columns, then move C4 from "Do not Display" to "Display in Report".

View 8 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 :: 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

Any Inbuilt Procedure To Mask Table Columns

Aug 11, 2012

i never try masking of oracle columns before, and i will want to try masking some columns in oracle database. Just to check if the columns are masked and user using sqlplus or sql developer will not be able to access the columns.

However my application is require to get the values in the back end. Is it possible for my web application to unmask the column values? does my web application requires to call any procedure(create by oracle) in order to unmask the values? I have google and found that oracle can use VPD to mask the columns.Just to check if VPD is a inbuilt procedure by oracle to perform masking? can VPD perform unmasking of values? can it be call by web application?

View 1 Replies View Related

SQL & PL/SQL :: Create Stored Procedure To Update Columns In Table

Dec 8, 2010

I have three tables fixtures, fixture_teams and team_tbl

fixtures consists of:

create table Fixture_tbl(
fixt_id varchar2(8),
fixt_date date,
fixt_time varchar2(10),
fixt_location location_t,
umpire_id varchar2(8),
player_of_match player_of_match,
home_team varchar2(20),
away_team varchar2(20),
[code]....

creating a stored procedure that updates the points column in the teams_tbl , the value that is updated in to the points column will be retrieved from the fixture_team table. so if team a has more goals than team b then the points column for team a will be increased by 6 else if the scores are equal they get 4 points each.

View 13 Replies View Related

SQL & PL/SQL :: Revised Procedure Pass_ref_cur So That It Can Accept Multiple Columns?

Oct 25, 2011

i was asked to built a report file that will run from oracle form using web. show_document() built-in object to produce a data extract. the objective is to built a dynamic SQL and use that to a cursor.

CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
[code]....

I found out that the procedure can only accepts a single column. When I run this code it works fine because I am using a single column on the SQL statement.

DECLARE
rec_array SYS_REFCURSOR;
BEGIN
open rec_array For 'select p.muni from chips.project p, chips.proj_type pt where p.type = pt.id';
pass_ref_cur(rec_array);
CLOSE rec_array;
END;

The objective is to use a multiple column. How can I revised the procedure pass_ref_cur so that it can accept multiple columns?

View 17 Replies View Related

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies View Related

Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns

Jun 16, 2010

I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.

View 4 Replies View Related

Performance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?

Apr 4, 2011

I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.

Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.

Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.

So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.

Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.

What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.

I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.

What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.

So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.

View 12 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?

Jul 12, 2012

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 :

Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

View 8 Replies View Related

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 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

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....

View 8 Replies View Related







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