SQL & PL/SQL :: Anonymous Block Completed?

Apr 29, 2011

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

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Call Cursor Within Anonymous Block

Apr 17, 2013

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

View 5 Replies View Related

SQL & PL/SQL :: Error While Using Sys_refcursor In Anonymous Block

Sep 13, 2013

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

View 4 Replies View Related

Create A Function And Call It In Anonymous Block

Oct 22, 2013

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

View 5 Replies View Related

SQL & PL/SQL :: Create Autonomous Transaction In Anonymous Block?

Aug 24, 2011

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

View 5 Replies View Related

Server Administration :: Status Of Anonymous Block Program?

Jan 4, 2012

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?

View 3 Replies View Related

PL/SQL :: Unexpected Result With Select Max (column) In Anonymous Block

Nov 27, 2012

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

View 6 Replies View Related

SQL & PL/SQL :: Place Insert Statement In Loop Inside Anonymous Block?

Nov 25, 2012

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;

View 6 Replies View Related

SQL & PL/SQL :: Execution Completed With Warning

May 2, 2011

I'm trying to run the following procedure and I'm getting he following message:

Warning: execution completed with warning
PROCEDURE etl_bago Compiled.

The procedure is created, the problem is that when I tried to run it with parameters I'm getting this error:

exec etl_bago ('july19.csv');

Error report:

ORA-06550: line 1, column 7:

PLS-00905: object SYSTEM.ETL_BAGO is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
[code]......

Btw, I'm using SQL Developer 1.5.3. And the procedure script is here:

[URL].....

View 33 Replies View Related

Partitions For Archiving - Date With Completed Flag?

Jan 29, 2011

I am working on an archiving strategy. I want to roll off transactions that are older than seven days, but only if they are flagged as Completed. The numbers of transactions are very large so this is a worthwhile venture.

The only strategy I have been able to come up with so far is to partiton on date. Then when 7 days comes up, sweep the about-to-be archived day for the few remaining not Completed transactions, put those into a new table (a new version of this partiton) and switch partitions. Each day I do this until the older parititions are empty.

View 7 Replies View Related

How Many GB Completed In X Time-frame By RMAN On Oracle 11.2.0.2

Jun 23, 2011

is there a way to find-out how many GB completed in x time-frame by RMAN on oracle 11.2.0.2.

View 5 Replies View Related

Performance Tuning :: Verify If Procedure Has Been Completed

May 10, 2011

Is there a way to know if a procedure has been completed/ still running?

View 2 Replies View Related

SQL & PL/SQL :: Procedure Successfully Completed But Not Able To View The Data

Sep 26, 2010

I've created one procedure with following code.

PROCEDURE MaxNo IS
A Number;
BEGIN
Select Max(Supplier_ID)+1 into A from supplier ;
END;
SQL> Select Max(Supplier_ID)+1 from supplier;
MAX(SUPPLIER_ID)+1
------------------
6

when I execute it in a SQL PLus

SQL> SET SERVEROUTPUT ON
SQL> exec MaxNo;

PL/SQL procedure successfully completed.

View 4 Replies View Related

SQL & PL/SQL :: How To Call A Procedure From Anonymous Blocks

Jul 12, 2010

I have a written a script which doesnt take any parameter. this is an anonymous block. I run my script as below: SQL> @filename.sql

I would like to convert this into a procedure which should be called in an anonymous block. run a procedure from an anonymous block.

I would also like to know how to run the same from the SQL prompt.

View 5 Replies View Related

Client Tools :: Remove / Prevent PL/SQL Procedure Successfully Completed Message

Aug 20, 2010

I do not want the "PL/SQL procedure successfully completed." message in my output file - I don't see/recognise an option to suppress or remove this.

-- Test Proc
--
set linesize 500
set pagesize 50000
set tab off
set wrap off
set serveroutput on
set feedback on
[code]...

PL/SQL procedure successfully completed.

View 2 Replies View Related

Server Utilities :: IMP-00018 - Partial Import Of Previous Table Completed

Oct 11, 2011

I am trying to import the schema into 11g database, which i took on Oracle 9i database. While import is running, data file is full as auto extension was not enabled.

I got the following error:

. . importing table "WO_GL_ACCOUNT_SUMMARY"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table PWRPLANT.WO_GL_ACCOUNT_SUMMARY by 1024 in tabl
espace PWRPLANT
IMP-00018: partial import of previous table completed: 7055845 rows imported.

Then I increased the datafile size and finally Import terminated successfully with warnings. At this point, I want to know whether WO_GL_ACCOUNT_SUMMARY Table was imported with out missing any rows .

View 7 Replies View Related

SQL & PL/SQL :: How To Send Email In HTML Format By Executing Anonymous Procedure

Oct 11, 2010

How to send email in HTML format as an attachment/email body by executing oracle anonymous procedure

View 1 Replies View Related

Enterprise Manager :: Export Dashboards Or Access Them Via Anonymous User?

Jan 28, 2013

We have OEM and within are some great metrics and dashboards, with key information.We want to post some of these dashboards and metrics on our wiki page and/or within our application dashboards (we use jaspersoft.com/)

