SQL & PL/SQL :: Block Using Substitution Variable

Oct 3, 2012

Declare a variable called sal to store the salary of an employee. In the executable part of the program, do the following:

a.Store an employee name in a substitution variable.
b.Store his or her salary in a variable.
c.If the salary is less than 5,000, give the employee a 10% raise and display the message "<Employee Name> salary updated" in the window.
d.If the salary is more Last Name Message than or equal to 3,000, print the employee's salary in the format, "<Employee Name> earns ............. No raise is given."

So the result should ask for the last name

- Himuro and message should show [Himuro's salary updated]
- Greenberg [Greenberg earns 12000. No raise is given.]
- Patel [Patel's salary updated]

Here is my PL/SQL

set serveroutput on

DECLARE
v_emp_sal employees.salary%type;
v_emp_last_name employees.last_name%type;
BEGIN
v_emp_last_name :=initcap('&emplastname');
SELECT salary
INTO v_emp_sal
[code]......

question:
How can I get the apostrophe in the dbms_output.put_line. Attached is the create employees table code.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Substitution Variable In Sqlplus

Jul 20, 2011

Here is a simple sqlplus line command with & substitution run time variable.

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;
Enter value for sal:

Now as you see, oracle asks to Enter Value for substitution variable in line just below the SQL>. I wonder if there is one or more line could be left there so to improve the readability. So it should look like:

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;

Enter value for sal:

View 2 Replies View Related

SQL & PL/SQL :: Variable Substitution In Using Clause

May 5, 2011

Below is the code, I am trying.

I have all the bind variables in "using_stmt" variable. How can i make this work. Currently I am getting error

ORA-01008: not all variables bound
--------------------------------------
DECLARE
SQL_STMT varchar2(200);
using_stmt varchar2(200);
dept_id number := 50;
dept_name varchar2(14) := 'PERSONNEL';
loc varchar2(13) := 'DALLAS';
rec_count number;
[code]......

View 13 Replies View Related

SQL & PL/SQL :: Substitution Variable With And Without Value Assigned

Jun 14, 2011

I have a table emp with columns [id,name,job_id,dept_id]. now I make a simple select query.

select id,name,job_id,dept_id
from emp
where id = &a
and name = &b
and job_id =&c
and dept_id =&d;

now in this case it will ask the values for all the columns and will according print result.

Now my questions is what will happen if I just pass the value for only id and name not for other two...?

View 35 Replies View Related

Client Tools :: Inserting Data With Substitution Variable?

May 24, 2012

I want to insert the value with '&'. But this is treated as substitution variable by oracle.So how can it is possible to do like this

INSERT INTO DEPT (DEPTNO, LOC, DNAME)
VALUES (50, '&NEW YORK', 'SALES');

View 3 Replies View Related

Client Tools :: Escape / Quote Ampersand (&) Because No Variable Substitution

Apr 5, 2011

I wish to replace the string "Benutzername&Kontakt" with nothing.

SELECT REPLACE (role_owner, 'Benutzername&Kontakt', '')
FROM JC_NAME_62
WHERE jc_name LIKE 'SAP_R4_BIN5\_%' ESCAPE ''

But my Toad prompts me always to fill in the variable. I don't wish to do that, I wish to escape the &

View 5 Replies View Related

PL/SQL :: Declaring Variable Twice In A Block Is Redundant Or It Gives Any Benefit

May 5, 2013

if table exists then, i whould like to delete it and re create it again.to do so i have two method pls tel me which one i should follow.

declaraion of variable v_strTN twoice in 1) query, does the twoice declaration gives any benefit or it is redundant?

1)
DECLARE
v_strTN VARCHAR2(30);
BEGIN
BEGIN
   SELECT TABLE_NAME INTO v_strTN FROM USER_TABLES
   WHERE UPPER(TABLE_NAME)='ABC';

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Using $ Substitution In Select Statements?

Oct 11, 2011

Is it possible to have $ substitutions in Select statements ?

For example

Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00

For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?

View 2 Replies View Related

SQL & PL/SQL :: How To Use Substitution Variables In FROM Phrase

May 17, 2010

HOW i can use the Substitution Variables in select statement in From phrase

we need execute select statement in different users in run time.

View 15 Replies View Related

Client Tools :: Substitution Variables

Feb 22, 2010

How I go about writing a query that doesn't see the '&' symbol as a substitution character. The problem I have is:

select replace
( fnd_profile.value( 'XXBPO_URL')
, '&DOCID'
, ai_dfv.bpo__document_link
) document_link
from ap_invoices_all ai
, ap_invoices_all_dfv ai_dfv
where 1=1
-- where clause

The problem above is that instead of replacing the characters &DOCID with the required value, it is prompting for input.

View 4 Replies View Related

