Below is the code I am facing problem using tablename as variable.
I have five tavble is scheme Emp1,Emp2..Emp5
CREATE OR REPLACE
procedure emp_up as
tablename1 varchar2(30) ;
Begin
For x in 1..5
LOOP
tablename1 := 'EMP' ||to_char(x);
EXECUTE IMMEDIATE 'update '||tablename1 || 'set ename = ''ZZZZZ'' where ename in (''MILLER'')';
END LOOP;
End;
Error : Identifier EMP must be declare
I need a helo to pivot table with variable columns, I have a pivot table :
SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null) FOR dia IN ('20130805' ,'20130812','20130819','20130826'))
a but I need to run the select with values for dia , getting from a other table :
SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null) FOR dia IN (select dia from v_dia_mes ))
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..
Following is the example :
Declare
test_tab varchar2(10) := 'tab_test';
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || :a || ' (col1 NUMBER)';
dbms_output.put_line(sql_stm);
EXECUTE IMMEDIATE sql_stm
using test_tab;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;
After running above block it is giving error : ORA-01008: not all variables bound.
im as using oracle 8 with sqltools 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.
consider the trigger below,
CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG
AFTER UPDATE
ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS
[code].....
The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?
what is use of cursor variables?
View 1 Replies View RelatedThe following code is getting the 'Not all Variables bound' error. There are only two variables so I don't see the order being an issue. Assume the integers are assigned elsewhere.
DateTime beginDT = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt);
DateTime endDT = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);
SQL.Append(" WHERE DATE >= :beginDTParameter ");
SQL.Append("AND DATE < :endDTParameter");
OracleCommand cmd = connection.CreateCommand();
cmd.Parameters.Add(new OracleParameter("beginDTParameter", OracleType.DateTime)).Value = beginDT;
cmd.Parameters.Add(new OracleParameter("endDTParameter", OracleType.DateTime)).Value = endDT;
Explain about Global variables in Plsql? What is use of these global variables and where do use these variables?
View 1 Replies View RelatedI was new to Oracle. When i am Executing this Query, I was getting the error "ORA - 010008 - not all variables bound" .
VARIABLE sathya NUMBER
BEGIN
SELECT sal INTO :sathya FROM emp WHERE empno=7902;
END;
A way to identify variables declared in a procedure that are not being used? I had thought plsql_warnings might do the trick but it does not.
I have inherited a lot of code that was ill conceived and unfortunately have started to notice a trend.
HOW i can use the Substitution Variables in select statement in From phrase
we need execute select statement in different users in run time.
I have written follwoing code in form.
Declare
V_Empno Number:=51469;
Begin
Insert Into Emp(EmpNo,Ename)
[Code]....
When I run Above Code Then Error Is ORA-01008 Not all variables bound. Then I changed above code with following.
Declare
V_Empno Number:=51469;
Begin
[Code]....
Above code is working. but i am unable to understand why Dup_Val_On_Index is generating error ora-01008.
i have some confusion with bind and host variable.
View 4 Replies View RelatedPROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2,
P_Txn_Ccy IN VARCHAR2,
P_Allocation IN VARCHAR2,
l_lty_id IN VARCHAR2 ) IS
l_balance_fmt NUMBER := 0;
BEGIN
[code]....
it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..
how to nullify variables in a procedure in pl/sql
View 1 Replies View RelatedI have written a small code while going through the PL/SQL guide but I got a message for the BIND VARIABLE. I don't think I have used any bind variable in this code.
<<outer>>
declare
v_sal1 number(7,2) := 60000;
v_comm number(7,2) : v_sal1 * 0.20;
v_message varchar2(2000) := 'eligible for commission';
begin
[code]........
Create and invoke the GET_JOB function to return a job title.
a.Create and compile a function called GET_JOB to return a job title.
b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION:
CREATE OR REPLACE
FUNCTION GET_JOB(
p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35)
set serveroutput on
DECLARE
P_JOBID VARCHAR2(10);
v_jobtitle VARCHAR2(200);
[code]...
I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:
declare
START_dt VARCHAR2(30);
END_DT VARCHAR2(30);
begin
SELECT '01-APR-2011'
INTO END_DT
FROM DUAL;
[code]....
The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.
I was given a SQL query to develop another query that will need to do something similar. The query I was given I believe is a stored procedure. It is PL/SQL and has a SELECT statement that looks like
SELECT :var1 AS var1
FROM t1
WHERE
:var1 = 1
In the past I have seen where variables can be used in the WHERE clause like
SELECT c1
FROM t1
WHERE
c1 = :my_column_valueand this makes sense to me.
Usually :my_column_value is declared in the DECLARE block of the PL/SQL. In the query I was given, :my_column_value is not declared in any DECLARE blocks (maybe I wasn't given the entire code??). But even if it was, that would be even more confusing because in the DECLARE block it could be assigned one value and then set to another value in the WHERE clause???
The first query doesn't make sense to me and when I run it as plain SQL I get an error like "SP2-0552: Bind variable "var1" not declared. I am guessing that such a syntax is not valid for regular SQL?
The only thing I can gather that the first query does is something along the lines of
SELECT 1 as var1
so it will output a 1 for every row. Not always that interesting of a query, but it is needed sometimes, I know. But I still don't understand this type of assignment.
So what's going on here? I'd be happy to look it up and read about it myself, but I don't know what to search for.
I am running this query and getting this exception ORA-01008 All variables are not bound .
SELECT EQMT_INGT_LOG_ID, EQMT_ID,
XMLSerialize(DOCUMENT XMLType(ingLog.BUCK_SLIP_XML) AS CLOB) BUCK_SLIP_XML
FROM TOS_EQMT_INGT_LOG ingLog
where BUCK_SLIP_XML is not null and ingt_date between to_date(:fromDate, 'MM/DD/YYYY HH24:MI')
[code]...
I have a SELECT INTO statement as follows:
-----
SELECT SUM(s1.PRODUCT_QTY) INTO anz
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID
AND s2.REGION = 'ANZ'
AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
[Code]..
The statement sums up all the products attributed to the region ANZ and stores it in a NUMBER variable called anz.
The issue is that I have multiple regions: ANZ, JP, ASEAN etc. Wat I would do is to create a statement for each and every region. Is there a way to consolidate them all into a single statement. that means something like
-----
SELECT SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY) INTO anz, jp, asean
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE ..................
-----
set server output on;
DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
WHILE num < 5 LOOP
[code]...........
the output for this loop is:
1+1=2
2+2=4
3+3=6
4+4=8
this is what I wanted. Both variables are incrementing together. However, when I try to use a FOR loop to do the same thing:
set serveroutput on;
DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
FOR i in 1..4 LOOP
[code].......
I get the result:
1+1=2
1+2=3
1+3=4
1+4=5
2+5=7
2+6=8
2+7=9
2+8=10
3+9=12
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 8
I understand the output means the inner loop is running its course before the outer loop is started and then continuing on to exceed its limit, but I don't understand why this is or how to make it so I get the same results as the WHILE loops.
We have an application, where we invoke a URL pointing to a folder on the Application Server, so that users can download files from the same.
There is user authentication provided for the URL.
Example :
Our application ABC (example) which is built with Oracle Forms 10g, hosted on Application server (itxxx123) inovkes a module GEN9999 which on pressing a button calls a URL which is open in the Internet Explorer browser.
If the URL has been called from the Forms or the Application (ABC), then it should be allowed.
If the URL is called standalone, by Opening IE Browser and invoking the URL, this should not be allowed.
However, there have been security concerns raised during Audit, saying that users can access the URL directly from the Browser instead of the Application. Also the URL is saved the Browser history, so even if the user is no longer working in the Application Team, and has moved out of the team, then the ex-user can still access the URL and view and download files.
Is there any way to restrict access to the URL such that is only accessible when it is called from the Application, and that access is restricted when directly called from the Browser.
I have seen that environment variables in httpd.conf may be usable for the above. However, not sure how to use them from Oracle Forms.
is it possible to create tables using variables in pl/sql such as the following example which is actually in php. i would like to create a table dynamically.
$create_samp_table = CREATE TABLE $MCSCHEMA.".$samp_id." AS SELECT * FROM $MCSCHEMA.".$samp_src_table." WHERE ROWNUM = 0
I have a requirement where I have to return data as per filter clauses provided on the front end, which may or may not be filled as per the users' choice.
To create a test case, I have created a query joining the emp and dept tables and I add the where clauses dynamically depending on whether the filter has been provided or not.
CREATE OR REPLACE TYPE emp_ot AS OBJECT (
empno NUMBER(4),
ename VARCHAR2(10),
[Code]....
It works very well, the 'literals' are being converted into bind variables (due to CURSOR_SHARING being set to SIMILAR) and the optimizer is able to figure out the correct execution path every time, although it is true that potentially 5 versions of this query will be sitting in the shared pool, but the DBAs are happy to live with that.
Going forward in version 12, CURSOR_SHARING=SIMILAR will be deprecated and the DBAs are not happy for us to write this sort of code anymore.
Is there a suitable way to achieve what this piece of code does?
I have tried and tested this method:
SELECT emp.empno, emp.ename, emp.job, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.empno = NVL(p_empno, emp.empno)
AND emp.ename LIKE NVL(p_ename, emp.ename)||'%'
...
but the query takes far longer to execute in this manner (that is using my real tables).
Having trouble with my variables in this staement.
SELECT WP_USERS.F_NAME || ', ' || WP_USERS.L_NAME AS NAME, WP_PROJECTS.TITLE, WP_PROJECTS.CLIENT_MANAGER,
WP_PROJECTS.PERSON_DAYS
FROM WP_PROJECTS, WP_PROJECTS_USERS, WP_USERS
WHERE WP_PROJECTS.PROJECT_ID = WP_PROJECTS_USERS.PROJECT_ID AND WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND
WP_PROJECTS_USERS.USER_ID = WP_USERS.USER_ID AND (WP_PROJECTS.SITE = "@SITE") AND (WP_PROJECTS."SECTION" = '@SECTION')
I am having some issues in creating the following trigger. It gets created but with compilation errors.
create or replace trigger min_max_update_trg
after insert on tidlrrep
referencing new as n
for each row
declare
bltMaxSize number;
repMaxSize number;
[code].....
when i tried without the comparison if repMaxSize < bltMaxSize then it got created successfully.
I have taken some procedures code from SVN repository.These procedures are split into different schema. There are some client and common parameters used in the from clause of table in these sp.for example
select *
from client.emp;
during the compling procedure, we replace the client value into some schema name.
select *
from scott.emp;
The client and common parameter should be different for every schema.
Is any possible to create global varaible for client and common parameter and subsitute value in it.i.e without editing client and common parameter?
How I go about writing a query that doesn't see the '&' symbol as a substitution character. The problem I have is:
select replace
( fnd_profile.value( 'XXBPO_URL')
, '&DOCID'
, ai_dfv.bpo__document_link
) document_link
from ap_invoices_all ai
, ap_invoices_all_dfv ai_dfv
where 1=1
-- where clause
The problem above is that instead of replacing the characters &DOCID with the required value, it is prompting for input.