The following query gives me 28,800 as sum(sal)
SELECT SUM(salary)
FROM employees
WHERE department_id =60
O/P is : 28800But when i use the above query in anonymous block it gives me 684400
DECLARE
v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
I am trying to run the following script but some how i am getting message: anonymous block completed. where i am doing wrong and also how can i display the values with their column names.
set serveroutput on size 1000000
Declare
v_pr_nbr document.document_nbr%type:= ' ' ;
cursor c1 is
[code]...
I'm having an issue with an Oracle ref_cursor, how to call the cursor within an anomimous Pl/Sql Block. I did it once but I can't get it working now.
DECLARE
v_cursor sys_refcursor;
t_cursor v_cursor%rowtype;
BEGIN
OPEN v_cursor FOR
[code].....
For my task I tried to write a package that has sys_refcursor in it.It compiled well.Now I want to see the results of it and wrote a anonymous block which gave errors.
CREATE OR REPLACE
PACKAGE xyz
IS
[Code]....
Error:Invalid reference to variable 'OBJ_CUR'.
I made this script but I still don't quite understand if the syntax is correct. I just wanted to create a function and call it in an anonymous block. I then wanted it to use a variable as a parameter in an iteration and output the variable every iteration. It's done basically but I know it's not 100% right. The fibonacci function looks like its going to loop an infinite number of times if the parameter is greater than 2.
CREATE OR REPLACE PACKAGE myPACKAGE AS
CREATE OR REPLACE FUNCTION fibonacci
(n BINARY_DOUBLE) RETURN BINARY_DOUBLE IS
BEGIN
IF n <= 2 THEN
RETURN 1;
[code]........
Is it possible to create autonomous transaction in anonymous block.
find the below code, when I am trying to execute I am getting error
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
[code].....
One of our developers team member had created a Anonymous block program to do something in the Database, and he forgotten and terminated the session without confirming the program's status, whether it was fully ran or not.
Is there any way to check out the status of this, which happened yesterday?
can we place insert statement in loop inside anonymous block?
CREATE TABLE DEP(DEPTID NUMBER(5) NOT NULL PRIMARY KEY,DNAME VARCHAR2(10),LOCID VARCHAR2(10));
DECLARE
I NUMBER(5);
BEGIN
I := 0;
LOOP
INSERT INTO DEP VALUES(&DEPTID,'&DNAME',&LOCID);
I := I+1;
EXIT WHEN I = 5;
END LOOP;
END;
I have a table called temp which has nearly 20 columns when i insert values in some columns then null is inserted successfully in remaining column except one column,this column takes string of unexpected characters(like-"inverted question mark") which are not exist on my keyboard, how it is possible. it is happening only in this column and this is only one column of "nvarchar" type , is this because of it(datatype)?
View 4 Replies View RelatedI have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
when in sqlplus I execute:
select * from mytable;
in sqlplus I get the returned rows of this table. But this is not the case when I do in plsql the following:
create or replace procedure myproc is
a varchar2(50);
begin
a := 'select * from mytable';
execute immediate a;
end;
there is nothing I can see. Is there a way to catch this result set and see it on the screen? Something that is not creating new structures like cursors, etc?
I got this select that gets me a couple thousand rows, or over a million, and i can't get how to export it into a new database.
View 2 Replies View RelatedSELECT * from TABLE_1 where a=b;
Is it possible to make the SELECT (PL/SQL is OK ) wait/block in case there are no rows found until another session inserts rows that match the condition?
Something like this
cursor c is select * from TABLE_1 where a=b order by column_a for update skip locked;
row c%rowtype;
begin
open c;
[Code].....
Or the INSERTing session should send some kind of notification (semaphore?) to the reading session?
I have this query
SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
i have a little curiosity in result of a select statement.
One database has following characterset settings:
NLS_CHARACTERSET WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
BUP
OPEN
Another database has following characterset settings:
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
and the same select shows only a 2 line result:
SQL> select INSTANCE_NAME,STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST
( IDx number,
STARTx number,
DURATION number,
RESULT number );
insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
What is the sql code for the RESULT-column?
i have a table emp with three column
columns are (empid varchar,empnomini varchar,nominitype varchar), data in table like
empid empnomini nominitype
1 x B
1 y c
2 xx B
2 yyyy c
and i want data comes like
empid nominitype b nominitype c
1 x y
2 xx yyyy.
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
i want to select dynamic column names in my select statement in my function.
View 4 Replies View RelatedI have a question regarding the optimal way to code a dynamic SELECT INTO statement. Below are the 2 posiibilities I know of:
1. Dynamically executing the SELECT statement and making use of the INTO clause of the EXECUTE IMMEDIATE
statement
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
[code]...
2. Encapsulating the SELECT INTO statement in a block and dynamically exectuting the block
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
[code]...
which way would be preferred? I know the second method uses a bind variable for the INTO clause, but does the first one also use bind varialbes (no semi-colon)? Any differences in terms of efficiency or speed?
QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;
NAME DOJ QUAL EMPID
---------- --------------- ---------- -----
sam 21-mar-2012 mca
sony 03-jan-2000 mba 10610
maya 21-mar-2012 m.arch
sonna 18-mar-2012 mis
[code]...
i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?
While writing a procedure I went into this problem. Whenever I write Query : Select * from dba_pending_transactions It works fine.
But whenever I use same Select Query inside PL-SQL block it gives error Table or view not exist. Dba_pending_transactions is view.
SQL> declare
2 v_count number(2);
3 begin
4 execute immediate 'select count(*) from dba_ending_transactions' into v_count;
5 dbms_output.put_line(v_count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4
Same error I get when i use it inside a procedure.
I am having a table structure like below.
city amt1 tx_date
-----------------------------------------------------
Blr 10000 20050101
Delhi 25000 20050101
Blr 10000 20050102
Blr 2100 20050103
DELHI ...... 20050104
...... ....... ........
i have to place the data in following manner.
city 20050101 20050102 20050103 20050104 ...........etc
-------------------------------------------------------
Blr 10000 10000 2100
Delhi 25000 0 0
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
I want to load query result into .xls file with column names by using plsql.
Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.
Can we automate in PLSQL? If it is possible pls share the script.
Ex: select ename,eno,dept,deptno,sal,hiredate from employee;
ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.
I am trying to find difference between two dates .I am getting 73045 as the difference. I want 12 as difference. find the below lines of
request_date:=TO_DATE('1-jul-2011','dd-mon-yyyy');
Today_date:=TO_DATE(sysdate,'dd-mon-yyyy');
dbms_output.put_line('>> difference is ' ||( Today_date-requestdate) || ' days' );
Output:-difference is 73045 days
( if tried '2-jul-2011' as request_date, i am getting 73046.
I've come across an fatal error which terminates my database instance. It's basically cropped up twice over a weekend, and the instance needs to be restarted as a result.There doesn't appear to be any entry in the alert log that ties with the time of the failure, so I only have the trace file entries to go on...
CODE*** 2010-07-30 22:15:31.238
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Failed
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Failed
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Failed
kgskrecoveryrecalc: Failed kgsksysstop for 6
kgsksysstop: from KGSKWT_INTSTATECHNG6 retry FALSE mode
kgsksysstop: Succeeded
[code]....
I had a look on my old metalink account to see if there was anything relating to that ORA-56710 error code, but the one article I foun d seemed to be related to RAC instances (which I'm sure that this instance isn't).
I'm using Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production.
I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC
The contents of rows in Table above are:
select * from emp;
select * from dept;
Can I write a PLSQL block that can select these 2 rows from the table and execute them?
I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)
how to compare these two tables to generate something like the following. I can join these two tables to generate the diff but it is one row per account.
client_nbr branch_cd, account_cd, action column, old_value, new_value
8888 123 45678 C account_clsfn_cd 004 005
8888 123 45678 C buy_cd 98 99
8888 012 34546 A sell_cd 12
8888 321 98765 D dividend_cd 1
I am using Oracle 10g so Unpivot cannot be used.
CREATE TABLE T1
(
CLIENT_NBR CHAR(4 BYTE) NOT NULL,
BRANCH_CD CHAR(3 BYTE) NOT NULL,
ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
ACCOUNT_CLSFN_CD CHAR(3 BYTE),
SELL_CD CHAR(2 BYTE),
BUY_CD CHAR(2 BYTE),
[code]....
I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.
My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).So I could easily drop old data by dropping old partitions and tablespaces.
Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.The problem of this idea is that i have to update billions of rows.
It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.Is there any way to "link" this information to a column in MESSAGE table?
I mean something like this:
alter table MESSAGE
add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;
Is there some possibility like that? Would the partitioning on this column / statement work?Would the value of the keep_message be calculated on runtime?
If so will the performance influence be noticeable?If so will the performance also sink if the application is querying all rows except the keep_message?