Application Express :: Substitution String For List Entry

Jul 4, 2012

I have created a list with name Test_List and 5 values in it.

I want to pass the values dynamically when i select the list values (any of the 5 values).

I tried:

For each list setting(List Details --> Static list entries --> Target) i have set the
Request as ?Test_List=#Test_List#
Note: Here Test_List is my list name.

Also i have tried

Request as ?Test_List=&Test_List.

But nothing seems to be working...

View 8 Replies View Related

Application Express :: Is There A Built-in Substitution String For Item With Cursor Focus

Jun 27, 2012

I was wondering if there is a substitution string ( or something else) that could be used to 'return' the name of a Form item that has cursor focus?

View 3 Replies View Related

Forms :: Multi Data Block And Checkbox Field Based On Control Block

Nov 19, 2010

i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field

e.g

item11 item21 item31 chkbox1
item12 item22 item32 chkbox2

Scenario like this :

My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field

when i checked chkbox1 , my cursor goes to item31...not item32

View 12 Replies View Related

Forms :: Distribute Master Block Total Amount In Detail Block Line Wise

May 5, 2012

i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example

Cheque amount in master block = 291

Invoice wise receiveable amount is as follows

Invoice No , receivable amount , Received amount

10, 196 , 0
20 , 95 , 0
30 , 54 , 0

Result should be as follows:

Invoice No , receivable amount , Received amount

10, 196, 196
20 , 95 , 95
30 , 54, 0

Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.

View 2 Replies View Related

Backup & Recovery :: Error / Corrupt Block Relative Dba / 0x01c12a58 (file 7 / Block 76376)

Jan 25, 2011

i have an error with block corruption

Error: Corrupt block relative dba: 0x01c12a58 (file 7, block 76376)

What all the ,methods I can go for if we are working on a production environment with out any down time.

I can go for dbms repair package and restore and recover

View 3 Replies View Related

Forms :: How To Read Values In A Multi Record Control Block Using Pre-insert Trigger (block Level)

Jul 24, 2010

I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.

I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".

View 3 Replies View Related

SQL & PL/SQL :: How To Set Variable

Jun 12, 2012

How do I set variables at the top of my code? I want to set the date as the variable.

Select * from employees where employee_dt > '2011-01-01'

View 1 Replies View Related

Get Variable Declaration?

Apr 24, 2013

it is possible to undeclared a variable if so how dont worry am with you to solve any problem lets we can do it

View 1 Replies View Related

SQL & PL/SQL :: Variable Losing Its Value

Jul 21, 2010

I have written an explicit cursor (procedure given below) and the issue I have is, when the cursor runs the sql statement

(CURSOR csr_address is
SELECT rtrn_id,
entp_abn,prog_program_cd,
sched_nbr,schd_version_yr,
litm_line_item_nbr, revise_val_text
FROM RETURN_LINE_ITEMS
WHERE sched_nbr = '000'
AND prog_program_cd = '01' AND litm_line_item_nbr in ('016','023')
AND rtrn_id = v1_rtrn_id;)

against a particular return id, it fetches 2 rows; one for line item 016 and the other one for line item 023 where in the litm_line_item_nbr for 016 is 016 and for litm_line_item_nbr for 023 is 023. Once that's done, (I have used a For loop cursor), it loops through as follows:

FOR country_rec in csr_address LOOP
v_line_item_16 := country_rec.litm_line_item_nbr;
if v_line_item_16 = '016' then
v_line_item_16 :='016'
end if;
[code]....

View 8 Replies View Related

SQL & PL/SQL :: How To Use Bind Variable

Jan 12, 2012

I have the below cursor 1 which is working already.For my requirement i want to use bind variable like second cursor.But its telling Bind Variable "p_col_list" is NOT DECLARED.

How to use bind variable Here.

Cursor1:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql ;
END;

