SQL & PL/SQL :: Create Table Using Bind Variables In EXECUTE IMMEDIATE

Feb 22, 2010

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.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Bind Variables - Create Or Replace Procedure

Jun 12, 2012

create or replace procedure my_proc(p_user in varchar2) is
l_cursor sys_refcursor;
l_query constant varchar2(1000) :=
'select a'
|| 'from ' || p_user || '.user_table'
|| 'where param0 = :x'
|| 'and param1 = :x'
|| 'and param2 = :x'

[Code]...

Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?

View 6 Replies View Related

SQL & PL/SQL :: Local Variables Are Bind Variables

Apr 27, 2012

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;

View 8 Replies View Related

SQL & PL/SQL :: Bind And Host Variables

Dec 22, 2011

i have some confusion with bind and host variable.

View 4 Replies View Related

SQL & PL/SQL :: Bind Variables Message

Sep 2, 2011

I 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]........

View 8 Replies View Related

SQL & PL/SQL :: FUNCTIONS - Bind Variables?

Nov 20, 2012

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]...

View 5 Replies View Related

SQL & PL/SQL :: Dynamic Where Clauses And Bind Variables?

Jun 6, 2012

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).

View 5 Replies View Related

SQL & PL/SQL :: How Many Maximum Number Of Bind Variables

Mar 10, 2010

How many maximum number of bind variables,can we use in Execute Immediate.

View 2 Replies View Related

SQL & PL/SQL :: Bind Variables / Unable To Fetch Value

Sep 8, 2011

CODE
Select
Nvl(Sum(DbAmt),0), Nvl(Sum(CrAmt),0)
From FnTrantt A
Where A.GrpCode=:1 And
A.CmpCode=:2 And';
A.DiviCode=:3 And';
A.SubDiVCd=:4 And';
A.FinYear>=''0001'' And';
A.VchDate Between :5 And :6' And
A.GlCode In :7;
[code]....

In The Above mentioned Code, am using bind variables, In variable no. 7 in passing character string ('05124','05125')I am not able to fetch the value,

View 3 Replies View Related

PL/SQL :: Bind Variables And Expression Evaluation

Aug 29, 2013

i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem: 

select egp.bsn,egp.klantnummer as "Persoonsnummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from   pr_eigenschappen2      egpwhere(egp.bsn = :b1 or :b2 is null)and rownum < 51 egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics. when run with b1:=928818 and b2:=928818  (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2. if the query is changed to select egp.bsn,egp.klantnummer  as "Persoons nummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from   pr_eigenschappen2      egpwhere(egp.bsn   = :b1 or 928818 is null)

and rownum < 51the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.

View 23 Replies View Related

Precompilers, OCI & OCCI :: Bind Variables API Obndrv()?

Apr 21, 2009

can i have some real time code piece for bind variables steps and obndrv(...)

View 1 Replies View Related

Explain Plan Differences With Or Without Nvl On Bind Variables?

Jul 1, 2013

We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --

Just bind variable --select *from   MTL_MATERIAL_TRANSACTIONS mmtwhere  1 = 1and    mmt.INVENTORY_ITEM_ID = :p1and    mmt.ORGANIZATION_ID   = :p2and    mmt.TRANSACTION_REFERENCE = :p3--and    mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from   MTL_MATERIAL_TRANSACTIONS mmtwhere  1 = 1and    mmt.INVENTORY_ITEM_ID = :p1and   

[code]...

View 3 Replies View Related

Application Express :: Collection In 4.1 Using Bind Variables?

Oct 4, 2012

I am trying to create a collection using bind variables in APEX 4.1. I have the following procedure but all I get is an error in the debug page DOH ORA-20104:

create_collection_from_query_b Error:ORA-01006: bind variable does not exist

My code is

DECLARE
l_names wwv_flow_global.vc_arr2;
l_values wwv_flow_global.vc_arr2;
l_names(1) := 'EXPENSE_FROM';
l_names(2) := 'EXPENSE_TO';

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Bind Variables / Developing Stored Procedures In Developer

May 16, 2010

Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.

The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:

