Questions tagged [plsql]

PL/SQL (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. Questions with regular DML or DDL statements should be tagged with "sql" and "oracle", NOT with "plsql".

0
votes
0answers
22 views

PLSQL DBMS_SCHEDULER wait for response

I've created a DBMS_SCHEDULER to run a batch-script from PL/SQL. The script starts as expected but now I need to find a way to pause my PL/SQL code untill the batch-script has done it's work. Is ...
0
votes
2answers
20 views

Missing privilege - Oracle System event trigger problem 12c

I have a system event trigger. It worked fine until I tried to move the code to a different database. I made a mistake: forgot to export the original user, so I created a total new one. So after I ...
0
votes
0answers
47 views

how to structure my procedure, and my cursors

Need a procedure that will retrieve the information based on the select i have a above that contains the missing rows, missing information, that needs to be mapped and inserted into TP_DOC_MAP, so it'...
2
votes
2answers
27 views

Oracle INVALID_NUMBER exception in my function does not work

The Oracle function below assign 'A' to number variable. But the Exception clause does not catch INVALID_NUMBER system exception. CREATE OR REPLACE FUNCTION TEST2 (P1 IN VARCHAR2) RETURN NUMBER ...
1
vote
2answers
29 views

Add a table name dynamically in a stored procedure

I've been trying to find ways to change the table name dynamically in a query but not found one so far that works. The query has repetitive code (around 150 lines of code inside each case statement) ...
0
votes
1answer
28 views

Table is mutating, trigger may not see it

There is no error when I create the trigger. But when I fire it, an error message displays that the table is mutating. How do I solve this problem of table mutating? CREATE OR REPLACE TRIGGER ...
0
votes
1answer
28 views

How to solve “Encountered the symbol ”end-of-file“ when expecting one of the following: ” in sql

How should I end the transaction below so that I don't receive anymore the error: Line/Col: 30/19 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( ...
1
vote
1answer
49 views

Solve “cannot perform a DML operation inside a query ” [duplicate]

The pl SQL script below transfers the amount c of money from account a to b. Why isn't allowed to update the table in the function / how can it be fixed? create or replace function ueberweisung (a ...
1
vote
1answer
24 views

how to put a condition in plsql procedure when count =0 then call a procedure or else continue as it is

i am editing a procedure in oracle forms like normal procedure in PLSQL where at the end of the procedure i am creating a new condition , when the count = 0 then call a different procedure or else ...
2
votes
1answer
33 views

access to child variable pl sql

I am trying to access the child variable of the object through the parent declare i a22; i1 VARCHAR(10); begin i := a22('a22', t_b1( b22('b22', t_a1(a22('a22_2', t_b1(b22(...
1
vote
0answers
52 views

Table name, column names as argument to stored procedure

I am a newbie to stored procedure and to PL/SQL. There is an existing procedure to copy data from one table to another. I want to rewrite the stored procedure to accept table name and column names as ...
-2
votes
1answer
15 views

How to add extra column in the INTO section of SQL Trigger

How to add an extra column (say column_2) in the below INTO section of my code along with my Column_1. I assume we can do that by adding comma (,) and just add column_2 (like this INTO :new.Column_1, ...
0
votes
1answer
32 views

how to use trigger in a way to have all updated columns of base table to multiple rows of audit table?

I am trying to insert old and new values of 'Base' table into auditing table 'Audit'. Problem is in requirement of how data to be stored in Audit table. If more then one columns are updated I should ...
0
votes
0answers
16 views

Add vArray of XMLType inside other xml tags in plsql oracle

I have a cursor that returns a list of invoices. I will need to generate a tax document for payment called GNRE. For each note I must generate with the TDadosGNRE tag. So I have a vArray of XMLType ...
-1
votes
0answers
37 views

How to disable autocommit in oracle apex 5?

I am calling a PL/SQL function over dblink from SQL Workshop in APEX 5. SELECT MY_FUNCTION(...params...) FROM DUAL; When I have "Autocommit" unchecked it works properly, but when I have "Autocommit"...
0
votes
3answers
32 views

How to update another table's value using insertion trigger of another table

I am working on a database project on national election system . While casting vote for a certain candidate I want to automatically update total_votes +1 in the results table . The total_votes of all ...
0
votes
2answers
31 views

harcode Rows with union

I have a select query SELECT COUNT(DISTINCT CASE WHEN LANGUAGE = 'CHINESE' THEN email_address END) AS CHINESE, COUNT(DISTINCT CASE WHEN LANGUAGE = 'FRENCH' THEN email_address END) AS FRENCH,...
-2
votes
2answers
38 views

Null output is overwritten with the next output

The output of the code should not overwrite the null values. I need the output in the attached format(1st output) and it is being shown in different format(2nd output). Please suggest me how to ...
2
votes
2answers
53 views

Oracle / Plsql do I need to check that the file is saved?

ORA-29280: ORA-06512: "SYS.UTL_FILE", line 270 ORA-06512: "SYS.UTL_FILE", line 1243 ORA-06512: line 9 declare fhandle UTL_FILE.FILE_TYPE; begin /********************************...
0
votes
1answer
57 views

PL/SQL Replacing characters in a string using another table

I need to create function that replaces characters in a string to characters from another table. What I'm trying returns exactly the same string from the beginning. Table t_symbols is: +--------------...
1
vote
1answer
27 views

Oracle Reports - How to Capture Page Number and Insert to Database?

I have fields linked to page number and total page number. And that's pretty simple.(Image below) But I also want to capture the page number in "BETWEEN PAGES " trigger and insert into database.(...
-1
votes
2answers
57 views

Execute Immediate?

Why do we use execute immediate command in PL/SQL? I'm looking at some procedures written by a previous colleague, and I see that the person has used execute immediate a lot many time to log the ...
0
votes
2answers
41 views

is it possible to concatenate a value of a variable into the ACCEPT prompt text?

I am trying to add a value of a variable into the accept prompt box in oracle developer ACCEPT confirm char PROMPT 'Are you sure you want to delete org_name ? ' ||org_name ; SET SERVEROUTPUT ...
0
votes
0answers
38 views

How we can specify that the TRG_2 trigger should be executed before the TRG_1 trigger by recreating the TRG_1 trigger using the execute clause?

I have created the two trigger, trigger 1 name TRIGGER_FOLLOWS_TEST_TRG_2 & trigger 2 name TRIGGER_FOLLOWS_TEST_TRG_1. I want the TRIGGER_FOLLOWS_TEST_TRG_2 trigger to execute before the ...
0
votes
3answers
34 views

Grant/revoke execute permission to procedure from package

I've couple of stored procedures(Let's say PROC_1 and PROC_2) inside one package(i.e. PROC_PKG) which is under one of schema/user (i.e. A). I need to grant execute permission to another user(i.e. B). ...
0
votes
1answer
38 views

WITH Clause Enhancements in Oracle Database 12c [on hold]

I would like to know the performance issues(if any) of PROCEDURE and FUNCTION usage in new WITH Clause feature introduced in 12c release 1.
0
votes
1answer
27 views

Pass a variable from one sql script to another as parameter

I am trying to call a sql script from another script, and encountering some issues when trying to pass a variable as parameter. When defining a constant, I can pass it like this: DEFINE MY_PARAM = "...
1
vote
2answers
24 views

Get distinct emails from semi colon separated string

I have three tables in plsql TBL_A,TBL_B and TBL_C, I have email column in each table and have multiple values in that column separated by ';', I want to combine DISTINCT email values from all 3 ...
-2
votes
2answers
40 views

for resolving multi value loop used but error coming

ORA-06550: line 10, column 89: PLS-00103: Encountered the symbol ";" when expecting one of the following: * & - + / at mod remainder rem .. || multiset year day ...
1
vote
1answer
35 views

How to work with PL/SQL Arrays or Collections as Parameters then JOIN them Together by Index

I am attempting to create a really simple procedure that creates an account or several accounts. In order to accomplish this I wanted the implementation of the Call that will be made to this ...
0
votes
2answers
25 views

How to store changes history data in Oracle and handle the non-changed values

Hey guys I have one table called Users: Then I have another table holding the changes, made to the above table, called Users_History_Changes: I know I need trigger, which fires, when one table is ...
0
votes
1answer
37 views

Oracle APEX - no leading zero

I have an app build in Oracle APEX 18.2. Every number field in app have missing leading zero. For example when the number is 0.5, APEX displays it as .5. The problem occurs also in SQL Workshop. In ...
1
vote
0answers
29 views

How to set the policy Context in oracle inside sql query “mo_global.set_policy_context('S',orgid);”

There are some views in Oracle will not have data until you set the context policy. I need to set in the SQL query so that I run the query once. BEGIN MO_GLOBAL.SET_POLICY_CONTEXT ('S', 82); END; ...
2
votes
1answer
53 views

Why comparing varchar/number works

Why this block gives no error with NULL; inside. And raises error (ORA-06502: PL/SQL: numeric or value error) with any other instruction inside? BEGIN IF 'x' = 1 THEN NULL; --dbms_output....
-1
votes
1answer
30 views

Error(15,5): PLS-00103 while saving the stored procedure

I have written a stored procedure which would pull the counts from tables and if there is any mismatch in counts some sql query will be executed to capture the missed item. The output of the stored ...
0
votes
1answer
34 views

How to use antlr4 plsql grammar (java and org.antlr.v4.gui.TestRig)

Context I am trying to parse some SQL stored in an application configuration using the Java classes generated from the plsql antlr4 grammar files. In this context I'd like to know how to use ...
0
votes
3answers
46 views

Make in (SQL) dynamic for incoming values

Is it possible for in statement to be dynamic? like dynamic comma separation for example: DATA=1 select * from dual where account_id in (*DATA); DATA=2 select * from dual where account_id in (*...
0
votes
1answer
29 views

DBMS_APPLICATION_INFO seems to work in procedure but not in a job

The below sample code illustrates an issue I am having with dbms_application_info. If I use it in the below procedure: create or replace procedure test01 is vsql varchar2(50); begin vsql := 'select ...
0
votes
0answers
24 views

PrimeOuput start time is null after many hours?

Problem: My ETL (Deployed on SQL Server) is hanging on dataflow where it is extracting (from Oracle Source Attunity driver) information. In the past, it has processed 6 mil records. However in this ...
0
votes
1answer
35 views

how to make beforeInsert trigger to set specific value in column if other column is null

i have this table , that i want to make before insert trigger that will check if the new inserted value had null in column A , then it will insert 0 in column B + it will set created_On with ...
1
vote
0answers
33 views

How to display data from the object

When I use SELECT to display the table, in the column the ID and Subject column is displayed with content, but in the date column only SYSTEM.DATAS is displayed create or replace type dataS as object ...
0
votes
2answers
77 views

record linking to each other in PL/SQL Developer

There was a need to create records in PL / SQL Developer, which refer to each other. I started to understand all this recently, therefore there were doubts about the achievement of this goal, but also ...
0
votes
2answers
57 views

How to rectify “PLS-00302: component must be declared” error

I am trying to compile below code but I am getting an error as: "PLS-00302: component 'DEPT_ID' must be declared". DECLARE DEPT_ID DEPARTMENTS.DID%TYPE; DEPT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE; ...
0
votes
1answer
47 views

How to pass a parameter once for multiple select

I am currently trying to declare a parameter with which I can use it in several select, when I try to launch the current query it puts me several errors in particular : PLS-00103: Encountered the ...
0
votes
1answer
29 views

Procedure as an argument in PL/SQL

I want to execute a procedure which takes another procedure as an argument which has some other arguments or parameters. E.g. ProcA(Proc_B(Name Varchar2, ID Varchar2)) Is this possible? If,so, ...
0
votes
1answer
46 views

Encrypt 3DES on oracle, decrypt doesn't work on DB2

I need to encrypt data on oracle and decrypt on DB2. I can use only DBMS_OBFUSCATION_TOOLKIT on oracle. set serveroutput on; DECLARE L_TEXT VARCHAR2(16) := '1234567890123456'; U_ID ...
2
votes
2answers
54 views

Materialized view Vs Temp tables in Oracle

I have a base transaction table. Then I have around 15 intermediate steps, where I'm combining dimension tables, performing some aggregation and implementing business logic. The way I'm handling ...
0
votes
1answer
22 views

PL/SQL Query to get the total of both two columns in a subquery

I have a two subquery below to get the total of same column. Now, I want is to get the total of the two subquery I used. How can I do that? Subquery1 (select sum(aila.total_rec_tax_amount) from ...
1
vote
1answer
34 views

Generate .txt file and insert it into an oracle table pl / sql

I have a procedure that receives the contents of a file in a single variable and I divide it into lines and divide the fields of each line delimited by commas, all that process I do it correctly but ...
0
votes
1answer
32 views

Is there a way to execute robocopy cmd from a Stored Procedure in PL/SQL?

I have to move certain files from a local directory(which is fixed) into another server. I can use robocopy from Windows cmd line to do the same. Is there a way in which i can create a job which ...