SQL & PL/SQL :: Display Boolean Value Of A Variable In DBMS Print Statement?
May 29, 2012
I want to display Boolean value of a variable in DBMS Print statement. I am able to do it by using simple if condition checking .How to print the value of Boolean variable directly ?
SQL> declare
2 boo boolean := true;
3 begin
4
5 if boo then
6 dbms_output.put_line('boolean variable value TRUE by checking if condition ');
[code]....
ORA-06550: line 9, column 24:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
View 6 Replies
ADVERTISEMENT
Aug 28, 2012
CREATE OR REPLACE FUNCTION is_overdue (due_date IN DATE,paid_date IN DATE)
RETURN BOOLEAN
IS
days_between NUMBER (2) := due_date - paid_date;
BEGIN
[code]......
When i try using this compilation, i am getting error for this program:
SQL> begin
2 dbms_output.put_line(is_overdue('07-nov-1987','01-aug-2012'));
3 end;
4 /
dbms_output.put_line(is_overdue('07-nov-1987','01-aug-2012'));
*
ERROR at line 2:
ORA-06550: line 2, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
To get output for this program? Show me how to compile.
View 24 Replies
View Related
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 Replies
View Related
Apr 17, 2013
i want to print a message just before asking for input to a bind variable
SQL> SET serveroutput on;
SQL> DECLARE
2 pname varchar2(20);
[Code].....
but i want to print 'hello world' before asking value for bind variable like:
hello world
Enter value for num: 1
old 9: WHERE ID = #
new 9: WHERE ID = 1;
then how to achive it?
View 4 Replies
View Related
Jun 29, 2013
i want to retrieve a value from the database and then print in a display item. how can i do it?
this code is the retrieval part but it doesn't work:
DECLARE
num number(10);
BEGIN
SELECT ID_CD
INTO num
FROM cd_details
WHERE price=599;
end;
View 7 Replies
View Related
Oct 4, 2012
how can i put button in a form to print the information i displayed in this form.
View 1 Replies
View Related
Feb 16, 2011
I created a sample application in java which concatenates three strings and displays the result in a final string.
Check the -
public class conCat
{
public static void main (String[] args)
{
String Message1 = "This ";
String Message2 = "is a Sample ";
[code].......
This is a Sample Application
And the three strings are concatenated to the final string "Message" only. How to concatenate them vertically? The required output is :
This is a sample Application
View 1 Replies
View Related
Jul 5, 2011
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT
Location,
LEVEL,
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
View 7 Replies
View Related
May 26, 2010
am new to oracle based coding and am having a heck of a time trying to figure this out. Basically i need to declare 2 date variables, Begin date and End Date.
I then need an if statement that says if begin date = 0 or is null then set it to the first day of the previous month. and if end date = 0 or is null then set it to the last day of the previous month.
So for today 5/26/2010 i would need
begin date = 4/1/2010
end date = 4/30/2010
I have searched and tried but so far to no avail.
View 10 Replies
View Related
May 27, 2010
following is my query model
SELECT INV.VENDOR_ID,inv.invoice_date,inv.po_header_id,
INV.INVOICE_ID,
INV.INVOICE_NUM,
[Code]....
WHAT i have to write in select field
View 3 Replies
View Related
Apr 5, 2011
here is what i am trying to do: im as using oracle 8 with sqltool
i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;
but could not get it to work.
View 2 Replies
View Related
Mar 1, 2012
open cp_cursor for 'Select curtailprogramkey from curtailProgram where curtailprogramid like 'l_rec.curtailprogramid%'';
NOTE: l_rec.curtailprogramid is varible. what is wrong in the above statement?
View 2 Replies
View Related
Apr 20, 2012
I have statement like SELECT * FROM DIVISIONS;
i have to run this statement for different table, and I would like to pass the table name as variable
ex:
create or replace precedure dynamic_execute
v_tbl varchar2(30);
begin
---- assign table name here
v_tbl := DIVISIONS;
EXECUTE IMMEDIATE('SELECT * FROM '||v_tbl);
end;
/
When I executed the procedure I got error, how to pass the table name as variable and execute the statement successfully.
View 5 Replies
View Related
Jul 25, 2010
I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:
select * from scott.emp
and i want to write it as:
select * from variable.emp
But at compilation of the form the compiler should know the above variable name.
I have tried to use following select statement but it does not work.
select user into :global.username from user_users
I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.
View 4 Replies
View Related
Jul 27, 2010
i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.
example:
-----------
from 'ABCDEFGH' to 'ACEG'
removing 'BDFH' from the source string 'ABCDEFGH'
i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.
View 14 Replies
View Related
Jul 20, 2012
I have to display a 24 hours time as an index. (00:00 - 23:00) That is easy, if the data exists.
But, if the data is not regularly exists on specific hour (the time/hours is not regular, i.e: 00:00, 01:00, 05:00, 08:00, 10:00, 11:00, 23:00), then it is OK for a TABLE, but not for a CHART.
Here is the story:
I wrote an SQL statement that can "choose" what to display respectively to the the available time. The result will be displayed on the chart: Days or Time is used for X-Axis, and the respective Value on the Y-Axis.
Example:
+ if the data consists of "days" (i.e: the last 5 days from now, 16/07/2012, 17/07/2012, 18/07/2012, 19/07/2012, 20/07/2012), then the chart will show those dates on the X-Axis.
+ if the data consists of "hours" (i.e: 1 single day, from 00:00 hour - 23:00), then the chart shows hours on the X-Axis.
If the 'hours' are not regular, means: there is no data on specific hours, the query result is also not regular, means: the X-Axis-value 'jumps' irregularly.
Question:
Is it possible to query our own static value?
for example:
select 'hello' from dual --> result: "hello", 1 column, 1 row
But how to display this in multiple rows, i.e.:
Time
*****
0
1
2
3
4
5
...
...
23
Note: the X-Axis on the chart template can be only customized for 1 single mode, example: "Days", but of course it is not going to show up properly if the query result are in "Hours". It means: I have to find the workaround on the SQL Query.
DB: ORA 11
View 9 Replies
View Related
Dec 1, 2012
I'm trying to create a table 'a' with column name as 'flag' and datatype as 'boolean' ie. create table a(flag boolean);
is it possible in sql or pl/sql in oracle?
If its possible to create a table and also let me now inserting row and what boolean should return.
View 1 Replies
View Related
Mar 24, 2011
I have this SQL statement:
WITH data
AS (SELECT user_id,
jc_name,
[Code]....
I wish to do something like
if results > 10 print an error message (and no results)
if results < 10 print the results/output
View 1 Replies
View Related
Dec 27, 2012
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....
View 17 Replies
View Related
Mar 12, 2013
Application Express 4.1.1.00.23 ( plus all earlier versions that I've ever used)
When using the wizard to create a Validation of type "PLSQL Function returning Boolean", why is it mandatory to enter a value in the text field "Error Message" on the screen that follows? This message is never used as the message actually displayed comes from a PLSQL return statement.
View 5 Replies
View Related
Oct 17, 2012
In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
View 1 Replies
View Related
Apr 23, 2012
How to pass parameter value to a procedure in schedule jobs ?
--procedure :
create or replace procedure updating_temptable(empcode in varchar2) is
begin
update ts_employee_master mas
set mas.last_accessed = sysdate
where mas.employee_code = empcode;
end;
--scheduler
begin
sys.dbms_job.submit(job => :job,
what => 'updating_temptable;',
next_date => to_date('24-04-2012 00:30:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(SYSDATE)+1+30/1440');
commit;
end;
/
How to pass employee_code in the procedure[updating_temptable ] in dbms job scheduler ?
View 5 Replies
View Related
Mar 6, 2013
I am begginer programing oracle and I have a issue to resolve but I can't resolve it.I have a procedure that upload a image from a directory. But when I pass the path, the function bfilename put a slash "/" in the path. I don't know why.Here my code.
CREATE OR REPLACE PROCEDURE p_grava_assinatura_gestor AS
---------------------------------------------------------
-- Crio o Cursor dos Nomes dos Arquivos JPG. --
---------------------------------------------------------
CURSOR cursor_nome_arquivo IS
SELECT p.id, (p.empresa || '_' || p.chapa || '.JPG') AS noarq
[code]...
View 9 Replies
View Related
Oct 29, 2010
I am trying to redefine a table as to get clear of the thousands of chained and migrated rows. The darn table contains a lob column.I am trying to complete the process with the dbms_redefinition package.
I've createad the temporary table with all option to nologging. I've started the redefinition at midnight and it not yet completed and I can't find why it is taking so long. I've done the process with a table, similar in size in a Dev environement and it took at most two hours. In production, the process has been going on for 10h30 now.
I can see my tablespace size increase from time to time and when I querry the dba_segments to find out the size of both tables my temporary table is now 4 times the size of the original table.
View 7 Replies
View Related
Oct 7, 2010
I am creating the following dbms_sql procedure...
CREATE or replace PROCEDURE table_demo
(tabname IN varchar2)
IS
cursor_name INTEGER;
rows_processed INTEGER;
[code]...
There are no compilation errors.I call this code from the following anonymous block...
DECLARE
X CHAR:='T';
BEGIN
TABLE_DEMO(X);
END;
This also compiles successfully and without any errors. It runs properly as wellHowever when I run 'select * from T'. Then system throws up the error of table or view does not exist.
View 12 Replies
View Related
Sep 24, 2012
SET serveroutput on size 1000000
SET wrap on
SET linesize 80
DECLARE
v_text VARCHAR2 (4000);
v_res NUMBER;
v_num NUMBER;
[code]...
View 6 Replies
View Related
Aug 11, 2011
Database 1(sm01):
=============
oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.Daily jobs are running fine. But hourly job is not executing automatically. If forced (exec dbms_job.run(<enter here job number>), this execute fine.
job_queue_processes=5
total jobs in schema=2503
total jobs in db = 2614
Even there are many jobs scheduled, next_date for 2234 jobs are lesser than the sysdate. Again in 269(2503-2234) jobs, 2265 are having NULL in the interval column.
Database 2(sm02):
=============
oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.All the jobs are not running automatically. If forced (exec dbms_job.run(<enter here job number>), these execute fine.
job_queue_processes=5
total jobs in schema=7
total jobs in db = 7
I planning to follow the below steps to avoid the above issue.
1.) Restart the job queue process by executing alter system set job_queue_processes=0
2.) Increase the value for the job_queue_processes.
3.) Restart the database
But I got stuck in the 2nd step. what value I need to put for this job_queue_processes parameter?
View 2 Replies
View Related
Sep 7, 2010
Under what userid(privileges) does a scheduled DBMS Jos run, is it under the same user that scheduled the job?
Does oracle internally log in(opens a new session) in order to run a scheduled DBMS Job?
some reference websites where I can get some details about how the scheduled jobs are executed?
View 2 Replies
View Related
May 18, 2010
I'm currently busy with database consolidation, so I'm searching for a solution to generate some useful DDL to prepare the new target database before importing the application's data. This should include TABLESPACE DDL and all additional users with their grants.
So first I thought of developing a simple script, which will create the CREATE TABLESPACE DDL but with transformed datafile paths.But my throws some errors and I don't understand why:
ORA-31604: invalid NAME parameter "STORAGE" for object type TABLESPACE in function SET_FILTER
declare
l_hObject NUMBER;
l_Objddl CLOB;
[code]...
View 7 Replies
View Related
Mar 27, 2013
I saw bunch of other posts but I could find the post that exactly explaining about where the value returned as systimestamp / sysdate comes from or impacted Here is my situation I have an access to this db (let me call db A) and when I access it, I get following result. I don’t have full access to this db so I cannot experiment a lot here.
SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE
--------------------------------------------------------- --------------------------------------------------------- ------------------------------------------------ --------------------- ----------------------------
27-MAR-13 02.31.55.041411 AM +00:00 26-MAR-13 07.31.55.041416 PM -07:00 26-MAR-13 07.31.55.041416 PM +00:00 -07:00
I’m in PST timezone.I have my db which I have full access as well as its host.I can make result like db A on my db if I started up db and its listener while TZ environment variable equal to UTC.Now I saw in other post that someone was trying to retrieve systimestamp value in a job executed via dbms_scheduler.run_job.
So I did that in two ways. 1 with use_current_session = true and 2 is false for the same.On my db, results are the same (both returns time in UTC) but on db A, I got UTC time when use_current_session = true and PST when use_current_session = false.
So questions are:
What could be the difference in setup between my db and db A?
Is there a query, logfile, or anything I should check to find out what can be the difference?
I tried to find the cause with my db and I could see the same result as db A which is to see UTC time if use_current_session = true and PST time if use_current_session = false by bringing up the db listener after I set TZ environment variable equal to PST8PDT. However this causes systimestamp from sqlplus session become also a PST time.
The reason I’m playing around with the setup and checking systimestamp value is because we are facing the situation where everywhere except pl/sql job submitted by enterprise scheduler service is pointing wrong timezone (PST instead of UTC)
View 0 Replies
View Related