Cursor2:
DECLARE
emp_cv sys_refcursor;
iid NUMBER := 1;
i_sql varchar2(100);
p_col_list varchar2(2000) := 'aaa,bbb,ccc,ddd';
BEGIN
i_sql := 'select '''||REPLACE(:p_col_list, ',', ''',''')||''' from dual '||CHR(10) ;
dbms_output.put_line(i_sql);
OPEN emp_cv FOR i_sql using p_col_list;
END;

View 2 Replies View Related

SQL & PL/SQL :: XML - Return New Row For Each Variable?

Oct 31, 2013

I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:

<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>

I would like to be able to return a new row for each variable, For example:

VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.

View 2 Replies View Related

SQL & PL/SQL :: Table As Out Variable?

Mar 2, 2012

I have plsql table as out parameter in one function.From another function i called that function ,its executing perfectly but while returning to called place its saying a character to numeric conversion error.I checked all the l

Note: i can not able post all that functions here.

View 12 Replies View Related

SQL & PL/SQL :: Assigning Value To A Variable?

Jun 14, 2011

Can I assign the outcome of a select query to a defined variable

like

var=select emp id from emp where empname='ddf'

View 5 Replies View Related

SQL & PL/SQL :: Variable In View

May 12, 2012

create view sample as

var varchar(100);
var:= select * from employee;

execute immediate var;

can we create like this ... my requirement is like this...

View 6 Replies View Related

SQL & PL/SQL :: Variable In The From Clause

May 8, 2013

Is there a way to have a variable in the FROM clause or another way to have the table name as a variable? I'm trying to not repeat the same query three times with only the table name being different.

---the tables

create table org_a (emp_id number(5) not null, name varchar2(20));
create table org_b (emp_id number(5) not null, name varchar2(20));
create table org_c (emp_id number(5) not null, name varchar2(20));

---the records

INSERT ALL
INTO org_a (emp_id, name) VALUES (00001, 'MISTER WHITE')
INTO org_a (emp_id, name) VALUES (00002, 'MISTER ORANGE')
INTO org_b (emp_id, name) VALUES (00003, 'MISTER PINK')
INTO org_b (emp_id, name) VALUES (00004, 'MISTER BROWN')
INTO org_c (emp_id, name) VALUES (00005, 'MISTER BLUE')
INTO org_c (emp_id, name) VALUES (00006, 'MISTER BLOND')
SELECT * FROM dual;

---verify inserts

SELECT * FROM org_a
UNION ALL
SELECT * FROM org_b
UNION ALL
SELECT * FROM org_c;

---i want the table name to be dependent on a variable. eventually,
---i intend to link v_org to a form with radio buttons (values: 1, 2, 3)
---to keep this simple, i'll just assign 1 to v_org

DECLARE

v_org number(1) := 1;
v_table varchar2(5);

BEGIN
v_table :=
CASE v_org WHEN 1 THEN 'org_a'
WHEN 2 THEN 'org_b'
WHEN 3 THEN 'org_c'
END;

SELECT * FROM v_table;

END;

--this is what i receive

SQL> /
SELECT * FROM v_table;
*
ERROR at line 10:

ORA-06550: line 10, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored

View 22 Replies View Related

SQL & PL/SQL :: Value Too Large For Variable

Jan 21, 2011

I HAVE DECLARED A VARIABLE

VAR1 VARCHAR2(20000);

BUT STILL WHEN I ASSIGN SOME STRINGS TO THAT VARIABLE I GET "VALUE TOO LARGE" MESSAGE. WHAT SHOULD I DO?

View 2 Replies View Related

SQL & PL/SQL :: Variable Value Is Not Being Recognize

Mar 27, 2013

reading the value from csv file and store into a variable and then use that variable in select statement in

filter. I have tried the following ways but it is now working.

File content is

first, second, third, forth
Y, N, 001|002, abc
N, Y, 003|004|005|006, xyz
n, Y, 007|008|009, mno

[code].....

View 8 Replies View Related

SQL & PL/SQL :: Variable Use In IN Condition?

Dec 5, 2010

I had a procedure in which there will be multiple update and select statements Eg: Update table T1 set Column1='X' where Column2 in ('A','B','C') All the update/select queries will have same set of values in the in condition ('A','B','C') will remain same. But the tables and columns will vary.So, I would like to declare a variable/array which holds the values in the in condition

var1 := {'A','B','C'}

and use in my statement like :-

Update table T1 set Column1='X' where Column2 in var1. Is there any way to acheive this?

View 7 Replies View Related

SQL & PL/SQL :: Using Incremental Variable

Jan 20, 2011

I have a stocking program , i need to use opening balance + debit-credit

In the beginning of my cursor then what ever the result i need to but it in a variable to used for the next record; i try many time but i failed

my statement look like this

opening balance 1000

receive used balance
-----------------------------------------------
0 50 950
0 100 850
100 0 1850

View 3 Replies View Related

SQL & PL/SQL :: Variable Is NULL

May 21, 2013

I can't figure out why my variables are not filled up?

TEST CASE

CREATE TABLE LIST_STEP_LINK
(
FAL_SCHEDULE_STEP_ID NUMBER(12) ,
FAL_SCHEDULE_PLAN_ID NUMBER(12) ,
SCS_STEP_NUMBER NUMBER(9) ,

[Code]....

Result:

SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> DECLARE
2 VAR_SCS_LONG_DESCR FAL_LIST_STEP_LINK.SCS_LONG_DESCR%type;
3 VAR_SCS_FREE_DESCR FAL_LIST_STEP_LINK.SCS_FREE_DESCR%type;

[Code]....

PL/SQL procedure successfully completed.

SQL>

View 13 Replies View Related







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