We do not want to give our normal users the access into OEM, so is there a way to somehow export these dashboards or access them via an anonymous user somehow?Either the dashboards themselves would be great, or somewhere to export the data the dashboards user - as i can then create the dashboards myself using the data.

example of dashboards/info to use would be:
- General, Host CPU, Active Sessions, SQL Response Time, Space Summary, etc...

View 1 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 :: Junk Value In Block

Jan 5, 2011

I am running the stand alone pl/sql block. it opens the for cursor and the updating the table. select value in the cursor having 3 lack records. the server output is returning junk values. Why i am getting this junk value here.

below is the format:

SET SERVEROUTPUT ON

BEGIN
FOR cursor name select
LOOP
UPDATE table_name
END LOOP;
[code].....

View 6 Replies View Related

PL/SQL :: DB Block Gets While Delete?

May 22, 2013

From plan I would expect the delete command to be processed very quickly, however it does huge amounts of db block gets. I assume this is because of deleting the row from other structures (indexes, foreign keys in tables etc.) How can I find out which object scan caused the db block gets ?

SQL> delete from messages2 where id = 11004240718;
1 row deleted.
Elapsed: 00:00:03.79
Execution Plan
----------------------------------------------------------
Plan hash value: 140582024

[code]....

View 25 Replies View Related

SQL & PL/SQL :: Clob For Return In Block

Aug 24, 2010

I want to execute a procedure and view its output. The procedurename is GET_clnts which has one of the parameters as clob where i am passing xmltype data to it.How can i execute the procedure to view the output. i can execute the SQL statement by itself, but not sure how to call a procedure from a plsql block..

DECLARE
clientid NUMBER;
OUT_RESULTSET clob;
BEGIN
GET_clnts ( clientid, OUT_RESULTSET);
DBMS_OUTPUT.Put_Line('');
END;
[code]...

View 2 Replies View Related

SQL & PL/SQL :: Loop Through Records Using Block?

Nov 8, 2011

How do I loop through a Input parameter (varchar_table) and pass the input value to a select query.

Procedure Test
(
param1 IN dbms_sql.varchar2_table
)

[Code]....

1. How do I define temp_tbl and is it a best practice to use temporary table - if not what is the best method to do the same.

View 5 Replies View Related

Forms :: How To Get Next Value In Master Block

May 3, 2010

I created one table with out primary key like well_1 .in that table have have more than 30 columns.

SQL> select uwi,analysis_obs_no,fluid_type,top,base,date_sampled from well_1
2 where uwi ='1000 and base =2871.5;

UWI obs_NO F TOP BASE
---------------- ---- ------ ----- - -----------------------------
1000 1 G 2870.5 2871.5
1000 3 G 2516 2871.5
1000 4 G 2870.5 2871.5
1000 2 G 2870.5 2871.5
1000 6 G 2516 2871.5
1000 5 G 2516 2871.5
1000 7 G 2516 2871.5
1000 1 L 2516 2871.5
1000 2 L 2516 2871.5
7 rows selected.

i did code like this in from in search panel. in taht form i will enter uwi and base values and iwill click search button.then it will display all record values in master block .after taht i will click next button then it will display next values(all values).

i am getting all values (1 G to 7 G values).but iam not getting last 2 records when i click next button (1 L to 2 L values ).

next button

select analysis_obs_no,fluid_type
into :ctrl1.OBS_NO,:ctrl1.fluid_type
from well_gas_anal
where analysis_obs_no =(select min(analysis_obs_no)
from well_gas_anal
[code].....

i tried in SQL*PLUS like this :

select rowid,analysis_obs_no,fluid_type,rownum
2 from well_gas_anal
3 where rowid=(select min(rowid)
4 from well_gas_anal
5 where analysis_obs_no > &a
6 and uwi ='1000' and base =2871.5 )
7 and uwi ='1000' and base =2871.5
8* and fluid_type is not null
SQL> /

View 1 Replies View Related

SQL & PL/SQL :: How To Know The Block Size Of A Table

Jul 12, 2011

AS of we know that we can get the number of blocks occupied by data in a table by querying the user_tables Data Dictionary.

My questions are,

1.)how to know if i want the each block size ?(i.e each block size either it is 4kb or 8kb or 16kb..)?

2.) Is the block size is same for all blocks in a table and in Database or it varies?

3.)The block size is DB dependent or Table dependent or Machine dependent(32-bit, 64-bit and OS)?

View 5 Replies View Related

SQL & PL/SQL :: Further Changes To Block By Transaction Not Allowed

Jun 6, 2011

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.

ORA-08007: Further changes to this block by this transaction not allowed

UPDATE table_20
SET capxuser = ?,
capxtimestamp = ?,
capxaction = ?,

[code]...

1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.

View 10 Replies View Related

SQL & PL/SQL :: How To Find OS Block Size

Oct 25, 2002

I find posted and written in many places that the DB block size should be a multiple of the OS block size. I can't find any information, however, on how to find what the OS block size is for an OS. How to find the OS block size for Windows and UNIX systems (Solaris, Linux, and HP-UX)?

View 4 Replies View Related







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