Stack Overflow
Stack Overflow

Questions tagged [plsql-package]

A PL/SQL package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use this tag for questions referring to the PL/SQL packages usage and behaviour.

240 questions
0
votes
3 answers

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). So I've already tried with following…
Gopal00005
  • 2,061
  • 4
  • 35
  • 54
0
votes
1 answer

Where are package level variables stored in oracle? Any table?

The variables that we declare at the package level, which can be used within the package, where are these stored when the package is being executed. Is their any table where we can check? Checked a few websites including the oracle documentation,…
sach0888
  • 1
  • 1
0
votes
2 answers

Need help to create purging operation executing in loop

I am going to run one pl/sql block which deletes all the data which is older than 30 days in a table. Condition is like this: It will delete 200k data at a time and after deleting 200k data I have to give wait period for 10-15 sec. There are around…
nodev_101
  • 99
  • 10
0
votes
1 answer

What does it means when there are two functions with the same name but different parameter in the package body?

In the package spec, the function name only appears once therefore it's not overloaded. In the package body, the same function name appears twice using different set of parameters. One of them has the same parameter as the one in the package spec.…
John Al
  • 159
  • 1
  • 11
0
votes
1 answer

Passing Variable in Sum Function in PLSQL

This is the simple code to just calculate the sum of a column in a table. However, the column name is dynamic each time it is changed. for that, I tried to do the following code but it is not working when passing the variable in the function, but if…
Waleed Raza
  • 27
  • 6
0
votes
1 answer

Many to Many Relationship table Not working cannot delete Value from that

declare begin for i in (select aid ,address from address) loop for j in (select aid ,address from address ) loop if i.address=j.address then if i.aid!=j.aid then update employee_add set aid=i.aid …
user11000599
0
votes
2 answers

How to handle exception in oracle package?

I have a oracle package consisting of many procedures. e.g. pkg( proc 1 proc 2 proc 3 ); while executing the package proc 1 gets called first and within proc 1,proc 2 is called. SO what if i face an exception in proc 2 then i want to rollback all…
lobh
  • 33
  • 7
0
votes
1 answer

29283. 00000 - "invalid file operation"

I want to load data from a CSV file, using UTL_FILE, but an error has occurred (see below), please note that I'm connecting to the database remotely and the CSV file is in my local machine. 29283. 00000 - "invalid file operation" *Cause: An…
AbdeAMNR
  • 165
  • 1
  • 5
  • 18
0
votes
0 answers

Calling Another procedure with Insert(DML commands)

Calling another procedure with insert query(same package). Procedure 2 is parametized with array as input for manipulation Approaches Tried: 1-exec CreateShipmentLines(p_result); 2-CreateShipmentLines(p_result); Compiled sucessfully but record did…
rahul bhandari
  • 1
  • 5
0
votes
1 answer

how to define a dbms_scheduler.create_job in a pl/sql package?

BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'delete_partition', job_type => 'STORED_PROCEDURE', start_date => '28-APR-08 07.00.00 PM Australia/Sydney', repeat_interval => 'FREQ=MONTHLY;INTERVAL=1', /*…
p_eazy
  • 27
  • 1
  • 6
0
votes
1 answer

Is there a way in oracle 11g to store temporary values in a table?

I have a table type input+output(IO) variable in oracle(with 5 columns), i read values from it in a loop and and also perform validations, if there's an validation fired i put an error message in fifth column. if the fifth column is null i insert…
sssophkc
  • 19
  • 3
0
votes
1 answer

Unable to parse CRLF file in oracle

I am trying to read a file which is sent by an external system to our server and load it into a oracle database table. I am using the utl_file package to read the data from the file. When I am creating the file with similar contents in the unix…
Soumik.Talukdar
  • 9
  • 3
0
votes
1 answer

PL/SQL Function Return View RowType internal exception

I have the below function which compiles. DOC_ISSUE_REFERENCE STANDS for a VIEW CREATE OR REPLACE PACKAGE BODY INHOUSE_CUST_API AS FUNCTION Get_Budget_Doc( company IN VARCHAR2, budget_process_id IN…
MuhanadY
  • 752
  • 1
  • 12
  • 40
0
votes
1 answer

How to dynamically create record in PL/Sql based on Table name as Input

I want to understand how to create the record type dynamically based on the table name received as input to the procedure. Ex: PROCEDURE xxtest(p_table_name IN VARCHAR2) IS TYPE t_test_type IS TABLE OF p_table_name%ROWTYPE; v_test_type…
Sushil
  • 1
  • 1
0
votes
1 answer

PLSQL: Assign array returned by one function into an array defined in another function

I have two functions – func1 and func2. func1 selects some values from a table and assigns it to an array and returns this array. func2 calls func1. func2 uses the array returned by func1 to perform some operations. My question is: How to assign the…
Delin Mathew
  • 249
  • 3
  • 6
  • 15
1 2 3
…
15 16