SQL & PL/SQL :: How To Reference Constants Declared In Package From Adhoc Query
Oct 25, 2010
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 have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT. PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER;
[code]...
This function must be moved to a package. Which additional statement must be added to the function to allow you to continue using the function in the group by the clause of a select statement?
A. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS); B. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS); C. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS); D. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY); Answer: A
----I had gone through this question in dumps,about PRAGMA_RESTRICT_REFERENCES, but i m not aware of WNDS, WNPS..or reference for this term, & how it works in above exampl
I am new to plsql, and I am working with re-creating existing stored procedures which was written like 10 years back with a lot of constant values used and hard coded values used. I am trying to give a new look to those SP and also to make it easier to understand.
would like to replace those constants which is defined into SP with "record" datatype used by plsql.
e.g AccountType CHAR(3) := 'LMN'; ValueType NUMBER := 34; Reasonsign CHAR(3) := 'er'; To
[code]...
And using this Constant_values type in places where the constants are refered.
I need to say I am an absolute NOOB when it comes to SQL.I need a script to run in TOAD that will reference a CSV file saved onto my local hard drive. I'll try and describe exactly what I need to do.
The current script which I use via TOAD on our companies READ ONLY database is this:
SELECT d.number_id, d.status_id, FROM table.number_t d WHERE d.number_id IN ('1230001', '1230002', '1230003')
This will return a result for each number that exists within the table.number table along with the status of each number i.e. active or inactive. A very basic query.
What I need to be able to do is run that query but instead of having to copy each number into TOAD manually, I need TOAD to check a .csv file of said numbers and then return the results.So I imagine the query would look something like:
SELECT d.number_id, d.status_id, FROM table.number_t d WHERE d.number_id IN (check
I need to create a bat file which include a query to run a package. I use plsql developer to develop the package. its username,password and database is user,pswd,db1 respectively. the query to run the package is "SELECT
COLUMN1 AS "LAST NAME", COLUMN2 AS "FIRST NAME", COLUMN3 AS "LOCATION"
FROM TABLE(PKG.GET_SUM('09-NOV-2010','12-NOV-2010')) "
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
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;
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 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.
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
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
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
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
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
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
