SQL & PL/SQL :: How To Use DBMS_SQL Package
Apr 27, 2010
How to use DBMS_SQL package. I tried with the following procedure.
CREATE OR replace PROCEDURE Crt_tab_inst(tab_name VARCHAR2,
col1_name VARCHAR2,
col1_value VARCHAR2)
IS
cur BINARY_INTEGER := dbms_sql.open_cursor;
fdbk BINARY_INTEGER;
[code]........
But when Iam executing the procedure, it is throwing the below error. This is the error Iam getting:
SQL> EXEC crt_tab_inst('MYTAB','MYCOL','NAME1');
BEGIN crt_tab_inst('MYTAB','MYCOL','NAME1'); END;
*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SCOTT.CRT_TAB_INST", line 21
ORA-06512: at line 1
View 10 Replies
ADVERTISEMENT
Nov 14, 2011
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
001 SET SERVEROUTPUT ON
002 DECLARE
003 db_link_varVARCHAR2(30);
004 source_cursorINTEGER;
005 destination_cursorINTEGER;
006 src_csrINTEGER;
007 dst_csrINTEGER;
008 rundate_varDATE;
009 instance_name_var VARCHAR2(30);
[code]....
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
View 8 Replies
View Related
Aug 13, 2012
Let me the difference between the DBMS_SQL and Execute immediate .
*) Which one is faster ?
*) Where to use DBMS_SQL and Execute immediate (correct usage of both)?
View 4 Replies
View Related
Aug 2, 2011
is it possible to execute large(clob) dynamic sql by DBMS_SQL .Is there any restriction like length ...
View 4 Replies
View Related
Jul 4, 2012
I am using dbms_sql for creation and execution of dynamic query. But after execution,the results returned are not correct. Just wanted to know is there some way by which I can find the final text of dynamic query created by dbms_sql?
View 14 Replies
View Related
May 28, 2013
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').
What I would need is something like $$PLSQL_UNIT
View 8 Replies
View Related
Jan 15, 2012
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:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
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?
View 4 Replies
View Related
Oct 23, 2013
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746
po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
View 14 Replies
View Related
Mar 31, 2004
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies
View Related
Jan 12, 2011
Why Package has to be created as two parts
1) Package spec
2) Package Body ?
Quote:PLS-00304: cannot compile body of '<PACKAGE_NAME>' without its specification
I learned it as a Thumb of rule! But IF there is any rule for it give that URL/link for that.
View 25 Replies
View Related
Mar 31, 2011
is it possible that two different package can contain same procedure?
View 1 Replies
View Related
Oct 20, 2010
Trying to accomplish:
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR
-------- ----------------------------------------------------------------
. ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
View 39 Replies
View Related
Jul 17, 2012
In package specification, there 3 procedures But in package body, there are 2 procedures...will this execute?
View 14 Replies
View Related
Mar 8, 2012
There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.
Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.
ORA-00600: internal error code, arguments: [kkxprpic8], [], [], [], [], [], [], []
I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?
View 6 Replies
View Related
Nov 22, 2011
We are working on a POC to build Web services from Oracle PL/SQL packages. For this we need to create a PL-SQL package having a stored procedure which will accept one input parameter and sends out data sets with multiple rows.
The type of the output parameter should be compatible with JDBC so as to publish the package as a web service. We tried using an Array and Ref cursor in PL/SQL but it didn't work with JDBC.
what type of PL/SQL output we should use here?
Our Environment : Oracle 10.2.0.3.0, Jdeveloper (to convert PL/SQL package into to Web Service) and Web Logic 11G server to deploy the services.
View 4 Replies
View Related
Apr 27, 2007
I ran the dbms_rectifier_diff.differences package successfully just to know the differences after it i droppped the all tables from the schema but now I have some funny table names exist in my schema like BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0. I want to remove it but dont know how, when I run the following command on sql prompt i get error message says 'sql command not properly ended'.
SQL>drop table BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0;
View 1 Replies
View Related
Apr 20, 2013
I have written a package including 13 procedures to insert data into a temp table but i am getting duplicate rows in dat table how to track from which procedure i am getting duplicate rows.
step 1 : procedures are inserting data using some joins into a temp table.
step 2: from temp table data will get stored into a target table where if the data already exist it will update the target table else will insert the data.(its a procedure where i used merge).
as i have duplicate data on temp table i am not able to update data on target table.
View 6 Replies
View Related
Jun 11, 2012
I have created a PL/SQL package and complied it and I got a meaasge with no errors.But I dont know how do I run the package to check whether the data has been populated in tables or not.I want to know do I need to write any code for execution to see the output.If I need to write how can I do it in TOAD.
View 6 Replies
View Related
Feb 14, 2013
I am unable to compile my package because of a lock being held on it . Let me provide the details for better clarity .
select * from dba_ddl_locks where session_id=111 and owner='RAHUL';
session_id owner name type mode_held mode_requested
111 RAHUL RAHUL_PKG BODY NULL None
111 RAHUL RAHUL_PKG Table/Procedure/Type NULL NoneThe session details of the session id 111 is as below
select sid,serial#,user#,username,command,status,process,sql_exec_start from v$session where sid=122
sid serial# user# username command status process SQL_EXEC_START
111 3558 222 RAHUL 47 KILLED 4420 13-DEC-12 02.00.00 AM
The strange this is that we have tried killing this session and the status above even shows ' 'KILLED', stilll the dba_ddl_locks is showing the lock details on the package RAHUL_PKG and we are unable to compile the package (the locking session is running since 13-Dec-2012 ) and we are unable to kill that . Also , is it like if i have executed a package that is referencing some other package then we wont be able to compile the referenced package until and unless the dependent package has completed its execution ??
View 6 Replies
View Related
Aug 30, 2007
I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure
View 2 Replies
View Related
Dec 10, 2007
in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:
Hops_Haku.veto(opnum);
But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?
Until now I've only gotten error, even after declaring the variable in the declaration part.
View 6 Replies
View Related
Mar 24, 2008
I have created one function in the package.
function : Tier_wh
package : Order_DESC
Function defined as
function TIER_WH( message in out Xorder_desc)
...
...
end;
Xorder_desc is defined as
create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);
how to execute this function which is defined in the package .
View 1 Replies
View Related
May 8, 2013
I have an Oracle Package with a procedure in which
package with a procedure in which there is a truncate partition, ALTER TABLE table_name TRUNCATE partition_name DROP STORAGE and the all is run with an EXECUTE IMMEDIATE 'alter table ...' .
The point is that the procedure in the package is started from another DB via DB Link (schema USER1) and doesn't work because of lack of privileges.
Instead, if the same procedure is started as a procedure, standalone, not in the package but from the same user (USER1) it works perfectly.
Don't understand why and which privileges must give to the user to run the procedure from inside the package.
View 3 Replies
View Related
Jun 11, 2012
How to see the error Oracle is returning?
SQL> select owner, object_name, object_type from sys.dba_objects
2 where status = 'INVALID' order by owner;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
CRMAPP
CRM
PACKAGE BODY
SQL> alter package CRMAPP.CRM compile;
Warning: Package altered with compilation errors.
SQL> show errors;
No errors.
SQL> select owner, object_name, object_type from sys.dba_objects
2 where status = 'INVALID' order by owner;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
CRMAPP
CRM
PACKAGE BODY
View 2 Replies
View Related
Apr 21, 2012
how to write the package for deletion?
View 1 Replies
View Related
May 24, 2011
I have a package which has two procedures in it.
The second of which was put in just as a test:
PROCEDURE DST_RPT_INVOICE_REPRINT(refCur OUT Dsti_Rpt_Init_Pkg.RC, param_locationid VARCHAR2,
param_companycode VARCHAR2, param_frominvdate DATE, param_toinvdate DATE, param_project VARCHAR2,
param_invtype VARCHAR2, param_printed NUMBER) AS.....
Dsti_Rpt_Init_Pkg.PRINT_OUTPUT(strSql);
END DST_RPT_INVOICE_REPRINT
All this has within is an SQL statement which is built up (using the string, 'strSql') How can I view the output of a refCur to check what the final strSql is?
View 3 Replies
View Related
May 14, 2010
can we use htp package in function?
something like
if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;
View 4 Replies
View Related
Oct 25, 2012
I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.
To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...
SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;
However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).
So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?
Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?
View 3 Replies
View Related
Mar 30, 2013
I am looking for a pl sql package which can give the following grants:
1. select_catalog_role
2 select any table
we want to run this package multiple names and each time we will give username as input during runtime.
View 1 Replies
View Related
Sep 20, 2012
what is ref cursor?
How to use ref cursor in a package or in a function?
View 7 Replies
View Related