SELECT em.event_title AS event_name,
e.start_date AS start_date,
e.end_date AS end_date
FROM d_event_ml em
inner join d_event e
ON em.event_id = e.event_id
WHERE em.language_id = 46
AND e.end_date >= SYSDATE
AND e.stream_id IN ( :v_x1 )
AND e.event_id IN (SELECT event_id
FROM d_events_seas
[code]....

and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....

View 7 Replies View Related

Client Tools :: Providing Bind Variables As Values In Insert Statement?

Aug 23, 2011

I executed the following PL/SQL block in SqlDeveloper :

VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/

And it gave the error : Quote:Error report:

ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"

The same code when executed in iSqlPlus gave no error.

View 13 Replies View Related

PL/SQL :: Create Temporary Table Using Execute Immediate

Jun 11, 2012

How can i create a temporary table using EXECUTE IMMEDIATE ??

Like:

CREATE GLOBAL TEMPORARY table new_table as (Select * from old_table);

View 11 Replies View Related

Oracle - Create Tables Using Variables In PL/SQL?

Feb 3, 2010

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

View 1 Replies View Related

SQL & PL/SQL :: Create A Procedure Using EXECUTE IMMEDIATE?

Apr 14, 2013

I am trying to create a procedure using the EXECUTE IMMEDIATE. I have been having problems calling it from an anonymous block

My code

CREATE OR REPLACE PROCEDURE homework
(p_table_name VARCHAR2)
IS
v_department_id departments.department_id%TYPE;
BEGIN
EXECUTE IMMEDIATE

[code]....

I called the procedure from an anonymous block

BEGIN
homework('Employees');
END;

It gives me an error

ORA-00905: missing keyword

View 3 Replies View Related

PL/SQL :: Create Sequence Using EXECUTE IMMEDIATE Statement?

Dec 14, 2012

Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.

DECLARE
TEMP   VARCHAR2(20);
BEGIN
TEMP     := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;

This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.

View 10 Replies View Related

SQL & PL/SQL :: Trigger To Execute Immediate Create Or Replace View?

Jan 30, 2013

i try to make trigger to execute immediate create or replace view

(
CREATE OR REPLACE TRIGGER xxxx
BEFORE INSERT ON table1
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW
declare
l number ;
v_ddl varchar2(4000);
v_job number;
[code]....

it give me error for insuffition privilage in execute immediate and after i make GRANT CREATE ANY TRIGGER TO user give me error can't commit in trigger.

View 8 Replies View Related

PL/SQL :: Can't Compile And Execute Function (Create Or Replace)

Jun 6, 2013

I cant compile & execute this function.

create or replace
FUNCTION get_branding_testing
RETURN r_brand
IS
BEGIN
CURSOR c1
IS
SELECT b.branding_code, c.name_desc     
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Bind Variable - Invalid Table Name

Jul 13, 2010

create table t (
col1 varchar2(2),
col2 varchar2(1),
tab_name varchar2(50));

[Code]....

In this case :v will be replaced with t1. I got the error invalid table name.

where table t1 has its own structure.

View 5 Replies View Related

Execute Create Procedure / Function From SQL Script In SQLPlus

Feb 17, 2010

I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.

Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...

I am using the following command:

sqlplus u/p@<someserver> @<path_to_sqlfile>.sql

this sql file contains create procedure pl/sql code

Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?

View 8 Replies View Related

PL/SQL :: Scheduling - Create Oracle Job That Will Execute Shell Script?

Jul 2, 2012

I need to create an oracle job that will execute a shell script. i need to run it only when I call it(so not to be scheduled).

View 5 Replies View Related

SQL & PL/SQL :: Using Table Name As Variables?

Aug 23, 2010

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

View 15 Replies View Related

Create Trigger To Execute A Stored Procedure During Source Code Compile?

Nov 9, 2009

I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.

View 1 Replies View Related

PL/SQL :: Pivot Table With Variables Columns

Aug 27, 2013

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 ))

View 3 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

SQL & PL/SQL :: Block To Select 2 Rows From Table And Execute Them

Aug 3, 2010

I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC

The contents of rows in Table above are:

select * from emp;
select * from dept;

Can I write a PLSQL block that can select these 2 rows from the table and execute them?

View 9 Replies View Related

SQL & PL/SQL :: Execute Dynamic Query Stored In Table?

May 27, 2011

I want to execute a dynamic query which is stored in a Table.

Output of that query should be stored in database server.

Is there any way i can create a dynamic procedure? I have created a sample code but issue is i cannot make the below data type dynamic as per the query.

en com_fund_info_m%ROWTYPE;
CREATE TABLE TEMP (SQLSTATEMENT VARCHAR2(100))
DECLARE
TYPE r_cursor IS REF CURSOR;
c_emp r_cursor;
en com_fund_info_m%ROWTYPE;

[code].....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved