Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well.

learn more… | top users | synonyms

0
votes
0answers
29 views

plsql script continue excution even there ar promt statement

I am facing new problem in plsql , i programmed a script for 200 line but ,there is no wrong but its weird wrong ,it means oracle doesnot inform with error ,but from human being it error like iam ...
0
votes
0answers
35 views

Oracle Calling a Procedure Returning a CURSOR in another Procedure

I have 2 packages and a procedures that return a sysref cursor. Now i want to call either of the 2 packages and procedures in another package and procedure that will take the decision on an input ...
0
votes
1answer
47 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
0
votes
0answers
153 views

execute .sql script inside an anonymous pl/sql block which is invoked by a shell script

I got a shell script which connects to SQLPLUS and executes the .sql file like below: read a x=`echo $a | cut -d , -f 1` y=`echo $a | sed 's/.*,//'` c=`echo $USER | cut -c -2`"OPR"`echo $USER | ...
0
votes
1answer
73 views

Migrating stored procedure from Sybase to Oracle having temporary tables

I am migrating a few stored procedures from Sybase to Oracle using Oracle Developer tool. The tool migrates alomost 70%-80% of the stored procedures except a few cases where there is a temporary table ...
0
votes
1answer
49 views

Insert Into does not work with multiple rows

Here Is my code : CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER ...
0
votes
0answers
33 views

Running DBMS_AUDIT_MGMT package under other user than SYS

I want to run DBMS_AUDIT_MGMT package under user CENTRUMADMIN. But error occurs even when I try to run this part of code: BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => ...
0
votes
0answers
75 views

How to startup a oracle database with sql plus or with any developer tool without connecting to the Database

How to startup a database without connecting to Database. Actually what I did is I shutdown my database and then my sql plus screen hanged and so closed unhandled. then I tried to connect my ...
0
votes
0answers
46 views

Strange behavior with context index on matarialized view

We are experiencing a very strange behavior with a materialized view on oracle. Here is the statement that we've used to create the materialized view: CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW ...
0
votes
0answers
40 views

My stored procedures often get invalidated

The reason for invalidation should be because of some DDL activity, but I don't have any DDL in the SPs. I need to find what is that DDL statement. If the object is in an invalid state, when you call ...
0
votes
0answers
40 views

Sorting the view on which the stored procedure runs

I want to sort the view on a particular column before my stored procedure runs. Is there a way to do this through the stored procedure? Can anyone show me a MySQL example? This is what the stored ...
0
votes
1answer
29 views

Delete all types created by a user

In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;
0
votes
1answer
53 views

PL/SQL cursors and records

I am a new to Pl/sql and I need to learn to write simple pl/sql scripts. Is there any difference between cursors and records in PL/sql? So far I find them both similar.
0
votes
0answers
113 views

export query results to file

I am facing the following issue. I want to execute a query and export all the results in a file. My query is saved as in varchar2 type and i can execute immediate on it and give me all the results. ...
0
votes
0answers
38 views

From keyword missing in view

I am this view which I am trying to compile. Given below is the view. CREATE OR REPLACE FORCE VIEW "My_View" ("my_ORDER", "REQUESTOR", "DEPARTMENT", "HOD", "SHIP_TO", "SOLD_TO", "PRODUCT", ...
0
votes
0answers
79 views

Issue with a where clause by using Oracle

I'm currently facing a problem while I'm adding a trigger to the database that I use at home. I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production The problem ...
0
votes
0answers
114 views

Oracle data obfuscation on specific columns with set field sizes

Would like to obfuscate data held in Oracle tables, but: Requirements only apply to specific columns e.g. personal data obfuscated value should not be longer then column size possibly only ...
0
votes
0answers
19 views

How to display '0' against an empty database field (number) with defined fomat mask [duplicate]

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 25,000 Data2 ...
0
votes
1answer
660 views

Oracle strong ref cursor with dynamic sql

I'm using Oracle 10g. I have a query in a stored procedure that selects entire rows from a table. Currently it returns a strongly typed ref cursor (tablename%rowtype). Now I have to add some dynamic ...
0
votes
0answers
500 views

'oracle.jdbc.driver.OracleSQLException': ORA-01031: insufficient privileges. (SYS.DBMS_XMLSAVE)

I have this function (written here by someone), placed in the package ENVER.ENCODING_VERIFIER: FUNCTION loadxml_table (p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_format IN VARCHAR2, p_tmpClob IN ...
0
votes
0answers
27 views

Issue: PLS-00103

I have a package body: PACKAGE BODY "PKG_SYNONYM_TEST" AS type t_table as object ( synWord varchar2(500) ); type t_nested_table as table OF t_table; function search1(i_string varchar2) RETURN ...
0
votes
0answers
47 views

Logical or in oracle PL/SQL

I have this code: declare error1 number; error2 number; error3 number; begin error1 := 1; error2 := 0; error3 := error1 | error2; -- should be error3 = 1 end; But I dont know how to ...
0
votes
0answers
68 views

Error ORA-06550

CREATE OR REPLACE PROCEDURE pro_department_report AS v_did department.deptid%TYPE; v_dname department.dname%TYPE; v_sname student.sname%TYPE; v_count NUMBER(2); v_sdid student.deptid%TYPE; CURSOR ...
0
votes
0answers
243 views

Creating and tuning a materialized view in oracle for use by Cognos BI

I am currently in the middle of a Cognos BI implementation and am having difficulty in tuning a view in order for SQL Server to select its contents for fulfilling the incremental load in a timely ...
0
votes
1answer
288 views

Call pl/sql function and discard result

I'm calling a function from pl/sql, but I do not require the result in this case. Is there a cleaner way to call this function without using a dummy variable? Works: junk := performWork(); Does ...
0
votes
0answers
250 views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0
votes
0answers
1k views

12154 error when using PL/SQL Developer

I have installed a few versions of Oracle on my machine. I have tried 64 bit and 32 bit versions. I have installed 11G. I need Oracle 10G 32 bit. Using the following guide I removed all versions of ...
0
votes
1answer
652 views

ora 20001 error in oracle dbms scheduler

i have written a test job like begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'test_job' ,job_type =>'plsql_block' ,job_action => 'null' ,enabled => true ); end; / after a while when ...
0
votes
1answer
276 views

Installing Oracle Database 12c in Windows 7, INS-08109 error [closed]

I'd like to learn PL/SQL so I'm trying to install Oracle. I downloaded the 2 zip files and extracted to the same directory. I run the "setup.exe" file and the install GUI starts: In step 1 I enter ...
-3
votes
1answer
68 views

Why DBMS_METADATA.GET_DLL not work with EXEC command ?

Using Oracle 11g I just trying to understand the concept here.... Why the command bellow not work? (executed at SQL Developer) execute dbms_metadata.get_ddl('VIEW', 'VW_CARGA','INT') Error ...