How many OUT parameters can be declared in function header ?
A) None
B) Any Number
C) One for every return declared in the definition
D) Depends on what the RETURN datatype is
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View RelatedAPEX 4.1.1.00.23/Oracle 11g
When attempting to use apex_web_service.make_rest_request, I found that it doesnt work with one of my web services because the service requires header parameters. The wrapper apex_web_service.make_rest_request is missing the two header parameters (p_http_headers, p_http_hdr_values) that are in the internal package wwv_flow_web_services.make_rest_request. I have tried passing the values into the two available p_parm_name and p_parm_value parameters, but it doesnt work. They have to be in the header.
It does work within APEX application when it allows me to add a header parm, but I am trying to schedule it nightly as a job from the DB.
Will it hurt anything for me to use the wwv_flow_web_services.make_rest_request?
i have a function which takes in two variables and return a varchar.
ex: Function(var1,var2) return as varchar2.
in the function,i query a table for var1 and var2 and concatenate the result set to return a varchar. But if either var1 or var2 is null,then my query in the function fetches the result set for the other variable.
My question is,how would i pass a null value through the function and handle it in the function.
The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.
The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?
I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.
I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.
The code for the procedure is
CREATE OR REPLACE PROCEDURE add_new_classes
(p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number
p_course_id classes.course_id%TYPE,
p_period classes.period%TYPE,
p_frequency classes.frequency%TYPE,
[code]....
I then use this to test it
BEGIN
add_new_classes(1002,'first','daily',3002);
END;
and the error I get is
Quote:ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
1. BEGIN
2. add_new_classes(1002,'first','daily',3002);
3. END;
When I replace the params manualy with dates in format of:
'27-feb-2011 08:00:00'
I get the real output.
But when I call the function with the TEST button in the PLSQL I don't get any output (Empty table).
Here is the original function (Bad output) and attaching a file with manuly added dates (Good output):
FUNCTION WhatRoomsTaken(dStartTime date, dEndTime date, dEventDate date)
RETURN Genrefcursor IS
Retval Genrefcursor;
BEGIN
open Retval for
[code].....
Why do I need to change in order to get the good output by calling the function with params?
what are the parameters expected for app_window.set_window_position() function?
View 4 Replies View RelatedI have an issue with rather complicated function.Basically it is using DBMS_SQL to execute a very long statement with many parameters (~6000 of them) and binding them with DBMS_SQL.BIND_VARIABLE. Variables are called :1,:2,...,:6000.
When this arguments set is too large - I am receiving error "ORA-00939: too many arguments for function".
Currently I am thinking about dividing the query into subqueries and executing them all with performance decrease.
I am trying to execute the below and getting the error:
PLS-00201: identifier 'DBMS.REFRESH' must be declared
The mv is in my schema.. so I am owner. I thought I once read that a dbms.refresh had to be in a block.. so I added the begin and end.. but that didn't resolve it.
I have tried two ways, both resulting in the above error:
execute dbms.refresh('mv_bb_basket');
begin
dbms.refresh('mv_bb_basket');
end;
I'm getting above error when I run following code
BEGIN
OPEN c_report_data;
FOR rec in c_report_data
LOOP
IF
rec.ESR_SALESMAN <> lv_rep_num THEN
ln_ttl_rep_cnt := ln_ttl_rep_count + 1 ;
ELSE
ln_ttl_rep_cnt := ln_ttl_rep_cnt;
END IF;
I am facing the same problem: SP2-0552: Bind variable "OLD" not declared. When my script create_trigger.sql is executed,there is no error but when i execute it inside a pl/sql block it get above error...In the trigger we are using if conditions
if(:new.sumthing=1)and (:old.sumthing=0)the
do this..
I need export data from remote server i.e. in Oracle8i envtt. but in my machine having client oracle10g.
I m using command from local machine :
expdp test/test@test_env_tns dumpfile=abcd_dat.dat logfile='abcd.log
then i m getting error :
UDE-00008: operation generated ORACLE error 6550
ORA-06550: line 1, column 52:
PLS-00201: identifier 'SYS.DBMS_DATAPUMP' must be declared
ORA-06550: line 1, column 52:
PL/SQL: Statement ignored
[/code]
Note : I m executing command from my local machine command prompt
I am using oracle 10g database (10.2.0.1.0), which trouugh an error while i am trying to EXPORT.
View 4 Replies View Relatedhere is what i am trying to do: im as using oracle 8 with sqltool
i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;
but could not get it to work.
I am importing excel data to oracle table by column mapping using forms 6i. I am using ole2 package and i also created one package.which is given below. My code gets compiled, I am unable to insert record to the table.Values are passed to the procedure, i am able to see the values while debugging, but all values are seen in record 1 (as looping is there) all other remaining records are empty.i.e records are changing but seen only in record 1 and all other records are empty.I also get message 'DATA INSERTED INTO THE TABLE' but when i check it with sql, the table is empty.
PROCEDURE get_excel IS
APPLICATION OLE2.OBJ_TYPE;
WORKBOOKS OLE2.OBJ_TYPE;
WORKBOOK OLE2.OBJ_TYPE;
WORKSHEETS OLE2.OBJ_TYPE;
[code]...
I'm trying to create trigger:
CREATE OR REPLACE TRIGGER TBI_ID
BEFORE
INSERT
ON zoo.risk_eval#ctypein
[code]...
Here is error message:
ORA-24344: success with compilation error
2/14 PLS-00201: identifier 'ZOO.RISK_EVAL_CTYPEIN#ID_SEQ' must be declared
2/3 PL/SQL: Statement ignored
I am trying to create a simple trigger for practice. I am getting SP2-0552: Bind variable "NEW" not declared. error.
Below is the trigger script:
create or replace User_Cxt_Trigger
AFTER
INSERT or UPDATE OR DELETE ON NAME_VALUE_PREFS
referencing new as new old as old
FOR EACH ROW
[code]....
i m getting this DBMS_LOCK error, i have DBA privilege
Declare
l_count integer := 0;
l_count_t integer := 0;
error_msg varchar2(4000);
begin
select count(*) into l_count from user_objects where object_name='PAKAGE_NAME' and object_type='PACKAGE';
if l_count=0 then
DBMS_OUTPUT.PUT_LINE('ERROR: Package PAKAGE_NAME does not exist in the database');
[code].....
ERROR: Package PAKAGE_NAME or its body is in INVALID state
PLS-00201: identifier 'DBMS_LOCK' must be declared
PL/SQL procedure successfully completed.
I am getting following error
SQL> BEGIN
2
3 DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
4
5 gname => 'ORCL_REP',
[code]...
ERROR at line 1:
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_REPCAT_UTL2@ORCL.BABU' must be declared
ORA-06550: line 1, column 8:
[code]...
I inherited the management of an Oracle 10.2.0.3 database Standard Edition on a 32 bit Microsoft Windows Server 2003 R2 platform with some invalided objects related to SYSMAN schema.
I suppose that probably these invalid objects are derived from a bad **previous upgrading operation*(probably from 10.2.0.1 to 10.2.0.3).Furthermore I looked for a solution on the net and I found that this trouble could be issued dropping and recreating again the Database Control Repository using emca plus specific commands. [URL]On Windows Systems I followed these commands on my test database and the repository was successfully created and all the previous invalidated objects where corrected.
However there is still a view not functioning. I tried to compile it again after the creation of the new Database Control Repository with the command alter view <viewname> compile but it returns the following errors:
Progetto: sqldev.temp:/IdeConnections%23jhoray01XDB.jpr
Errore(7,3): PL/SQL: Statement ignored
Errore(7,19): PLS-00201: identifier 'DBMS_CRYPTO' must be declared
I am trying to create a report and seem to be struggling when trying to declare the columns 'DISK_READS' and 'CPU_TIME' within the statement.
Statement:
set serveroutput on
declare
cursor sqltext_cur is
select sql_id,child_number,sql_text, elapsed_time
from (select sql_id,child_number,sql_text,elapsed_time, cpu_time,
disk_reads,
RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
[code]....
Error:
ERROR at line 18:
ORA-06550: line 18, column 51:
PLS-00302: component 'CPU_TIME' must be declared
ORA-06550: line 18, column 3:
PL/SQL: Statement ignored
ORA-06550: line 19, column 53:
PLS-00302: component 'DISK_READS' must be declared
ORA-06550: line 19, column 3:
PL/SQL: Statement ignored
Why exceptions does not raise in cursors declared by user like in the following program
create or replace function sal (p_id employees.department_id%Type) return number is
----sal1 employees.salary%type:=0;
cursor cx is
select * from employees where department_id=p_id;
begin
for i in cx
[code]...
i just what to understand the differnce between the variable declared under the Begin and variable declared under Declared. to understand this i tried a sample plsql procedure as show below
create or replace
PACKAGE BODY TEST_DEV AS
PROCEDURE TESTING(I_NAME VARCHAR2,
I_AGE NUMBER) AS
INTEGE binary_integer := 100;
[Code] .......
the Line L_nums1 NUMBER := 100/0; is throwing error(PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:)
if i remove the line L_nums1 NUMBER := 100/0; and just declare the variable out side the begin , and using inside the same variable inside the begin works fine
Why question is why we are not able to decalre, initialize and use the same Number variable insde the begin
Getting error when using record type as in parameter.
PLS-00306: wrong number or types of arguments in call to 'SAL_UPDATE_PROC'
PLS-00302: component 'ENAME' must be declared
PLS-00302: component 'SAL' must be declared
CREATE OR REPLACE PACKAGE emp_details_proc
[code]....
I am not getting any error.
We have a package that contains mostly constants like:
c_flag_true CONSTANT CHAR(1) := 'Y'
When I try to reference it in these constants in an adhoc query it doesn't work:
SQL> select * from FINANCIAL_BATCH_STATUS_HISTORY where active_flag = constants_pkg.c_flag_true;
select * from FINANCIAL_BATCH_STATUS_HISTORY where active_flag = constants_pkg.c_flag_true
ORA-06553: PLS-221: 'C_FLAG_TRUE' is not a procedure or is undefined
Is there a simple solution to make the reference work?
i prepare database server and when i import my dump file from old database it import normal but before it end it give me these errors and these errors it seems that it in loop
IMP-00017: following statement failed with ORACLE error 6550:
"BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_IMP_SKIP_INDEXES_OFF; END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 12:
PLS-00302: component 'DBMS_EXPORT_EXTENSION' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
db: 11g
I have import dumpfile for schema refresh but when import completes it made many packages and procedures invalid....
Package compilation error:
pl/sql: Declaration ignore
PLS-00201: identifier 'schema_T_Dump.em_id' must be declared..
I have tried all sort of compilation things but still packages are invalid..I think type is missing in target database from source...
I'm trying to sort out my coloumn headers and make things a bit neater using the code....
column name format A9 heading 'Emp|Name'
column birthdate format A9 heading 'Birth|Date'
select name, birthdate
from Employee
where ....;
It seems to display all the results in double space if i use more than one 'column' statement though? One statement works fine, what've i gotta change?
I have a requirement like this, There is a temporary table with one number column. This column would be updated in between pages trigger. In the header I want to display the value from this temporary table on each page. I tried this by putting a frame and displaying the values, but it displays same value for all the column.
The table is properly getting updated in between pages trigger. I just want to know how to print the refreshed value in the header.